I have a large data set with around 1 million records and 80 columns. To speed up processing, I am using data.table. I need to create a new column based on a condition and I am lost as to how to do this in data.table
Below is the code for sample data:
set.seed(1200)
N_Blocks = 1348
cyc=200
City1 <- vector()
City2 <- vector()
a1 <- vector()
a2 <- vector()
for (a in 1:cyc) {
City1 <- sample(paste("City", formatC(a, width=nchar(cyc), flag="0"), sep=""),N_Blocks,rep=T)
a1 <- sample(0:1,N_Blocks,rep = T)
City2 <- append(City2,City1)
a2 <- append(a2,a1)
}
df1 <- data.frame(City2,a2)
Now the requirement is that for each city (currently we are having 200 cities in this sample data) and for a2 == 1 I need to create a new column that will have the total number of 1s divided in 12 months. So for example City001 & a2 == 1 with the seed of 1200 I get 671 records. So the new column Months needs to have codes 01-12. So the 1st 56 records where a2 == 1 will have code 01 then next 56 records will have code 02 and so on.....and the last 55 records of City001 with a2 == 1 will have code 12 (so the total adds to 671). Something like splitting the selection of a2 for every City in 12 months.
We can get the City level summary of selection from the command -
table(df1$City2,df1$a2)
Can we achieve this using data.table?