CODEWITHSUNDEEP
pythonpandasdataframe
Matt-pow
Matt-pow

Reputation: 986

Compare two dataframe cell by cell with conditions on columns

I want to compare two dataframe and output a dataframe with its differences. However, I can tolerate date difference within 2 days differences, and score within 5 points difference. I will keep the values from df1 if they are within the acceptable ranges.

df1

id    group      date        score
10     A       2020-01-10     50
29     B       2020-01-01     80
39     C       2020-01-21     84
38     A       2020-02-02     29

df2

id    group      date        score
10     B       2020-01-11     56
29     B       2020-01-01     81
39     C       2020-01-22     85
38     A       2020-02-12     29

My expected output :

id    group           date                      score
10     A -> B       2020-01-10                50 -> 56
29     B            2020-01-01                   80
39     C            2020-01-21                   84
38     A            2020-02-02 -> 2020-02-12     29

Thus, I want to compare the dataframe cell by cell and condition on certain columns.

I started on this :

df1.set_index('id', inplace=True)
df2.set_index('id', inplace=True)
result = []
for col in df1.columns:
    for index, row in df1.iterrows():
        diff = []
        compare_item = row[col][index]
        for index, row in df2.iterrows():
            if col == 'date':
                # acceptable if it's within 2 days differences
            if col == 'score':
                # acceptable if it's within 5 points differences
            if compare_item == row[col][index]:
                diff.append(compare_item)
            else:
                diff.append('{} --> {}'.format(compare_item, row[col]))
    result.append(diff)
df = pd.DataFrame(result, columns = [df1.columns])

Upvotes: 2

Views: 702

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150745

Let's try:

thresh = {'date':pd.to_timedelta('2D'),
          'score':5}

def update(col):
    name = col.name

    # if there is a threshold, we update only if threshold is surpassed
    if name in thresh:
        return col.where(col.sub(df2[name]).abs()<=thresh[name], df2[name])

    # there is no threshold for the column
    # return the corresponding column from df2
    return df2[name]

df1.apply(update)

Output:

   group       date  score
id                        
10     B 2020-01-10     56
29     B 2020-01-01     80
39     C 2020-01-21     84
38     A 2020-02-12     29

Upvotes: 1

Related Questions