使用样本数据:
df = pd.DataFrame({'key1' : ['a','a','b','b','a'],
'key2' : ['one', 'two', 'one', 'two', 'one'],
'data1' : np.random.randn(5),
'data2' : np. random.randn(5)})
df
data1 data2 key1 key2
0 0.361601 0.375297 a one
1 0.069889 0.809772 a two
2 1.468194 0.272929 b one
3 -1.138458 0.865060 b two
4 -0.268210 1.250340 a one
我试图弄清楚如何按key1分组数据并仅对key2等于“ one”的data1值求和。
这是我尝试过的
def f(d,a,b):
d.ix[d[a] == b, 'data1'].sum()
df.groupby(['key1']).apply(f, a = 'key2', b = 'one').reset_index()
但这给了我一个“无”值的数据框
index key1 0
0 a None
1 b None
这里有什么想法吗?我正在寻找与以下SQL等效的Pandas:
SELECT Key1, SUM(CASE WHEN Key2 = 'one' then data1 else 0 end)
FROM df
GROUP BY key1
仅供参考-我已经看到了有条件的熊猫总和,但无法转换那里提供的答案来处理总和而不是计数。
提前致谢
首先按key1列分组:
In [11]: g = df.groupby('key1')
然后为每个组取subDataFrame,其中key2等于“ one”并求和data1列:
In [12]: g.apply(lambda x: x[x['key2'] == 'one']['data1'].sum())
Out[12]:
key1
a 0.093391
b 1.468194
dtype: float64
为了解释发生了什么,让我们看一下“ a”组:
In [21]: a = g.get_group('a')
In [22]: a
Out[22]:
data1 data2 key1 key2
0 0.361601 0.375297 a one
1 0.069889 0.809772 a two
4 -0.268210 1.250340 a one
In [23]: a[a['key2'] == 'one']
Out[23]:
data1 data2 key1 key2
0 0.361601 0.375297 a one
4 -0.268210 1.250340 a one
In [24]: a[a['key2'] == 'one']['data1']
Out[24]:
0 0.361601
4 -0.268210
Name: data1, dtype: float64
In [25]: a[a['key2'] == 'one']['data1'].sum()
Out[25]: 0.093391000000000002
通过将数据帧限制为仅那些key2等于1的数据帧,可能会更容易/更清楚地做到这一点:
In [31]: df1 = df[df['key2'] == 'one']
In [32]: df1
Out[32]:
data1 data2 key1 key2
0 0.361601 0.375297 a one
2 1.468194 0.272929 b one
4 -0.268210 1.250340 a one
In [33]: df1.groupby('key1')['data1'].sum()
Out[33]:
key1
a 0.093391
b 1.468194
Name: data1, dtype: float64
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句