Home > front end >  Is there a way to concatenate multiindex Pandas Dataframes with different column levels?
Is there a way to concatenate multiindex Pandas Dataframes with different column levels?

Time:05-15

I am reading data from some csv files and a typical dataframe looks like this:

Type  Animal  Animal   Animal
Color Black   Black    Red
Value 0       0        0
Value 0.1     0.2      0.3
Value 0.1     0.4      0.5

So, basically, for each animal, for each color, there is an array of values. To read the data, I am using the following line of code:

df1 = pd.read_csv(csv_path, header = [0,1])

I have another similar dataframe, but with one more header row, which looks like this:

Type  Animal  Tool     Tool
Color Black   Red      Green
ID    1       2        3 
Value 0       0        0
Value 0.1     0.2      0.3
Value 0.1     0.4      0.5

This is how I read the dataframe above:

df2 = pd.read_csv(csv_path, header = [0,1,2])

Now I want a dataframe that contains all the data, something like this:

Type  Animal  Animal   Animal  Animal  Tool     Tool
Color Black   Black    Red     Black   Red      Green
ID                             1       2        3    
Value 0       0        0       0       0        0
      0.1     0.2      0.3     0.1     0.2      0.3
      0.1     0.4      0.5     0.1     0.4      0.5
          

Is there any way to achieve this for this type of data?

CodePudding user response:

df1 = df1.T
df1['ID'] = None
df1 = df1.set_index('ID', append=True).T

pd.concat([df1, df2], axis=1)
  • Related