update data frame based on data from another data frame using pandas python

Question

I have two data frames df1 and df2. Both have first column common SKUCode=SKU

df1:

df2:

I want to update df1 and set SKUStatus=0 if SKUCode matches SKU in df2.

I want to add new row to df1 if SKU from df2 has no match to SKUCode.

So after the operation df1 looks like following:

One way I could get this done is via df2.iterrows() and looping through values however I think there must be another neat way of doing this? Thank you

import pandas as pdx

df1=pdx.DataFrame({'SKUCode':['A','B','C','D'],'ListPrice':[1798,2997,1798,999],'SalePrice':[1798,2997,1798,999],'SKUStatus':[1,1,1,0],'CostPrice':[500,773,525,300]})

df2=pdx.DataFrame({'SKUCode':['X','Y','B'],'Status':[0,0,0],'e_date':['31-05-2020','01-06-2020','01-06-2020']})


df1.merge(df2,left_on='SKUCode')

This might help, https://stackoverflow.com/questions/53645882/pandas-merging-101 — sushanth, Jun 02 '20 at 11:22
it would be good if u can add sample input data instead of screen host, it will allow us to re-produce the issue. — sushanth, Jun 02 '20 at 11:34

score 0 · Answer 1 · answered Jun 02 '20 at 11:18

0

I'm not sure exactly if I understood you correctly but I think you can use .loc. something along the lines of:

df1.loc[df2['SKUStatu'] != 0, 'SKUStatus'] = 1

answered Jun 02 '20 at 11:18

snatchysquid

1,283
9
24

score 0 · Answer 2 · answered Jun 02 '20 at 11:19

0

You should have a look at pd.merge function [https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html].

First rename a column with the same name (e.g rename SKU to SKUCode). Then try:

df1.merge(df2, left_on='SKUCode')

If you provide input data (not screenshots), I can try with the appropriate parameters.

answered Jun 02 '20 at 11:19

pyOliv

1,253
1
6
21

Looking at provide code but I get a type error when trying above: object of type 'NoneType' has no len() – failsafe100 Jun 02 '20 at 11:31
jst added the code. I think the issue is that two DFs are not of same size – failsafe100 Jun 02 '20 at 12:08

sushanth · Accepted Answer · 2020-06-02T12:31:33.747

try this, using outer merge which gives both matching and non-matching records.

In [75]: df_m = df1.merge(df2, on="SKUCode", how='outer')                                                                                                         

In [76]: mask = df_m['Status'].isnull()                                                                                                                       

In [77]: df_m.loc[~mask, 'SKUStatus'] = df_m.loc[~mask, 'Status']

In [78]: df_m[['SKUCode', "ListPrice", "SalePrice", "SKUStatus", "CostPrice"]].fillna(0.0)

output

  SKUCode  ListPrice  SalePrice  SKUStatus  CostPrice
0       A     1798.0     1798.0        1.0      500.0
1       B     2997.0     2997.0        0.0      773.0
2       C     1798.0     1798.0        1.0      525.0
3       D      999.0      999.0        0.0      300.0
4       X        0.0        0.0        0.0        0.0
5       Y        0.0        0.0        0.0        0.0

merge operation is converting my int columns like ListPrice to decimal, is there a way to stop this? — failsafe100, Jun 02 '20 at 15:03

update data frame based on data from another data frame using pandas python

3 Answers3