How to automate a repetitive calculation on the rows of a pandas dataframe?

Question

I have a large dataset - 1 minute resolution of air pollution between 2018-2020, a small snippet looks like this:

             datetime    date  time type  ... day minute second dayofyear
1 2017-12-19 17:08:30  171219  1708  air  ...  19      8     30       353
2 2018-01-05 15:22:30  180105  1522  air  ...   5     22     30         5
3 2018-01-05 15:23:30  180105  1523  air  ...   5     23     30         5
4 2018-01-05 15:24:30  180105  1524  air  ...   5     24     30         5
5 2018-01-05 15:25:30  180105  1525  air  ...   5     25     30         5

Where the datetime values are the first two columns. Also, there are about 50 columns in total and the full data is here: https://drive.google.com/file/d/15yxPIoPEpQ3Gwb00nCLQMgQM5E_NHcW-/view?usp=sharing

I am trying to create a plot of 'd13C column on the y axis and the inverse of 'total_co2' on the x axis and then fit a regression line to this data, which I am doing like this:

from numpy.polynomial.polynomial import polyfit
from scipy import stats

period = MyData[((MyData['year']==2019) & (MyData['month']==12) & (MyData['day']==31)) #    defining the time period I want from the data
p=(period['total_co2'])**-1 # defining the x axis data
q = period['d13C'] # defining the y axis data
c, m = polyfit(p,q,1) # creating a regression line, with y interecpt,c and gradient, m 
slope, intercept, r_value, p_value, std_err = stats.linregress(p, q) # calculating some statistical properties of the regression line. I'm mainly interested in the R^2 value
print('R-squared: ', r_value**2)

This code will fit a regression line to the p,q data for all the rows with 'year'=2019, 'month' =12, 'day'=31 in MyData dataframe. I am trying to do this for the whole dataset and only save/keep the dates where R-squared >=0.8. For the case above, 31/12/2019 I get R-squared =0.554 so I would want to neglect this date. Currently, I am going through the data manually by just changing the months, days and years and checking the R-squared value. This is taking a while since there's just so much data!

Ultimately, my goal is to create a list or dataframe or something that has a collection of all the dates where the R-squared is >=0.8, like this:

  Accepted dates
0  23-11-2019
1  24-11-2019
2  29-11-2019

Is there a way to automate this process? Currently I'm trying to write a for loop to iterate over each row in the air df and adding an if statement as a filter, but am struggling with this. Ps, I'm fairly new to python and have pretty much just been learning as I go!

Any help would be much appreciated. Thank you.

user_9808272 · Answer

I have tested this code and I believe it provides the output you are looking for:

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

# Restricted the columns and set the dtypes to deal with memory issues when importing a large csv
MyData = pd.read_csv('.../MyData.txt', usecols=['total_co2', 'd13C', 'year', 'month', 'day', 'datetime'], dtype={'total_co2':np.float64, 'd13C':np.float64, 'year':str, 'month':str, 'day':str})

# Created a helper column that is used later to filter and report out the period
MyData['ymd'] = MyData['year'] +'-'+ MyData['month'] +'-'+ MyData['day']

# Empty list that will receive all of the periods with acceptable r-squareds
accepted_date_list = []

# for loop to filter the dataframe according to the unique periods (created with the helper column above)
for d in MyData['ymd'].unique():
    acceptable_date = {} # create a dictionary to populate and send to the list
    period = MyData[MyData.ymd == d] # filter the dataframe with the unique periods created above
    p=(period['total_co2'])**-1 
    q = period['d13C'] 
    c, m = polyfit(p,q,1) 
    slope, intercept, r_value, p_value, std_err = stats.linregress(p, q)

    if r_value**2 > 0.8: # if statement provides the test. If r2 is acceptable, populate the dictionary then send the dictionary to the list
        acceptable_date['period'] = d
        acceptable_date['r-squared'] = r_value**2
        accepted_date_list.append(acceptable_date)
    else:
        pass
   
accepted_dates = pd.DataFrame(accepted_date_list) # convert the list to a Pandas DataFrame (or whatever else you want to do with it)

print(accepted_dates)

Output:

        period  r-squared
0     2018-1-6   0.910516
1     2018-1-9   0.917216
2    2018-1-10   0.980263
3    2018-1-11   0.965971
4    2018-1-12   0.894795
5    2018-1-13   0.831683
6    2018-1-18   0.852207
7    2018-1-21   0.944162
8    2018-1-22   0.871262
9    2018-1-26   0.844020
10   2018-1-27   0.890742
11   2018-1-30   0.971747
...

How to automate a repetitive calculation on the rows of a pandas dataframe?

1 Answers