Add multiple columns with the same group and sum

Question

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.

user2332849 · Accepted Answer · 2022-05-17 22:23:30Z

1

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")

answered May 17, 2022 at 22:23

user2332849

1,4581 gold badge9 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Yeezus Over a year ago

Thank you so much! It really is that easy. Thank you for your time and work!

Martin Gal Over a year ago

You could also use summarise(across(`2019`:`2020`, sum, .names = "{.col}_words")).

Yeezus Over a year ago

Thank you! What is the difference to the first solution? Is it more efficient or faster?

user2332849 Over a year ago

The first solution is easier to read for a beginner; the second one is more concise, but performance is about the same.

Collectives™ on Stack Overflow

Add multiple columns with the same group and sum

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related