0

Hey I am working on a dataset which include Location and Date. I want to create a new data frame from my original one. New data frame will include Dates (only month) and a number correspond to sum of the same month. For example, If my dataset is like below:

USA 2011-01-01
USA 2012-01-01
UK  2010-05-01
UK  2009-05-01
USA 2012-07-01

my new data frame will be as below;

January  2
May      2
July     1

Can anyone help please?

Ram
  • 359
  • 1
  • 6
  • 15

2 Answers2

1

UPDATE:

stack(table(format(as.Date(df$V2), "%B")))

Produces

  values     ind
1      2 January
2      1    July
3      2     May

Another option (courtesy Ananda Mahto):

stack(table(df$V2))    # here V2 has the dates

Produces

  values        ind
1      2 2010-05-01
2      2 2011-01-01
3      1 2012-07-01

table counts the V2 values (dates); stack makes a data frame out of the result adding a column with the names (ind).

BrodieG
  • 51,669
  • 9
  • 93
  • 146
  • Which work good. Can you check my question again (because I change). and I think I need to apply another function. Thanks. – Ram Mar 07 '14 at 15:42
  • appreciated @BrodieG. This is exactly what I want. Thanks a lot. – Ram Mar 07 '14 at 23:35
  • Only one last question. when I apply this code the output is starting from April. How can I get an output it will start from January and end in December? – Ram Mar 07 '14 at 23:56
  • On the resulting data frame, try something like `df.new[order(as.Date(df.new$ind, format="%B")),]` – BrodieG Mar 08 '14 at 01:38
0

Use the aggregate() function with length():

old.data <- data.frame(date = rep(c('a', 'b'), 10))
aggregate(old.data$date, by = list(old.data$date), FUN = length)
Christopher Louden
  • 7,540
  • 2
  • 26
  • 29
  • Which is perfect. But what if I want to sum only based on months. (So i will not care which year). Do you have any suggestion. I edited my question for my new need. – Ram Mar 07 '14 at 15:39
  • Change the `by` to the month, which can be extracted from the date vector as explained [here](http://stackoverflow.com/a/9749666/2196842). – Christopher Louden Mar 07 '14 at 16:03