I want to add rows to a dataframe based on a columns values for each row so a string value of (1:2:3) will create a new column and add rows for that column as described in the example below:
I have this kind of data:
Col1 | Col2
1 | 1:2:3
2 | 4:5
I want to transform it to look like this:
Col1 | Col2
1 | 1
1 | 2
1 | 3
2 | 4
2 | 5
I know that this can be done using nested for loops, but I'm sure there's a better way to do it.
Do split and explode
df=df.assign(Col2=df.Col2.str.split(':')).explode('Col2')
Out[161]:
Col1 Col2
0 1 1
0 1 2
0 1 3
1 2 4
1 2 5
df = pd.DataFrame({'Col1':[1,2],'Col2':['1:2:3','4:5']})
Split the values in Col2 so they are lists and explode.
>>> df['Col2'] = df.apply(lambda x: x['Col2'].split(':'), axis = 1)
>>> df.explode('Col2')
Col1 Col2
0 1 1
0 1 2
0 1 3
1 2 4
1 2 5
