Append Dataframe to previously existing MultiIndex DataFrame

Question

Suppose I have a MultiIndex DataFrame that looks as such:

           C1   C2    C3
A1   B1
     B2
A2   B1
     B2

How can I append the following dataframe:

      C1   C2    C3
B3
B4
B5
B6

underneath A1 so that the final product looks like this:

           C1   C2    C3
A1   B1
     B2
     B3
     B4
     B5
     B6
A2   B1
     B2

Roshan Santhosh · Accepted Answer · 2020-01-04 03:35:32Z

1

You will have to reset the indices of both the dataframes and then merge them, before setting the multi-index again.

For my example, my index names are cp_name and products, original multi-index dataframe is d and single index dataframe is temp.


#Setting the second index as a variable in the single-index dataframe
temp['cp_name'] = 'A'

#Resetting the indices
d_ = d.reset_index()
temp_ = temp.reset_index()

out = pd.concat([d_, temp_])


out.sort_values(['cp_name','products']).set_index(['cp_name','products'])

Original Datasets :


                  le_id  run_seq  cp_id  tran_amnt currency  current
cp_name products                                                    
A       U           101        1    201        100      USD    201.0
B       U           102        1    202        200      USD      NaN
        V           103        1    202        672      INR      NaN

          le_id  run_seq  cp_id cp_name  tran_amnt currency current
products                                                           
X           104        3    205       E        437      SGD     NaN
V           102        3    203       C        783      INR     NaN

Final output

                  cp_id currency current  le_id  run_seq  tran_amnt
cp_name products                                                   
A       U           201      USD     201    101        1        100
        V           203      INR     NaN    102        3        783
        X           205      SGD     NaN    104        3        437
B       U           202      USD     NaN    102        1        200
        V           202      INR     NaN    103        1        672

answered Jan 4, 2020 at 3:35

Roshan Santhosh

6874 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

abdoulsn Over a year ago

But how it find out the cp_name of X which was not in two datasets? I notice it add it just in the first cp_name A.

Roshan Santhosh Over a year ago

The question asked to explicitly add the second single-index dataframe under cp_name A of the first multi-index dataframe. Therefore, I set cp_name to A for the second dataset using temp['cp_name'] = 'A'

Collectives™ on Stack Overflow

Append Dataframe to previously existing MultiIndex DataFrame

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related