Insert one dataframe into another in Python

Question

Hi I have the following two DataFrame's (index level == 2):

df1 = pd.DataFrame()
df1["Index1"] = ["A", "AA"]
df1["Index2"] = ["B", "BB"]
df1 = df1.set_index(["Index1", "Index2"])
df1["Value1"] = 1
df1["Value2"] = 2
df1

df2 = pd.DataFrame()
df2["Index1"] = ["X", "XX"]
df2["Index2"] = ["Y", "YY"]

df2["Value1"] = 3
df2["Value2"] = 4
df2 = df2.set_index(["Index1", "Index2"])
df2

I would like to create the following DataFrame with 3-level index where the first level indicates from which DataFrame the values are taken. Note all DataFrames have exactly the same columns:

How can I do this in the most automatic way? Ideally I would like to have the following solution:

# start with empty dataframe
res = pd.DataFrame(index = pd.MultiIndex(levels = [[], [], []],
                                        codes = [[],[],[]],
                                        names = ["Df number", "Index1", "Index2"]),
                  columns = ["Value1", "Value2"])

res = AddDataFrameAtIndex(index = "DF1", level = 0, dfToInsert = df1)
res = AddDataFrameAtIndex(index = "DF2", level = 0, dfToInsert = df2)

PaulS · Accepted Answer · 2022-10-08 14:48:34Z

2

A possible solution, based on pandas.concat:

pd.concat([df1, df2], keys=['DF1', 'DF2'], names=['DF number'])

Output:

                         Value1  Value2
DF number Index1 Index2                
DF1       A      B            1       2
          AA     BB           1       2
DF2       X      Y            3       4
          XX     YY           3       4

edited Oct 8, 2022 at 14:48

answered Oct 8, 2022 at 14:44

PaulS

27.1k3 gold badges19 silver badges40 bronze badges

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

2 Comments

mozway Over a year ago

or pd.concat({'DF1': df1, 'DF2': df2}, names=['DF number'])

PaulS Over a year ago

Your solutions are always creative, @mozway! Thanks!

Collectives™ on Stack Overflow

Insert one dataframe into another in Python

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