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

Question

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.

`identically-labeled Series objects` this error comes because of the unmatched number of rows between df1 and df2. when you perform `df1["E"] == df2["E"]` the rows will not be in same shape in both the sides. — Saravanakumar V, Dec 07 '20 at 05:55
Maybe help [how to provide a great pandas example](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples), hard to know answer without sample data in both DataFrames. — jezrael, Dec 08 '20 at 11:37

jezrael · Accepted Answer · 2020-12-08T12:14:12.630

1

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))

edited Dec 08 '20 at 12:14

answered Dec 07 '20 at 05:53

jezrael

822,522
95
1,334
1,252

it creates unwanted extra rows. ( I changed on='left' to how='left) – G. Hak. Dec 08 '20 at 12:13
@G.Hak. - How working if removed duplicated rows? – jezrael Dec 08 '20 at 12:15
the sample I tested has 223 rows after applying the above code I got 297 rows then I used drop_duplicates() and the result was 295 rows so it has extra rows yet. – G. Hak. Dec 08 '20 at 16:30

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

1 Answers1