CODEWITHSUNDEEP
r
Spearfisher
Spearfisher

Reputation: 8783

R - count element in a data.frame to populate another data.frame

I have a dataset looking like this:

 id     day        hour
 1234   Monday     8
 5678   Wednesday  2

I have a dataframe representing a timetable:

       Monday Tuesday Wednesday Thursday Friday Saturday Sunday
    1       0       0         0        0      0        0      0
    2       0       0         0        0      0        0      0
    3       0       0         0        0      0        0      0
    4       0       0         0        0      0        0      0
    5       0       0         0        0      0        0      0
    6       0       0         0        0      0        0      0
    7       0       0         0        0      0        0      0
                                 ....

I'd like to count the number of item for each hour of the day and each day of the week. I am new to R so so far, the only way I managed to do this is by looping through my data set to increment to value of my data. I was wondering if there were any built in function in R which would help me do this quickly?

Many thanks

Upvotes: 0

Views: 58

Answers (1)

MrFlick
MrFlick

Reputation: 206232

Let's say this is your input data

weekdays<-c("Sun","Mon","Tue","Wed","Thur","Fri","Sat")
set.seed(15)
N<-100
dd<-data.frame(
   id=1:N,
   weekday=factor(sample(weekdays, N, replace=T), levels=weekdays),
   hour=factor(sample(1:24, N, replace=T), levels=1:24)
)

Note that I've made sure weekday and hours are both factors. This ensures the correct ordering by specifying levels and let's R know about all values that may not be observed in your data so you get a complete table. Anyway, you can create the table of counts with

with(dd, table(hour, weekday))

And the results look like this 

    weekday
hour Sun Mon Tue Wed Thur Fri Sat
  1    0   0   0   0    0   1   1
  2    1   0   0   0    1   0   2
  3    2   0   1   1    2   1   1
  4    1   0   0   0    2   1   0
  5    1   0   0   0    0   0   0
  etc....

That actually returns an object of class "table". If you do need a data.frame, you can do this transformation.

data.frame(unclass(with(dd, table(hour, weekday))))

So we're not really filling a mostly empty data.frame, we are just creating a new one with the data you want.

Upvotes: 1

Related Questions