3
$\begingroup$

I have two dataframes, one is current week's information, one is of last week. I want to create a new dataset that lists all the changes during the week. Please see the following example: if there is a change, indicates the change, otherwise leaves it as blank. If there is new item, indicates it is new. enter image description here

I tried to compare these two columns, but I keep getting an error

can only compare identically-labelled series objects
$\endgroup$
3
  • $\begingroup$ The easiest way of accomplishing this would be to join the two dataframes using the ID columns and then compare the columns to check for changes. $\endgroup$
    – Oxbowerce
    Commented Jan 7, 2022 at 17:37
  • $\begingroup$ Hi, Here is the my code, it does not work well. it returns whole list of past to current for the row that has change. 'df=pd.merge(current,past,how='left', on='ID') df.columns.tolist() conditions=[df['Rating _x']!=df['Rating_y'], df[‘Rating _x']==df['Rating _y']] choices=[f"from: {df['Rating_y']} to: {df['Rating_x']}", " "] change['Rating']=np.select(conditions,choices,default='NA')' $\endgroup$
    – Scarlett Q
    Commented Jan 7, 2022 at 20:41
  • $\begingroup$ See my answer for an example with code. $\endgroup$
    – Oxbowerce
    Commented Jan 7, 2022 at 21:08

3 Answers 3

2
$\begingroup$
import pandas as pd
import numpy as np

old = pd.DataFrame({
    "ID": ["AA", "BB", "CC"],
    "Rating": ["High", "Low", "Medium"],
    "Status": ["On track", "Monitor", "On track"]
})

new = pd.DataFrame({
    "ID": ["AA", "BB", "CC", "DD"],
    "Rating": ["Medium", "High", "Medium", "Low"],
    "Status": ["On track", "On track", "On track", "Monitor"]
})

(
    old
    # join the two dataframes used the ID column as a key
    .merge(new, how="outer", on="ID", suffixes=("_old", "_new"))
    # compare columns between old and new dataframe and assign new values
    .assign(
        Rating = lambda x: np.select(
            [x["Rating_new"].notna() & x["Rating_old"].isna(), x["Rating_new"] != x["Rating_old"]],
            ["New", "From '" + x["Rating_old"] +  "' To '" + x["Rating_new"] + "'"],
            default=np.nan
        ),
        Status = lambda x: np.select(
            [x["Status_new"].notna() & x["Status_old"].isna(), x["Status_new"] != x["Status_old"]],
            ["New", "From '" + x["Status_old"] +  "' To '" + x["Status_new"] + "'"],
            default=np.nan
        )
    )
    # select final columns
    .loc[:, ["ID", "Rating", "Status"]]
)
ID Rating Status
AA From 'High' To 'Medium' nan
BB From 'Low' To 'High' From 'Monitor' To 'On track'
CC nan nan
DD New New
$\endgroup$
0
$\begingroup$

Please merge (left Join) the current table to previous table, Now you will have all the 4 columns in one dataframe. You can apply concatenate of columns to get desired results.

Please share dataframe creation code if you need help with code creat

$\endgroup$
1
  • $\begingroup$ The comparison code does not work. The if statement gives me error" ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()." df=pd.merge(current,past,how='left', on='ID') if df['Value1_x']==df['Value1_y']: change['Value1']=" " else: change['Value1']="Change from "+ df['value1_y'] +" To "+df['value1_x'] $\endgroup$
    – Scarlett Q
    Commented Jan 7, 2022 at 19:23
0
$\begingroup$

Brute force, but it solves the problem. This might be a good start:

old = pd.DataFrame({"a":[1,2,3],"b":["a","b","c"],"c":["23-11","11-90",None]})
new = pd.DataFrame({"a":[9,2,3],"b":["a","x","y"],"c":["23-11",None,None]})
check = pd.DataFrame([], columns = ["a","b","c"])

for ix,row in new.iterrows():
    for col in new.columns:
        if new.loc[ix,col] != old.loc[ix,col]:
            check.loc[ix,col] = f"from: {old.loc[ix,col]} to: {new.loc[ix,col]}"
        else:
            check.loc[ix,col] = ""

old:

enter image description here

new:

enter image description here

check:

enter image description here

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.