The Prototype Dataframe is missing consecutive numbers in the A Prime column. Once they have been filled in, the missing values in the B column need to be interpolated from A Prime.
After this has been completed, I can merge the Prototype Dataframe with the Primary Dataframe.
The interpolations are grouped by Prototype name.
What is the pythonic way to recreate this dataframe with missing values, interpolate the B column, and join the Prototype Dataframe to the Primary Dataframe?
Prototype Dataframe
| Proto Name| A Prime | B |
|-----------|---------|---------|
| Alpha | 3.26 | 0.16608 |
| Alpha | 3.24 | 0.16575 |
| Alpha | 3.22 | 0.16540 |
| Alpha | 3.20 | 0.16506 |
| Alpha | 3.18 | 0.16470 |
| Alpha | 3.16 | 0.16434 |
| Alpha | 3.14 | 0.16398 |
| Bravo | 1.52 | 0.10759 |
| Bravo | 1.50 | 0.10687 |
| Bravo | 1.48 | 0.10614 |
| Bravo | 1.46 | 0.10541 |
| Bravo | 1.44 | 0.10469 |
| Bravo | 1.42 | 0.10396 |
Below is the primary dataframe which I have merged on using a left join.
Primary Dataframe
| Name | Date | A | A Prime | B | Proto Name |
|--------|-----------|------|---------|---------|------------|
| Z3Pats | 6/1/2018 | 3.24 | 3.24 | 0.16575 | Alpha |
| Z3Pats | 5/1/2018 | 3.23 | NaN | NaN | Alpha |
| Z3Pats | 4/1/2018 | 3.21 | NaN | NaN | Alpha |
| Z3Pats | 3/1/2018 | 3.20 | 3.20 | 0.16506 | Alpha |
| Z3Pats | 2/1/2018 | 3.16 | 3.16 | 0.16434 | Alpha |
| CO-119 | 5/1/2018 | 1.53 | NaN | NaN | Bravo |
| CO-119 | 4/1/2018 | 1.51 | NaN | NaN | Bravo |
| CO-119 | 3/1/2018 | 1.48 | 1.48 | 0.10614 | Bravo |
| CO-119 | 2/1/2018 | 1.48 | 1.48 | 0.10614 | Bravo |
| CO-119 | 1/1/2018 | 1.45 | NaN | NaN | Bravo |
| CO-119 | 12/1/2017 | 1.44 | 1.44 | 0.10469 | Bravo |
| CO-119 | 11/1/2017 | 1.41 | 1.41 | 0.10396 | Bravo |
I use multiIndex, reindex, and interpolate:
which gives