使用groupby后,在Pandas中计算差异导致意想不到的结果,如何解决?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (89)

我已经得到了一个数据,试图在其中附加一列顺序不同的内容。

以下是一些具有正确结构的数据:

import pandas as pd
import numpy as np
import random
from itertools import product

random.seed(1)       # so you can play along at home
np.random.seed(2)    # ditto

# make a list of dates for a few periods
dates = pd.date_range(start='2013-10-01', periods=4).to_native_types()
# make a list of tickers
tickers = ['ticker_%d' % i for i in range(3)]
# make a list of all the possible (date, ticker) tuples
pairs = list(product(dates, tickers))
# put them in a random order
random.shuffle(pairs)
# exclude a few possible pairs
pairs = pairs[:-3]
# make some data for all of our selected (date, ticker) tuples
values = np.random.rand(len(pairs))

mydates, mytickers = zip(*pairs)
data = pd.DataFrame({'date': mydates, 'ticker': mytickers, 'value':values})

我的框架:

     date        ticker      value
0    2013-10-03  ticker_2    0.435995
1    2013-10-04  ticker_2    0.025926
2    2013-10-02  ticker_1    0.549662
3    2013-10-01  ticker_0    0.435322
4    2013-10-02  ticker_2    0.420368
5    2013-10-03  ticker_0    0.330335
6    2013-10-04  ticker_1    0.204649
7    2013-10-02  ticker_0    0.619271
8    2013-10-01  ticker_2    0.299655

我的目标是向这个数据文件中添加一个新列,该列将包含连续的更改。数据必须是为了做到这一点,但排序和差异需要按序进行,这样另一个排序器中的空白就不会对给定的排序造成影响。我不希望根据执行差异所必需的内容重新排序结果。代码如下:

data1 = data.copy() #let's leave the original data alone for later experiments
data1.sort(['ticker', 'date'], inplace=True)
data1['diffs'] = data1.groupby(['ticker'])['value'].transform(lambda x: x.diff())
data1.sort_index(inplace=True)
data1

和返回:

     date        ticker      value       diffs
0    2013-10-03  ticker_2    0.435995    0.015627
1    2013-10-04  ticker_2    0.025926   -0.410069
2    2013-10-02  ticker_1    0.549662    NaN
3    2013-10-01  ticker_0    0.435322    NaN
4    2013-10-02  ticker_2    0.420368    0.120713
5    2013-10-03  ticker_0    0.330335   -0.288936
6    2013-10-04  ticker_1    0.204649   -0.345014
7    2013-10-02  ticker_0    0.619271    0.183949
8    2013-10-01  ticker_2    0.299655    NaN

我用这里所示的更简洁的代码替换上面的中间行:

data2 = data.copy()
data2.sort(['ticker', 'date'], inplace=True)
data2['diffs'] = data2.groupby('ticker')['value'].diff()
data2.sort_index(inplace=True)
data2

事实上,data1等于data2。如果我这样做:

data3 = data.copy()
data3.sort(['ticker', 'date'], inplace=True)
data3['diffs'] = data3.groupby('ticker')['value'].transform(np.diff)
data3.sort_index(inplace=True)
data3

我得到了一个奇怪的结果:

     date        ticker     value       diffs
0    2013-10-03  ticker_2    0.435995    0
1    2013-10-04  ticker_2    0.025926   NaN
2    2013-10-02  ticker_1    0.549662   NaN
3    2013-10-01  ticker_0    0.435322   NaN
4    2013-10-02  ticker_2    0.420368   NaN
5    2013-10-03  ticker_0    0.330335    0
6    2013-10-04  ticker_1    0.204649   NaN
7    2013-10-02  ticker_0    0.619271   NaN
8    2013-10-01  ticker_2    0.299655    0

这是怎么回事?

提问于
用户回答回答于

只需传递一个lambda来进行转换(这相当于直接传递afuncton对象,例如np.diff(或Series.diff))。因此,这相当于data 1/data 2

In [32]: data3['diffs'] = data3.groupby('ticker')['value'].transform(Series.diff)

In [34]: data3.sort_index(inplace=True)

In [25]: data3
Out[25]: 
         date    ticker     value     diffs
0  2013-10-03  ticker_2  0.435995  0.015627
1  2013-10-04  ticker_2  0.025926 -0.410069
2  2013-10-02  ticker_1  0.549662       NaN
3  2013-10-01  ticker_0  0.435322       NaN
4  2013-10-02  ticker_2  0.420368  0.120713
5  2013-10-03  ticker_0  0.330335 -0.288936
6  2013-10-04  ticker_1  0.204649 -0.345014
7  2013-10-02  ticker_0  0.619271  0.183949
8  2013-10-01  ticker_2  0.299655       NaN

[9 rows x 4 columns]

np.diff不遵循numpy自己的unfunc准则来处理数组输入(它尝试各种方法来强制输入和发送输出。问题是np.diff没有正确地处理索引并进行自己的计算(在这种情况下是错误的)。

用户回答回答于

你可以看到这个系列.diff()方法不同于np.diff(),代码如下:

In [11]: data.value.diff()  # Note the NaN
Out[11]: 
0         NaN
1   -0.410069
2    0.523736
3   -0.114340
4   -0.014955
5   -0.090033
6   -0.125686
7    0.414622
8   -0.319616
Name: value, dtype: float64

In [12]: np.diff(data.value.values)  # the values array of the column
Out[12]: 
array([-0.41006867,  0.52373625, -0.11434009, -0.01495459, -0.09003298,
       -0.12568619,  0.41462233, -0.31961629])

In [13]: np.diff(data.value) # on the column (Series)
Out[13]: 
0   NaN
1     0
2     0
3     0
4     0
5     0
6     0
7     0
8   NaN
Name: value, dtype: float64

In [14]: np.diff(data.value.index)  # er... on the index
Out[14]: Int64Index([8], dtype=int64)

In [15]: np.diff(data.value.index.values)
Out[15]: array([1, 1, 1, 1, 1, 1, 1, 1])

扫码关注云+社区

领取腾讯云代金券