How can I sum values in another df based on a condition in both df's in r?

Question

I'm quite new to R, but I cant find the right way to solve my problem.

I have two df's: one on session level and one on client level. In the client level df I want to sum the number of sessions done by a client in the session df.

The df's look like:

          df1                                df2
Sessionid     Client id                    Clientid     
   1              1                           1                 
   2              1                           2                 
   3              2                  
   4              2                  
   5              2

I want output like:

          df1                                df2
Sessionid     Client id             Clientid     Number_of_sessions
   1              1                    1                 2
   2              1                    2                 3
   3              2                  
   4              2                  
   5              2

AlexB · Accepted Answer · 2020-05-24T08:35:49.647

One option would be to use dplyr library:

df %>%
  group_by(Clientid) %>%
  summarise(Number_of_sessions = n())

# Clientid Number_of_sessions
# <int>              <int>
# 1        1           2
# 2        2           3

or

aggregate(Sessionid ~ Clientid, df, length)

which returns the same thing.

Data:

structure(list(Sessionid = 1:5, Clientid = c(1L, 1L, 2L, 2L, 
2L)), class = "data.frame", row.names = c(NA, -5L)) -> df

Allan Cameron · Answer 2 · 2020-05-24T08:34:41.137

1

You could try

library(dplyr)
count(group_by(df1, `Client id`), name = "Number_of_sessions")
#> # A tibble: 2 x 2
#> # Groups:   Client id [2]
#>   `Client id` Number_of_sessions
#>         <int>              <int>
#> 1           1                  2
#> 2           2                  3

edited May 24 '20 at 08:34

answered May 24 '20 at 08:28

Allan Cameron

147,086
7
49
87

Ric S · Answer 3 · 2020-05-24T08:38:03.873

1

A simple base R solution would be

df2 <- as.data.frame(table(df1$Clientid))
names(df2) <- c("Clientid", "Number_of_sessions")

Output

#   Clientid Number_of_sessions
# 1        1                  2
# 2        2                  3

edited May 24 '20 at 08:38

answered May 24 '20 at 08:30

Ric S

9,073
3
25
51

To make it a data frame use `as.data.frame(table(df$Clientid))` – Jan May 24 '20 at 08:31
Correct, I edit my answer. Thanks – Ric S May 24 '20 at 08:37

How can I sum values in another df based on a condition in both df's in r?

3 Answers3