pandas MultiIndex: insert a row at top and keep first level of index hidden-CodePudding

In MultiIndex dataframe:

import pandas as pd
a = [['a', 'b', 2, 3], ['c', 'b', 5, 6], ['a','c', 8, 9]]
df = pd.DataFrame(a, columns=['I1', 'I2', 'v1', 'v2'])
df = df.groupby(['I1', 'I2']).first()

I want to insert a row ex at top and keep the first level of MultiIndex hidden. The expected result is

I tried concat:

data_ex = {'v1':[99], 'v2': [98]}
df_ex = pd.DataFrame(data_ex, index = [('ex','ex')])
pd.concat([df_ex, df])

However it become

I also tried first concat without index, then groupby multiply index. But pandas will automatically sort by MultiIndex. As a result, ex row cannot be set at top.

CodePudding user response：

You need pass the correct index format

df_ex = pd.DataFrame(data_ex, index = pd.MultiIndex.from_tuples([('ex','ex')],names=["I1", "I2"]))

pd.concat([df_ex, df])
Out[783]: 
       v1  v2
I1 I2        
ex ex  99  98
a  b    2   3
   c    8   9
c  b    5   6

CodePudding user response：

You need to create a pd.MultiIndex first.

import pandas as pd
a = [['a', 'b', 2, 3], ['c', 'b', 5, 6], ['a','c', 8, 9]]
df = pd.DataFrame(a, columns=['I1', 'I2', 'v1', 'v2'])
df = df.groupby(['I1', 'I2']).first()

data_ex = {'v1':[99], 'v2': [98]}
df_ex = pd.DataFrame(data_ex, index = [('ex','ex')])
# create multi-index from the index of tuples
df_ex.index = pd.MultiIndex.from_tuples(df_ex.index)
# name the columns of the multi-index
df_ex.index.names = ["I1", "I2"]

pd.concat([df_ex, df], axis=0)