topic Re: I want to compare two data frames. In output I wish to see unmatched Rows and the columns identified leading to the differences. in Data Engineering

I want to compare two data frames. In output I wish to see unmatched Rows and the columns identified leading to the differences.

Databricks_POC — Mon, 20 Dec 2021 09:14:14 GMT

Re: I want to compare two data frames. In output I wish to see unmatched Rows and the columns identified leading to the differences.

Anonymous — Mon, 20 Dec 2021 19:26:51 GMT

Are you trying to do change data capture between an older and newer version of the same table?

Re: I want to compare two data frames. In output I wish to see unmatched Rows and the columns identified leading to the differences.

Databricks_POC — Tue, 21 Dec 2021 08:59:42 GMT

No, I wish to compare two tables. Same Scenario as in case of Minus/Except query. However apart from the mismatched rows, I wish to also know which are those columns leading to the difference.

Re: I want to compare two data frames. In output I wish to see unmatched Rows and the columns identified leading to the differences.

Pholo — Tue, 11 Jan 2022 18:00:58 GMT

Hi @vinita shinde

I don't know if It meets your requirement, but you can try something like that

import pyspark.sql.functions as F
from pyspark.sql import Row
 
def is_different(col1, col2, cols):
  if col2 in cols:
    return F.col(col1) == F.col(col2)
  else:
    return F.col(col1).alias(col1)
 
def find_difference(data1, data2, id_columns):
  cols = data1.columns
  data_difference = data1.subtract(data2) # First you find the difference in rows
  data_difference = data_difference.join(data2.select(*[F.col(i).alias(f'b_{i}') if i not in id_columns else F.col(i).alias(i) for i in cols]), on = id_columns, how = 'left')
  # you join the differences with the second dataframe
  return data_difference.select(*[is_different(x, f"b_{x}",data_difference.columns) for x in cols]) # then you find out which columns cause the differences
 
# df1_spark first dataframe
# df2_spark second datafame
# third option is a list of ids of the Row
display(find_difference(df1_spark, df2_spark, ['ID'] ))

The results will be somethong like that

Let me know if It meets your requirements

Re: I want to compare two data frames. In output I wish to see unmatched Rows and the columns identified leading to the differences.

bhargavi1 — Thu, 28 Apr 2022 08:53:19 GMT

@vinita shinde are you Cracked this Code?

Re: I want to compare two data frames. In output I wish to see unmatched Rows and the columns identi

Enthusiastic_Da — Tue, 26 Dec 2023 18:32:28 GMT

How to print only what columns changed instead of displaying all columns?

I just want to print for every row PRIMARY Key (ID) and only column names that changed in the second data frame as comma separated.