Count values greater than threshold and assign to appropriate year pandas

user_9622066 0
Viewed 1443

I have a dataframe that looks like this:

Date    DFW
242 2000-05-01 00:00:00 75.92
243 2000-05-01 12:00:00 75.02
244 2000-05-02 00:00:00 71.96
245 2000-05-02 12:00:00 75.92
246 2000-05-03 00:00:00 71.96
... ... ...
14991   2020-07-09 12:00:00 93.90
14992   2020-07-10 00:00:00 91.00
14993   2020-07-10 12:00:00 93.00
14994   2020-07-11 00:00:00 89.10
14995   2020-07-11 12:00:00 97.00

The df contains the max value of temperature for a specific location every 12 hours from May - July 11 during 2000-2020. I want to count the number of times that the value is >90 and then store that value in a column where the row is the year. Should I use groupby to accomplish this?

Expected output:

Year   count
2000   x
2001   y
...   ...
2019   z
2020   a
pandas dataframe datetime python
2 Answers

You can do with groupby:

# extract the years from dates
years = df['Date'].dt.year

# compare `DFW` with `90`
# gt90 will be just True or False
gt90 = df['DFW'].gt(90)

# sum the `True` by years
output = gt90.groupby(years).sum()

# set the years as normal column:
output = output.reset_index()

All that in one line:

df['DFW'].gt(90).groupby().sum().reset_index()
user_42384080

One possible approach is to extract and create a new column for year (let's say "year") and then,

df[df['DFW'] > 90].groupby('year').count().reset_index()
user_53531280
Related