CODEWITHSUNDEEP
pythonpandasdataframe
G. Hak.
G. Hak.

Reputation: 361

Pandas Dataframe updating a column based comparing some other columns with the columns of another dataframe with different number of columns

I have two data frames

df1: 4 columns and 2000 rows

   E G H L
0
1
2
3
...

df2: 26 columns (can be more later) and unknown rows

  A B C D E ... Z
0
1
2
3
...

I want to update column L of df2 with the value of column L of df1 in such a way that if the value of columns E G H is the same in df1 and df2.

I tried different solutions I found in Stackoverflow but I got different errors.

For example from based on this Q/A: Compare two pandas dataframes and update one, depending on results

I tried :

if df2['E'] == df1['E'] and  df2['G'] == df1['G'] and \
   df2['H'] == df1['h']:
    df2['L'] = df1['L']

I got ValueError: Can only compare identically-labeled Series objects

based on Pandas "Can only compare identically-labeled DataFrame objects" error I change it to

if df2['E'] == df1['E'] and  df2['G'] == df1['G'] and \
   df2['H'] == df1['h'] and df1.reset_index(drop=True) == df2.reset_index(drop=True):
    df2['L'] = df1['L']

and I got the same error

Here is more information: df1 is actually a map and based on the value of the columns E G H I have to find the value of L and put it in the same rows in the column L of df2. Here is an example: Assuming that in data frame 1 I have

E item: RAM, G stock: No-2, H State(Province):NY, L Part Code:5X456

Now for every row in the data frame 2 that the value of E item, G stock, and H State is the same as above I need to update L Part Code to 5X456.

Upvotes: 1

Views: 583

Answers (1)

jezrael
jezrael

Reputation: 862681

Idea is use left join only necessary columns (h war renamed to H for match) with remove original L column and added new df1.L and last for original order of columns use DataFrame.reindex:

df11=df1.rename(columns={'h':'H'})[['E','G','H','L']].drop_duplicates(subset=['E','G','H'])
df2 = (df2.drop(['L'], axis=1)
          .merge(df11, on=['E','G','H'], how='left')
          .reindex(df2.columns, axis=1))

Upvotes: 1

Related Questions