1

I would like to know how to summarize more columns in R. I have a data.frame (df) like this:

device  customer  message  count_today  count_yesterday
1       x          a        5              3
1       x          b        1              2
2       y          c        0              1
3       z          a        5              2
1       x          a        7              4

I would like to get a summary of the message counts per customer. This is the result I hope to get:

customer  count_today  count_yesterday
x          13            9
y          0             1
z          5             2

I tried the summarise function to get partial results for count_today and count_yesterday, however it seems to be summarizing the number of occurrences of each device.

This is the code I tried:

df_today <- select(df, customer, count_today) %>%
  group_by(cutomer) %>%
    summarise(count_today=n()) %>%
      arrange(., customer)

df_yesterday <- select(df, customer, count_yesterday) %>%
  group_by(cutomer) %>%
    summarise(count_yesterday=n()) %>%
      arrange(., customer)

df_final <-merge(x = df_today, y = df_yesterday, by = "customer", all.x = TRUE, all.y = TRUE)

All I am getting is this:

customer  count_today  count_yesterday
x          3            3
y          1            1
z          1            1

Coul you please help me with this? Thanks for any suggestions.

Jaap
  • 81,064
  • 34
  • 182
  • 193
dloudtrain
  • 301
  • 2
  • 9

1 Answers1

1

By summarising with sum instead of n, you'll get the result you're after.

library(dplyr)

df %>%
  group_by(customer) %>%
  summarise_at(c("count_today", "count_yesterday"), sum)

# A tibble: 3 x 3
  customer count_today count_yesterday
  <chr>          <int>           <int>
1 x                 13               9
2 y                  0               1
3 z                  5               2
Aron Strandberg
  • 3,040
  • 9
  • 15