Sort a dataframe by day, but excluding the year

Question

I am working on a dataFrame with multiple years of data with a timestamp for each value. I am struggling with sorting data for summer/non-summer months. I am not sure how to tell pandas to get the data with dates June 15 to September 15, however discarding the year.

df['is_summer'] = df['Date'].dt.month.between(6,9) # This works for June 1 to September 30 for every year
# I want to do this, this is pseudo code
df['is_summer'] = df['Date'].dt.day.between(6-15,9-15) # From June 15 to September 15 for every year
# I also want to this 
df['is_late_night'] = df['Date'].dt.time.between(20:00,23:59) # From 20:00 to 23:59 for every day

I am having a difficult time finding the documentation for this. I want to know the correct syntax for the .between() for time, month, day, and year. Thank you for the help

Does this help: https://stackoverflow.com/questions/16139306/determine-season-given-timestamp-in-python-using-datetime — oppressionslayer, Dec 21 '19 at 01:24
do you only want a solution using `.between`? there are other ways using mask and slice which are less messy than `.between` — Andy L., Dec 21 '19 at 03:00
Yes, I am interested in using mask and slice. I am not familiar with the concept, but most certainly interested. — Luis Enriquez-Contreras, Dec 21 '19 at 03:20

score 1 · Answer 1 · answered Dec 21 '19 at 01:37

I would divide it into 3 simplier conditions

df = pd.DataFrame({'date': pd.date_range(start='1/1/2016', end='1/08/2018')})

select_month78 = df.date.dt.month.between(7,8)
select_month6 = (df.date.dt.month==6) & (df.date.dt.day >= 15)
select_month9 = (df.date.dt.month==9) & (df.date.dt.day <= 15)

df['is_summer'] = select_month78 | select_month6 | select_month9

df[df.is_summer]

Output:

          date  is_summer
166 2016-06-15       True
167 2016-06-16       True
168 2016-06-17       True
169 2016-06-18       True
170 2016-06-19       True
..         ...        ...
619 2017-09-11       True
620 2017-09-12       True
621 2017-09-13       True
622 2017-09-14       True
623 2017-09-15       True

[186 rows x 2 columns]

Yacine Mahdid · Accepted Answer · 2019-12-21T15:37:29.447

1

You can use a boolean mask to filter the pandas dataframe, it will look something like this:

import numpy as np
import pandas as pd

# creating random date ranging across many years
df = pd.DataFrame(np.random.random((1000,3)))
df['date'] = pd.date_range('2000-1-1', periods=1000, freq='D')

# Creating the boolean mask to keep everything from June to August
mask = (df['date'].dt.month > 6) & (df['date'].dt.month <= 8)

# Applying the boolean mask to the data frame an printing it
print(df.loc[mask])

The mask creation can be embedded in the filtering step so in your case the solution is a one liner

only_summer_data = df.loc[(df['date'].dt.month >= 6) & (df['date'].dt.month <= 8))

If you want to have the day too we get the following:

start_mask = ((df['date'].dt.month == 6) & (df['date'].dt.day >= 15)) | (df['date'].dt.month > 6)

end_mask = ((df['date'].dt.month == 8) & (df['date'].dt.day <= 15)) | (df['date'].dt.month < 8)

mask = start_mask & end_mask
print(df.loc[mask])

However as the control over the date filtering are more fine grained, the boolean mask solution can get really verbose.

edited Dec 21 '19 at 15:37

answered Dec 21 '19 at 01:37

Yacine Mahdid

723
5
17

1

how does it know to filter 15 / 15 of June/Sept? – Umar.H Dec 21 '19 at 14:48
You can simply add two more condition where you check if the month is == 6 or 8 and that the day is bigger or smaller than 15. The mask will be containing 4 conditions. – Yacine Mahdid Dec 21 '19 at 14:57
`((df['date'].dt.month == 6 & df['date].dt.day > 15) | (df['date'].dt.month > 6) )& etc...` – Yacine Mahdid Dec 21 '19 at 15:13
but that will be > 15 for every month not starting at the 15th of June regardless of year? – Umar.H Dec 21 '19 at 15:16

wwii · Answer 3 · 2019-12-21T03:41:18.517

Use the dayofyear component to define the range and make comparisons - this will let you restrict the filter to a date range disregarding the year.

>>> start = pd.to_datetime('06-15-2000').dayofyear
>>> end = pd.to_datetime('09-15-2000').dayofyear
>>> start,end
(167, 259)
>>> df = pd.DataFrame(pd.date_range('2010-01-01', periods=52, freq='SM'),columns=['Date'])
>>> df[(df['Date'].dt.dayofyear >= start) & (df['Date'].dt.dayofyear <= end)]
         Date
11 2010-06-30
12 2010-07-15
13 2010-07-31
14 2010-08-15
15 2010-08-31
16 2010-09-15
35 2011-06-30
36 2011-07-15
37 2011-07-31
38 2011-08-15
39 2011-08-31
40 2011-09-15
>>>

Or

>>> df.loc[df['Date'].dt.dayofyear.between(start,end)]

Sort a dataframe by day, but excluding the year

3 Answers3