Pandas: Split cell into multiple colums, write NaN

user_11072883 0
Viewed 82

I have the following df:

df = pd.DataFrame({'Category': ['root:catA', 'root:catA:catB'], 'Age':[32, 34]})

Now I want to split the column Category by : into, in total 4, columns. Since there will be less columns because row0 has will result in 4 filled columns, row1 in 5 filled columns, the rest of the columns should be filled with NaN.

Expected output:

df = pd.DataFrame({'Category': ['root:catA', 'root:catA:catB'], 'Age':[32, 34], 'Cat1':['root', 'root'], 'Cat2':['catA', 'catA'], 'Cat3':['NaN', 'CatB'], 'Cat4':['NaN', 'NaN']})

I tried the following:

df[['cat1','cat2', "cat3", "cat4"]] = df.Category.str.split(":",expand=True)

But I am getting a ValueError: Columns must be same length as key

pandas dataframe python
2 Answers

Almost there, you just needed an additional reindex step:

df['Category'].str.split(':', expand=True).reindex(range(4), axis=1)                                                   

      0     1     2   3
0  root  catA  None NaN
1  root  catA  catB NaN

Now, assigning will work.

Alternatively, join them together.

(df['Category'].str.split(':', expand=True)
               .reindex(range(4), axis=1)
               .rename(lambda x: f'cat{x+1}', axis=1)
               .join(df))

   cat1  cat2  cat3  cat4        Category  Age
0  root  catA  None   NaN       root:catA   32
1  root  catA  catB   NaN  root:catA:catB   34
user_49090870

Your expand only gives 3 columns, you need to remove Cat4 and assign later:

df[['cat1','cat2', "cat3"]] = df.Category.str.split(':', expand=True)
df['cat4'] = np.nan
user_42384080
Related