I have a dataframe that looks kind of like this with many more rows and columns:
> df <- data.frame(country = c ("Australia","Australia","Australia","Angola","Angola","Angola","US","US","US"), year=c("1945","1946","1947"), leader = c("David", "NA", "NA", "NA","Henry","NA","Tom","NA","Chris"), natural.death = c(0,NA,NA,NA,1,NA,1,NA,0),gdp.growth.rate=c(1,4,3,5,6,1,5,7,9))
> df
country year leader natural.death gdp.growth.rate
1 Australia 1945 David 0 1
2 Australia 1946 NA NA 4
3 Australia 1947 NA NA 3
4 Angola 1945 NA NA 5
5 Angola 1946 Henry 1 6
6 Angola 1947 NA NA 1
7 US 1945 Tom 1 5
8 US 1946 NA NA 7
9 US 1947 Chris 0 9
I am trying to add x number of new columns, where x corresponds to the number of unique leaders (column leader) satisfying the condition of leader being dead (natural.death==1). In the case of this df, I would expect to get 2 new columns for Henry and Tom, with values of 0,0,0,0,1,0,0,0,0 and 0,0,0,0,0,0,1,0,0, respectively. I would preferably have the two new columns called id1 and id2 according to the order of data presented in natural.death. I need to create 69 new columns as there 69 leaders who died, so I am looking for a non-manual method to deal with this.
I already tried loops, if, for, unique, mtabulate, dcast, dummies but I could not get anything work unfortunately.
I am hoping to get:
> df <- data.frame(country = c ("Australia","Australia","Australia","Angola","Angola","Angola","US","US","US"), year=c("1945","1946","1947"), leader = c("David", "NA", "NA", "NA","Henry","NA","Tom","NA","Chris"), natural.death = c(0,NA,NA,NA,1,NA,1,NA,0),gdp.growth.rate=c(1,4,3,5,6,1,5,7,9),
+ id1=c(0,0,0,0,1,0,0,0,0),id2=c(0,0,0,0,0,0,1,0,0))
> df
country year leader natural.death gdp.growth.rate id1 id2
1 Australia 1945 David 0 1 0 0
2 Australia 1946 NA NA 4 0 0
3 Australia 1947 NA NA 3 0 0
4 Angola 1945 NA NA 5 0 0
5 Angola 1946 Henry 1 6 1 0
6 Angola 1947 NA NA 1 0 0
7 US 1945 Tom 1 5 0 1
8 US 1946 NA NA 7 0 0
9 US 1947 Chris 0 9 0 0
Here is a crude way to do this
df <- data.frame(country = c ("Australia","Australia","Australia","Angola","Angola","Angola","US","US","US"), year=c("1945","1946","1947"), leader = c("David", "NA", "NA", "NA","Henry","NA","Tom","NA","Chris"), natural.death = c(0,NA,NA,NA,1,NA,1,NA,0),gdp.growth.rate=c(1,4,3,5,6,1,5,7,9))
tmp=which(df$natural.death==1) #index of deaths
lng=length(tmp) #number of deaths
#create matrix with zeros and lng columns, append to df
df=cbind(df,data.frame(matrix(0,nrow=nrow(df),ncol=lng)))
#change the newly added column names
colnames(df)[(ncol(df)-lng+1):ncol(df)]=paste0("id",1:lng)
for (i in 1:lng) { #loop over new columns
df[tmp[i],paste0("id",i)]=1 #at index i of death and column id+i set df to 1
}
country year leader natural.death gdp.growth.rate id1 id2
1 Australia 1945 David 0 1 0 0
2 Australia 1946 NA NA 4 0 0
3 Australia 1947 NA NA 3 0 0
4 Angola 1945 NA NA 5 0 0
5 Angola 1946 Henry 1 6 1 0
6 Angola 1947 NA NA 1 0 0
7 US 1945 Tom 1 5 0 1
8 US 1946 NA NA 7 0 0
9 US 1947 Chris 0 9 0 0