CODEWITHSUNDEEP
r
Rsp
Rsp

Reputation: 5

Grouping and counting in R

I have a shell program to do the following, but I wanted to try my hand at R, and I am stumped.

My dataset looks like this:

 <date>      <car>        <sale>   
 2014-05-08 mercedes-450  40000
 2014-05-08 mercedes-350  30000
 2014-05-08 mercedes-350  30000
 2014-05-08 toyota corolla 20000
 2014-05-08 toyota camry   30000
 2014-05-09 mercedes-450  40000
 2014-05-09 mercedes-450  40000

I would like to convert this to 2 data frames, one for mercedes and one for toyota.

so for mercedes: 

 2014-05-08 mercedes  100000
 2014-05-09 mercedes  80000

and for toyota:

 2014-05-08 toyota  50000

I need to do this so I can plot the sales for toyota and mercedes on separate graphs with date on x axes and sales on y.

How do I create the two data frames? I tried ddply, but all I get is one data trace that looks like this

 ddply(sales,c("date","car_type"),numcolwise(sum))

 2014-05-08 mercedes-450  40000
 2014-05-08 mercedes-350  60000
 2014-05-08 toyota corolla  20000

Upvotes: 1

Views: 51

Answers (3)

Ruthger Righart
Ruthger Righart

Reputation: 4921

A ddply solution:

library(plyr)

A<-df[grep("mercedes", df$car), ]
B<-df[grep("toyota", df$car), ]

ddply(A, .(date), summarize, val = sum(sale)) 
ddply(B, .(date), summarize, val = sum(sale))

Upvotes: 0

Sam Firke
Sam Firke

Reputation: 23034

Load the data frame:

df <- data.frame(
  date = c("2014-05-08","2014-05-08","2014-05-08","2014-05-08","2014-05-08","2014-05-09","2014-05-09"),
  car = c("mercedes-450","mercedes-350","mercedes-350","toyota corolla","toyota camry","mercedes-450","mercedes-450"),
  sale = c(40000,30000,30000,20000,30000,40000,40000),stringsAsFactors=FALSE)

Create a new variable for car make by splitting the car variable by "-" and " ":

splitstring <- strsplit(df$car," |-")
df$make <- unlist(lapply(splitstring,"[[",1))

Load the dplyr package that we'll use to calculate the grouped sums:

library(dplyr)

Calculate the sum of sales by make, by day:

results <- df %>%
  group_by(date,make) %>%
  summarise(total_sales = sum(sale))

Now if you want a different chart for each make, you could use make as the facet variable in a package like ggplot2 and keep your data in a single table. But if you want different tables for each make:

tables <- split(results,results$make)

Upvotes: 1

djas
djas

Reputation: 1023

# sample data
set.seed(1)
df = data.frame(date = rep(as.Date(c("10jan1970", "11jan1970", "31mar1960", "30jul1960"), "%d%b%Y"), 2),
                type = sample(1:2, 8, replace=T),
                sales = rnorm(8))

# "aggreate()" to calculate your daily sales
agg = aggregate(sales ~ date + type, data=df, FUN=sum)

# "split()" the result by type if you want different data frames for each type
tabs = split(agg, f=agg$type)
tabs

# $`1`
#         date type      sales
# 1 1970-01-10    1  0.9052891
# 2 1970-01-11    1 -0.8204684
#
# $`2`
#         date type      sales
# 3 1960-03-31    2  1.9992102
# 4 1960-07-30    2  1.1281679
# 5 1970-01-11    2 -0.3053884

Upvotes: 1

Related Questions