I have data that shows the price of products once every three years. The pandas dataframe looks something like this:
product_group 2012 2013 2014 2015 2016 2017 2018 2019 product
0 x NaN NaN 12 NaN NaN 14 NaN NaN shoe
1 x NaN NaN 2 NaN NaN 2.8 NaN NaN sock
2 y 14 NaN NaN 16 NaN NaN 16 NaN cheese
...
Separately, I also have a dataframe containing the average prices per year for each product group, as a percentage of base year 2015:
product_group 2012 2013 2014 2015 2016 2017 2018 2019
0 x 0.84 0.85 0.96 1 0.98 1.02 1.07 1.2
1 y 0.92 0.97 0.99 1 1.48 1.5 1.52 1.75
Now I need to find a price estimate for each product, each year by:
- Between two known prices: take the geometric mean of
- Extrapolated value from the earlier known value, with the average product group prices
- Retropolated value from the later value, with average product group prices
- So for example here, for the 2015 value of shoe we need the geometric mean of the known 2014 value (= 12), divided by 0.96 and the 2017 value(=14) divided by 1.02
- If only a later price is known: Retropolate from a this known later value (similar to step 2 in the first case)
- If only an earlier price is known: Extrapolate from this known earlier value (similar to step 1 in the first case)
Does anyone have an idea how to achieve this?
