How do I split out a column but keep the key for each entry?

Question

I have a dataframe that looks like this:

df = pd.DataFrame({"ID": ["P-1", "P-2", "P-3"], "Acc No": ['11234/57468/28576', '13245/13246', '46578/37264/35264'], "Company": ["red", "yellow", "blue"]})

print(df)

I would like to split out the 'Acc No' column so that each entry keeps the data from the remaining columns e.g.

df2 = pd.DataFrame({"ID": ["P-1","P-1","P-1", "P-2","P-2", "P-3","P-3","P-3"], "Acc No": ['11234','57468','28576', '13245','13246', '46578','37264','35264'], "Company": ["red", "red","red","yellow", "yellow", "blue", "blue", "blue"]})

print(df2)

I've tried splitting out the Acc No. column, which works but I can't find the correct way to match the new columns to the existing data.

How can I do this?

score 0 · Accepted Answer · answered Sep 19 '22 at 15:50

try this:

#df

ID  Acc No                  Company
0   P-1 11234/57468/28576   red
1   P-2 13245/13246         yellow
2   P-3 46578/37264/35264   blue

df['Acc No'] = df['Acc No'].str.split('/')

#df
    ID  Acc No                  Company
0   P-1 [11234, 57468, 28576]   red
1   P-2 [13245, 13246]          yellow
2   P-3 [46578, 37264, 35264]   blue

df.explode('Acc No')

    ID  Acc No  Company
0   P-1 11234   red
0   P-1 57468   red
0   P-1 28576   red
1   P-2 13245   yellow
1   P-2 13246   yellow
2   P-3 46578   blue
2   P-3 37264   blue
2   P-3 35264   blue

Thanks worked perfectly. I didn't know the explode function existed, very clever — routine_dizzy, Sep 20 '22 at 14:42

How do I split out a column but keep the key for each entry?

1 Answers1