count occurrences in column and group by id

Question

Hi I have the next dataframe

  user_id webpage
       1  google
       1    bing
       2  google
       2  google
       2   yahoo

and I would like to create a result dataframe like this

user_id  google  bing  yahoo
   1       1       1     0
   2       2       0     1

it counts the ocurrences by id

I just found a solution for counting the frequencies in general, but not turning the occurrences in its own columns and put the count in its own column.

Since you're spreading the counts, a better duplicate is: [Faster ways to calculate frequencies and cast from long to wide](https://stackoverflow.com/questions/8186133/faster-ways-to-calculate-frequencies-and-cast-from-long-to-wide) — divibisan, May 13 '19 at 14:49

score 1 · Accepted Answer · answered May 13 '19 at 14:59

Here's a tidyverse solution.

# Create data frame
df <- read.table(text = "  user_id webpage
       1  google
       1    bing
       2  google
       2  google
       2   yahoo", header = TRUE)

# Load libraries
library(dplyr)
library(tibble)
library(tidyr)

# Count and restructure
as_tibble(table(df)) %>% spread(webpage, n)
#> # A tibble: 2 x 4
#>   user_id  bing google yahoo
#>   <chr>   <int>  <int> <int>
#> 1 1           1      1     0
#> 2 2           0      2     1

^{Created on 2019-05-13 by the reprex package (v0.2.1)}

count occurrences in column and group by id

1 Answers1