Everyday R code (17) Pivot table in R to replace excel

Pivot table in R to replace excel

 

Step1

#converting data format using data.table

library(data.table)

live<-data.table(live)

 

Step2

#finding unique count for each bucket using list

uniqueData<-live[,list(Unique_user_Count=length(unique(User_ID))),by=list(Market, Company,Group)]

 

Step3

#pivot the table using dcast function in reshape2 package

#install the package if you haven’t

install.packages(“reshape2″)

library(reshape2)

pivot<-dcast(uniqueData, Market+Company ~ Group , value.var=”Unique_user_Count”, fun.aggregate=sum)

 

Step4 Finished and print

pivot

 

Dataset used:

 

Initial table “live”

Market	Company	Group	User_ID
Market1	Company1	Group2	1
Market1	Company2	Group2	2
Market1	Company1	Group4	3
Market1	Company2	Group3	4
Market2	Company1	Group3	5
Market2	Company1	Group3	6
Market2	Company2	Group1	7
Market2	Company2	Group4	8
Market3	Company3	Group1	9

 

Middle table in the process “uniqueData”

Market	Company	Group	Unique_user_Count
Market1	Company1	Group2	1
Market1	Company2	Group2	1
Market1	Company1	Group4	1
Market1	Company2	Group3	1
Market2	Company1	Group3	2
Market2	Company2	Group1	1
Market2	Company2	Group4	1
Market3	Company3	Group1	1

 

Final table “pivot”

Market	Company	Group1	Group2	Group3	Group4
Market1	Company1	0	1	0	1
Market1	Company2	0	1	1	0
Market2	Company1	0	0	2	0
Market2	Company2	1	0	0	1
Market3	Company2	1	0	0	0