-1

I have newly started to learn R, so my question may be utterly ridiculous. I have a data frame

data<- data.frame('number'=1:11, 'col1'=sample(10:20),'col2'=sample(10:20),'col3'=sample(10:20),'col4'=sample(10:20),'col5'=sample(10:20), 'date'= c('12-12-2014','12-11-2014','12-10-2014','12-09-2014', '12-08-2014','12-07-2014','12-06-2014','12-05-2014','12-04-2014', '12-04-2014', '12-03-2014') )

The number column is an 'id' column and the last column is a date. I want to count the number of times that each number occurs across (not per column, but the whole data frame containing data) the columns 2:6 and when they occurred. I am stuck on the first part having tried the following using data.table:

count <- function(){
    i = 1
    DT <-data.table(data[2:6])

    for (i in 10:20){
        DT[, .N, by =i]
        i = i + 1
    }

}

which gives an error that I don't begin to understand

    Error in `[.data.table`(DT, , .N, by = i) : 
  The items in the 'by' or 'keyby' list are length (1). Each must be same length as rows in x or number of rows returned by i (11)

Can someone help, please. Also with the second part that I have not even attempted yet i.e. associating a date or a row number with each occurrence of a number

bartektartanus
  • 15,284
  • 6
  • 74
  • 102
user1478335
  • 1,769
  • 5
  • 25
  • 37

1 Answers1

1

Perhaps you may want this

library(reshape2)
table(melt(data[,-1], id.var='date')[,-2])
#            value
#date         10 11 12 13 14 15 16 17 18 19 20
#  12-03-2014  0  0  1  0  0  1  0  0  1  2  0
#  12-04-2014  2  0  0  2  2  0  1  0  1  1  1
#  12-05-2014  0  0  0  0  0  0  1  1  2  0  1
#  12-06-2014  1  1  0  0  0  1  0  1  0  0  1
#  12-07-2014  0  1  0  1  0  1  1  1  0  0  0
#  12-08-2014  1  1  0  0  1  0  0  1  1  0  0
#  12-09-2014  0  0  2  0  1  2  0  0  0  0  0
#  12-10-2014  0  0  1  1  0  0  1  0  0  1  1
#  12-11-2014  0  1  1  0  0  0  1  0  0  1  1
#  12-12-2014  1  1  0  1  1  0  0  1  0  0  0

Or if you need a data.table solution (from @Arun's comments)

library(data.table)
dcast.data.table(melt(setDT(data),
           id="date", measure=2:6), date ~ value)
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thank you for your help. Can you suggest a good book to learn R in a structured way? – user1478335 Dec 27 '14 at 15:27
  • @user1478335 I would check `coursera` for any online courses. Regarding the books, some are listed http://stackoverflow.com/questions/1744861/how-to-learn-r-as-a-programming-language As in any programming language, practise is the key. – akrun Dec 27 '14 at 15:32
  • 1
    I'd avoid `table()` as much as possible, and do it as: `dcast.data.table(melt(setDT(data), id="date", measure=2:6), date ~ value)` – Arun Dec 27 '14 at 17:15
  • 1
    Sure! It's a syntactically great function, but scales rather poorly :-(. BTW from the next version of data.table on, we can use `dcast()` directly... no need for `.data.table`. – Arun Dec 27 '14 at 17:18