CODEWITHSUNDEEP
pandasinterpolation
404pio
404pio

Reputation: 1032

Interpolate stock data in pandas using 2 columns

I want to do interpolation of stock quotations. I have missing data for single day (as in example):

import numpy as np
import pandas as pd
dates = pd.date_range('20130101', periods=6)
df = pd.DataFrame(
    {'opening': [0.5, 1.3, np.NaN, 4, 5, 1],
     'closing': [0, 1, np.NaN, 2, 10, 2]}, index=dates)

            opening  closing
2013-01-01      0.5      0.0
2013-01-02      1.3      1.0
2013-01-03      NaN      NaN
2013-01-04      4.0      2.0
2013-01-05      5.0     10.0
2013-01-06      1.0      2.0

I need method to effieciently interpolate NaN's, that closing of 2013-01-02 is opening of 2013-01-03, and opening of 2013-01-04 is closing of 2013-01-03. Desired output:

2013-01-01      0.5      0.0
2013-01-02      1.3      1.0
2013-01-03      1.0      4.0
2013-01-04      4.0      2.0
2013-01-05      5.0     10.0
2013-01-06      1.0      2.0

I was trying to use apply, but it only have information about current row. I need access to previous and next row.

Upvotes: 0

Views: 85

Answers (1)

jezrael
jezrael

Reputation: 863166

Use DataFrame.assign because is necessary replace mssing values by forward or back filling 'paralel':

df = df.assign(opening = df['opening'].fillna(df['closing'].ffill()),
               closing = df['closing'].fillna(df['opening'].bfill()))
print (df)
            opening  closing
2013-01-01      0.5      0.0
2013-01-02      1.3      1.0
2013-01-03      1.0      4.0
2013-01-04      4.0      2.0
2013-01-05      5.0     10.0
2013-01-06      1.0      2.0

Upvotes: 3

Related Questions