Jeril Jeril - 1 month ago 11
Python Question

Pandas - concatenating two multi-index dataframes

I am having a dataframe as follows:

df.head()
Student Name Q1 Q2 Q3
Month Roll No
2016-08-01 0 Save Mithil Vinay 0.0 0.0 0.0
1 Abraham Ancy Chandy 6.0 5.0 5.0
2 Barabde Pranjal Sanjiv 7.0 5.0 5.0
3 Bari Siddhesh Kishor 8.0 5.0 3.0
4 Barretto Cleon Domnic 1.0 5.0 4.0


Now I wanted to make a hierarchical column index, so I did it the following way:

big_df = pd.concat([df['Student Name'], df[['Q1', 'Q2', 'Q3']]], axis=1, keys=['Name', 'IS'])


and was able to get the following:

>>> big_df
Name IS
Student Name Q1 Q2 Q3
Month Roll No
2016-08-01 0 Save Mithil Vinay 0.0 0.0 0.0
1 Abraham Ancy Chandy 6.0 5.0 5.0
2 Barabde Pranjal Sanjiv 7.0 5.0 5.0
3 Bari Siddhesh Kishor 8.0 5.0 3.0
4 Barretto Cleon Domnic 1.0 5.0 4.0


Now for the second iteration, I want to concatenate only the
Q1, Q2, Q3
values from the new dataframe to the
big_df
dataframe (the previously concatenated dataframe). Now the dataframe for the second iteration is as follows:

Student Name Q1 Q2 Q3
Month Roll No
2016-08-01 0 Save Mithil Vinay 0.0 0.0 0.0
1 Abraham Ancy Chandy 8.0 5.0 5.0
2 Barabde Pranjal Sanjiv 7.0 5.0 4.0
3 Bari Siddhesh Kishor 8.0 4.0 3.0
4 Barretto Cleon Domnic 2.0 3.0 4.0


I wanted the
big_df
like the following:

Name IS CC
Student Name Q1 Q2 Q3 Q1 Q2 Q3
Month Roll No
2016-08-01 0 Save Mithil Vinay 0.0 0.0 0.0 0.0 0.0 0.0
1 Abraham Ancy Chandy 6.0 5.0 5.0 8.0 5.0 5.0
2 Barabde Pranjal Sanjiv 7.0 5.0 5.0 7.0 5.0 4.0
3 Bari Siddhesh Kishor 8.0 5.0 3.0 8.0 4.0 3.0
4 Barretto Cleon Domnic 1.0 5.0 4.0 2.0 3.0 4.0


I tried the following codes, but all are giving error:

big_df.concat([df[['Q1', 'Q2', 'Q3']]], axis=1, keys=['CC'])

pd.concat([big_df, df[['Q1', 'Q2', 'Q3']]], axis=1, keys=['Name', 'CC'])


Where am I doing the error? Kindly help. I am new to Pandas

Answer

First, you're way better off setting your index to be ['Month', 'Roll no.', 'Student Name']. That will simplify your concat syntaxes a lot and ensure you match on the name of the students too.

df.set_index('Student Name', append=True, inplace=True)

Second, I suggest you do it differently and store your df dataframes (with the Q1/Q2/Q3 values) during your iteration with a reference to the name for the highest column level (e.g.: 'IS', 'CC'). A dict would be perfect for this, and pandas does accept a dict as an argument to pd.concat

# Creating a dictionnary with the first df from your question
df_dict = {'IS': df}

# Iterate....
   # Append the new df to the df_dict
   df_dict['CC'] = df

Now, after looping through, here's your dict:

df_dict

In [10]: df_dict

Out[10]:
{'CC':                                             Q1   Q2   Q3
 Month      Roll No Student Name                         
 2016-08-01 0       Save Mithil Vinay       0.0  0.0  0.0
            1       Abraham Ancy Chandy     6.0  5.0  5.0
            2       Barabde Pranjal Sanjiv  7.0  5.0  5.0
            3       Bari Siddhesh Kisho     8.0  5.0  3.0
            4       Barretto Cleon Domnic   1.0  5.0  4.0,
 'IS':                                             Q1   Q2   Q3
 Month      Roll No Student Name                         
 2016-08-01 0       Save Mithil Vinay       0.0  0.0  0.0
            1       Abraham Ancy Chandy     8.0  5.0  5.0
            2       Barabde Pranjal Sanjiv  7.0  5.0  4.0
            3       Bari Siddhesh Kisho     8.0  4.0  3.0
            4       Barretto Cleon Domnic   2.0  3.0  4.0}

So now if you concat, pandas does it nicely, and automatically for you:

In [11]: big_df = pd.concat(df_dict, axis=1)
         big_df

Out[11]: 

enter image description here


If you really wanted to do it iteratively, you should prepend your new multilevel ('CC') before concat with big_df

df.columns = pd.MultiIndex.from_tuples([('IS', x) for x in df.columns])

# Then you can concat, give the same result as the picture above.
pd.concat([big_df, df], axis=1)