I have a table 1 that contains numbers. I would like to create rows that the number of rows comes from Total column. Other column values would be created randomly according to table 1 column values. For instance, table 2 column1 value should has 3 "1" value and rest should be "0". Is there anyone can help me with this?
Table 1
Year Age Total column1 column2 column3
2017 15 10 3 4 2
Desired Table
Year Age Total column1 column2 column3
2017 15 1 1 0 0
2017 15 1 0 1 0
2017 15 1 0 0 1
2017 15 1 0 0 1
2017 15 1 1 0 0
2017 15 1 0 1 0
2017 15 1 1 0 0
2017 15 1 0 1 0
2017 15 1 0 1 0
2017 15 1 0 0 0
Instead of having three columns with mutually exlusive binary entries, it might be simpler to just convert it froma wide to long format and then expand it:
df<-data.frame(year=2017,age=15,col1=3,col2=4,col3=2)
library(dplyr)
library(tidyr)
df %>%
gather('key','value',col1:col3) %>%
# year age key value
# 1 2017 15 col1 3
# 2 2017 15 col2 4
# 3 2017 15 col3 2
filter(value>0) %>% # Avoid keys with 0 values
group_by(year,age,key) %>%
expand(value=1:value)%>%
# year age key value
# <dbl> <dbl> <chr> <int>
# 1 2017 15 col1 1
# 2 2017 15 col1 2
# 3 2017 15 col1 3
# 4 2017 15 col2 1
# 5 2017 15 col2 2
# 6 2017 15 col2 3
# 7 2017 15 col2 4
# 8 2017 15 col3 1
# 9 2017 15 col3 2
ungroup()%>%select(-value)
# # A tibble: 9 x 3
# year age key
# <dbl> <dbl> <chr>
# 1 2017 15 col1
# 2 2017 15 col1
# 3 2017 15 col1
# 4 2017 15 col2
# 5 2017 15 col2
# 6 2017 15 col2
# 7 2017 15 col2
# 8 2017 15 col3
# 9 2017 15 col3