Merge two related dataframe to one-CodePudding

How can I create a new DF such that each teacher should contain a list of Students

Teacher df

              name  married                  school
0    Pep Guardiola     True  Manchester High School
1     Jurgen Klopp     True   Liverpool High School
2     Mikel Arteta    False            Arsenal High
3  Zinadine Zidane     True                     NaN

Student df

        teacher                name  age height weight
0   Mikel Arteta         Bukayo Saka   21   2.1m   80kg
1   Mikel Arteta  Gabriel Martinelli   21   2.1m   75kg
2  Pep Guardiola       Jack Grealish   27   2.1m   80kg
3   Jurgen Klopp     Roberto Firmino   31   2.1m   65kg
4   Jurgen Klopp    Andrew Robertson   28   2.1m   70kg
5   Jurgen Klopp        Darwin Nunez   23   2.1m   75kg
6  Pep Guardiola      Ederson Moraes   29   2.1m   90kg
7  Pep Guardiola       Manuel Akanji   27   2.1m   80kg
8   Mikel Arteta       Thomas Partey   29   2.1m   80kg

CodePudding user response：

If need new column filled by list of students use Series.map with aggregate list:

df1['students'] = df1['name'].map(df2.groupby('teacher')['name'].agg(list))

CodePudding user response：

You can consider using:

df.merge(df.groupby('teacher',as_index=False).agg({'name':list}),
                                   how='left',
                                   on='teacher',
                                   suffixes=('','_list'))

Retuning:

         teacher        name  age height weight                    name_list
0   Mikel Arteta        Saka   21   2.1m   80kg   [Saka, Martinelli, Partey]
1   Mikel Arteta  Martinelli   21   2.1m   75kg   [Saka, Martinelli, Partey]
2  Pep Guardiola    Grealish   27   2.1m   80kg   [Grealish, Moraes, Akanji]
3   Jurgen Klopp     Firmino   31   2.1m   65kg  [Firmino, Robertson, Nunez]
4   Jurgen Klopp   Robertson   28   2.1m   70kg  [Firmino, Robertson, Nunez]
5   Jurgen Klopp       Nunez   23   2.1m   75kg  [Firmino, Robertson, Nunez]
6  Pep Guardiola      Moraes   29   2.1m   90kg   [Grealish, Moraes, Akanji]
7  Pep Guardiola      Akanji   27   2.1m   80kg   [Grealish, Moraes, Akanji]
8   Mikel Arteta      Partey   29   2.1m   80kg   [Saka, Martinelli, Partey]