Fill in gaps in panel data using Python Pandas

Question

Consider an unbalanced panel where the gaps are informative (e.g. true zeros). I would like to add the zeros back in. Essentially, I am trying to recreate the functionality of the stata function, tsfill, in pandas.

Example data (I construct a balanced panel, and remove some of the observations):

import numpy as np
import pandas as pd
import datetime

np.random.seed(123456)

all_dates = pd.DataFrame(pd.date_range(datetime.date(2015,1,1),datetime.date(2015,12,31)),columns=['date'])
balanced_data=all_dates.copy()
balanced_data['id']=0
for x in range(99):
    appendme=all_dates
    appendme['id']=x+1
    balanced_data=balanced_data.append(appendme)

balanced_data.reset_index(inplace=True,drop=True)
balanced_data['random']=np.random.random_sample(balanced_data.shape[0])>=0.5

# remove some data
unbalanced_data=balanced_data[balanced_data['random']==1].reset_index(drop=True)

One way to make the panel balanced again is to merge the unbalanced panel to a dataframe with balanced id and date columns:

# construct one full set of dates for everyone
all_dates = pd.DataFrame(pd.date_range(unbalanced_data['date'].min(),unbalanced_data['date'].max()),columns=['date'])

length = unbalanced_data['id'].unique().size
all_dates_full=all_dates
for x in range(length-1):
    all_dates_full=all_dates_full.append(all_dates)

all_dates_full.reset_index(inplace=True,drop=True)

# duplicate ids to match the number of dates 
length = all_dates.size
ids=unbalanced_data['id'].drop_duplicates()
ids_full=ids
for x in range(length-1):
    ids_full=ids_full.append(ids)

ids_full.sort_values(inplace=True)
ids_full.reset_index(inplace=True,drop=True)

balanced_panel = pd.concat([all_dates_full,ids_full],axis=1)

rebalanced_data=pd.merge(balanced_panel,unbalanced_data,how='left',on=['id','date'])
rebalanced_data.fillna(False,inplace=True)

# check
balanced_data==rebalanced_data

In addition to being clunky, I find this approach is really slow as N gets big. I figured there must be a more efficient way to rebalance the panel, but I couldn't find it.

(PS This is my first question on stackoverflow, so any constructive criticism for future questions very much appreciated!)

Answer 1

As far as performance goes, appending dataframes in pandas is a slow operation when compared to appending lists. Indexes are immutable, so a new index is created each time you append. Here is a solution that builds collections outside of pandas and then joins them into a dataframe.

uid = unbalanced_data['id'].unique()
ids_full = np.array([[x]*len(all_dates) for x in range(len(uid))]).flatten()
dates = all_dates['date'].tolist() * len(uid)
balanced_panel = pd.DataFrame({'id': ids_full, 'date': dates})
rebalanced_data = pd.merge(balanced_panel, unbalanced_data, how=‌​'left',
                           on=['id', 'dat‌​e']).fillna(False)