How do I merge cells inside a dataframe?-CodePudding

I have a dataframe:

I want to process it through pandas and turn it into this format:

Can you tell me what method can be used to achieve this effect?

CodePudding user response：

As @mozway explains in the comments, you can use MultiIndex to get the desired output:

df.columns = pd.MultiIndex.from_tuples([('', 'Number'), ('', 'Name')]   list(map(lambda x: tuple(x.split()), df.loc[:,'January sales':'March commission'].columns)))

CodePudding user response：

If you want a fully programmatic method of converting a simple index to MultiIndex based on word split and using empty strings as filing values, use pandas.MultiIndex combined with itertools.zip_longest:

from itertools import zip_longest
df.columns = pd.MultiIndex.from_arrays(zip_longest(*df.columns.map(str.split), fillvalue=''))

Output:

   x  y   Jan        Feb     
        sales comm sales comm
0  .  .     .    .     .    .

Used dummy input:


df = pd.DataFrame(columns=['x', 'y', 'Jan sales', 'Jan comm', 'Feb sales', 'Feb comm'])
df.loc[0] = '.'