How to split a list-column in a Pandas Dataframe based on a condition on the elements of the list?

user_5560529 0
Viewed 110

I have a pandas DataFrame with one column, say:

df = pd.DataFrame({"combined_list": [["Netherlands|NL", "Germany|DE", "United_States|US", "Poland|PL"], ["Netherlands|NL", "Austria|AU", "Belgium|BE"], ["United_States|US", "Germany|DE"]]})

I want to create two columns from the combined_list column:

  • One containing al the normal country names (So everything before the last occurence of |)
  • One column containing all 2 letter abbreviations (The length of these is always 2) , so basically all remaining text after the last occurence of |

The resulting Dataframe should look like this:

countries                                      abbreviations
[Netherlands, Germany, United_States, Poland]  [NL, DE, US, PL]
[Netherlands, Austria, Belgium]                [NL, AU, BE]
[United_States, Germany]                       [US, DE]

How to achieve this?

I know if the column of the Dataframe would simply be one string, I could use various string split functions to achieve it, but can't find anything for columns of lists

pandas dataframe python
3 Answers
df_out = pd.DataFrame(
    df["combined_list"]
    .apply(lambda x: list(zip(*[s.split("|") for s in x])))
    .tolist(),
    columns=["countries", "abbreviations"],
)
print(df_out)

Prints:

                                       countries     abbreviations
0  (Netherlands, Germany, United_States, Poland)  (NL, DE, US, PL)
1                (Netherlands, Austria, Belgium)      (NL, AU, BE)
2                       (United_States, Germany)          (US, DE)

To have lists in the columns:

df_out = pd.DataFrame(
    df["combined_list"]
    .apply(lambda x: list(map(list, zip(*[s.split("|") for s in x]))))
    .tolist(),
    columns=["countries", "abbreviations"],
)
print(df_out)

Prints:

                                       countries     abbreviations
0  [Netherlands, Germany, United_States, Poland]  [NL, DE, US, PL]
1                [Netherlands, Austria, Belgium]      [NL, AU, BE]
2                       [United_States, Germany]          [US, DE]
user_100359850

Use explode and then convert to a dataframe, with groupby.agg

out_cols= ["countries", "abbreviations"]
out =(df['combined_list'].explode().str.split("|",expand=True)
      .groupby(level=0).agg(list).set_axis(out_cols,axis=1))

print(out)

                                       countries     abbreviations
0  [Netherlands, Germany, United_States, Poland]  [NL, DE, US, PL]
1                [Netherlands, Austria, Belgium]      [NL, AU, BE]
2                       [United_States, Germany]          [US, DE]
user_98406370

Here is another solution:

df2 = pd.DataFrame()
df2['Countries'] = df.apply(lambda row:[row['combined_list'][i].split('|')[0] for i in range(len(row['combined_list']))], axis=1)
df2['Abbreviations'] = df.apply(lambda row:[row['combined_list'][i].split('|')[1] for i in range(len(row['combined_list']))], axis=1)

print(df2)

                                       Countries     Abbreviations
0  [Netherlands, Germany, United_States, Poland]  [NL, DE, US, PL]
1                [Netherlands, Austria, Belgium]      [NL, AU, BE]
2                       [United_States, Germany]          [US, DE]
user_91620000
Related