代码之家 › 专栏 › 技术社区 › ulima2_

报告变量的第一个和最后一个非缺失观察值

dplyr r

2

ulima2_ · 技术社区 · 9 年前

我想找出我在五月数据帧中所有变量的第一个和最后一个非缺失观察值的时间。

library(tidyverse)

df <- tribble(
  ~year, ~country, ~series1, ~series2, 
  #--|--|--|----
  2003, "USA", NA, 5,
  2004, "USA", NA, 6, 
  2005, "USA", NA, 7, 
  2006, "USA", 10, 8,
  2007, "USA", NA, 4,
  2008, "USA", NA, 10,
  2009, "USA", 16, 12,
  2010, "USA", 12, 8,
  2011, "USA", 12, 13,
  2012, "USA", 13, 10,
  2013, "USA", 11, 5,
  2005, "FRA", 5, NA, 
  2006, "FRA", 6, NA, 
  2007, "FRA", 5, NA, 
  2008, "FRA", 4, NA,
  2009, "FRA", 9, NA,
  2010, "FRA", 7, NA, 
  2011, "FRA", 14, NA,
  2012, "FRA", 7, 11, 
  2013, "FRA", 6, 6,
  2014, "FRA", 5, 7,
  2015, "FRA", 4, 5
)

df_stats <- df %>%
  group_by(country) %>%
  summarize(First = min(year, na.rm = TRUE),
            Last  = max(year, na.rm = TRUE))

它返回 df_stats :

--------------------------
| country | First | Last |
--------------------------
| FRA     | 2005  | 2015 |
--------------------------
| USA     | 2003  | 2013 |
--------------------------

但是我想把这个单独拿给我的 series1 series2 .

所以我想得到:

-------------------------------------------------------------------------
| country | First.series1 | Last.series1 | First.series2 | Last.series2 |
-------------------------------------------------------------------------
| FRA     | 2005          | 2015         |  2012          | 2015        |
-------------------------------------------------------------------------
| USA     | 2006          | 2013         |  2003          | 2013        |
-------------------------------------------------------------------------

3 回复 | 直到 9 年前

1

4

talat 9 年前

以下是我的做法:

df %>% 
  gather(series, value, starts_with("series")) %>% 
  filter(!is.na(value)) %>% 
  group_by(country, series) %>%
  summarize(First = min(year, na.rm = TRUE),
            Last  = max(year, na.rm = TRUE))

# A tibble: 4 x 4
# Groups:   country [?]
  country  series First  Last
    <chr>   <chr> <dbl> <dbl>
1     FRA series1  2005  2015
2     FRA series2  2012  2015
3     USA series1  2006  2013
4     USA series2  2003  2013

2

4

akrun 9 年前

这里有一个选项,使用 data.table

library(data.table)
dcast(melt(setDT(df), id.var = c('year', 'country'), na.rm = TRUE)[, 
    .(First = min(year, na.rm = TRUE), Last = max(year, na.rm = TRUE)),
   .(country, variable)], country ~variable, value.var = c("First", "Last"), sep=".")
#   country First.series1 First.series2 Last.series1 Last.series2
#1:     FRA          2005          2012         2015         2015
#2:     USA          2006          2003         2013         2013

3

Preston 9 年前

library(tidyverse)

# Put the data in a tidy format
gathered_df <- df %>%
  gather(key = series_no, value = val, series1:series2, na.rm = TRUE)

# find the first and last by country and series
sum_df <- gathered_df %>%
  group_by(series_no, country) %>%
  summarise(Last = max(year),
            First = min(year))

# make min and max into a column, then add a label
# Eg First:series2
reduced_df <- sum_df %>%
  gather(key = measurement, value = year, First:Last) %>%
  mutate(label = factor(paste(series_no, ":", measurement))) %>%
  group_by(label) %>%
  select(label, year, country)

# Put the output in a table format as you wanted
output <- reduced_df %>%
  spread(key = label, value = year)