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 |