Creating new dataframes based on a existing dataframes-CodePudding

I have two dataframes that completes each other but I need to create a third taking data from both dataframes. But each data needs to match with with the right information.

So df1 looks like this

Name   Team   
John    A
Nathan  B
Eric    C

Df2 looks like this

Date         Name    Status   
 01/01/2022   John    Active
 02/01/2022   John    Active
 03/01/2022   John    Active
 01/01/2022  Nathan  Active
 02/01/2022  Nathan  Active
 03/01/2022 Eric    Deactivated 
 04/01/2022 Eric    Active

I need my python code to look at both dfs see that the names are equal to each other and bring the correct team so df3 should look like this

Date         Name    Status  team 
 01/01/2022   John    Active    A
 02/01/2022   John    Active    A
 03/01/2022   John    Active    A
 01/01/2022  Nathan   Active     B
 02/01/2022  Nathan   Active     B
 03/01/2022  Eric     Deactivated C
 04/01/2022  Eric     Active      C

Can anyone help ?

CodePudding user response：

Given two DataFrame objects:

DF1
     Name Team
0    John    A
1  Nathan    B
2    Eric    C

DF2
        Date    Name       Status
0 2022-01-01    John       Active
1 2022-02-01    John       Active
2 2022-03-01    John       Active
3 2022-01-01  Nathan       Active
4 2022-02-01  Nathan       Active
5 2022-03-01    Eric  Deactivated
6 2022-04-01    Eric       Active

Combining them with merge we get:

DF2.merge(DF1, how='inner', on='Name')

        Date    Name       Status Team
0 2022-01-01    John       Active    A
1 2022-02-01    John       Active    A
2 2022-03-01    John       Active    A
3 2022-01-01  Nathan       Active    B
4 2022-02-01  Nathan       Active    B
5 2022-03-01    Eric  Deactivated    C
6 2022-04-01    Eric       Active    C

For the given example an inner merge is sufficient.