How to do a Pandas comparison with keep shape=False, but maintain relationship with the username column

user_300978 0
Viewed 78

I'm trying to run a Pandas dataframe comparison df.compare(df2) that returns only differences between two dataframes, but keep the relationship between the first column (with user's names) and the output when using the argument keep_shape=False which will only display rows with differences and the indexes, but the relationship with the username column is not displayed.

How do I keep the name column (which is the first column) and use the argument keep_shape=False at the same time so I can identify the username and the changes at the same time.

Example:

import pandas as pd

df_1 = pd.read_excel('../output/spreadsheet_Jan_1.xlsx')
df_2 = pd.read_excel('../output/spreadsheet_Feb_1.xlsx')

df_compare = df_1.compare(df_2, keep_equal=True, keep_shape=False)

spreadsheet

I guess the image isn't showing...it's a spreadsheet with the df.compare() result showing the averages columns and the 'self' and 'other' columns split below the averages. The index is on the left hand side in the order of the 'keep_shape-False' format (e.g. 1, 6, 7, 8, 9 11, etc).

How do I match the usernames which is the first column along the left side with the associated indexes?

Thanks in advance.

pandas dataframe
2 Answers

Here is an example of one simple way to it:

import pandas as pd

df_1 = pd.DataFrame(
    {
        "fruit": {0: "banana", 1: "orange", 2: "apple", 3: "celery"},
        "quantity": {0: 22, 1: 8, 2: 7, 3: 10},
    }
)

df_2 = pd.DataFrame(
    {
        "fruit": {0: "banana", 1: "orange", 2: "apple", 3: "celery"},
        "quantity": {0: 27, 1: 8, 2: 8, 3: 10},
    }
)

In df_compare, we want to show the fruit names for which values are different in df_1 and df_2 (that is to say 'banana' and 'apple'):

df_compare = (
    df_1
    .compare(df_2, keep_equal=True, keep_shape=False)
    .pipe(lambda df_: df_.set_index(df_1.loc[df_.index, "fruit"]))
    .reset_index()
)

print(df_compare)
# Output
    fruit quantity      
              self other
0  banana       22    27
1   apple        7     8
user_112460560

thanks Laurent for the given dataset example:

df_1 = pd.DataFrame({"fruit": {0: "banana", 1: "orange", 2: "apple", 3: "celery"},
                     "quantity": {0: 22, 1: 8, 2: 7, 3: 10}})

df_2 = pd.DataFrame({"fruit": {0: "banana", 1: "orange", 2: "apple", 3: "celery"},
                     "quantity": {0: 27, 1: 8, 2: 8, 3: 10}})


df_compare = pd.concat([df_1['fruit'],
                        df_1.compare(df_2, keep_equal=True, keep_shape=False)],1).dropna()

print(df_compare)

    fruit  (quantity, self)  (quantity, other)
0  banana              22.0               27.0
2   apple               7.0                8.0
user_183445120
Related