Pivot like number manipulation in R

Question

I am trying to nest two subset data inside aggregate. but it returns different length of data and hence unable to compute. What options do i have?

newfile<- aggregate(cbind(subset(IVA,IVA$IVR.Agent =="Agent"),subset(IVA,IVA$IVR.Agent=="IVR")) ~ IVA$OrderNo, FUN = length, rm.action = TRUE)

Below is the error that I got

Error in data.frame(..., check.names = FALSE) : 
  arguments imply differing number of rows: 25039, 4585

I am trying to make a pivot table that will show Agent count and IVR count in separate columns just like in excel, if for a specific order there is no IVR it shows null otherwise its count.

DataSet looks like this

OrderNo IVR/Agent
    A1  IVR
    B1  Agent
    A2  Agent
    B2  IVR
    A3  Agent
    B3  Agent
    A4  Agent
    B4  Agent
    A5  IVR
    B5  Agent

Pivot

It's really difficult (actually impossible) to give you any answer without displaying how your data is structured. However, my preferred method for pivot tables is `ddply` in the `plyr` package or if you're familiar with `dplyr` try `group_by` — maloneypatr, Mar 24 '14 at 11:26
Please review the [guidelines for posting a minimal reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). — hrbrmstr, Mar 24 '14 at 11:28

John · Answer 1 · 2014-03-24T11:51:23.790

If your data is called myData the following seems to be what you need.

table(myData)

# which yields

       IVR.Agent
OrderNo Agent IVR
     A1     0   1
     A2     1   0
     A3     1   0
     A4     1   0
     A5     0   1
     B1     1   0
     B2     0   1
     B3     1   0
     B4     1   0
     B5     1   0

Just add up the columns for a grand total if you want one.

gt <- rowSums( table(s) )

or

tab <- table(s)
gt <- tab[,1] + tab[,2]

score 1 · Accepted Answer · answered Mar 24 '14 at 11:44

1

So, you wanna check out the reshape2 package and the cast function. Check out the code below:

# install.packages('reshape2') # only run if you haven't downloaded before
library(reshape2)

datCast <- dcast(dat, OrderNo ~ IVR.Agent, fun.aggregate = length, margins = T)

datCast
   OrderNo Agent IVR (all)
1       A1     0   1     1
2       A2     1   0     1
3       A3     1   0     1
4       A4     1   0     1
5       A5     0   1     1
6       B1     1   0     1
7       B2     0   1     1
8       B3     1   0     1
9       B4     1   0     1
10      B5     1   0     1
11   (all)     7   3    10

answered Mar 24 '14 at 11:44

maloneypatr

3,562
4
23
33

I got the below error after i ran the code. datCast <- dcast(newdata, OrderNo ~ IVR.Agent,fun.aggregate=length,margins=T) Error in `[.data.frame`(df, vars) : undefined columns selected – Shoaibkhanz Mar 24 '14 at 12:11
Do you have `reshape2` loaded? R's also case sensitive. Can you confirm the capitalized letters? – maloneypatr Mar 24 '14 at 12:52
Yes reshape is loaded, – Shoaibkhanz Mar 24 '14 at 13:03
library(reshape2) newdata <- data.frame(IVA$OrderNo, IVA$IVR.Agent ) datCast <- dcast(newdata, OrderNo ~ IVR.Agent,fun.aggregate=length,margins=T) – Shoaibkhanz Mar 24 '14 at 13:04
@Shoaibkhanz, reshape != reshape2. – A5C1D2H2I1M1N2O1R2T1 Mar 24 '14 at 13:40
Ahh, I see what you did. Try: newdata <- data.frame(OrderNo = IVA$OrderNo, IVR.Agent = IVA$IVR.Agent). With not defining the names of the columns, it defaults to to include everything. – maloneypatr Mar 24 '14 at 13:41
> newdata <- data.frame(OrderNo= IVA$OrderNo, IVR.Agent= IVA$IVR.Agent ) > datCast <- dcast(newdata, OrderNo ~ IVR.Agent,fun.aggregate=length,margins=T) Using IVR.Agent as value column: use value.var to override. – Shoaibkhanz Mar 24 '14 at 13:51
I got this error;Using IVR.Agent as value column: use value.var to override. – Shoaibkhanz Mar 24 '14 at 13:51
@AnandaMahto that was a typo, I am indeed using reshape2 – Shoaibkhanz Mar 24 '14 at 13:52
I don't think that's an error, but a message. It's defaulting to IVR.Agent – maloneypatr Mar 24 '14 at 13:58

Pivot like number manipulation in R

2 Answers2