使用pandas对同一列中的数值差异求和

Question

我正在查看纽约市地铁的MTA转门数据，试图找出每个车站的总交通量。每个车站都有许多独特的转门，进出口都会被计算在内。我想计算每个车站的入口和出口的总和，以确定给定时间段内的交通量。你知道吗

唯一的转门由SCP列中的ID和STATION列中的名称的组合决定。旋转栅门的交通量是累积的，因此在给定的时间范围内（比如一天），您需要找到起始值，从结束值中减去它，然后将车站每个旋转栅门的所有差异相加，以获得车站的交通量。你知道吗

数据帧如下所示：

    C/A    UNIT        SCP   STATION    LINENAME    DIVISION    DATE TIME   DESC    ENTRIES EXITS   NEW_DATE
0   A002    R051    02-00-00    59 ST   NQR456W BMT 04/28/2018  00:00:00    REGULAR 6598847 2235829 2018-04-28
1   A002    R051    02-00-00    59 ST   NQR456W BMT 04/28/2018  04:00:00    REGULAR 6598864 2235830 2018-04-28
2   A002    R051    02-00-00    59 ST   NQR456W BMT 04/28/2018  08:00:00    REGULAR 6598880 2235863 2018-04-28
3   A002    R051    02-00-00    59 ST   NQR456W BMT 04/28/2018  12:00:00    REGULAR 6598961 2235955 2018-04-28
4   A002    R051    02-00-00    59 ST   NQR456W BMT 04/28/2018  16:00:00    REGULAR 6599175 2236015 2018-04-28

到目前为止，我已经尝试了不同的df.groupby(['SCP', 'STATION'])组合，但我不太清楚如何正确地分组或应用其他方法来生成每个站的差异总和的结果。你知道吗

我想能够生成各种时间框架的输出。下面是我想要的一天的输出示例：

STATION   | DATE                | ENTRIES | EXITS | TOTALS |     
CHURCH AV | 2018-04-28 00:00:00 | 12948   | 9076  | 22024  |
59 ST     | 2018-04-28 00:00:00 | 20401   | 17907 | 38308  |

请注意，条目和出口不是从0开始的，而是随着时间的推移而累积的，因此数据集中的前两个条目如下所示。请注意，为了便于阅读，我在此处屏蔽了（…）不重要的列：

...  |   SCP   | STATION | ... |    DATE     |   TIME   | ...   | ENTRIES | EXITS  |
...  |02-00-00 | 59 ST   | ... |  2018-04-28 | 00:00:00 | ...   | 6598847 | 2235829|
...  |02-00-00 | 59 ST   | ... |  2018-04-28 | 04:00:00 | ...   | 6598864 | 2235830|

添加到目前为止我使用的代码。我还添加了一个我正在使用的数据源：

    # Importing and cleaning data
    may05_2018 = pd.read_csv('http://web.mta.info/developers/data/nyct/turnstile/turnstile_180505.txt')

    # Only including one of the several files for this example
    source_data = [may05_2018] 

    # Clean data
    all_converted_data = []
    for i in source_data:
        converted = i 
        converted['DATE'] = pd.to_datetime(i['DATE'] + ' ' + i['TIME'])
        converted.drop('TIME', axis=1, inplace=True)
        converted.rename(columns = lambda x: x.strip(), inplace=True)   
        all_converted_data.append(converted)

    # Create copy of data frame to maintain the original
    test_df = df.copy()

    # Function for calculating differences
    def mta_traffic(data, freq='D'):
        data.ENTRIES = data.ENTRIES - data.shift(1).ENTRIES
        data.EXITS = data.EXITS - data.shift(1).EXITS
        data = data.set_index(['STATION', 'SCP'])[['ENTRIES', 'EXITS', 'DATE']]
        data = data.resample(freq, on='DATE').sum()
        return data

    # Create df of data sums. These seem to generate legit values.
    test_df = test_df.groupby(['STATION', 'SCP']).apply(mta_traffic)

    # Add TOTALS column, the sum of ENTRIES and EXITS
    test_df['TOTALS'] = test_df['ENTRIES'] + test_df['EXITS']

    # Attempt to groupby STATION and find the sums per station
    station_traffic = test_df.groupby('STATION')['TOTALS'].sum()

输出

    STATION
    1 AV               2.135754e+06
    103 ST             4.971873e+08
    103 ST-CORONA      1.528737e+06
    104 ST            -5.682778e+09
    110 ST             9.083200e+05
    111 ST             3.939572e+07
    116 ST            -3.635802e+09

这就是价值观疯狂的地方。有些似乎是合法的，但另一些，如消极的，肯定是错误的。

现在我想弄清楚为什么按站分组的结果会产生错误的值，而未分组的结果似乎是好的。最后，我想输出不同时间段和间隔内的每个站点的通信量。你知道吗

Answer 1

数据示例：

        SCP STATION        DATE      TIME  ENTRIES   EXISTS    NEW_DATE
0  02-00-00   59 ST  04/28/2018  00:00:00  6598847  2235829  2018-04-28
1  02-00-00   59 ST  04/28/2018  04:00:00  6598864  2235830  2018-04-28
2  02-00-00   59 ST  04/28/2018  08:00:00  6598880  2235863  2018-04-28
3  02-00-00   59 ST  04/28/2018  12:00:00  6598961  2235955  2018-04-28
4  02-00-00   59 ST  04/28/2018  16:00:00  6599175  2236015  2018-04-28

我建议您这样做，以使日期具有良好的代表性：

series.DATE = pd.to_datetime(series.DATE) + pd.to_timedelta(series.TIME)

series = series.drop(['TIME', 'NEW_DATE'], 1)

输出：

        SCP STATION                DATE  ENTRIES   EXISTS
0  02-00-00   59 ST 2018-04-28 00:00:00  6598847  2235829
1  02-00-00   59 ST 2018-04-28 04:00:00  6598864  2235830
2  02-00-00   59 ST 2018-04-28 08:00:00  6598880  2235863
3  02-00-00   59 ST 2018-04-28 12:00:00  6598961  2235955
4  02-00-00   59 ST 2018-04-28 16:00:00  6599175  2236015

要对分组数据执行所需操作，可以定义函数，如：

def function(series, freq='D'):
    series.ENTRIES = series.ENTRIES - series.shift(1).ENTRIES
    series.EXISTS = series.EXISTS - series.shift(1).EXISTS
    series = series.set_index(['SCP', 'STATION'])[['ENTRIES', 'EXISTS', 'DATE']]
    series = series.resample(freq, on='DATE').sum()
    return series

然后与groupby一起使用：

series.groupby(['SCP','STATION']).apply(function)

输出：

                             ENTRIES  EXISTS
SCP      STATION DATE                       
02-00-00 59 ST   2018-04-28    328.0   186.0

（这里前3列是索引）

您还可以传递参数（例如，希望频率为3小时）：

series.groupby(['SCP','STATION']).apply(function, freq='3H')

输出：

                                      ENTRIES  EXISTS
SCP      STATION DATE                                
02-00-00 59 ST   2018-04-28 00:00:00      0.0     0.0
                 2018-04-28 03:00:00     17.0     1.0
                 2018-04-28 06:00:00     16.0    33.0
                 2018-04-28 09:00:00      0.0     0.0
                 2018-04-28 12:00:00     81.0    92.0
                 2018-04-28 15:00:00    214.0    60.0

但在这个例子中，首先要小心。在函数中，我使用shift，用上一个raw进行减法（我不知道您的数据在开始时是什么样子的，所以我们有NaN作为第一个raw，因为它没有上一个raw）。你知道吗