在带MultiIndex列的pandas DataFrame中添加字段
我一直在寻找这个问题的答案，因为看起来很简单，但到现在还没有找到任何相关的信息。如果我漏掉了什么，真心抱歉。我现在使用的是 pandas 0.10.0 版本，正在尝试处理以下格式的数据：
import pandas
import numpy as np
import datetime
start_date = datetime.datetime(2009,3,1,6,29,59)
r = pandas.date_range(start_date, periods=12)
cols_1 = ['AAPL', 'AAPL', 'GOOG', 'GOOG', 'GS', 'GS']
cols_2 = ['close', 'rate', 'close', 'rate', 'close', 'rate']
dat = np.random.randn(12, 6)
cols = pandas.MultiIndex.from_arrays([cols_1, cols_2], names=['ticker','field'])
dftst = pandas.DataFrame(dat, columns=cols, index=r)
print dftst
ticker AAPL GOOG GS
field close rate close rate close rate
2009-03-01 06:29:59 1.956255 -2.074371 -0.200568 0.759772 -0.951543 0.514577
2009-03-02 06:29:59 0.069611 -2.684352 -0.310006 0.730205 -0.302949 -0.830452
2009-03-03 06:29:59 2.077130 -0.903784 0.449857 -1.357464 -0.469572 -0.008757
2009-03-04 06:29:59 1.585358 -2.063672 0.600889 -1.741606 -0.299875 0.565253
2009-03-05 06:29:59 0.269123 0.226593 1.132663 0.485035 0.796858 -0.423112
2009-03-06 06:29:59 0.094879 -1.040069 0.613450 -0.175266 -0.065172 3.374658
2009-03-07 06:29:59 -1.255167 -0.326474 0.437053 -0.231594 0.437703 -0.256811
2009-03-08 06:29:59 0.115454 -1.096841 -1.189211 -0.208098 -0.807860 0.158198
2009-03-09 06:29:59 2.142816 0.173878 -0.160932 0.367309 -0.449765 -0.325400
2009-03-10 06:29:59 0.470669 -0.346805 1.152648 0.844632 1.031602 -0.012502
2009-03-11 06:29:59 -1.366954 0.452177 0.010713 -1.331553 0.226781 0.456900
2009-03-12 06:29:59 2.182409 0.890023 -0.627318 -1.516574 -1.565416 -0.694320
如你所见，我想表示的是三维时间序列数据。所以我有一个时间序列索引和多重索引的列。我对切片数据还算熟悉。如果我只想要收盘数据的移动平均，我可以这样做：
pandas.rolling_mean(dftst.ix[:,::2], 5)
ticker AAPL GOOG GS
field close close close
2009-03-01 06:29:59 NaN NaN NaN
2009-03-02 06:29:59 NaN NaN NaN
2009-03-03 06:29:59 NaN NaN NaN
2009-03-04 06:29:59 NaN NaN NaN
2009-03-05 06:29:59 0.410966 -0.412356 0.722951
2009-03-06 06:29:59 -0.103187 -0.497165 0.137731
2009-03-07 06:29:59 0.000194 -0.645375 -0.298504
2009-03-08 06:29:59 -0.074036 -0.541717 -0.035906
2009-03-09 06:29:59 -0.391863 -0.671918 -0.554380
2009-03-10 06:29:59 -0.336397 -0.411845 -0.992615
2009-03-11 06:29:59 -0.251645 -0.289512 -0.458246
2009-03-12 06:29:59 -0.138925 0.244572 -0.230743
但我无法创建一个新字段，比如叫 avg_close，并给它赋值。理想情况下，我想做类似下面的事情：
dftst[:,'avg_close'] = pandas.rolling_mean(dftst.ix[:,::2], 5)
即使我交换了多重索引的层级，我也无法让它工作：
dftst = dftst.swaplevel(1,0,axis=1)
print dftst['close']
ticker AAPL GOOG GS
2009-03-01 06:29:59 1.178557 -0.505672 -0.336645
2009-03-02 06:29:59 0.234305 0.581429 -0.232252
2009-03-03 06:29:59 -0.734798 0.117810 1.658418
2009-03-04 06:29:59 -1.555033 -0.298322 0.127408
2009-03-05 06:29:59 0.244102 -1.030041 -0.562039
2009-03-06 06:29:59 -0.297454 1.150564 -1.930883
2009-03-07 06:29:59 0.818910 -0.905296 1.219946
2009-03-08 06:29:59 0.586816 0.965242 0.928546
2009-03-09 06:29:59 -0.357693 0.071455 0.072956
2009-03-10 06:29:59 0.651803 -0.685937 0.805779
2009-03-11 06:29:59 0.569802 -0.062447 -1.349261
2009-03-12 06:29:59 -1.886335 0.205778 -0.864273
dftst['avg_close'] = pandas.rolling_mean(dftst['close'], 3)
----> 1 dftst['avg_close'] = pandas.rolling_mean(dftst['close'], 3)
/usr/local/lib/python2.7/dist-packages/pandas/core/frame.pyc in
__setitem__(self, key, value) 2041 else: 2042 # set column
-> 2043 self._set_item(key, value) 2044 2045 def _boolean_set(self, key, value):
/usr/local/lib/python2.7/dist-packages/pandas/core/frame.pyc in
_set_item(self, key, value) 2077 """ 2078 value = self._sanitize_column(key, value)
-> 2079 NDFrame._set_item(self, key, value) 2080 2081 def insert(self, loc, column, value):
/usr/local/lib/python2.7/dist-packages/pandas/core/generic.pyc in
_set_item(self, key, value)
544
545 def _set_item(self, key, value):
--> 546 self._data.set(key, value)
547 self._clear_item_cache()
548
/usr/local/lib/python2.7/dist-packages/pandas/core/internals.pyc in set(self, item, value)
951 except KeyError:
952 # insert at end
--> 953 self.insert(len(self.items), item, value)
954
955 self._known_consolidated = False
/usr/local/lib/python2.7/dist-packages/pandas/core/internals.pyc in insert(self, loc, item, value)
963
964 # new block
--> 965 self._add_new_block(item, value, loc=loc)
966
967 if len(self.blocks) > 100:
/usr/local/lib/python2.7/dist-packages/pandas/core/internals.pyc in
_add_new_block(self, item, value, loc)
992 loc = self.items.get_loc(item)
993 new_block = make_block(value, self.items[loc:loc+1].copy(),
--> 994 self.items)
995 self.blocks.append(new_block)
996
/usr/local/lib/python2.7/dist-packages/pandas/core/internals.pyc in make_block(values, items, ref_items)
463 klass = ObjectBlock
464
--> 465 return klass(values, items, ref_items, ndim=values.ndim)
466
467 # TODO: flexible with index=None and/or items=None
/usr/local/lib/python2.7/dist-packages/pandas/core/internals.pyc in
__init__(self, values, items, ref_items, ndim)
30 if len(items) != len(values):
31 raise AssertionError('Wrong number of items passed (%d vs %d)'
---> 32 % (len(items), len(values)))
33
34 self._ref_locs = None
AssertionError: Wrong number of items passed (1 vs 3)
如果我的列不是多重索引，我可以这样赋值：
start_date = datetime.datetime(2009,3,1,6,29,59)
r = pandas.date_range(start_date, periods=12)
cols = ['AAPL', 'GOOG', 'GS']
dat = np.random.randn(12, 3)
dftst2 = pandas.DataFrame(dat, columns=cols, index=r)
print dftst2
AAPL GOOG GS
2009-03-01 06:29:59 2.476787 2.386037 -0.777566
2009-03-02 06:29:59 -0.820647 1.006159 -0.590240
2009-03-03 06:29:59 0.433960 0.104458 0.282641
2009-03-04 06:29:59 0.300190 -0.300786 -1.780412
2009-03-05 06:29:59 -0.247919 1.616572 1.145594
2009-03-06 06:29:59 -0.779130 0.695256 0.845819
2009-03-07 06:29:59 0.572073 0.349394 -3.557776
2009-03-08 06:29:59 2.019885 0.358346 1.350812
2009-03-09 06:29:59 0.472328 -0.334223 -0.605862
2009-03-10 06:29:59 -1.570479 0.410808 0.616515
2009-03-11 06:29:59 1.177562 -0.240396 -2.126951
2009-03-12 06:29:59 0.311566 -1.743213 0.382617
要添加一个基于另一个字段的新字段，我可以这样做：
dftst2['GOOG_avg'] = pandas.rolling_mean(dftst2['GOOG'], 3)
print dftst2
AAPL GOOG GS GOOG_avg
2009-03-01 06:29:59 2.476787 2.386037 -0.777566 NaN
2009-03-02 06:29:59 -0.820647 1.006159 -0.590240 NaN
2009-03-03 06:29:59 0.433960 0.104458 0.282641 1.165551
2009-03-04 06:29:59 0.300190 -0.300786 -1.780412 0.269944
2009-03-05 06:29:59 -0.247919 1.616572 1.145594 0.473415
2009-03-06 06:29:59 -0.779130 0.695256 0.845819 0.670347
2009-03-07 06:29:59 0.572073 0.349394 -3.557776 0.887074
2009-03-08 06:29:59 2.019885 0.358346 1.350812 0.467666
2009-03-09 06:29:59 0.472328 -0.334223 -0.605862 0.124506
2009-03-10 06:29:59 -1.570479 0.410808 0.616515 0.144977
2009-03-11 06:29:59 1.177562 -0.240396 -2.126951 -0.054604
2009-03-12 06:29:59 0.311566 -1.743213 0.382617 -0.524267
我尝试过使用 Panel 对象，但到目前为止还没有找到一个快速的方法来添加一个字段，尤其是在我有多重索引列的情况下，理想中其他层级的列应该可以广播。我为之前可能有其他帖子回答这个问题而感到抱歉。任何建议都将非常感谢。
4 个回答
1
这个问题已经有十年了，但我遇到的情况和你完全一样。这里有一种一行代码的方法可以实现你想要的功能。虽然在pandas 0.18版本中，滚动平均的用法有点变化，但你明白我的意思。
avg_close = dftst.xs('close', axis=1, level=1).rolling(5).mean()
dftst[zip(avg_close.columns, ['avg_close']*len(avg_close.columns))] = avg_close
1
我不知道你想要的广播功能怎么做，但对于严格的赋值，这个应该可以：
dftst[(('GOOG', 'avg_close'))] = 7
更具体一点，不过还是没有广播功能：
for tic in cols_1:
dftst[(tic, 'avg_close')] = pandas.rolling_mean(dftst[(tic, 'close')],5)
5
你也可以考虑用一些简单的方法来调整数据格式，作为一种变通办法，因为目前没有一个API能完全满足你的需求。不过，我不建议在处理非常大的数据集时这样做，还是用Panel比较好。
In [30]: df = dftst.stack(0)
In [31]: df['close_avg'] = pd.rolling_mean(df.close.unstack(), 5).stack()
In [32]: df
Out[32]:
field close rate close_avg
ticker
2009-03-01 06:29:59 AAPL -0.223042 0.554996 NaN
GOOG 0.060127 -0.333992 NaN
GS 0.117626 -1.256790 NaN
2009-03-02 06:29:59 AAPL -0.513743 -0.402661 NaN
GOOG 0.059828 -0.125288 NaN
GS -0.336196 -0.510595 NaN
2009-03-03 06:29:59 AAPL 0.142202 -1.038470 NaN
GOOG -1.099251 -0.892581 NaN
GS 1.698086 0.885023 NaN
2009-03-04 06:29:59 AAPL -1.125821 0.413005 NaN
GOOG 0.424290 1.106983 NaN
GS 0.047158 0.680714 NaN
2009-03-05 06:29:59 AAPL 0.470050 1.845354 -0.250071
GOOG 0.132956 -0.488800 -0.084410
GS 0.129190 0.208077 0.331173
2009-03-06 06:29:59 AAPL -0.087360 -2.102512 -0.222934
GOOG 0.165100 -0.134886 -0.063415
GS 0.167720 0.082480 0.341192
2009-03-07 06:29:59 AAPL -0.768542 -0.176076 -0.273894
GOOG 0.417694 2.257074 0.008158
GS -1.744730 -1.850185 0.059485
2009-03-08 06:29:59 AAPL -0.297363 -0.633828 -0.361807
GOOG -1.096703 -0.572138 0.008667
GS 0.890016 -2.621563 -0.102129
2009-03-09 06:29:59 AAPL 1.038579 0.053330 0.071073
GOOG -0.614050 0.607944 -0.199001
GS -0.882848 0.596801 -0.288130
2009-03-10 06:29:59 AAPL -0.255226 0.058178 -0.073982
GOOG 1.761861 1.841751 0.126780
GS -0.549998 -1.551281 -0.423968
2009-03-11 06:29:59 AAPL 0.413522 0.149089 0.026194
GOOG -2.964163 1.825312 -0.499072
GS -0.373303 1.137001 -0.532173
2009-03-12 06:29:59 AAPL -0.924776 1.238546 -0.005053
GOOG -0.985956 -0.906590 -0.779802
GS -0.320400 1.239681 -0.247307