Append row to DataFrame in Pandas and putting it on bottom-CodePudding

I want to add a row to a multi-index dataframe and I want to group it in its outer index where the alphabetical order is important, i.e, I can't use df.sort_index().

Here is the problem.

Code:

import pandas as pd
import numpy as np

categories = {"A":["c", "b", "a"] , "B": ["a", "b", "c"], "C": ["a", "b", "d"] }
array = []
expected_fields = []
for key, value in categories.items():
    array.extend([key]* len(value))
    expected_fields.extend(value)
    
arrays = [array ,expected_fields]

tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples)
df =  pd.Series(np.random.randn(9), index=index)
df["A", "d"] = 2

print(df)

Output:

A  c    0.887137
   b   -0.105262
   a   -0.180093
B  a   -0.687134
   b   -1.120895
   c    2.398962
C  a   -2.226126
   b   -0.203238
   d    0.036068
A  d    2.000000 <------------
dtype: float64

Expected output:

A  c    0.887137
   b   -0.105262
   a   -0.180093
   d    2.000000  <--------------
B  a   -0.687134
   b   -1.120895
   c    2.398962
C  a   -2.226126
   b   -0.203238
   d    0.036068
dtype: float64

CodePudding user response：

df.loc[['A', 'B', 'C']]

output:

A  c    0.887137
   b   -0.105262
   a   -0.180093
   d    2.000000
B  a   -0.687134
   b   -1.120895
   c    2.398962
C  a   -2.226126
   b   -0.203238
   d    0.036068
dtype: float64

if you want get ['A', 'B', 'C'] by code, use following

idx0 = df.index.get_level_values(0).unique()
df.loc[idx0]

same result