0

Currently, I have a dataset that has data pulled on a weekly basis like such

Date            Value
2020-06-29      5.63
2020-07-06      6.01
2020-07-13      5.83

I am looking to fill in the gaps between the data by forward filling the values like such

Date            Value
2020-06-29      5.63
2020-06-30      5.63
2020-07-01      5.63
2020-07-02      5.63
2020-07-03      5.63
2020-07-04      5.63
2020-07-05      5.63
2020-07-06      6.01
2020-07-07      6.01
2020-07-08      6.01

and so on

Is there a way to automate this procedure using python?

Thanks

Ryan Reid
  • 189
  • 1
  • 3
  • 9
  • 3
    Does this answer your question? [Add missing dates to pandas dataframe](https://stackoverflow.com/questions/19324453/add-missing-dates-to-pandas-dataframe) – sushanth Aug 06 '20 at 14:39

1 Answers1

0

I created the dataframe just to replicate the data, but try this

# Import libraries
import pandas as pd
import numpy as np
from datetime import datetime

# Create DataFrame
df = pd.DataFrame({'Date':['2020-06-29','2020-07-06','2020-07-13'],
                   'Value': [5.63, 6.01, 5.83]})

# Convert to datetime datatype
df['Date'] = pd.to_datetime(df['Date']) 

# Store minimum date and max date in variables
s_range = df['Date'].min()
e_range = df['Date'].max()

# Generate date ranges between oldest and newest date
dates = pd.DataFrame(list(pd.date_range(s_range, e_range)),columns=['Date'])

# Merge dates DataFrame with original DataFrame
new_df = pd.merge(df,dates,on='Date',how='right').sort_values(['Date'])

# Reset index
new_df.reset_index(drop=True, inplace=True)

# Forward fill NaN with missing data
new_df.ffill(inplace=True)

    Date    Value
0   2020-06-29  5.63
1   2020-06-30  5.63
2   2020-07-01  5.63
3   2020-07-02  5.63
4   2020-07-03  5.63
5   2020-07-04  5.63
6   2020-07-05  5.63
7   2020-07-06  6.01
8   2020-07-07  6.01
9   2020-07-08  6.01
10  2020-07-09  6.01
11  2020-07-10  6.01
12  2020-07-11  6.01
13  2020-07-12  6.01
14  2020-07-13  5.83
Mike
  • 337
  • 3
  • 9