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)