Using a function relating two different dataframes with if condition

Hi there! I've got two different dataframes and I tried to use a function using these two dataframes to get the result of Points from DF1 over DF2 but if there is no points from a person in another dataframe it will divide by 1.

I tried to use apply function but in't able to relate two dataframes in the same function.

DF1

        Person      Points
    0   Person_1        25
    1   Person_2        20
    2   Person_3        14
    3   Person_4        23
    4   Person_5        40

DF2

    Person      Points
0   Person_1        10
1   Person_2        40
2   Person_3         2

Expected output:

DF_TOTAL

    Person      Points
0   Person_1       2.5
1   Person_2       0.5
2   Person_3         7
3   Person_4        23
4   Person_5        40

CodePudding user response：

df2 = df2.reindex(df1.index) #reindex to have df2 same lenght than df1
df2 = df2.fillna(1) #fill NaN values with 1
df1['Points'] = df1['Points'] / df2['Points'] #then divide
df1

index	Person	Points
0	Person_1	2.5
1	Person_2	0.5
2	Person_3	7.0
3	Person_4	23.0
4	Person_5	40.0

CodePudding user response：

Set the Person column as the indices of both DataFrames, so the division is done by aligning/ matching the Person values (regardless of the order of the rows). Then fill the NaN values (the extra rows of df1) with the appropriate values.

df_total = (
    df1.set_index('Person')
       .div(df2.set_index('Person'))
       .fillna(df1.set_index('Person'))
       .reset_index()
)

Output:

>>> df_total 

     Person  Points
0  Person_1     2.5
1  Person_2     0.5
2  Person_3     7.0
3  Person_4    23.0
4  Person_5    40.0