Pandas: Add an empty row after every index in a MultiIndex dataframe

Question

Consider below df:

              IA1  IA2  IA3
Name Subject               
Abc  DS        45   43   34
     DMS       43   23   45
     ADA       32   46   36
Bcd  BA        45   35   37
     EAD       23   45   12
     DS        23   35   43
Cdf  EAD       34   33   23
     ADA       12   34   25

How can I add an empty row after each Name index?

Expected output:

              IA1  IA2  IA3
Name Subject               
Abc  DS        45   43   34
     DMS       43   23   45
     ADA       32   46   36

Bcd  BA        45   35   37
     EAD       23   45   12
     DS        23   35   43

Cdf  EAD       34   33   23
     ADA       12   34   25

jezrael · Accepted Answer · 2021-01-12 07:17:15Z

Use custom function for add empty rows in GroupBy.apply:

def f(x):
    x.loc[('', ''), :] = ''
    return x

Or:

def f(x):
    return x.append(pd.DataFrame('', columns=df.columns, index=[(x.name, '')]))

df = df.groupby(level=0, group_keys=False).apply(f)
print (df)
             IA1 IA2 IA3
Name Subject            
Abc  DS       45  43  34
     DMS      43  23  45
     ADA      32  46  36
                        
Bcd  BA       45  35  37
     EAD      23  45  12
     DS       23  35  43
                        
Cdf  EAD      34  33  23
     ADA      12  34  25

anky · Accepted Answer · 2021-01-12 07:37:41Z

Adding another way using df.reindex and fill_value as '' after using pd.MultiIndex.from_product and Index.union and then sorting it.

idx = df.index.union(pd.MultiIndex.from_product((df.index.levels[0],[''])),sort=False)
out = df.reindex(sorted(idx,key=lambda x: x[0]),fill_value='')

print(out)

             IA1 IA2 IA3
Name Subject            
Abc  DS       45  43  34
     DMS      43  23  45
     ADA      32  46  36
                        
Bcd  BA       45  35  37
     EAD      23  45  12
     DS       23  35  43
                        
Cdf  EAD      34  33  23
     ADA      12  34  25

We use sort=False when using Index.union the index so order is retained , then using sorted on the first element returns:

sorted(idx,key=lambda x:x[0])

[('Abc', 'DS'),
 ('Abc', 'DMS'),
 ('Abc', 'ADA'),
 ('Abc', ''),
 ('Bcd', 'BA'),
 ('Bcd', 'EAD'),
 ('Bcd', 'DS'),
 ('Bcd', ''),
 ('Cdf', 'EAD'),
 ('Cdf', 'ADA'),
 ('Cdf', '')]

Ferris · Accepted Answer · 2021-01-12 07:38:32Z

# reset index 
dfn = df.reset_index()
# find the border idx of 'Name', [2, 5, 7]
idx_list = dfn.drop_duplicates('Name', keep='last').index
# use the border idx, create an empty df, and append to the origin df, then sort the index
df_append = pd.DataFrame('', index = idx_list, columns = dfn.columns)
obj = dfn.append(df_append).sort_index().set_index(['Name', 'Subject'])
print(obj)

             IA1 IA2 IA3
Name Subject            
Abc  DS       45  43  34
     DMS      43  23  45
     ADA      32  46  36
                        
Bcd  BA       45  35  37
     EAD      23  45  12
     DS       23  35  43
                        
Cdf  EAD      34  33  23
     ADA      12  34  25

Collectives™ on Stack Overflow

Pandas: Add an empty row after every index in a MultiIndex dataframe

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related