R：如何同时传播，分组，汇总和变异 [英] R: How to spread, group_by, summarise and mutate at the same time

问题描述

我想通过年份列在下面的数据（仅在此处显示前12行）中传播此数据（code> ，返回按 CountryName分组的订单的总和） 。然后计算2014年至2015年每个 CountryName在 Orders中的变化百分比。

I want to spread this data below (first 12 rows shown here only) by the column 'Year', returning the sum of 'Orders' grouped by 'CountryName'. Then calculate the % change in 'Orders' for each 'CountryName' from 2014 to 2015.

CountryName     Days        pCountry     Revenue    Orders  Year
United Kingdom  0-1 days    India        2604.799   13      2014
Norway          8-14 days   Australia    5631.123   9       2015
US              31-45 days  UAE          970.8324   2       2014
United Kingdom  4-7 days    Austria      94.3814    1       2015
Norway          8-14 days   Slovenia     939.8392   3       2014
South Korea     46-60 days  Germany      1959.4199  15      2014
UK              8-14 days   Poland       1394.9096  6.      2015
UK              61-90 days  Lithuania   -170.8035   -1      2015
US              8-14 days   Belize       1687.68    5       2014
Australia       46-60 days  Chile        888.72 2.  0       2014
US              15-30 days  Turkey       2320.7355  8       2014
Australia       0-1 days    Hong Kong    672.1099   2       2015

我可以使用较小的测试数据框进行此工作，但似乎只能返回无尽的错误例如完整的数据对因素无意义或行的重复标识符。经过数小时的阅读dplyr文档并尝试了我放弃的尝试。谁能帮忙使用此代码...

I can make this work with a smaller test dataframe, but can only seem to return endless errors like 'sum not meaningful for factors' or 'duplicate identifiers for rows' with the full data. After hours of reading the dplyr docs and trying things I've given up. Can anyone help with this code...

data %>% 
  spread(Year, Orders) %>% 
  group_by(CountryName) %>%
  summarise_all(.funs=c(Sum='sum'), na.rm=TRUE) %>% 
  mutate(percent_inc=100*((`2014_Sum`-`2015_Sum`)/`2014_Sum`))

预期输出将是类似于下面的表格。 （请注意：这些数字仅供参考，并非人工计算。）

The expected output would be a table similar to below. (Note: these numbers are for illustrative purposes, they are not hand calculated.)

CountryName  percent_inc
UK           34.2
US           28.2
Norway       36.1
...          ...

编辑

我必须对变量名进行一些编辑，请注意。

I had to make a few edits to the variable names, please note.

推荐答案

在数据仍为长格式时首先求和，然后进行传播。这是一个伪数据的示例：

Sum first, while your data are still in long format, then spread. Here's an example with fake data:

set.seed(2)
dat = data.frame(Country=sample(LETTERS[1:5], 500, replace=TRUE), 
                 Year = sample(2014:2015, 500, replace=TRUE),
                 Orders = sample(-1:20, 500, replace=TRUE))

dat %>% group_by(Country, Year) %>% 
  summarise(sum_orders = sum(Orders, na.rm=TRUE)) %>% 
  spread(Year, sum_orders) %>%
  mutate(Pct = (`2014` - `2015`)/`2014` * 100)

  Country `2014` `2015`        Pct
1       A    575    599  -4.173913
2       B    457    486  -6.345733
3       C    481    319  33.679834
4       D    423    481 -13.711584
5       E    528    551  -4.356061

如果您有多年的经验，将其保留为长格式可能更容易，直到您准备好制作一个不错的输出表：

If you have multiple years, it's probably easier to just keep it in long format until you're ready to make a nice output table:

set.seed(2)
dat = data.frame(Country=sample(LETTERS[1:5], 500, replace=TRUE), 
                 Year = sample(2010:2015, 500, replace=TRUE),
                 Orders = sample(-1:20, 500, replace=TRUE))

dat %>% group_by(Country, Year) %>% 
  summarise(sum_orders = sum(Orders, na.rm=TRUE)) %>% 
  group_by(Country) %>%
  arrange(Country, Year) %>%
  mutate(Pct = c(NA, -diff(sum_orders))/lag(sum_orders) * 100) 

   Country  Year sum_orders        Pct
    <fctr> <int>      <int>      <dbl>
 1       A  2010        205         NA
 2       A  2011        144  29.756098
 3       A  2012        226 -56.944444
 4       A  2013        119  47.345133
 5       A  2014        177 -48.739496
 6       A  2015        303 -71.186441
 7       B  2010        146         NA
 8       B  2011        159  -8.904110
 9       B  2012        152   4.402516
10       B  2013        180 -18.421053
# ... with 20 more rows

这篇关于R：如何同时传播，分组，汇总和变异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！