Comparing dataframes with different sizes in pandas and create new column based on comparison

Question

I am importing via pyodbc 2 dataframes: df1 and df2.

They are big and I need to compare them.

PS: They don't have the same size.

3 columns size Dataframes:

What I want to do:

If CODUSU columns are equal then df2[Situação] = 'K'

What I have done, but it's too slow:

for i in range(0,len(df2)):
  for k in range(0,len(df1)):
    if df2.loc[i][0] == df1.loc[k][0]:
        df2[i]["Situação"] = "K"

I also need to:

If in item in df1[CODUSU] and not in df2[CODUSU] then df2[Situação] = 'Q'

If in item in df2[CODUSU] and not in df1[CODUSU] then df2[Situação] = 'B'

You can do, df2.loc[df2['CODUSU'].isin(df1['CODUSU']), 'Situação'] = "K" — DOOM
– DOOM, Commented Mar 23, 2020 at 15:08
If you start using loops while dealing with pandas, that's probably the wrong way... As @DOOM wrote, isin is the way to go. — Hadas Arik
– Hadas Arik, Commented Mar 23, 2020 at 16:18
Thansk @HadasArik. That tip will help me a lot for future automatizations! — Lucas Dadalt
– Lucas Dadalt, Commented Mar 24, 2020 at 14:43

Serge Ballesta · Accepted Answer · 2020-03-23 17:03:41Z

1

You should do an outer merge on the dataframes asking for the indicator variable:

resul = df2.merge(df1, how='outer', on='A', suffixes=('', '_y'),indicator=True)

Do not forget the DTDOSE column for rows coming from df1 only:

resul.loc[resul['indicator'] == 'right_only', 'DTDOSE'] = resul.loc[
                                  resul['indicator'] == 'right_only', 'DTDOSE_y']

Time to compute the new values for the Situação column:

resul.loc[resul['indicator'] == 'both', 'Situação'] = 'K'
resul.loc[resul['indicator'] == 'right_only', 'Situação'] = 'Q'
resul.loc[resul['indicator'] == 'left_only', 'Situação'] = 'B'

And finally get rid of the auxiliary columns:

resul = resul[['CODUSU', 'DTDOSE', 'Situação']

answered Mar 23, 2020 at 17:03

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lucas Dadalt Over a year ago

Perfect! This was what I was searching for. Merge was very useful!

Collectives™ on Stack Overflow

Comparing dataframes with different sizes in pandas and create new column based on comparison

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related