如何自动对数据帧的行进行重复计算？

Question

我有一个很大的数据集——2018-2020年间空气污染的1分钟分辨率，一个小片段如下：

             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

其中日期时间值是前两列。此外，总共大约有50列，完整数据如下：https://drive.google.com/file/d/15yxPIoPEpQ3Gwb00nCLQMgQM5E_NHcW-/view?usp=sharing

我试图在y轴上绘制“d13C柱”的曲线，在x轴上绘制“总co2”的倒数，然后在该数据上拟合回归线，我的做法如下：

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)

在MyData dataframe中，对于“年”=2019、“月”=12、“日”=31的所有行，此代码将为p，q数据拟合一条回归线。我尝试对整个数据集执行此操作，并且只保存/保留R-squared>=0.8. 对于上面的例子，2019年12月31日，我得到R平方=0.554，所以我想忽略这个日期。目前，我正在手动查看数据，只需更改月份、天数和年份，并检查R平方值。这需要一段时间，因为数据太多了

最终，我的目标是创建一个列表或数据框，或者一个包含所有日期的集合，其中R平方为>=0.8，如下所示：

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

有没有办法使这个过程自动化？目前，我正试图编写一个for循环来迭代air df中的每一行，并添加一个if语句作为过滤器，但我很难做到这一点。顺便说一句，我对python还相当陌生，并且在学习的过程中一直在学习

任何帮助都将不胜感激。多谢各位

Answer 1

我已经测试了这段代码，我相信它提供了您想要的输出：

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)

输出：

        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
...