根据两列和各自的范围值创建计数矩阵 pandas

发布于 2025-01-11 03:30:00 字数 562 浏览 0 评论 0原文

我有一个如下所示的数据框，

name,value,id
meanerror,0.55,aa
meanamount,120,aa
meanerror,0.45,bb
meanamount,150,bb
meanerror,0.88,cc
meanamount,110,cc
meanerror,0.1,dd
meanamount,50,dd

我想从该数据框创建一个矩阵，如下所示。

,         meanamount,    total_y
meanerror,0-100,100-200
0.0-0.5,    1,    1,      2   
0.5-1,      0,    2,      2
total_x,    1,    3

我真正需要的是，在矩阵中，每个单元格应该包含 ids 的计数，这些 ids 的值（来自值列）在矩阵的 x 轴和 y 轴的范围内。例如，第一个单元格应包含 ids 计数，平均值在 0-100 范围内，平均误差在 0.0-5 范围内。

我尝试过 pandas 数据透视表和交叉表，但不确定如何实现这一点。有人可以帮忙吗？

原文

I have a dataframe which looks like below,

name,value,id
meanerror,0.55,aa
meanamount,120,aa
meanerror,0.45,bb
meanamount,150,bb
meanerror,0.88,cc
meanamount,110,cc
meanerror,0.1,dd
meanamount,50,dd

I would like to create a matrix from this dataframe like below.

,         meanamount,    total_y
meanerror,0-100,100-200
0.0-0.5,    1,    1,      2   
0.5-1,      0,    2,      2
total_x,    1,    3

what I actually need is, in the matrix, each cell should contain count of ids which has value(from value column) in the range on both x and y axis of the matrix. i.e for example the first cell should contain count of ids with meanamount in range 0-100 and meanerror in range 0.0-5.

I have tried pandas pivot table and crosstab but unsure how to achieve this. Can anyone help?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我很坚强 2025-01-18 03:30:00

创建一个数据透视表：

pt = df.pivot(index='id',columns='name',values='value')

# 名称 平均量 平均误差
＃ ID                         
# AA 120.0 0.55
# BB 150.0 0.45
# 抄送 110.0 0.88
# 日 50.0 0.10

将数量和错误放入垃圾箱：

pt['meanamount'] = pd.cut(pt['meanamount'], bins=range(0, 300, 100))
pt['meanerror'] = pd.cut(pt['meanerror'], bins=np.arange(0, 1.5, 0.5))

# 名称 平均量 平均误差
＃ ID                          
# aa (100, 200] (0.5, 1.0]
# bb (100, 200] (0.0, 0.5]
# cc (100, 200] (0.5, 1.0]
# dd (0, 100] (0.0, 0.5]

创建误差 x 数量的交叉表：

pd.crosstab(pt['meanerror'], pt['meanamount'], margins=True)

# 平均金额 (0, 100] (100, 200] 全部
# 平均误差                            
# (0.0, 0.5] 1 1 2
# (0.5, 1.0] 0 2 2
# 全部 1 3 4

Create a pivot table:

pt = df.pivot(index='id', columns='name', values='value')

# name  meanamount  meanerror
# id                         
# aa         120.0       0.55
# bb         150.0       0.45
# cc         110.0       0.88
# dd          50.0       0.10

Cut the amounts and errors into bins:

pt['meanamount'] = pd.cut(pt['meanamount'], bins=range(0, 300, 100))
pt['meanerror'] = pd.cut(pt['meanerror'], bins=np.arange(0, 1.5, 0.5))

# name  meanamount   meanerror
# id                          
# aa    (100, 200]  (0.5, 1.0]
# bb    (100, 200]  (0.0, 0.5]
# cc    (100, 200]  (0.5, 1.0]
# dd      (0, 100]  (0.0, 0.5]

Create a crosstab of error x amount:

pd.crosstab(pt['meanerror'], pt['meanamount'], margins=True)

# meanamount  (0, 100]  (100, 200]  All
# meanerror                            
# (0.0, 0.5]         1           1    2
# (0.5, 1.0]         0           2    2
# All                1           3    4

回复收藏 0 原文

~没有更多了~