Zanam Zanam - 1 month ago 11
Python Question

Pandas reading from csv file and filling missing values for datetime index

I have data in a csv file which appears as:

DateTime Temp
10/1/2016 0:00 20.35491156
10/1/2016 1:00 19.75320845
10/1/2016 4:00 17.62411292
10/1/2016 5:00 18.30190001
10/1/2016 6:00 19.37101638


I am reading this file into csv file as:

import numpy as np
import pandas as pd
data = pd.read_csv(r'C:\Curve.csv', index_col='DateTime')
newIndex = pd.date_range(np.min(data.index), np.max(data.index),freq='1H')
data.reindex(newIndex)


My goal is to backfill the missing hours 2 and 3 with 19.75320845. i.e. every time there is are missing data it should do the backfill.

Answer
import pandas as pd
data = pd.read_csv(r'Curve.csv', index_col='DateTime', parse_dates=['DateTime'])
data = data.asfreq('1H', method='ffill')

yields

                          Temp
DateTime                      
2016-10-01 00:00:00  20.354912
2016-10-01 01:00:00  19.753208
2016-10-01 02:00:00  19.753208
2016-10-01 03:00:00  19.753208
2016-10-01 04:00:00  17.624113
2016-10-01 05:00:00  18.301900
2016-10-01 06:00:00  19.371016

method='ffill' tells asfreq to "forward-fill" missing values using the last valid (non-NaN) value.