Sum by condition in two columns data frame R

Question

I have dataframe like this

date            apps    long
10/22/2013 23:51    A   2
10/22/2013 23:52    B   3
10/22/2013 23:52    C   1
10/23/2013 7:03     C   5
10/23/2013 7:13     A   1
10/23/2013 7:31     B   4
10/23/2013 7:31     A   5
10/23/2013 7:31     B   2
10/24/2013 0:54     B   3
10/24/2013 1:16     C   2
10/24/2013 1:16     C   1
10/24/2013 3:27     A   2
10/24/2013 7:30     A   3
10/24/2013 7:30     A   1

The problems that i have is : I want to sum how long A, B, C apps spent time for each days. so the output will looks like:

A 10/22/2013 2
A 10/23/2013 6
A 10/24/2013 6
etc...

I've tried some syntax but it did not work.

@Pascal thank you, it's work, but i still have problem because the format of date `10/22/2013 23:52` how to change to the `10/22/2013` to all of columns date in this dataframe, i am sorry i am very new in R, thank you for advanced. — user46543, Jul 02 '14 at 05:47
What is the `class()` of your date column? Is it "POSIXct" or "factor" or something else? — MrFlick, Jul 02 '14 at 05:55
@MrFlick the class of this column is factor, i tried this one `sapply(apps1$time,as.Date(apps1$time,format='%m/%d/%Y') )` but didnt work — user46543, Jul 02 '14 at 06:00

MrFlick · Accepted Answer · 2014-07-02T06:16:18.303

First, i'm assuming your data.frame is called dd. Here it is in a copy/pasteable form

dd <- structure(list(date = structure(c(1L, 2L, 2L, 3L, 4L, 5L, 5L, 
5L, 6L, 7L, 7L, 8L, 9L, 9L), .Label = c("10/22/2013 23:51", "10/22/2013 23:52", 
"10/23/2013 7:03", "10/23/2013 7:13", "10/23/2013 7:31", "10/24/2013 0:54", 
"10/24/2013 1:16", "10/24/2013 3:27", "10/24/2013 7:30"), class = "factor"), 
    apps = structure(c(1L, 2L, 3L, 3L, 1L, 2L, 1L, 2L, 2L, 3L, 
    3L, 1L, 1L, 1L), .Label = c("A", "B", "C"), class = "factor"), 
    long = c(2L, 3L, 1L, 5L, 1L, 4L, 5L, 2L, 3L, 2L, 1L, 2L, 
    3L, 1L)), .Names = c("date", "apps", "long"), class = "data.frame", row.names = c(NA, 
-14L))

You should convert your dates to a proper date value

dd$date <- as.POSIXct(as.character(dd$date), format="%m/%d/%Y %H:%M", tz="GMT")

Then you can create a nice data.frame with aggregate, here using as.Date to strip off time

aggregate(long ~ as.Date(date) + apps, dd, FUN=sum)

This returns

  as.Date(date) apps long
1    2013-10-22    A    2
2    2013-10-23    A    6
3    2013-10-24    A    6
4    2013-10-22    B    3
5    2013-10-23    B    6
6    2013-10-24    B    3
7    2013-10-22    C    1
8    2013-10-23    C    5
9    2013-10-24    C    3

when i run `dd$date <- as.POSIXct(as.character(dd$date), format="%m/%d/%Y %H:%M", tz="GMT")` the data in date column become NA, and I think the outputs not like that i want, i want group the name of apps for each days — user46543, Jul 02 '14 at 06:12
I posed enough to make exactly what I ran run on your computer as well. If you get NA values, spend some time figuring out how your data is different from mine. Perhaps take time to read [how to make a great R reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) for tips on how to improve questions to make them easier to answer. — MrFlick, Jul 02 '14 at 06:17
yes, definitely works, I am sorry, I have mistake. Thank you for your help — user46543, Jul 02 '14 at 06:29

score 0 · Answer 2 · answered Jul 02 '14 at 06:02

I'm fairly sure this is duplicated somewhere but I failed in my first three searches so here it is:

tapply( dat$long, list(dt = format( as.POSIXct(dat$date, "%d-%m-%Y %H:%M"), 
                                    "%d-%m-%Y"),
                        grp=dat$apps ),
                  sum)

score 0 · Answer 3 · answered Jul 02 '14 at 06:50

Using dplyr on Mr.Flick's dd

library(dplyr)
dd%>% 
group_by(apps, date=gsub("\\s+.*","",date))%>%
summarize(long=sum(long))
#      apps       date long
# 1    A 10/22/2013    2
# 2    A 10/23/2013    6
# 3    A 10/24/2013    6
# 4    B 10/22/2013    3
# 5    B 10/23/2013    6
# 6    B 10/24/2013    3
# 7    C 10/22/2013    1
# 8    C 10/23/2013    5
# 9    C 10/24/2013    3

Sum by condition in two columns data frame R

3 Answers3