Run function for each month of the year within a dataframe-CodePudding

I have a DataFrame in pandas, which has daily temperature values for each month of the years 2010 and 2011:

> day month year Temperature 
> 1      1  2010   269.798567
> 1      1  2011   274.085177
> ....
> 31     12  2010   273.610214
> 31     12  2011   274.855967
> [730 rows x 4 columns]

I want to sort the temperature from least to greatest for each month of the year. And calculate afterwards the cumulative distribution function (cdf) of each month and year.

I managed to do this with a filter, looking at only one specific month and one specific year. Now I'm struggling to apply this code for all months and years. My code looks like that:

month = 1
year = 2010
filt = (df['month'] == month) & (df['year'] == year)
dfMonth = df.loc[filt]

#Sort temperature from least to greatest
SortDF = dfMonth.sort_values(variable,ascending=True)

# calculate cdf
NumberOfDays = len(SortDF)
EmptyList = list(range(1, NumberOfDays 1))
CDF = [((element -0.5)/NumberOfDays)for element in EmptyList if element < NumberOfDays]
CDF.append(1)

# Add CDF values into Dataframe as new column
SortDF['CDF'   '_'   Temperature ] = CDF

And get finally:

day  month  year  Temperature  CDF_Temperature
25      1  2010   259.990152         0.016129
24      1  2010   260.644554         0.048387
....
28      1  2010   272.642832         0.951613
10      1  2010   273.004253         1.000000

I suspect that I will have to loop this. But I have no idea how.

CodePudding user response：

As a single code block

No loops, no lambda function. Very fast (~90ms for 11,688 rows from 1990 to 2022-01-01).

df = df.assign(
    date=pd.to_datetime(df[['day', 'month', 'year']])
).set_index('date')[['Temperature']]

by_month = pd.Grouper(freq='M')
df = df.assign(
    temp_sorted=df.groupby(by_month)['Temperature'].transform(sorted)
)

df = df.assign(
    CDF_temp=df.groupby(by_month)['temp_sorted'].agg('rank', pct=True)
)

Explanation (bit by bit)

This is easier if you first combine your columns day, month, year into a single date and make that the index.

Using just the four rows you provided as sample data:

df = pd.DataFrame({
    'day': [1, 1, 31, 31],
    'month': [1, 1, 12, 12],
    'year': [2010, 2011, 2010, 2011],
    'Temperature': [269.798567, 274.085177, 273.610214, 274.855967],
})

df = df.assign(
    date=pd.to_datetime(df[['day', 'month', 'year']])
).set_index('date')[['Temperature']]

>>> df
            Temperature
date                   
2010-01-01   269.798567
2011-01-01   274.085177
2010-12-31   273.610214
2011-12-31   274.855967

Now, you can group by month very easily. For example, computing the mean temperature for each month:

>>> df.groupby(pd.Grouper(freq='M')).mean()
            Temperature
date                   
2010-01-31   269.798567
2010-02-28          NaN
...
2010-11-30          NaN
2010-12-31   273.610214
2011-01-31   274.085177
2011-02-28          NaN
...
2011-11-30          NaN
2011-12-31   274.855967

Now, for the second part of your question: how to reorder the temperatures within the month, and compute a CDF of it. Let's first generate random data for testing:

np.random.seed(0)  # reproducible values
ix = pd.date_range('2010', '2012', freq='D', closed='left')
df = pd.DataFrame(
    np.random.normal(270, size=len(ix)),
    columns=['Temperature'], index=ix)

>>> df
            Temperature
2010-01-01   271.764052
2010-01-02   270.400157
2010-01-03   270.978738
2010-01-04   272.240893
2010-01-05   271.867558
...                 ...
2011-12-27   269.112819
2011-12-28   269.067211
2011-12-29   271.243319
2011-12-30   270.812674
2011-12-31   270.587259

[730 rows x 1 columns]

Sort the temperatures within each month:

by_month = pd.Grouper(freq='M')
df = df.assign(
    temp_sorted=df.groupby(by_month)['Temperature'].transform(sorted)
)

Note: while, with the values above, it looks like the temperatures have been reordered globally, this is not the case. They have been reordered only within each month. For example:

>>> df['2010-01-30':'2010-02-02']
            Temperature  temp_sorted
2010-01-30   271.469359   272.240893
2010-01-31   270.154947   272.269755
2010-02-01   270.378163   268.019204
2010-02-02   269.112214   268.293730

Finally, compute the CDF within each month:

df = df.assign(
    CDF_temp=df.groupby(by_month)['temp_sorted'].agg('rank', pct=True)
)

And we get:

>>> df
            Temperature  temp_sorted  CDF_temp
2010-01-01   271.764052   267.447010  0.032258
2010-01-02   270.400157   268.545634  0.064516
2010-01-03   270.978738   269.022722  0.096774
2010-01-04   272.240893   269.145904  0.129032
2010-01-05   271.867558   269.257835  0.161290
...                 ...          ...       ...
2011-12-27   269.112819   271.094638  0.870968
2011-12-28   269.067211   271.243319  0.903226
2011-12-29   271.243319   271.265078  0.935484
2011-12-30   270.812674   271.327783  0.967742
2011-12-31   270.587259   272.132153  1.000000