Add new rows to dataframe using existing rows from previous year

user_1833709 0
Viewed 32

I'm creating a Pandas dataframe from an existing file and it ends up essentially like this.

import pandas as pd
import datetime
data = [[i, i+1] for i in range(14)]
index = pd.date_range(start=datetime.date(2019,1,1), end=datetime.date(2020,2,1), freq='MS')
columns = ['col1', 'col2']
df = pd.DataFrame(data, index, columns)

Notice that this doesn't go all the way up to the present -- often the file I'm pulling from is a month or two behind. What I then need to do is add on any missing months and fill them with the same value as the previous year.

So in this case I need to add another row that is

2020-03-01   2   3

It could be anywhere from 0-2 rows that need to be added to the end of the dataframe at a given point in time. What's the best way to do this?

Note: The data here is not real so please don't take advantage of the simple pattern of entries I gave above. It was just a quick way to fill two columns of a table as an example.

pandas python-3.x
1 Answers

If I understand your problem, then the following should help you. This does assume that you always have data 12 months ago however. You can define a new DataFrame which includes the months up to the most recent date.

# First create the new index. Get the most recent date and add an offset.
start, end = df.index[-1] + pd.DateOffset(), pd.Timestamp.now()
index_new = pd.date_range(start, end, freq='MS')

Create your DataFrame

# Get the data from the previous year.
data = df.loc[index_new - pd.DateOffset(years=1)].values
df_new = pd.DataFrame(data, index = index_new, columns=df.columns)

which looks like

            col1    col2
2020-03-01     2       3

then just use;

pd.concat([df, df_new], axis=0)

Which gives 

           col1  col2
2019-01-01    0     1
2019-02-01    1     2
2019-03-01    2     3
...         ...   ...
2020-02-01   13    14
2020-03-01    2     3

Note

This also works for cases where the number of months missing is greater than 1.

Edit

Slightly different variation

# Create series with missing months added.
# Get the corresponding data 12 months prior.
s = pd.date_range(df.index[0], pd.Timestamp.now(), freq='MS')
fill = df.loc[s[~s.isin(df.index)] - pd.DateOffset(years=1)]

# Reindex the original dataframe
df = df.reindex(s)

# Find the dates to fill and replace with lagged data
df.iloc[-1 * fill.shape[0]:] = fill.values
user_95635370
Related