Remove row from data frame when index (DateTime) is Sunday

Question

Data examples

                              Open     High      Low    Close
DateTime                                                     
2016-01-03 00:00:00+00:00  1.08701  1.08723  1.08451  1.08515
2016-01-04 00:00:00+00:00  1.08701  1.09464  1.07811  1.08239
2016-01-05 00:00:00+00:00  1.08238  1.08388  1.07106  1.07502
2016-01-06 00:00:00+00:00  1.07504  1.07994  1.07185  1.07766
2016-01-07 00:00:00+00:00  1.07767  1.09401  1.07710  1.09256
2016-01-08 00:00:00+00:00  1.09255  1.09300  1.08030  1.09218

DateTime is an index, you must delete the row with DateTime as Sunday or Saturday (2016-01-03).

I am reading this data from a cvs file

df = pd.read_csv(filename, names=['DateTime','Open','High','Low','Close'],
                 parse_dates = [0], index_col = 'DateTime')

tried to do something like below, but didn't work.

df = df.drop(df[df.weekday() == 6].index) #delete Sundays

Answer 1

You can use asfreq('B')to reindexdf in line weekdays . Note, however, that if the workday is not in df.index, it asfreqwill return a DataFrame with a series of NaN to indicate the missing row. Also note that there df.indexmust be a DatetimeIndex.

In [106]: df.asfreq('B')
Out[106]: 
               Open     High      Low    Close
2016-01-04  1.08701  1.09464  1.07811  1.08239
2016-01-05  1.08238  1.08388  1.07106  1.07502
2016-01-06  1.07504  1.07994  1.07185  1.07766
2016-01-07  1.07767  1.09401  1.07710  1.09256
2016-01-08  1.09255  1.09300  1.08030  1.09218

---

Here is the setting used to get the result above:

import pandas as pd
df = pd.DataFrame(
    {'Close': [1.0851500000000001, 1.08239, 1.0750200000000001, 1.0776600000000001, 1.09256, 1.0921799999999999], 'DateTime': ['2016-01-03 00:00:00+00:00', '2016-01-04 00:00:00+00:00', '2016-01-05 00:00:00+00:00', '2016-01-06 00:00:00+00:00', '2016-01-07 00:00:00+00:00', '2016-01-08 00:00:00+00:00'], 'High': [1.0872299999999999, 1.0946400000000001, 1.08388, 1.0799399999999999, 1.0940099999999999, 1.093], 'Low': [1.0845100000000001, 1.0781100000000001, 1.0710600000000001, 1.07185, 1.0770999999999999, 1.0803], 'Open': [1.08701, 1.08701, 1.0823799999999999, 1.07504, 1.0776700000000001, 1.0925499999999999]})
df['DateTime'] = pd.to_datetime(df['DateTime'])
df = df.set_index('DateTime')
print(df.asfreq('B'))