pandas str split and apply, create multiindex df

user_3608005 0
Viewed 129

Splitting columns by patterns is easy:

import pandas as pd

_df = pd.DataFrame([['1 / 2 / 3', '4 / 5 / 6'], ['7 / 8 / 9', '10 / 11 / 12']])
_df.apply(lambda x: x.str.split(' / '))

           0             1
0  [1, 2, 3]     [4, 5, 6]
1  [7, 8, 9]  [10, 11, 12]

But how can I create a dataframe using expand=True as multiindex? I do not know where I could pass the index.

_df.apply(lambda x: x.str.split(' / ', expand=True))
ValueError: If using all scalar values, you must pass an index

Expected output (names of columns are not important, can be arbitrary):

           A         B
    a  b  c    a  b  c
0   1  2  3    4  5  6
1   7  8  9   10 11 12
pandas python
2 Answers

here is one way using df.stack and unstack with a little help using swaplevel:

s=_df.stack().str.split(' / ')
out = (pd.DataFrame(s.tolist(),index=s.index).unstack()
      .swaplevel(axis=1).sort_index(axis=1))

   0         1        
   0  1  2   0   1   2
0  1  2  3   4   5   6
1  7  8  9  10  11  12

To match the specific output, we can use:

from string import ascii_lowercase
out.rename(columns=dict(enumerate(ascii_lowercase)))

   a         b        
   a  b  c   a   b   c
0  1  2  3   4   5   6
1  7  8  9  10  11  12

Or better yet :)

from string import ascii_lowercase, ascii_uppercase
out.rename(columns=dict(enumerate(ascii_uppercase)), level=0, inplace=True)
out.rename(columns=dict(enumerate(ascii_lowercase)), level=1, inplace=True)


print(out)

   A         B        
   a  b  c   a   b   c
0  1  2  3   4   5   6
1  7  8  9  10  11  12
user_98406370

IIUC pd.concat

s=pd.concat([pd.DataFrame(df[x].str.split(' / ').tolist()) for x in df.columns], axis=1, keys=['a','b'])
   a         b        
   0  1  2   0   1   2
0  1  2  3   4   5   6
1  7  8  9  10  11  12
user_79645270
Related