gall3on
gall3on

Reputation: 63

Update Specific Pandas Rows with Value from Different Dataframe

I have a pandas dataframe that contains budget data but my sales data is located in another dataframe that is not the same size. How can I get my sales data updated in my budget data? How can I write conditions so that it makes these updates?

DF budget:
        cust   type   loc   rev   sales   spend
    0   abc    new    north 500   0       250
    1   def    new    south 700   0       150
    2   hij    old    south 700   0       150

DF sales:
        cust    type   loc    sales
    0   abc     new    north  15
    1   hij     old    south  18

DF budget outcome:
        cust   type   loc   rev   sales   spend
    0   abc    new    north 500   15      250
    1   def    new    south 700   0       150
    2   hij    old    south 700   18      150

Any thoughts?

Upvotes: 2

Views: 1651

Answers (1)

EdChum
EdChum

Reputation: 394469

Assuming that 'cust' column is unique in your other df, you can call map on the sales df after setting the index to be the 'cust' column, this will map for each 'cust' in budget df to it's sales value, additionally you will get NaN where there are missing values so you call fillna(0) to fill those values:

In [76]:

df['sales'] = df['cust'].map(df1.set_index('cust')['sales']).fillna(0)
df
Out[76]:
  cust type    loc  rev  sales  spend
0  abc  new  north  500     15    250
1  def  new  south  700      0    150
2  hij  old  south  700     18    150

Upvotes: 1

Related Questions