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

dplyr分组数据中的累积和

cumsum dplyr r

3

Edu · 技术社区 · 10 年前

我有一个数据帧 df (可下载 here )指的是一份公司登记册,看起来像这样:

    Provider.ID        Local.Authority month year entry exit total
1  1-102642676           Warwickshire    10 2010     2    0     2
2  1-102642676                   Bury    10 2010     1    0     1
3  1-102642676                   Kent    10 2010     1    0     1
4  1-102642676                  Essex    10 2010     1    0     1
5  1-102642676                Lambeth    10 2010     2    0     2
6  1-102642676            East Sussex    10 2010     5    0     5
7  1-102642676       Bristol, City of    10 2010     1    0     1
8  1-102642676              Liverpool    10 2010     1    0     1
9  1-102642676                 Merton    10 2010     1    0     1
10 1-102642676          Cheshire East    10 2010     2    0     2
11 1-102642676               Knowsley    10 2010     1    0     1
12 1-102642676        North Yorkshire    10 2010     1    0     1
13 1-102642676   Kingston upon Thames    10 2010     1    0     1
14 1-102642676               Lewisham    10 2010     1    0     1
15 1-102642676              Wiltshire    10 2010     1    0     1
16 1-102642676              Hampshire    10 2010     1    0     1
17 1-102642676             Wandsworth    10 2010     1    0     1
18 1-102642676                  Brent    10 2010     1    0     1
19 1-102642676            West Sussex    10 2010     1    0     1
20 1-102642676 Windsor and Maidenhead    10 2010     1    0     1
21 1-102642676                  Luton    10 2010     1    0     1
22 1-102642676                Enfield    10 2010     1    0     1
23 1-102642676               Somerset    10 2010     1    0     1
24 1-102642676         Cambridgeshire    10 2010     1    0     1
25 1-102642676             Hillingdon    10 2010     1    0     1
26 1-102642676               Havering    10 2010     1    0     1
27 1-102642676               Solihull    10 2010     1    0     1
28 1-102642676                 Bexley    10 2010     1    0     1
29 1-102642676               Sandwell    10 2010     1    0     1
30 1-102642676            Southampton    10 2010     1    0     1
31 1-102642676               Trafford    10 2010     1    0     1
32 1-102642676                 Newham    10 2010     1    0     1
33 1-102642676         West Berkshire    10 2010     1    0     1
34 1-102642676                Reading    10 2010     1    0     1
35 1-102642676             Hartlepool    10 2010     1    0     1
36 1-102642676              Hampshire     3 2011     1    0     1
37 1-102642676                   Kent     9 2011     0    1    -1
38 1-102642676        North Yorkshire    12 2011     0    1    -1
39 1-102642676         North Somerset    12 2012     2    0     2
40 1-102642676                   Kent    10 2014     1    0     1
41 1-102642676               Somerset     1 2016     0    1    -1

我的目标是创建一个反映最后一个变量累计和的变量( total )对于每个 Local.Authority 和每个 year . 只是两者之间的区别 entry 和 exit .我尝试通过申请 dplyr 基于以下基础:

library(dplyr)
 df.1 = df %>% group_by(Local.Authority, year) %>%
  mutate(cum.total = cumsum(total)) %>%
  arrange(year, month, Local.Authority)

产生以下(错误)结果:

> df.1
Source: local data frame [41 x 8]
Groups: Local.Authority, year [41]

   Provider.ID  Local.Authority month  year entry  exit total cum.total
        <fctr>           <fctr> <int> <int> <int> <int> <int>     <int>
1  1-102642676           Bexley    10  2010     1     0     1        35
2  1-102642676            Brent    10  2010     1     0     1        25
3  1-102642676 Bristol, City of    10  2010     1     0     1        13
4  1-102642676             Bury    10  2010     1     0     1         3
5  1-102642676   Cambridgeshire    10  2010     1     0     1        31
6  1-102642676    Cheshire East    10  2010     2     0     2        17
7  1-102642676      East Sussex    10  2010     5     0     5        12
8  1-102642676          Enfield    10  2010     1     0     1        29
9  1-102642676            Essex    10  2010     1     0     1         5
10 1-102642676        Hampshire    10  2010     1     0     1        23
..         ...              ...   ...   ...   ...   ...   ...       ...

