Split a delimited column but keep rows

ryda 0
Viewed 56

I have over 100 columns and 1000 rows. Some columns have duplicate pipe delimiters on consecutive rows. I'd like to separate it to a single string for each row.

Original Dataframe

**Cars  Color   Customization   Type**
Buick   yellow  radio   Car
Chevy   gray|brown|blue radio | exhaust truck
Chevy   gray|brown|blue radio | exhaust truck
Chevy   gray|brown|blue radio | exhaust truck
Mercedes    red|blue    seats| carpets  car
Mercedes    red|blue    seats| carpets  car

 df = pd.DataFrame ({'Cars': ['Buick', 'Chevy', 'Chevy', 'Chevy', 'Mercedes', 'Mercedes'], 
'Color': ['yellow', 'gray|brown|blue', 'gray|brown|blue', 'gray|brown|blue', 'red|blue', 'red|blue'],
 'Customization': ['radio', 'radio|exhaust', 'radio|exhaust', 'radio|exhaust', 'seats|carpets', 'seats|carpets'],
 'Type': ['car', 'truck', 'truck', 'truck', 'car', 'car']})

This how I'd like it to look

**Cars  Color   Customization   Type**
Buick   yellow  radio   Car
Chevy   gray    radio   truck
Chevy   brown   exhaust truck
Chevy   blue        truck
Mercedes    red seats   car
Mercedes    blue    carpets car

So far I used: df = df.apply(lambda x: x.str.split('|'), axis = 1) to create lists of the piped columns. Using explode() creates new rows which is not what I want it to do.

dataframe python
1 Answers

Try this :

import pandas as pd
from io import StringIO

s = """Cars Color   Customization   Type
Buick   yellow  radio   Car
Chevy   gray|brown|blue radio|exhaust   truck
Chevy   gray|brown|blue radio|exhaust   truck
Chevy   gray|brown|blue radio|exhaust   truck
Mercedes    red|blue    seats|carpets   car
Mercedes    red|blue    seats|carpets   car
"""

df = pd.read_csv(StringIO(s), delimiter='\t').drop_duplicates()

out = (df.set_index('Cars').apply(lambda x: x.str.split('|'))
              .apply(lambda x: x.apply(pd.Series).stack())
              .reset_index()
              .drop(columns=['level_1'])
       )

>>> display(out)

enter image description here

abokey0
Related