I've the following data. How can I add all dates (from 1st to the end of the month) ? also how can I remove saturdays and sundays from this dataset?
Date values
31/03/14 -0.0123
30/04/14 0.11168
30/06/14 0.0997
31/07/14 0.007
30/09/14 0.886
Date values
1/3/14
2/3/14
.....
..
31/3/14
1/4/14
2/4/14
....
.....
30/09/14
import pandas as pd
data = '''\
Date values
31/03/14 -0.0123
30/04/14 0.11168
30/06/14 0.0997
31/07/14 0.007
30/09/14 0.886'''
# This operation includes reading the dataset, converting Date to Datetime and
# setting Date as index
df = pd.read_csv(pd.compat.StringIO(data),sep='\s+',parse_dates=['Date'],index_col='Date')
# Resample day
df = df.resample('D').sum() # or first() or mean()
# Remove weekdays smaller than 5 (saturday and sunday) and reset
df = df.loc[df.index.weekday < 5].reset_index()
print(df.head())
And you get (printing first 5 rows):
Date values
0 2014-03-31 -0.0123
1 2014-04-01 NaN
2 2014-04-02 NaN
3 2014-04-03 NaN
4 2014-04-04 NaN
The equivalent assuming you already loaded your dataset (compact). I also added not May or August mask here if you want to exclude those months.
df = df.set_index(pd.to_datetime(df.Date)).drop('Date', axis = 1)
df = df.resample('D').first()
m1 = df.index.weekday < 5 # mask1 (no sat/sun)
m2 = ~df.index.month.isin([5,8]) # mask2 (not May or August)
df = df.loc[m1 & m2].reset_index()
df.fillna(method='ffill', inplace=True) or bfill and you got a na-fill too.You can using date_range
df.Date=pd.to_datetime(df.Date)
s=pd.DataFrame({'Date':sum([pd.date_range(x,y,freq='D').tolist() for x,y in zip(pd.to_datetime(df.Date.dt.strftime('%Y-%m')),df.Date)],[])})
s=s.merge(df)
s=s[s.Date.dt.weekday<5]
