How to iterate over rows between 2 different dataframes and capture the entire row

Question

Is there way easier way to capture the difference in data between two dataframe. I am trying to compare 2 dataframe and find the missing. I found this attached link but it's not satisfying my requirement. In that post they comparing using Id but I wanted to compare the entire rows of 2 dataframes and find the mismatch row whether it is from A or B.

Iterate over different dataframe

DF 1:

country    from         to       flag
('GB',  'GB390065', 'GB66903',  'Y')
('FR',  'FR418458', 'FR765617', 'Y')
('FR',  'FR629810', 'FR855277', 'Y')
('FR',  'FR494010', 'FR332891', 'Y')
('FR',  'FR740500', 'FR907878', 'Y')
('FI',  'NB444135', 'NB234471', 'Y')

DF 2:

country    from         to       flag
('GB',  'GB390065', 'GB66903',  'Y')
('FR',  'FR418458', 'FR7656',   'Y')
('FR',  'FR629810', 'FR855277', 'Y')
('FR',  'FR4910',   'FR33891',  'Y')
('FR',  'FR740500', 'FR907878', 'Y')
('FI',  'NB444135', 'NB234471', 'Y')

My expected output:

country    from         to       flag   Available
('FR',  'FR418458', 'FR7656',   'Y'       df2)
('FR',  'FR4910',   'FR33891',  'Y'       df2)

The challenge I face here is from column and to column are same data only so when I tried using merge function, it is taking any one of the column and giving the result which is not meeting the requirement.

The output I got is

               country_code from_cust_id  ... to_cust_guid Merge_status
       322443         DE       DE180556  ...          NaN    left_only
       322444         DE        DE22191  ...          NaN    left_only
       322445         DE      DE2625168  ...          NaN    left_only
       322446         DE        DE17705  ...          NaN    left_only
       322447         DE      DE2556758  ...          NaN    left_only

      [5 rows x 7 columns]
              country_code from_cust_id  ... to_cust_guid Merge_status
       0                DE          NaN  ...     DE485137   right_only
       1                DE          NaN  ...     DE467209   right_only
       2                DE          NaN  ...     DE651068   right_only
       3                DE          NaN  ...     DE459729   right_only
       4                DE          NaN  ...     DE448683   right_only

yes i tried that but i couldn't get the entire row of data and which df it is either A or B — sumathi, Jan 29 '20 at 11:08
posted the output which i got . Actually some of the data are only available in the left dataframe but in the output is showing as NaN for some columns@jezrael — sumathi, Jan 29 '20 at 11:39
check if this helps https://stackoverflow.com/questions/20225110/comparing-two-dataframes-and-getting-the-differences/20228113#20228113 — N.Moudgil, Jan 29 '20 at 12:03

Vishnudev Krishnadas · Answer 1 · 2020-01-31T06:59:12.447

2

Try

df1['Available'] = 'df1'
df2['Available'] = 'df2'

available = pd.concat([df1, df2]).drop_duplicates(subset=['from', 'to'], keep=False)

edited Jan 31 '20 at 06:59

answered Jan 29 '20 at 21:07

Vishnudev Krishnadas

10,679
2
23
55

still i'm not getting the solution. Any help please@Vishnudev and @jezrael – sumathi Jan 30 '20 at 12:46
@sumathi Please provide proper dataframes in your question. – Vishnudev Krishnadas Jan 31 '20 at 07:01

score 2 · Answer 2 · answered Jan 31 '20 at 08:28

Here's a way you can do by converting dataframe to a set of tuples:

a1 = set(df1.apply(tuple,1).values)
a2 = set(df2.apply(tuple,1).values)

print(a1)

{('FI', 'NB444135', 'NB234471', 'Y'),
 ('FR', 'FR418458', 'FR765617', 'Y'),
 ('FR', 'FR494010', 'FR332891', 'Y'),
 ('FR', 'FR629810', 'FR855277', 'Y'),
 ('FR', 'FR740500', 'FR907878', 'Y'),
 ('GB', 'GB390065', 'GB66903', 'Y')}


print(a2)

{('FI', 'NB444135', 'NB234471', 'Y'),
 ('FR', 'FR418458', 'FR7656', 'Y'),
 ('FR', 'FR4910', 'FR33891', 'Y'),
 ('FR', 'FR629810', 'FR855277', 'Y'),
 ('FR', 'FR740500', 'FR907878', 'Y'),
 ('GB', 'GB390065', 'GB66903', 'Y')}

# now do set difference (in df2 not in df1)
diffs = pd.DataFrame(np.array([x for x in a2 - a1]))
diffs['Available'] = 'df2'

print(diffs)

    0         1        2  3 Available
0  FR  FR418458   FR7656  Y       df2
1  FR    FR4910  FR33891  Y       df2

Arun V · Answer 3 · 2020-01-31T11:01:55.340

Try this

df1 = pd.DataFrame(Mylist1,columns=['country','from','to','flag'])
df2 = pd.DataFrame(Mylist2,columns=['country','from','to','flag'])
# print(df2)
indexes = (df1 != df2).any(axis=1)
indexes1 = (df2 != df1).any(axis=1)
df3 = df2.loc[indexes]
df4 = df1.loc[indexes1]
pd.options.mode.chained_assignment = None
df3['Available'] = 'df1'
df4['Available'] = 'df2'
# print(df3)
# print(df4)
df_row_reindex = pd.concat([df3, df4], ignore_index=False)
print(df_row_reindex)


country from    to         flag Available
FR  FR418458    FR7656     Y    df1
FR  FR4910      FR33891    Y    df1
FR  FR418458    FR765617   Y    df2
FR  FR494010    FR332891   Y    df2

How to iterate over rows between 2 different dataframes and capture the entire row

3 Answers3