How to loop through a pandas grouped time series?

user_13825900 0
Viewed 1128

I have a dataframe like this:

                      datetime type   d13C  ...  dayofyear week         dmy
1       2018-01-05 15:22:30  air  -8.88  ...          5    1    5-1-2018
2       2018-01-05 15:23:30  air  -9.08  ...          5    1    5-1-2018
3       2018-01-05 15:24:30  air -10.08  ...          5    1    5-1-2018
4       2018-01-05 15:25:30  air  -9.51  ...          5    1    5-1-2018
5       2018-01-05 15:26:30  air  -9.61  ...          5    1    5-1-2018
                    ...  ...    ...  ...        ...  ...         ...
341543  2018-12-17 12:42:30  air  -9.99  ...        351   51  17-12-2018
341544  2018-12-17 12:43:30  air  -9.53  ...        351   51  17-12-2018
341545  2018-12-17 12:44:30  air  -9.54  ...        351   51  17-12-2018 
341546  2018-12-17 12:45:30  air  -9.93  ...        351   51  17-12-2018
341547  2018-12-17 12:46:30  air  -9.66  ...        351   51  17-12-2018

Full data here: https://drive.google.com/file/d/1KmOwnpvrG2Edz1AlLyD0CKZlBpaFervM/view?usp=sharing

I'm plotting d13C column on the Y-axis and inverse total_co2 on the X and then fitting a regression line for each day in the data. I then filter out and store the dates I want depending on if the r^2 value of the regression line is > 0.8 like this:

import pandas as pd
from numpy.polynomial.polynomial import polyfit
import numpy as np
from scipy import stats

df = pd.read_csv('dataset.txt', usecols = ['datetime', 'type', 'total_co2', 'd13C', 'day','month','year','dayofyear','week','hour'], dtype = {'total_co2':
np.float64, 'd13C':np.float64, 'day':str, 'month':str, 'year':str,'week':str, 'hour': str, 'dayofyear':str}) 
    
df['dmy'] = df['day'] +'-'+ df['month'] +'-'+ df['year'] # adding a full date column to make it easir to filter through
# the rows, ie. each day
# window18 = df[((df['year']=='2018'))] # selecting just the data from the year 2018

accepted_dates_list = [] # creating an empty list to store the dates that we're interested in
for d in df['dmy'].unique(): # this will pass through each day, the .unique() ensures that it doesnt go over the same days  
    acceptable_date = {} # creating a dictionary to store the valid dates
    period = df[df.dmy==d] # defining each period from the dmy column
    p = (period['total_co2'])**-1
    q = period['d13C']
    c,m = polyfit(p,q,1) # intercept and gradient calculation of the regression line
    slope, intercept, r_value, p_value, std_err = stats.linregress(p, q) # getting some statistical properties of the regression line
    
    
    if r_value**2 >= 0.8:
        acceptable_date['period'] = d # populating the dictionary with the accpeted dates and corresponding other values
        acceptable_date['r-squared'] = r_value**2
        acceptable_date['intercept'] = intercept
        accepted_dates_list.append(acceptable_date) # sending the valid stuff in the dictionary to the list
    else:
        pass


accepted_dates18 = pd.DataFrame(accepted_dates_list) # converting the list to a df
print(accepted_dates18)

But now I want to do the same thing, just over three day periods which I'm trying to select from the day of year column (unsure if this is the best way or not). For example, I would want to fit the regression line using all the rows with dayofyear=5, dayofyear=6, dayofyear=7, then for the next three days until the end of the data. There are some days missing, but essentially I just need to do this for every 3 days in the data.

The output dataframe I am then trying to get would have the list of the three day intervals with the r^2 >0.8, so anything like this that will show the valid date range:

  Accepted dates
0  23-08-2018 - 25-08-2018
1  26-08-2018 - 28-08-2018
2  31-08-2018 - 02-09-2018
3  15-09-2018 - 17-09-2018
4  24-09-2018 - 26-09-2018

I'm not too sure what to do to iterate over every three days. Any help would go a long way, thanks!

pandas-groupby pandas time-series python
2 Answers

Your code loops through a list of unique dates and filters the dataframe on each iteration.

Pandas implemented this with df.groupby(). It can be used to loop and get each group or it can be combined with aggregations, function applications, and transformations. You can read more about it on the user guide. This function can return groups according to any the columns (or set of columns) in df, levels of the index, or any other exogenous list-like with the same length as df (we are grouping rows, but note it can also group columns). It even has implementations for the most common statistical aggregations like mean, stdev, and corr, among many others.

Now to your problem. You not only want the correlation but the equation, so you do need to loop. And to get three-day groups you can use that dayofyear column with a twist.

Take this data

import io
fo = io.StringIO(
'''datetime,d13C
2018-01-05 15:22:30,-8.88
2018-01-05 15:23:30,-9.08
2018-01-06 15:24:30,-10.0
2018-01-06 15:25:30,-9.51
2018-01-07 15:26:30,-9.61
2018-01-07 15:27:30,-9.61
2018-01-08 15:28:30,-9.61
2018-01-08 15:29:30,-9.61
2018-01-09 15:26:30,-9.61
2018-01-09 15:27:30,-9.61
''')
df = pd.read_csv(fo)
df.datetime = pd.to_datetime(df.datetime)
fo.close()

With the code for grouping and looping

first_day = 5
days_to_group = 3
for doy, gdf in df.groupby((df.datetime.dt.dayofyear.sub(first_day) // days_to_group)
        * days_to_group + first_day):
    print(gdf, '\n')
    print(doy, '\n')

Output

             datetime   d13C
0 2018-01-05 15:22:30  -8.88
1 2018-01-05 15:23:30  -9.08
2 2018-01-06 15:24:30 -10.00
3 2018-01-06 15:25:30  -9.51
4 2018-01-07 15:26:30  -9.61
5 2018-01-07 15:27:30  -9.61

5

             datetime  d13C
6 2018-01-08 15:28:30 -9.61
7 2018-01-08 15:29:30 -9.61
8 2018-01-09 15:26:30 -9.61
9 2018-01-09 15:27:30 -9.61

8

Now you can plug your code into this loop and get what you need.

PS

You can also use df.datetime.dt.floor('3d') as the grouper but I am not aware of how to control the first_day, so use it with caution.

user_66928980

Here is one approach. As I understand it, the primary goal is to get from current observations (multiple per day) to a 3-day moving average. First, I created a smaller, simpler data set:

import pandas as pd
df = pd.DataFrame({'counter': [*range(100)],
                  'date': pd.date_range('2020-01-01', periods=100, freq='7H')})
df = df.set_index('date')
print(df.head())

                     counter
date                        
2020-01-01 00:00:00        0
2020-01-01 07:00:00        1
2020-01-01 14:00:00        2
2020-01-01 21:00:00        3
2020-01-02 04:00:00        4

Second, I re-sampled on a daily basis:

df2 = df['counter'].resample('1D').mean()  # <-- called df2
print(df2.head())

date
2020-01-01     1.5
2020-01-02     5.0
2020-01-03     8.5
2020-01-04    12.0
2020-01-05    15.5
Freq: D, Name: counter, dtype: float64

Third, I computed mean value for a 3-day moving window:

print(df2.rolling(3).mean().head())

date
2020-01-01     NaN
2020-01-02     NaN
2020-01-03     5.0
2020-01-04     8.5
2020-01-05    12.0
Freq: D, Name: counter, dtype: float64

Seems like resample().mean() and rolling().mean() would be useful in this case.

user_136085990
Related