我通过检查变量中的级别来确认这些结果 地方当局 出现在不同年份的(例如肯特):

> check = df.1 %>% filter(Local.Authority == "Kent")
> check
Source: local data frame [3 x 8]
Groups: Local.Authority, year [3]

  Provider.ID Local.Authority month  year entry  exit total cum.total
       <fctr>          <fctr> <int> <int> <int> <int> <int>     <int>
1 1-102642676            Kent    10  2010     1     0     1         4
2 1-102642676            Kent     9  2011     0     1    -1        42
3 1-102642676            Kent    10  2014     1     0     1        44

应该在哪里:

Provider.ID Local.Authority month  year entry  exit total cum.total
       <fctr>          <fctr> <int> <int> <int> <int> <int>     <int>
1 1-102642676            Kent    10  2010     1     0     1         1
2 1-102642676            Kent     9  2011     0     1    -1         0
3 1-102642676            Kent    10  2014     1     0     1         1

有人知道从积蓄中得到这些结果可能会发生什么吗?提前表示感谢。

2 回复 | 直到 10 年前

1

4

Arun kumar mahesh 10 年前

当您按本地分组时。权限;年,它采用唯一的值,并将结果打印为1,-1,1,因此只按局部分组更好。cumsum基于总值和结果1,0,1工作的机构

 df <- df %>%
      group_by(Local.Authority) %>%
      mutate(cum.to = cumsum(total))

    > df
    Source: local data frame [3 x 8]
    Groups: Local.Authority [1]

      Provider.ID Local.Authority month  year entry  exit total cum.to
            <chr>           <chr> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>
    1 1-102642676            Kent    10  2010     1     0     1      1
    2 1-102642676            Kent     9  2011     0     1    -1      0
    3 1-102642676            Kent    10  2014     1     0     1      1

2

0

Edu 10 年前

我找到了解决问题的办法。我重新开始了我的课程,我只得到了地方当局的结果分组,然后安排:

> df.1 = df %>% group_by(Local.Authority) %>%
+ mutate(cum.total = cumsum(total)) %>%
+ arrange(year, month, Local.Authority)
> df.1
Source: local data frame [41 x 8]
Groups: Local.Authority [36]

   Provider.ID  Local.Authority month  year entry  exit total cum.total
        <fctr>           <fctr> <int> <int> <int> <int> <int>     <int>
1  1-102642676           Bexley    10  2010     1     0     1         1
2  1-102642676            Brent    10  2010     1     0     1         1
3  1-102642676 Bristol, City of    10  2010     1     0     1         1
4  1-102642676             Bury    10  2010     1     0     1         1
5  1-102642676   Cambridgeshire    10  2010     1     0     1         1
6  1-102642676    Cheshire East    10  2010     2     0     2         2
7  1-102642676      East Sussex    10  2010     5     0     5         5
8  1-102642676          Enfield    10  2010     1     0     1         1
9  1-102642676            Essex    10  2010     1     0     1         1
10 1-102642676        Hampshire    10  2010     1     0     1         1

现在检查“Kent”会产生预期结果:

> check = df.1 %>% filter(Local.Authority == "Kent")
> check
Source: local data frame [3 x 8]
Groups: Local.Authority [1]

  Provider.ID Local.Authority month  year entry  exit total cum.total
       <fctr>          <fctr> <int> <int> <int> <int> <int>     <int>
1 1-102642676            Kent    10  2010     1     0     1         1
2 1-102642676            Kent     9  2011     0     1    -1         0
3 1-102642676            Kent    10  2014     1     0     1         1