CODEWITHSUNDEEP
rcombinations
vaettchen
vaettchen

Reputation: 7659

Count combinations of two variables in a data.frame

I have two data.frames - g that contains all possible (here: 8) combinations of two variables, and h with 62 observations of any of the 8 combinations (dput() at the bottom).

I have added a third column to g that is supposed to take the observation count for each combination in h:

> g
  where what days
1    sg free    0
2    in free    0
3    hk free    0
4    de free    0
5    sg work    0
6    in work    0
7    hk work    0
8    de work    0

I want to count how often each of the combinations in g appear in h and I do so now with an old-fashioned nested loop that works well:

for( i in seq( nrow( g ) ) )
    for( j in seq( nrow( h ) ) )
        if( all( g[ i, 1:2 ] == h[ j, ] ) ) g[ i, 3 ] <- g[ i, 3 ] + 1

which gives me what I want:

> g
  where what days
1    sg free   10
2    in free    0
3    hk free    4
4    de free    4
5    sg work   18
6    in work   10
7    hk work    6
8    de work   10

But I wonder whether there are less cryptic, more concise ways of doing this; I'm particularly curious whether base R is providing tools that I have not discovered.

Data:

g <- structure(list(where = structure(c(1L, 2L, 3L, 4L, 1L, 2L, 3L, 
4L), .Label = c("sg", "in", "hk", "de"), class = "factor"), what = structure(c(1L, 
1L, 1L, 1L, 2L, 2L, 2L, 2L), .Label = c("free", "work"), class = "factor"), 
days = c(0, 0, 0, 0, 0, 0, 0, 0)), .Names = c("where", "what", 
"days"), out.attrs = structure(list(dim = c(4L, 2L), dimnames = structure(list(
Var1 = c("Var1=sg", "Var1=in", "Var1=hk", "Var1=de"), Var2 = c("Var2=free", 
"Var2=work")), .Names = c("Var1", "Var2"))), .Names = c("dim", "dimnames")), 
row.names = c(NA, -8L), class = "data.frame")

h <- structure(list(values = c("sg", "sg", "sg", "sg", "sg", "sg", 
"sg", "sg", "sg", "sg", "sg", "sg", "sg", "sg", "in", "in", "in", 
"in", "in", "hk", "hk", "hk", "hk", "hk", "de", "de", "de", "de", 
"de", "de", "de", "sg", "sg", "sg", "sg", "sg", "sg", "sg", "sg", 
"sg", "sg", "sg", "sg", "sg", "sg", "in", "in", "in", "in", "in", 
"hk", "hk", "hk", "hk", "hk", "de", "de", "de", "de", "de", "de", 
"de"), values.1 = c("free", "work", "work", "work", "work", "free", 
"free", "work", "work", "work", "work", "work", "free", "free", 
"work", "work", "work", "work", "work", "free", "free", "work", 
"work", "work", "work", "work", "free", "free", "work", "work", 
"work", "free", "work", "work", "work", "work", "free", "free", 
"work", "work", "work", "work", "work", "free", "free", "work", 
"work", "work", "work", "work", "free", "free", "work", "work", 
"work", "work", "work", "free", "free", "work", "work", "work"
)), .Names = c("values", "values.1"), row.names = c(NA, -62L), class = "data.frame")

Upvotes: 0

Views: 2990

Answers (1)

Mark
Mark

Reputation: 4537

There's a simple tidy solution to this. I changed the column names in h to match what was in g (where and what). Group by the two values and summarize - this will give a count of the combinations. Then, do a left_join back to g and you have your counts.

library(dplyr)

h_s = h %>% 
  group_by(where,what) %>% 
  summarise(days=n())

g %>% 
  left_join(h_s,by=c("where","what")) %>% 
  select(where,what,days=days.y) %>%
  mutate(days = ifelse(is.na(days),0,days))

EDIT

The reason for the left join is to make sure any cases not found in h are represented. I added a mutate to convert missing values to 0.

Upvotes: 1

Related Questions