2

Wanted to count the occurrence of a variable in a data frame. It’s easy to do in Excel PivotTable and now learning how to do it in R.

      v_time visitor
1/2/2018 16:07    Jack
1/3/2018 16:09    Jack
1/3/2018 16:12   David
1/3/2018 16:16    Kate
1/2/2018 16:21   David
1/2/2018 16:32    Jack
1/4/2018 16:33    Kate
1/4/2018 16:55    Jack

Excel can make it easily like this:

enter image description here

I’ve tried some lines but still not getting there.

visitor <- c("Jack", "Jack", "David", "Kate", "David", "Jack", "Kate", "Jack")
v_time <- c("1/2/2018 16:07","1/3/2018 16:09","1/3/2018 16:12","1/3/2018 16:16","1/2/2018 16:21","1/2/2018 16:32","1/4/2018 16:33", "1/4/2018 16:55")
df <- data.frame(v_time, visitor)

as.Date(as.POSIXct(df$v_time, "%m/%d/%Y"))

library(plyr)
count(df$visitor, 'v_time')

as.data.frame(table(df$visitor))

What’s the way to produce the Excel PivotTable alike output in R? Thank you.

Mark K
  • 8,767
  • 14
  • 58
  • 118
  • Possible duplicate of [marginal total in tables](https://stackoverflow.com/questions/6649004/marginal-total-in-tables) – Henrik Mar 04 '18 at 11:12

2 Answers2

3

The hardest part is just getting the day component out of the column.

library(data.table)
dcast(setDT(df)[, day := sub(" .*$", "", v_time)], visitor ~ day, fun.aggregate = length)
#> Using 'day' as value column. Use 'value.var' to override
#>    visitor 1/2/2018 1/3/2018 1/4/2018
#> 1:   David        1        1        0
#> 2:    Jack        2        1        1
#> 3:    Kate        0        1        1
Hugh
  • 15,521
  • 12
  • 57
  • 100
2

We can remove the time component with either regex (preferred way is to convert to Date class with as.Date 

df$v_time <- sub("\\s+.*", "", df$v_time)

Or 

df$v_time <- as.Date(df$v_time, "%m/%d/%Y")

and then do the table

as.data.frame.matrix(table(df[2:1]))

If we need the sum on both margins, do with addmargins

addmargins(table(df[2:1]))

The reason OP's code is not working is after converting to Date, it is not assigned to 'v_time'

akrun
  • 874,273
  • 37
  • 540
  • 662