逐行扩展Tibble

Question

我的问题如下:我可以使用mutate创建新的列，例如作为现有列的组合，但是如果我需要创建新的行作为现有行的组合，该怎么办？例如，考虑

df<-structure(list(year = c(2013L, 2014L, 2015L, 2016L, 2017L, 2013L, 
2014L, 2015L, 2016L, 2017L), reporter = c("EU28", "EU28", "EU28", 
"EU28", "EU28", "UK", "UK", "UK", "UK", "UK"), partner = c("ACP", 
"ACP", "ACP", "ACP", "ACP", "ACP", "ACP", "ACP", "ACP", "ACP"
), nace = c("FDI", "FDI", "FDI", "FDI", "FDI", "FDI", "FDI", 
"FDI", "FDI", "FDI"), inward_stock = c(85483.9, 108674.6, 98536.9, 
114328.5, 174077.2, 4733.1, 5229.2, 5892.5, 7542.7, 20759), outward_stock = c(189229.3, 
223497.6, 325336.3, 301348.9, 304675.4, 38683, 46732.6, 49357.3, 
46985.6, 39748.4)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-10L))

我想为2013-2017年添加新的行，其中有一个新的记者EU27，内向和外向股票的价值是没有英国贡献的EU28的价值。

例如，在2013年，EU27的inward_stock为85484- 4733 = 80751，outward_stock为189229.-38683 = 150546。

有没有人知道如何在不经历繁琐的旋转/取消旋转的情况下实现这一点？我想总共添加5个新行

谢谢!

Answer 1

我不确定在不做旋转的情况下，您希望这有多大的动态性，但是过度适合您的示例的解决方案可能是，

new_reporter <- 'EU27'
l1 <- split(df, df$reporter)

rbind(df, data.frame(year = l1[[1]][1], 
                     reporter = new_reporter, 
                     partner = l1[[1]]$partner, 
                     nace = l1[[1]]$nace, 
                    l1[[1]][c(5:6)] - l1[[2]][c(5:6)]))

# A tibble: 15 x 6
#    year reporter partner nace  inward_stock outward_stock
#   <int> <chr>    <chr>   <chr>        <dbl>         <dbl>
# 1  2013 EU28     ACP     FDI         85484.       189229.
# 2  2014 EU28     ACP     FDI        108675.       223498.
# 3  2015 EU28     ACP     FDI         98537.       325336.
# 4  2016 EU28     ACP     FDI        114328.       301349.
# 5  2017 EU28     ACP     FDI        174077.       304675.
# 6  2013 UK       ACP     FDI          4733.        38683 
# 7  2014 UK       ACP     FDI          5229.        46733.
# 8  2015 UK       ACP     FDI          5892.        49357.
# 9  2016 UK       ACP     FDI          7543.        46986.
#10  2017 UK       ACP     FDI         20759         39748.
#11  2013 EU27     ACP     FDI         80751.       150546.
#12  2014 EU27     ACP     FDI        103445.       176765 
#13  2015 EU27     ACP     FDI         92644.       275979 
#14  2016 EU27     ACP     FDI        106786.       254363.
#15  2017 EU27     ACP     FDI        153318.       264927 

Answer 2

我有一个解决方案，使用来自dplyr的gather和spread：

df %>%
gather(type, stock, -c(year, reporter, partner, nace)) %>%
spread(reporter, stock) %>%
mutate(EU27=EU28-UK) %>%
gather(reporter, stock, -c(year, partner, nace, type)) %>%
spread(type, stock)

输出：

# A tibble: 15 x 6
    year partner nace  reporter inward_stock outward_stock
   <int> <chr>   <chr> <chr>           <dbl>         <dbl>
 1  2013 ACP     FDI   EU27           80751.       150546.
 2  2013 ACP     FDI   EU28           85484.       189229.
 3  2013 ACP     FDI   UK              4733.        38683 
 4  2014 ACP     FDI   EU27          103445.       176765 
 5  2014 ACP     FDI   EU28          108675.       223498.
 6  2014 ACP     FDI   UK              5229.        46733.
 7  2015 ACP     FDI   EU27           92644.       275979 
 8  2015 ACP     FDI   EU28           98537.       325336.
 9  2015 ACP     FDI   UK              5892.        49357.
10  2016 ACP     FDI   EU27          106786.       254363.
11  2016 ACP     FDI   EU28          114328.       301349.
12  2016 ACP     FDI   UK              7543.        46986.
13  2017 ACP     FDI   EU27          153318.       264927 
14  2017 ACP     FDI   EU28          174077.       304675.
15  2017 ACP     FDI   UK             20759         39748.

Answer 3

使用data.table包，我们可以通过以下方式来解决这个问题

library(data.table)
setorder(setDT(df), year, -reporter)
df[, .(reporter = "EU27", partner = "ACP", nace = "FDI", inward_stock = diff(inward_stock), outward_stock = diff(outward_stock)), year] %>% 
  rbind(df)

另一种选择

library(data.table)
setDT(df)
df[, -"reporter"][, c(.(reporter = "EU27"), lapply(.SD, function(x) if(is.numeric(x)) -diff(x) else unique(x))), year] %>% 
  rbind(df)