Choosing values with df.quantile() for separate years and months

user_5818300 0
Viewed 42

I have a large data set and I want to add values to a column based on the higest values in another column in my data set.

Easy, I can just use df.quantile() and access the appropriate values

However, I want to check for each month in each year.

I solved it for looking at years only, see code below.
I'm sure I could do it for months with nested for loops but I'd rather avoid it if I can.

I guess the most pythonic way would by to not loop at all but use pandas in a smarter way..

Any suggestion?

Sample code:

df = pd.read_excel(file)
df.index = df['date']
df = df.drop('date', axis=1)
df['new'] = 0

year = (2016, 2017, 2018, 2019, 2020)
for i in year:
    df['new'].loc[str(i)] = np.where(df['cost'].loc[str(i)] < df['cost'].loc[str(i)].quantile(0.5), 0, 1)

print(df)

Sample input file =

            cost
date                 
2016-11-01    30   
2016-12-01    29    
2017-11-01    40    
2017-12-01    45    
2018-11-30   240    
2018-12-01   200    
2019-11-30   220    
2019-12-30   180    
2020-11-30   150    
2020-12-30   130

Output

            cost  new
date                 
2016-11-01    30    1
2016-12-01    29    0
2017-11-01    40    0
2017-12-01    45    1
2018-11-30   240    1
2018-12-01   200    0
2019-11-30   220    1
2019-12-30   180    0
2020-11-30   150    1
2020-12-30   130    0

Desired output (if quantile works like that on single values, but as an example)

            cost  new
date                 
2016-11-01    30    1
2016-12-01    29    1
2017-11-01    40    1
2017-12-01    45    1
2018-11-30   240    1
2018-12-01   200    1
2019-11-30   220    1
2019-12-30   180    1
2020-11-30   150    1
2020-12-30   130    1

Thank you _/_

pandas python
1 Answers

An interesting question, it took me a little while to work out a solution!

import pandas as pd

df = pd.DataFrame(data={"cost": [30, 29, 40, 45, 240, 200, 220, 180, 150, 130],
                        "date": ["2016-11-01", "2016-12-01", "2017-11-01",
                                 "2017-12-01", "2018-11-30", "2018-12-01",
                                 "2019-11-30", "2019-12-30", "2020-11-30",
                                 "2020-12-30"]})

df["date"] = pd.to_datetime(df["date"])
df.set_index("date", inplace=True)

df["new"] = df.groupby([lambda x: x.year, lambda x: x.month]).transform(lambda x: (x >= x.quantile(0.5))*1)

#Out: 
#            cost  new
#date                 
#2016-11-01    30    1
#2016-12-01    29    1
#2017-11-01    40    1
#2017-12-01    45    1
#2018-11-30   240    1
#2018-12-01   200    1
#2019-11-30   220    1
#2019-12-30   180    1
#2020-11-30   150    1
#2020-12-30   130    1

What the important line does:

  • Groups by the index year and month
  • For each item in the group, calculates whether it is greater than or equal to the 0.5 quantile (as bool)
  • Multiplying by 1 creates an integer bool (1/0) instead of True/False

The initial creation of the dataframe should be equivalent to your df = pd.read_excel(file)

Leaving out the , lambda x: x.month part of the groupby (by year only), the output is the same as your current output:

#            cost  new
#date                 
#2016-11-01    30    1
#2016-12-01    29    0
#2017-11-01    40    0
#2017-12-01    45    1
#2018-11-30   240    1
#2018-12-01   200    0
#2019-11-30   220    1
#2019-12-30   180    0
#2020-11-30   150    1
#2020-12-30   130    0
user_185715650
Related