I have a problem that requires looking up the prices for products with a slight twist.
Product sold table
Date Prod_1 Prod_2 Prod_3
5/1/2020 0 0 0
6/1/2020 0 0 0
7/1/2020 0 0 0
8/1/2020 100 1,000 50
9/1/2020 120 1,100 100
10/1/2020 140 1,200 150
11/1/2020 160 1,300 200
12/1/2020 180 1,400 250
1/1/2021 200 1,500 300
2/1/2021 220 1,600 350
3/1/2021 240 1,700 400
4/1/2021 260 1,800 450
5/1/2021 280 1,900 500
6/1/2021 300 2,000 550
The price table looks like this
Date Prod_1 Prod_2 Prod_3
8/1/2020 50 10 2.00
9/1/2020 55 11 2.50
10/1/2020 56 12 2.65
11/1/2020 58 13 2.70
12/1/2020 60 14 2.80
1/1/2021 10 10 1.00
I want to compute something that will look up the price for each product depending on the date. If the price data does not contain the date, it simply uses the last price in the table. I have tried indexing on Date and reindexing to multiply but can't seem to get it right.
Final output should be something like this
Date Rev_Prod_1 Rev_Prod_2 Rev_Prod_3
5/1/2020 0 0 0
6/1/2020 0 0 0
7/1/2020 0 0 0
8/1/2020 5,000 10,000 100
9/1/2020 6,600 12,100 250
10/1/2020 7,840 14,400 398
11/1/2020 9,280 16,900 540
12/1/2020 10,800 19,600 700
1/1/2021 2,000 15,000 300
2/1/2021 2,200 15,000 350
3/1/2021 2,400 16,000 400
4/1/2021 2,600 17,000 450
5/1/2021 2,800 18,000 500
6/1/2021 3,000 19,000 550
what I have so far is able to join the tables on dates
prod_data = pd.read_csv('../Product_sold.csv')
prod_data.set_index('Date', inplace=True)
price_data = pd.read_csv('../Product_cost.csv')
price_data.set_index('Date', inplace=True)
prod_data_comb = prod_data.join(price_data)
which produces the output
Prod_1 Prod_2 Prod_3 Prod_1_Pr Prod_2_Pr Prod_3_Pr
Date
5/1/2020 0 0 0 NaN NaN NaN
6/1/2020 0 0 0 NaN NaN NaN
7/1/2020 0 0 0 NaN NaN NaN
8/1/2020 100 1,000 50 50.0 10.0 2.00
9/1/2020 120 1,100 100 55.0 11.0 2.50
10/1/2020 140 1,200 150 56.0 12.0 2.65
11/1/2020 160 1,300 200 58.0 13.0 2.70
12/1/2020 180 1,400 250 60.0 14.0 2.80
1/1/2021 200 1,500 300 10.0 10.0 1.00
2/1/2021 220 1,600 350 NaN NaN NaN
3/1/2021 240 1,700 400 NaN NaN NaN
4/1/2021 260 1,800 450 NaN NaN NaN
5/1/2021 280 1,900 500 NaN NaN NaN
6/1/2021 300 2,000 550 NaN NaN NaN
from which I can multiply but I want the prices after the data 1/1/2021 to be same as 1/1/2021 for multiplication.
