0
$\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
    Jan 7 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
    Jan 7 at 20:41
  • $\begingroup$ See my answer for an example with code. $\endgroup$
    – Oxbowerce
    Jan 7 at 21:08
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
    Jan 7 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$
0
$\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$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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