Replace values between dataframes with different sizes and multiple conditions

Question

So I have 2 dataframes, from different sizes, df1 = (578, 81) and df2 = (1500, 59), all lines on df1 exists in df2, and all columns in df2 exists in df1, my problem is, I have a value that i want to update in df1 based on 6 conditions, so to update the column X, the values at columns X1, X2, Y1, Y2, Z1 and Z2 must be equal on both DataFrames.

On java I would do somenthing like:

for(i=0;i<df1.length;i++){
    for(k=0;k<df2.length;k++){
        if(df1[i][1]==df2[k][1] && df1[i][2]==df2[k][2] ...){
            df1[i][0] = df2[k][0];
    }
}

Answer 1

You can easily use numpy.where. And i think it should work best in this case too.

Let's say you have the following DataFrames

import pandas as pd

df1=pd.DataFrame({'X':[1,3,4,6,5],
                  'X1':[2,3,4,6,3],
                  'Y1':[4,2,1,51,3],
                  'Z1':[2,3,4,1,5]})

df2=pd.DataFrame({'L':[2,3,4,1,4],
                  'X2':[2,3,4,6,5],
                  'Y2':[4,3,4,6,3],
                  'Z2':[2,2,1,51,3]})

And you want to change the value of X based on the conditions if X1==X2 & Y1==Y2 & Z1==Z2 . Also lets say the value you want to update is from column L in this case.

You can use numpy.where like this

df1['X']=np.where((df1['X1']==df2['X2'])&(df1['Y1']==df2['Y2'])&(df1['Z1']==df2['Z2']),df2['L'],,df1['X'])

It would only change the first row as the conditions only gets satisfied there. This function is changing the values to df2['L'] if it meets the condition and keeping the original values if the conditions are not met.

Read more about np.where

Update: The dataframes in the question are not equal. It doesn't matter if they don't have equal columns but the rows should be equal for the sake of comparison. Below is the example in which the two data frames are not equal and how numpy.where is performed in that case.

import pandas as pd
import numpy as np

df1=pd.DataFrame({'X':[1,3,4,6,5],
                  'X1':[2,3,4,6,3],
                  'Y1':[4,3,1,51,3],
                  'Z1':[2,3,4,1,5]})

df2=pd.DataFrame({'L':[2,3,4,1,4,5,1],
                  'X2':[2,3,4,6,5,2,3],
                  'Y2':[4,3,4,6,3,8,7],
                  'Z2':[2,3,1,51,3,9,9],
                  'R2':[2,5,1,2,7,3,9]})

#make both the dataframes equal

for i in range(len(df2)-len(df1)):
    df1=df1.append(pd.Series(),ignore_index=True)

df1['X']=np.where((df1['X1']==df2['X2'])&(df1['Y1']==df2['Y2'])&(df1['Z1']==df2['Z2']),df2['L'],df1['X'])

#drop those null values which have been appended above to get back to original
df1=df1.dropna(how='all')    

Answer 2

Do the 6 columns make a row unique? If so, I would use merge:

columns_join= ['col1', 'col2', 'col3', 'col4', 'col5', 'col6']
columns_update= ['upd1', 'upd2', 'upd3']

df_merged= df_to_update[columns_join].merge(df_source[columns_join + columns_update], on=columns_join, how='left', suffixes=['', '_src'], indicator='_join_ind')

for col in columns_update:
    df_to_update.loc[df_merged['_join_ind']=='both', col]= df_merged[col + '_src']

# now df_to_update contains the result

If the 6 fields do not identify a row (could result in more rows), you would need to make the merged result unique afterwards, but in that case, your Java version would also just always result in the values of the last row. In that case you can use DataFrame.grouby and e.g. last as the aggregation function.

EDIT: in case you need to apply an aggregation, it's probably best to apply the aggregation to the dataframe you take the values. To aggregate it accordingly using the last value of each group and merge it afterwards, just replace the line with the .merge above by:

df_agg= df_source.groupby(columns_join)[columns_update].aggregate('last')
df_merged= df_to_update[columns_join].merge(df_source[columns_join + columns_update], left_on=columns_join, right_index=True, how='left', suffixes=['', '_src'], indicator='_join_ind')