CODEWITHSUNDEEP
pythonpandasdataframejoin
Sesha Gagan Tipparaju
Sesha Gagan Tipparaju

Reputation: 1

Filling values based on values in another dataframe

I have two dataframes as shown below. I'd like to create non_promo_price and price columns in sales_df. If the week and year values for a row in sales_df lie between the start_week, start_year and end_week, end_year in promo_df, the non_promo_price and price values in promo_df need to mapped to sales_df. The values need to be 0 if there is no week corresponding to it in promo_df.


sales_df.head()
S.no ppg year week value_ty unit_ty price/unit
0 Daia_Refill_1.8liter 2018 1 8963.05 1631 5.50
1 Daia_Refill_1.8liter 2018 9 29205.14 4434 6.59
2 Daia_Refill_1.8liter 2018 10 30062.83 4561 6.59
3 Daia_Refill_1.8liter 2018 11 22691.00 3443 6.59
4 Daia_Refill_1.8liter 2018 12 29381.84 4463 6.58
5 Downy Perfume_Refill_490-580ml 2021 22 48742.50 9945 4.90

promo_df.head()

sno. ppg non_promo_price price start_year start_week end_year end_week
0 Daia_Refill_1.8liter 6.20 5.28 2018 2 2018 4
1 Daia_Refill_1.8liter 6.20 5.29 2018 7 2018 9
2 Daia_Refill_1.8liter 5.99 5.49 2018 20 2018 22
3 Daia_Refill_1.8liter 5.99 5.09 2018 25 2018 27
4 Daia_Refill_1.8liter 6.65 5.48 2019 1 2019 3
5 Daia_Refill_1.8liter 6.65 5.29 2019 12 2019 14

I tried a left join on sales_df but it was giving me null values for non_promo_price and price for weeks that have no data in promo_df.

pd.merge(sales_df, promo_df, left_on=["ppg", "year", "week"], right_on=["ppg", "start_year", "start_week"], how="left",)

S no. ppg year week value_ty unit_ty price/unit non_promo_price price start_year start_week end_year end_week
0 Daia_Refill_1.8liter 2018 1 8963.05 1631 5.50 NaN NaN NaN NaN NaN NaN
1 Daia_Refill_1.8liter 2018 9 29205.14 4434 6.59 NaN NaN NaN NaN NaN NaN
2 Daia_Refill_1.8liter 2018 10 30062.83 4561 6.59 NaN NaN NaN NaN NaN NaN

Upvotes: 0

Views: 38

Answers (1)

BeRT2me
BeRT2me

Reputation: 13242

temp_df = sales_df.merge(promo_df, how='left')
temp_df = temp_df[(temp_df.week.between(temp_df.start_week, temp_df.end_week)) & (temp_df.year.between(temp_df.start_year, temp_df.end_year))]
print(temp_df)

Output:

                     ppg  year  week  value_ty  unit_ty  price/unit  non_promo_price  price  start_year  start_week  end_year  end_week
7   Daia_Refill_1.8liter  2018     9  29205.14     4434        6.59             6.20   5.29      2018.0         7.0    2018.0       9.0

Upvotes: 1

Related Questions