Home > other >  Insert one dataframe into another in Python
Insert one dataframe into another in Python

Time:10-09

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:

enter image description here

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)

CodePudding user response:

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
  • Related