Interpolate both directions with pandas while first and last values are missing?

user_10724206 0
Viewed 141

I have a tiny issue while trying to interpolate the electricity demand data via pandas. I have the projections for yearly electricity consumption in my country for the years 2020,2025,2030,2035 and 2040. What I need to do is extend these projections backward until 2017 and forward until 2050. In the code below you can see my initial data frame.

Year    Demand1 (TWh)   Demand2 (TWh)   Demand3 (TWh) 
2017     NaN                  NaN          NaN
2018     NaN                  NaN          NaN  
2019     NaN                  NaN          NaN  
2020    305.0                305.0        305.0
2021    NaN                   NaN          NaN  
2022    NaN                   NaN          NaN  
2023    NaN                   NaN          NaN
2024    NaN                   NaN          NaN  
2025    366.0                 370.0        373.0    
2026    NaN                   NaN          NaN
......
2030.   427.0                440.0         450.000000
......
2035    485.0                507.0          527.000000
......
2040    545.0                591.0          636.000000

So, basically, I am trying to fill these NaN values. However, when I'm trying to apply interpolations with the code below I cannot find the intended results.

demand['Demand1 (TWh)'] = demand['Demand1 (TWh)'].interpolate(method="linear",limit_direction='both')
demand['Demand2 (TWh)'] = demand['Demand2 (TWh)'].interpolate(method="linear",limit_direction='both')
demand['Demand3 (TWh)'] = demand['Demand3 (TWh)'].interpolate(method="linear",limit_direction='both')
demand



Year    Demand1 (TWh)   Demand2 (TWh)   Demand3 (TWh)
2017    305.0             305.0         305.000000 
2018    305.0             305.0         305.000000
2019    305.0             305.0         305.000000 
2020    305.0             305.0         305.000000
2021    317.2             318.0         317.683429 
2022    329.4             331.0         330.825143
2023    341.6             344.0         344.425143 
2024    353.8             357.0         358.483429
2025    366.0             370.0         373.000000 
2026    378.2             384.0         387.974857
2027    390.4             398.0         403.408000 
2028    402.6             412.0         419.201143
2029    414.8             426.0         434.764571 
2030    427.0             440.0         450.000000
2031    438.6             453.4         464.907429 
2032    450.2             466.8         479.486857
2033    461.8             480.2         493.968000 
2034    473.4             493.6         509.729143
2035    485.0             507.0         527.000000 
2036    497.0             523.8         545.780571
2037    509.0             540.6         566.070857 
2038    521.0             557.4         587.870857
2039    533.0             574.2         611.180571 
2040    545.0             591.0         636.000000
2041    545.0             591.0         636.000000
2042    545.0             591.0         636.000000
2043    545.0             591.0         636.000000
2044    545.0             591.0         636.000000
2045    545.0             591.0         636.000000
2046    545.0             591.0         636.000000
2047    545.0             591.0         636.000000
2048    545.0             591.0         636.000000
2049    545.0             591.0         636.000000
2050    545.0             591.0         636.000000

But as you can see, 2017-2018-2019 values are the same as 2020 and from 2041 to 2050 all values are the same as 2040. I could not understand why and how to solve it. I would appreciate any help. Thanks!

pandas interpolation python
2 Answers

I believe that the issue here is that there is only one set of values at 2020 to interpolate from 2017 to 2020 and that there is only one set of values at 2040 to interpolate from 2040-2050. You're either going to have to accept the limits of your model, or add data at or before 2017 and at or after 2050 if you don't want these values to all be the same.

user_46252530

To extrapolate those ranges, we can go to a lower level; to scipy.interpolate.interp1d which has a fill_value parameter that we can supply with extrapolate:

from scipy.interpolate import interp1d

df = df.set_index("Year")

df.apply(lambda col: interp1d(*zip(*col.dropna().items()), 
                              fill_value="extrapolate")(col.index))

where we first set the Year as the index to ignore it in the interpolation computations and then pass the NaN-dropped column's values to interp1d which gives a function back. We then immediately call this function with the index of the column, i.e., all the years. The *zip(* there converts each column into two arrays of index & values that scipy uses to interpolate. This process happens for each column with apply,

to get (I copied the data between 2017-2026):

      Demand1 (TWh)  Demand2 (TWh)  Demand3 (TWh)
Year
2017          268.4          266.0          264.2
2018          280.6          279.0          277.8
2019          292.8          292.0          291.4
2020          305.0          305.0          305.0
2021          317.2          318.0          318.6
2022          329.4          331.0          332.2
2023          341.6          344.0          345.8
2024          353.8          357.0          359.4
2025          366.0          370.0          373.0
2026          378.2          383.0          386.6

enter image description here

user_93321870
Related