Python Pandas split list by delimiter into own columns

Question

new to python and still learning. Have tried many posts already but none are working. Might need help with the syntax etc. 2 parts to my question:

First part - I want to split column 'resources' by every unique value and make new columns from them. Similar to the picture below with the columns highlighted yellow enter image description here

CustID, Resources, 100, 200, 30, 50
222, 100;200;30;50, 1 , 1, 1, ,1

The second way - i want to split 'resources' by every unique value into one column. This might lead to duplication of rows which is fine... Similar to the below picture enter image description here

CustID, Resources, New Column
222, 100;200;30;50, 100
222, 100;200;30;50, 200
222, 100;200;30;50, 30
222, 100;200;30;50, 50

`df.assign(resources=df['resources'].str.split(';')).explode('resources')` — Erfan, Feb 28 '21 at 19:38
Add data as text pls, it's not possible to copy a picture to reproduce your problem. — Erfan, Feb 28 '21 at 19:43

score 1 · Accepted Answer · answered Feb 28 '21 at 20:02

For your first question, use str.get_dummies:

dummies = df['Resources'].str.get_dummies(sep=";")
df = pd.concat([df, dummies], axis=1)

   CustID      Resources  100  200  30  50
0     222  100;200;30;50    1    1   1   1

For the second question, the explode solution given in comments works:

df = df.assign(
    Resources=df['Resources'].str.split(';')
).explode('Resources', ignore_index=True)
   CustID Resources
0     222       100
1     222       200
2     222        30
3     222        50

Python Pandas split list by delimiter into own columns

1 Answers1