1

I am a new python user and have a few questions regarding filling NA's of a data frame.

Currently, I have a data frame that has a series of dates from 2022-08-01 to 2037-08-01 with a frequency of monthly data.

However, after 2027-06-01 the pricing data stops and I would like to extrapolate the values forward to fill out the rest of the dates. Essentially I would like to take the last 12 months of prices and fill those forward for the rest of the data frame. I am thinking of doing some type of groupby month with a fillna(method=ffill) however when I do this it just fills the last value in the df forward. Below is an example of my code.

Above is a picture you will see that the values stop at 12/1/2023 I wish to fill the previous 12 values forward for the rest of the maturity dates. So all prices fro 1/1/2023 to 12/1/2023 will be fill forward for all months.

import pandas as pd

mat = pd.DataFrame(pd.date_range('01/01/2020','01/01/2022',freq='MS'))
prices = pd.DataFrame(['179.06','174.6','182.3','205.59','204.78','202.19','216.17','218.69','220.73','223.28','225.16','226.31'])

example = pd.concat([mat,prices],axis=1)
example.columns = ['maturity', 'price']

Output

0  2020-01-01  179.06
1  2020-02-01   174.6
2  2020-03-01   182.3
3  2020-04-01  205.59
4  2020-05-01  204.78
5  2020-06-01  202.19
6  2020-07-01  216.17
7  2020-08-01  218.69
8  2020-09-01  220.73
9  2020-10-01  223.28
10 2020-11-01  225.16
11 2020-12-01  226.31
12 2021-01-01     NaN
13 2021-02-01     NaN
14 2021-03-01     NaN
15 2021-04-01     NaN
16 2021-05-01     NaN
17 2021-06-01     NaN
18 2021-07-01     NaN
19 2021-08-01     NaN
20 2021-09-01     NaN
21 2021-10-01     NaN
22 2021-11-01     NaN
23 2021-12-01     NaN
24 2022-01-01     NaN
ARE
  • 99
  • 9
  • If you can access the value that you want to fill all `NaN` values with, why not just place it in `fillna(some value)`? – Michael S. Aug 04 '22 at 15:36
  • 1
    Can you please include a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example)? It's hard to know what you want without seeing before data, and desired output. – BeRT2me Aug 04 '22 at 15:37
  • Just added a example picture, does this help sorry if it was not clear – ARE Aug 04 '22 at 15:55
  • @ARE How am I supposed to do testing on an image? [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – BeRT2me Aug 04 '22 at 15:59
  • @BeRT2me Thank you for your patience I added a few example lines of code essentially the goal would be to take the previous 12 values and assign them to the next 12 NANs. It is safe to assume that the date range of NA's will go far longer than this example – ARE Aug 04 '22 at 16:12
  • would this https://stackoverflow.com/questions/48000804/pandas-extrapolate-missing-values-at-the-tail or this https://stackoverflow.com/questions/22491628/extrapolate-values-in-pandas-dataframe help you? – n4321d Aug 04 '22 at 16:15

1 Answers1

0

Is this what you're looking for?

out = df.groupby(df.maturity.dt.month).ffill()
print(out)

Output:

     maturity   price
0  2020-01-01  179.06
1  2020-02-01   174.6
2  2020-03-01   182.3
3  2020-04-01  205.59
4  2020-05-01  204.78
5  2020-06-01  202.19
6  2020-07-01  216.17
7  2020-08-01  218.69
8  2020-09-01  220.73
9  2020-10-01  223.28
10 2020-11-01  225.16
11 2020-12-01  226.31
12 2021-01-01  179.06
13 2021-02-01   174.6
14 2021-03-01   182.3
15 2021-04-01  205.59
16 2021-05-01  204.78
17 2021-06-01  202.19
18 2021-07-01  216.17
19 2021-08-01  218.69
20 2021-09-01  220.73
21 2021-10-01  223.28
22 2021-11-01  225.16
23 2021-12-01  226.31
24 2022-01-01  179.06
BeRT2me
  • 12,699
  • 2
  • 13
  • 31
  • Hey @BeRT2me, thank you for this. Would you be able to provide me some explanation on how the forward fill operates here? How does it know to define twelve months and which twelve months to take from? Thank you – ARE Aug 08 '22 at 21:09
  • It groups by month number, so a group for each month number is made, and within each of those groups data is filled forward. So if the last value if finds for month 1 is 179.06, then all future month 1's will be filled with 179.06 unless they already have their own value. – BeRT2me Aug 08 '22 at 23:53