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 |