Linear extrapolation in dataframes

I have a dataset with the number of households from 2009 to 2019 on a regional level. The dataset is very complete, but some of the data is missing. As an example, I have these two regions, IE01 and IE04:

n2hn_df.loc['IE01']

    Out[2]: 
    2009    455300.0
    2010    460600.0
    2011    465500.0
    2012         NaN
    2013         NaN
    2014         NaN
    2015         NaN
    2016         NaN
    2017         NaN
    2018         NaN
    2019         NaN
    Name: IE01, dtype: float64



n2hn_df.loc['IE04']
Out[3]: 
2009         NaN
2010         NaN
2011         NaN
2012    320700.0
2013    315300.0
2014    310500.0
2015    307500.0
2016    315400.0
2017    323300.0
2018    329300.0
2019    339700.0
Name: IE04, dtype: float64

I would like to complete the datasets with a linear extrapolation (as the number of households doesn’t change drastically over the years). I know that interpolating is easy, but something like

n2hn_df.interpolate(method='linear',axis=1,limit_direction='both',inplace=True)

only pads the dataset with the closest value found in both directions. I haven’t found a simple way to extrapolate data in dataframes, so I’d like to ask for your advice on the best way to do it. I’d appreciate any help you can provide. Thanks in advance!

EDIT: An example of the dataframes I want to extrapolate data from is:

enter image description here

Answer

I did something similar a while ago. It isn’t super pretty, but maybe you can use it. As an example I’m using the following DataFrame (modified version of your second example):

         value
year          
2009       NaN
2010       NaN
2011       NaN
2012  320700.0
2013  315300.0
2014  310500.0
2015  307500.0
2016  315400.0
2017       NaN
2018       NaN
2019       NaN

year is the index!

The 1. step is filling up the end piece of NaNs:

increment = df.value.diff(1).mean()
idx_max_notna = df.value[df.value.notna()].index.array[-1]
idx = df.index[df.index >= idx_max_notna]
df.value[idx] = df.value[idx].fillna(increment).cumsum()

Result:

         value
year          
2009       NaN
2010       NaN
2011       NaN
2012  320700.0
2013  315300.0
2014  310500.0
2015  307500.0
2016  315400.0
2017  314075.0
2018  312750.0
2019  311425.0

As increment I’ve used the mean of the existing diffs. If you want to use the last diff then replace it with:

increment = df.value.diff(1)[df.value.notna()].array[-1]

The 2. step of filling up the start piece of NaNs is more or less the same, just with the column value reversed, and at the end re-reversed:

df.value = df.value.array[::-1]
increment = df.value.diff(1).mean()
idx_max_notna = df.value[df.value.notna()].index.array[-1]
idx = df.index[df.index >= idx_max_notna]
df.value[idx] = df.value[idx].fillna(increment).cumsum()
df.value = df.value.array[::-1]

Result:

         value
year          
2009  324675.0
2010  323350.0
2011  322025.0
2012  320700.0
2013  315300.0
2014  310500.0
2015  307500.0
2016  315400.0
2017  314075.0
2018  312750.0
2019  311425.0

Important: The method assumes that there is no gap in the index (missing year).

As I said, not very pretty, but it worked for me.

(PS: Just to clarify the use of ‘similar’ above: This is indeed linear extrapolation.)


EDIT

Sample frame (the first 3 rows of the frame in the screenshot):

n2hn_df = pd.DataFrame(
        {'2010': [134.024, np.NaN, 36.711], '2011': [134.949, np.NaN, 41.6533],
         '2012': [128.193, np.NaN, 33.4578], '2013': [125.131, np.NaN, 33.4578],
         '2014': [122.241, np.NaN, 33.6356], '2015': [115.301, np.NaN, 35.5919],
         '2016': [108.927, 520.38, 40.1008], '2017': [106.101, 523.389, 41.38],
         '2018': [96.1861, 526.139, 49.0906], '2019': [np.NaN, np.NaN, np.NaN]},
        index=pd.Index(data=['AT', 'BE', 'BG'], name='NUTS_ID')
    )
            2010      2011      2012  ...     2017      2018  2019
NUTS_ID                               ...                         
AT       134.024  134.9490  128.1930  ...  106.101   96.1861   NaN
BE           NaN       NaN       NaN  ...  523.389  526.1390   NaN
BG        36.711   41.6533   33.4578  ...   41.380   49.0906   NaN

Extrapolation:

# Transposing frame
n2hn_df = n2hn_df.T
for col in n2hn_df.columns:
    # Extract column
    ser = n2hn_df[col].copy()

    # End piece
    increment = ser.diff(1).mean()
    idx_max_notna = ser[ser.notna()].index.array[-1]
    idx = ser.index[ser.index >= idx_max_notna]
    ser[idx] = ser[idx].fillna(increment).cumsum()

    # Start piece
    ser = pd.Series(ser.array[::-1])
    increment = ser.diff(1).mean()
    idx_max_notna = ser[ser.notna()].index.array[-1]
    idx = ser.index[ser.index >= idx_max_notna]
    ser[idx] = ser[idx].fillna(increment).cumsum()
    n2hn_df[col] = ser.array[::-1]

# Re-transposing frame
n2hn_df = n2hn_df.T

Result:

            2010      2011      2012  ...     2017      2018        2019
NUTS_ID                               ...                               
AT       134.024  134.9490  128.1930  ...  106.101   96.1861   91.456362
BE       503.103  505.9825  508.8620  ...  523.389  526.1390  529.018500
BG        36.711   41.6533   33.4578  ...   41.380   49.0906   50.638050