Split values in Data frame columns

Question

I have a Data Frame name df and I want to remove this '|' in fuel column

id  car       fuel
1   Mercedes  petrol|diesel|gas
2   Audi      gas|petrol

So that my data look like this

id  car        fuel
1   Mercedes   petrol
1   Mercedes   diesel
1   Mercedes   gas
2   Audi       gas
2   Audi       petrol

This is the code that I tried

df_1 = hb.copy()
df_2 = hb.copy()
df_3 = hb.copy()

df_1['fuel'] = df_1['fuel'].apply(lambda x:x.split('|')[0])
df_2['fuel'] = df_2['fuel'].apply(lambda x:x.split('|')[1])
df_3['fuel'] = df_3['fuel'].apply(lambda x:x.split('|')[2])

And this give IndexError: list index out of range

Possible duplicate of [Split (explode) pandas dataframe string entry to separate rows](https://stackoverflow.com/questions/12680754/split-explode-pandas-dataframe-string-entry-to-separate-rows) — shaik moeed, Jul 02 '19 at 07:36

score 1 · Accepted Answer · answered Jul 02 '19 at 08:40

Try this:

    df=pd.DataFrame({'car':['Mercedes','Audi'],'fuel':['petrol|diesel|gas','gas|petrol']}) #your dataframe
    df2=pd.DataFrame()                                       #new black dataframe
    for i in range(0,len(df)):                               #iterating over df
        list1=df.iloc[i,1].split('|')                        #split each value of 'fuel' and store it in a list
        for j in range(0,len(list1)):                        #iterating over list1
            list2={'car':df.iloc[i,0],'fuel':list1[j]}       #make a dict of each combination of 'car' and elements of list1-'fuel'
            df2=df2.append(list2,ignore_index=True)          #append each value to the blank df

score 1 · Answer 2 · answered Jul 02 '19 at 08:48

This is one approach.

Ex:

df = pd.DataFrame({
        "id":[1,2],
        "car":["Mercedes","Audi"],
        "fuel":["petrol|diesel|gas","gas|petrol"]
        })
df["fuel"] = df["fuel"].str.split("|")
#Ref https://stackoverflow.com/a/48532692/532312
lst_col = 'fuel'
df = pd.DataFrame({
      col:np.repeat(df[col].values, df[lst_col].str.len())
      for col in df.columns.drop(lst_col)}
    ).assign(**{lst_col:np.concatenate(df[lst_col].values)})[df.columns]
print(df)

Output:

        car    fuel  id
0  Mercedes  petrol   1
1  Mercedes  diesel   1
2  Mercedes     gas   1
3      Audi     gas   2
4      Audi  petrol   2

score 0 · Answer 3 · answered Jul 02 '19 at 08:12

You can try something like this:

#Create the dataframe
df = pd.DataFrame({
        "id":[1,2],
        "car":["Mercedes","Audi"],
        "fuel":["petrol|diesel|gas","gas|petrol"]
        })

#Create a new dataframe from the series, with car as the index
new_df = pd.DataFrame(df.fuel.str.split('|').tolist(), index=df.car).stack()

#Get rid of the secondary index
new_df = new_df.reset_index([0, 'car'])

#Add the 'id' back to the dataframe
#Note: There is probably a much more elegant way of doing this
new_df.loc[:,'id'] = new_df.car.apply(lambda x: df[df.loc[:,'car'] == x].id.values[0])

#Rename the columns
new_df.columns = ["car","fuel","id"]

Thanks for your answer but this is not the right answer which I am finding for large data set. — Arsh Singh, Jul 02 '19 at 08:26

Split values in Data frame columns

3 Answers3