Drop rows in a dataframe where first column is a weekend date

user_817659 0
Viewed 1555

I have a Dataframe that looks like (e.g.) this:

print(df)

           date     high      low   close
0    2008-01-01   15.540   15.540   15.54
1    2008-01-02   15.750   15.210   15.25
2    2008-01-03   15.450   14.950   15.02
3    2008-01-04   14.990   14.400   14.48
4    2008-01-05   14.890   14.400   14.78
5    2008-01-06   14.890   14.400   14.78
....

I would like to remove the rows from the Dataframe whose date column contains a weekend date.

           date     high      low   close
0    2008-01-01   15.540   15.540   15.54
1    2008-01-02   15.750   15.210   15.25
2    2008-01-03   15.450   14.950   15.02
3    2008-01-04   14.990   14.400   14.48
4 <-- has been removed since 1/05/2008 is a Saturday   
5 <-- has been removed since 1/06/2008 is a Sunday
....

I tried this:

df = df[~df.date.dt.weekday_name.isin(['Saturday','Sunday']).any(0)]

but it doesn't work.

pandas python-3.x datetime
3 Answers

You can create an exclusion list (in case you want to add other days) like so:

day_exclusion = ['Saturday', 'Sunday']

The code below converts the date column to a datetime dtype, which was only necessary given I used pd.read_clipboard() to recreate your dataframe (if your dtype is already a datatime you can remove the pd.to_datetime portion). Then it returns a dataframe with all days not in you exclusion list.

df[~(pd.to_datetime(df['date']).dt.weekday_name.isin(day_exclusion))]

Results in:

enter image description here

user_65672700

The "easier" approach would make use of dt.weekday (0 monday, 6 sunday)

df = df[df.date.dt.weekday < 5]

or:

df.query('date.dt.weekday < 5', inplace=True)

Full example:

import pandas as pd

df = pd.DataFrame({
    'date': pd.date_range(start='2019-01-01', end='2019-01-07'),
    'name': [
        'Robert Baratheon',
        'Jon Snow',
        'Daenerys Targaryen',
        'Theon Greyjoy',
        'Tyrion Lannister',
        'Cersei Lannister',
        'Sansa Stark'
    ]
})

df = df[df.date.dt.weekday < 5]
print(df)

Returns:

        date                name
0 2019-01-01    Robert Baratheon
1 2019-01-02            Jon Snow
2 2019-01-03  Daenerys Targaryen
3 2019-01-04       Theon Greyjoy
6 2019-01-07         Sansa Stark
user_73863320

This piece of code work perfectly.

It will remove "Saturday" and "Sunday" records from dataframe

        # *********** Removing weekend data from dataframe. ***************
    df["weekday"] = pd.to_datetime(df.date).dt.dayofweek
    no_weekend_data = df.drop(df.loc[df["weekday"] > 4].index)
user_76171710
Related