Add multiple columns with the same group and sum

May 18, 2022

I’ve got this dataframe and I want to add the last two columns to another dataframe by summing them and grouping them by "Full.Name"

# A tibble: 6 x 5
# Groups:   authority_dic, Full.Name [6]
  authority_dic Full.Name          Entity                      `2019` `2020`
  <chr>         <chr>              <chr>                        <int>  <int>
1 accomplished  Derek J. Leathers  WERNER ENTERPRISES INC           1      0
2 accomplished  Dirk Van de Put    MONDELEZ INTERNATIONAL INC       0      1
3 accomplished  Eileen P. Drake    AEROJET ROCKETDYNE HOLDINGS      1      0
4 accomplished  G. Michael Sievert T-MOBILE US INC                  0      3
5 accomplished  Gary C. Kelly      SOUTHWEST AIRLINES               0      1
6 accomplished  James C. Fish, Jr. WASTE MANAGEMENT INC             1      0

This is the dataframe I want to add the two columns to: Like you can see the "Full.Name" column acts as the grouping column.

# A tibble: 6 x 3
# Groups:   Full.Name [6]
  Full.Name            `2019` `2020`
  <chr>                 <int>  <int>
1 A. Patrick Beharelle   5541   3269
2 Aaron P. Graft          165    200
3 Aaron P. Jagdfeld         4      5
4 Adam H. Schechter       147    421
5 Adam P. Symson         1031    752
6 Adena T. Friedman      1400   1655

I can add one column using the following piece of code, but if I want to do it with the second one, it overwrites my existing one and I am only left with one instead of two columns added.

narc_auth_total <- narc_auth %>% group_by(Full.Name) %>% summarise(`2019_words` = sum(`2019`)) %>% left_join(totaltweetsyear, ., by = "Full.Name")

The output for this command looks like this:

# A tibble: 6 x 4
# Groups:   Full.Name [6]
  Full.Name            `2019` `2020` `2019_words`
  <chr>                 <int>  <int>        <int>
1 A. Patrick Beharelle   5541   3269           88
2 Aaron P. Graft          165    200            2
3 Aaron P. Jagdfeld         4      5            0
4 Adam H. Schechter       147    421            2
5 Adam P. Symson         1031    752           15
6 Adena T. Friedman      1400   1655           21

I want to do the same thing and add the 2020_words column to the same dataframe. I just cannot do it, but it cannot be that hard to do so. It should be summarized as well, just like the 2019_words column. When I add "2020" to my command, it says object "2020" not found.

Thanks in advance.

>Solution :

If I have understood you well, this will solve your problem:

narc_auth_total <- 
  narc_auth %>% 
  group_by(Full.Name) %>% 
  summarise(
    `2019_words` = sum(`2019`),
    `2020_words` = sum(`2020`)
  ) %>% 
  left_join(totaltweetsyear, ., by = "Full.Name")