K S K S - 1 month ago 8
Python Question

delete a row from dataframe when the index (DateTime) is Sunday

Sample data

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 the index, need to delete the row which has 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 did not work.

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

Answer

You could use asfreq('B') to reindex df to rows which are business days. Note, however, that if a business day is missing from df.index, then asfreq will return a DataFrame with a row of NaNs to indicate the missing row. Also note that df.index must 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 setup used to produce 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'))