Conditional calculation on different sized dataframes in Python

user_14038807 0
Viewed 68

I am working with two dataframes df1, df2 in Latitude/Longitude/Value format.

    print(df1)
    0    1    2
0  -85 -175  1.8
1  -80 -170  1.6
..   

    print(df2)
    0    1    2
0 -70 -150  2.5
1 -80 -170  2.0
2 -85 -175  3.0
..

I'd like to multiply df1[2] by df2[2] if df1[0] = df2[0] and df1[1] = [1]. I tried using the following:

df1['multiplied']=np.where((df1[0]==df2[0],df1[2]*df2[2],np.nan) #if column 1 of df1 equals column 1 
                                                                     #of df2, multiply. Else give NaN

I am aware that I neglect the second condition in this example. Downside here is, comparison of column values is element-wise (my lat/lon data are not sorted). Also different df size causes an error "Can only compare identically-labeled Series objects".

In another approach, I tried checking whether df1[0] and df2[0] are the same and give out a new df that matches the criteria:

dfnew=df1.loc[df1[0]==df2[0]]

This lead me to the same error as above.

I wonder if you have any advice on how to apply conditional calculations on unsorted dataframes of different size?

pandas conditional-statements dataframe numpy python
2 Answers

  1. Merge your dataframes on identical long/lats. Note, "inner" only preserves the intersection of keys:

    df3 = df1.merge(df2, on = [0,1], how = 'inner')

  2. Multiply the long/lats with themselves:

    df3['multiplied_long'] = df3.0**2

    df3['multiplied_lat'] = df3.1**2

user_140385060

If you want your dataframe to include only the matching rows:

merged = df1.merge(df2,on=[0,1])
merged[2] = merged['2_x'] * merged['2_y']
merged = merged.drop(['2_x', '2_y'], axis=1)

output:

    0    1    2
0 -85 -175  5.4
1 -80 -170  3.2

And if you want it to include all rows from df2 (obviously, you can change it to include all rows of df1 or both) and set non-matching ones to NaN:

merged = df1.merge(df2,on=[0,1],how='right')
merged[2] = merged['2_x'] * merged['2_y']
merged = merged.drop(['2_x', '2_y'], axis=1)

output:

    0    1    2
0 -70 -150  NaN
1 -80 -170  3.2
2 -85 -175  5.4
user_49759810
Related