python - 如何分组间隔索引,汇总列表列表的平均值并加入另一个数据框?
问题描述
我有两个数据框。它们看起来像这样:
df_a
Framecount probability
0 0.0 [0.00019486549333333332, 4.883635666666667e-06...
1 1.0 [0.00104359155, 3.9232405e-05, 0.0015722045000...
2 2.0 [0.00048501002666666667, 1.668179e-05, 0.00052...
3 3.0 [4.994969500000001e-05, 4.0931635e-07, 0.00011...
4 4.0 [0.0004808829, 5.389742e-05, 0.002522127933333...
.. ... ...
906 906.0 [1.677140566666667e-05, 1.1745095666666665e-06...
907 907.0 [1.5164155000000002e-05, 7.66629575e-07, 0.000...
908 908.0 [8.1334184e-05, 0.00012675669636333335, 0.0028...
909 909.0 [0.00014893802999999998, 1.0407592500000001e-0...
910 910.0 [4.178489e-05, 2.17477925e-06, 0.02094931, 0.0...
和:
df_b
start stop
0 12.12 12.47
1 13.44 20.82
2 20.88 29.63
3 31.61 33.33
4 33.44 42.21
.. ... ...
228 880.44 887.92
229 888.63 892.07
230 892.13 895.30
231 895.31 900.99
232 907.58 908.35
我想合并df_a.probability
到df_b
df_b.startdf_a.Framecount
和df_b.stop
. 的聚合统计df_a.probability
应该是mean
,但我遇到了错误,因为df_a.probability
它是 dtype np 数组。
我正在尝试使用此代码:
idx = pd.IntervalIndex.from_arrays(df_text['start'], df_text['stop'])
df_text.join(df_vid.groupby(idx.get_indexer_non_unique(df_vid['Framecount']))['probability'].apply(np.mean), how='left')
第 1 行创建索引并确定分组。在第 2 行中,我试图实现 group by 并按平均值聚合所有df_a.probability
属于 groupby 索引的值。我想要每个 groupby 一个数组,它是 groupby 索引中所有数组的平均值。这段代码给了我这个错误:
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
<ipython-input-271-19c7d58fb664> in <module>
1 idx = pd.IntervalIndex.from_arrays(df_text['start'], df_text['stop'])
2 f = lambda x: np.mean(np.array(x.tolist()), axis=0)
----> 3 df_text.join(df_vid.groupby(idx.get_indexer_non_unique(df_vid['Framecount']))['probability'].apply(np.mean), how='left')
~/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in groupby(self, by, axis, level, as_index, sort, group_keys, squeeze, observed)
5808 group_keys=group_keys,
5809 squeeze=squeeze,
-> 5810 observed=observed,
5811 )
5812
~/anaconda3/lib/python3.7/site-packages/pandas/core/groupby/groupby.py in __init__(self, obj, keys, axis, level, grouper, exclusions, selection, as_index, sort, group_keys, squeeze, observed, mutated)
407 sort=sort,
408 observed=observed,
--> 409 mutated=self.mutated,
410 )
411
~/anaconda3/lib/python3.7/site-packages/pandas/core/groupby/grouper.py in get_grouper(obj, key, axis, level, sort, observed, mutated, validate)
588
589 elif is_in_axis(gpr): # df.groupby('name')
--> 590 if gpr in obj:
591 if validate:
592 obj._check_label_or_level_ambiguity(gpr, axis=axis)
~/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py in __contains__(self, key)
1848 def __contains__(self, key) -> bool_t:
1849 """True if the key is in the info axis"""
-> 1850 return key in self._info_axis
1851
1852 @property
~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py in __contains__(self, key)
3898 @Appender(_index_shared_docs["contains"] % _index_doc_kwargs)
3899 def __contains__(self, key) -> bool:
-> 3900 hash(key)
3901 try:
3902 return key in self._engine
TypeError: unhashable type: 'numpy.ndarray'
我尝试了多种聚合规范,包括:
df_text.join(df_vid.groupby(idx.get_indexer_non_unique(df_vid['Framecount']))['probability'].apply(lambda x: np.mean(np.array(x.tolist()), axis=0)), how='left')
或者
df_text.join(df_vid.groupby(idx.get_indexer_non_unique(df_vid['Framecount']))['probability'].apply((np.mean), how='left')
或者
df_text.join(df_vid.groupby(idx.get_indexer_non_unique(df_vid['Framecount']))['probability'].mean()), how='left')
我得到同样的错误。
我该如何做到这一点?
解决方案
- 发生错误是因为以这种方式
idx.get_indexer_non_unique(df_vid['Framecount'])
创建了一个tuple
,而您不能创建groupby
一个元组。df_vid.groupby(idx.get_indexer_non_unique(df_vid['Framecount'])[0])
选择中的第一个数组tuple
会起作用。
idx.get_indexer(df_a.fc)
将产生一个数组,其中包含fc
所属区间的索引。如果没有匹配的区间,索引将显示为-1
。df_a.groupby(idx.get_indexer(df_a.fc))
按索引数组分组。.agg({'prob': list})
将每个 的所有列表聚合fc
成一个列表。- 每个组的结果是一个列表列表
.prob.map(np.mean)
返回组中所有列表的总体平均值.prob.apply(lambda x: [np.mean(v) for v in x])
为每个列表返回一个均值列表。- 没有
'fc'
值落入垃圾箱12.12 - 12.47
。
import pandas as pd
import numpy as np
# setup df with start and stop ranges
data = {'start': [12.12, 13.44, 20.88, 31.61, 33.44, 880.44, 888.63, 892.13, 895.31, 907.58], 'stop': [12.47, 20.82, 29.63, 33.33, 42.21, 887.92, 892.07, 895.3, 900.99, 908.35]}
df = pd.DataFrame(data)
# setup sample df_a with Framecount as fc, and probability as prob
np.random.seed(365)
df_a = pd.DataFrame({'fc': range(911), 'prob': np.random.randint(1, 100, (911, 14)).tolist()})
# this will convert the column to np.arrays instead of lists; the remainder of the code works regardless
# df_a.prob = df_a.prob.map(np.array)
# create an IntervalIndex from df start and stop
idx = pd.IntervalIndex.from_arrays(df.start, df.stop, closed='both')
这将在 axis=0 上创建一个均值列表
dfg = df_a.groupby(idx.get_indexer(df_a.fc)).agg({'prob': list}).prob.apply(lambda x: np.mean(x, axis=0))
# join df with dfg
dfj = df.join(dfg)
# display(dfj) for list of means
start stop prob
0 12.12 12.47 NaN
1 13.44 20.82 [49.3, 57.1, 51.4, 45.9, 47.1, 45.9, 45.9, 55.3, 32.6, 48.0, 42.0, 45.0, 50.4, 54.4]
2 20.88 29.63 [42.7, 42.6, 46.0, 45.9, 54.1, 55.9, 50.1, 55.2, 51.7, 54.0, 37.6, 60.9, 49.2, 45.6]
3 31.61 33.33 [87.5, 49.0, 46.5, 54.5, 75.0, 47.0, 24.0, 40.5, 52.5, 21.0, 51.0, 72.5, 34.5, 50.5]
4 33.44 42.21 [48.6, 66.2, 45.8, 64.7, 43.1, 69.0, 54.4, 52.1, 52.6, 59.6, 51.1, 42.1, 43.3, 38.0]
5 880.44 887.92 [51.9, 50.6, 63.7, 47.7, 51.3, 34.9, 51.3, 53.0, 53.4, 65.1, 38.6, 49.4, 48.1, 44.1]
6 888.63 892.07 [45.2, 23.5, 67.2, 68.0, 38.2, 47.2, 50.2, 75.8, 35.2, 46.8, 55.0, 57.5, 44.2, 78.0]
7 892.13 895.30 [61.3, 44.0, 43.3, 36.3, 63.7, 89.7, 51.7, 57.0, 50.0, 68.7, 80.7, 46.3, 66.7, 11.3]
8 895.31 900.99 [68.2, 44.6, 50.8, 35.2, 53.2, 40.4, 34.8, 77.4, 61.0, 35.2, 26.0, 47.8, 30.4, 55.4]
9 907.58 908.35 [17.0, 78.0, 24.0, 33.0, 88.0, 3.0, 43.0, 2.0, 36.0, 48.0, 8.0, 87.0, 36.0, 34.0]
这将为每个组创建一个平均值
dfg = df_a.groupby(idx.get_indexer(df_a.fc)).agg({'prob': list}).prob.map(np.mean)
# join df with dfg
dfj = df.join(dfg)
# display(dfj) for overall mean
start stop prob
0 12.12 12.47 NaN
1 13.44 20.82 47.877551
2 20.88 29.63 49.380952
3 31.61 33.33 50.428571
4 33.44 42.21 52.182540
5 880.44 887.92 50.224490
6 888.63 892.07 52.303571
7 892.13 895.30 55.047619
8 895.31 900.99 47.171429
9 907.58 908.35 38.357143
推荐阅读
- branch.io - 用于打开网页的深层链接,用户可以在其中提交表单,然后将他带到应用程序下载页面
- c++ - char* with text 是否会像使用 malloc 一样自动保留内存?
- php - 我正在学习函数式编程,我想知道是否有更好的方法来做到这一点?
- postgresql - Postgres间隔Spring数据动态参数不起作用
- jekyll - 在 github 页面上获取页面贡献者
- asp-classic - 如果未确定用户,则显示限制页面,否则,显示所需页面
- r - 随机森林确定解释变异的最重要的预测因子
- java - E/RecyclerView:没有附加适配器;跳过布局,不重复没有任何效果(不显示框架)
- powershell - 如何使用凭据和无表单连接到 Exchange Online (connect-exopssession)?
- circuit-sdk - 使用 node.js 传递主题、内容等