pandas, update dataframe values ​with a not in the same format dataframe

user_15415267 0
Viewed 80

i have two dataframes. The second dataframe contains the values ​​to be updated in the first dataframe. df1:

data=[[1,"potential"],[2,"lost"],[3,"at risk"],[4,"promising"]]
df=pd.DataFrame(data,columns=['id','class'])

id  class
1   potential
2   lost
3   at risk
4   promising

df2:

data2=[[2,"new"],[4,"loyal"]]
df2=pd.DataFrame(data2,columns=['id','class'])

id  class
2   new
4   loyal

expected output:

data3=[[1,"potential"],[2,"new"],[3,"at risk"],[4,"loyal"]]
df3=pd.DataFrame(data3,columns=['id','class'])

id  class
1   potential
2   new
3   at risk
4   loyal

The code below seems to be working, but I believe there is a more effective solution.

final=df.append([df2])
final = final.drop_duplicates(subset='id', keep="last")

addition: Is there a way for me to write the previous value in a new column? like this:

id   class      prev_class    modified date
1    potential  nan           nan
2    new        lost          2022.xx.xx 
3    at risk    nan           nan
4    loyal      promising     2022.xx.xx
pandas dataframe python
3 Answers

Your solution is good, here is alternative with concat and added DataFrame.sort_values:

df = (pd.concat([df, df2])
        .drop_duplicates(subset='id', keep="last")
        .sort_values('id', ignore_index=True))
print (df)
   id      class
0   1  potential
1   2        new
2   3    at risk
3   4      loyal

Solution is change if need add previous class values and today:

df3 = pd.concat([df, df2])
mask = df3['id'].duplicated(keep='last')

df31 = df3[mask]
df32 = df3[~mask]

df3 = (df32.merge(df31, on='id', how='left', suffixes=('','_prev'))
           .sort_values('id', ignore_index=True))
df3.loc[df3['class_prev'].notna(), 'modified date'] = pd.to_datetime('now').normalize()
print (df3)
   id      class class_prev modified date
0   1  potential        NaN           NaT
1   2        new       lost    2022-03-31
2   3    at risk        NaN           NaT
3   4      loyal  promising    2022-03-31
user_29010020

We can use DataFrame.update

df = df.set_index('id')
df.update(df2.set_index('id'))
df = df.reset_index()

Result

print(df)

   id      class
0   1  potential
1   2        new
2   3    at risk
3   4      loyal
user_128331660

You can operate along your id's by setting them as your index, and use combine_first to perform this operation. Then assigning youre prev_class is extremely straightforward because you've properly used the Index!

df = df.set_index('id')
df2 = df2.set_index('id')

out = (
    df2.combine_first(df)
    .assign(
        prev_class=df2["class"],
        modified=lambda d: 
            d["prev_class"].where(
                d["prev_class"].isna(), pd.Timestamp.now()
            )
    )
)

print(out) 
        class prev_class                    modified
id                                                  
1   potential        NaN                         NaN
2         new        new  2022-03-31 06:51:20.832668
3     at risk        NaN                         NaN
4       loyal      loyal  2022-03-31 06:51:20.832668
user_142784480
Related