Combining three similar Pandas datasets, keeping original values

Question

So I have the three similar data sets given by the lines below:

import pandas as pd

df1 = pd.DataFrame({'Name': ['Michael', 'Samantha', 'Jimmy'], 'Gender': ['M', 'F', 'M'], 'Mon': [0,1,2], 'Tue': [0,3,5], 'Wed': [0,5,3]})

df2 = pd.DataFrame({'Name': ['Michael', 'Samantha', 'Jimmy'], 'Gender': ['M', 'F', 'M'], 'Mon': [1,2,4], 'Tue': [2,3,5], 'Wed': [1,4,5]})

df3 = pd.DataFrame({'Name': ['Michael', 'Samantha', 'Jimmy'], 'Gender': ['M', 'F', 'M'], 'Mon': [5,4,0], 'Tue': [4,6,5], 'Wed': [1,7,6]})

Each data frame, respectively and appears as such:

>>> df1

       Name Gender  Mon  Tue  Wed

0   Michael      M    0    0    0

1  Samantha      F    1    3    5

2     Jimmy      M    2    5    3

>>> df2

       Name Gender  Mon  Tue  Wed

0   Michael      M    1    2    1

1  Samantha      F    2    3    4

2     Jimmy      M    4    5    5

>>> df3

df3

       Name Gender  Mon  Tue  Wed

0   Michael      M    5    4    1

1  Samantha      F    4    6    7

2     Jimmy      M    0    5    6

Is there any way to create the resultant data frame that combines a numbers into one data frame? A result would appear as:

       Name Gender        Mon     Tue     Wed

0   Michael      M    [0,1,5] [0,2,4] [0,1,1]

1  Samantha      F    [1,2,4] [3,3,6] [5,4,7]

2     Jimmy      M    [2,4,0] [5,5,5] [3,5,6]

The order of my data would have to be maintained. The first item in my list doesn't necessarily have to come from a first dataset (df1), but I would like to always know where my number from my first dataset lands so that I can pull that specific value out of a combined data frame.

Answer 1

You need to concat then groupby

df = pd.concat([df1,df2,df3]).set_index(['Name','Gender']).groupby(level=[0,1]).agg(list).reset_index()

Out[20]: 

       Name Gender        Mon        Tue        Wed

0     Jimmy      M  [2, 4, 0]  [5, 5, 5]  [3, 5, 6]

1   Michael      M  [0, 1, 5]  [0, 2, 4]  [0, 1, 1]

2  Samantha      F  [1, 2, 4]  [3, 3, 6]  [5, 4, 7]

Want to be a master in Data Science? Enroll in this Data Science Courses