I have a string column in dataframe like this:
ID col1
id1 AA's 2015:45,BB:96
id2 Jigga:91,OO:73,BB:34
I want to create a new dataframe out of this which can take the shape:
ID var1 var2 var3 var4
id1 45 96 0 0
id2 0 34 91 73
where var1=AA's 2015,var2=BB,var3=Jigga,var4=OO
I have stored all distinct values of string's first values in a list like this:
["AA's 2015","BB","Jigga","OO"]
I want to iterate through this list and for each value create a variable var[i] which will take up it's value from col1 for that particular ID.
I can use the for loop for iterating through the list. But how to lookup the value and put in var[i]?
Any ideas will be appreciated