在对数据框的一列进行装箱后，如何制作一个新的数据框以计算每个箱中的元素数量? [英] After binning a column of a dataframe, how to make a new dataframe to count the number of elements in each bin?

问题描述

说我有一个数据框，df:

>>> df

Age    Score
19     1
20     2
24     3
19     2
24     3
24     1
24     3
20     1
19     1
20     3
22     2
22     1

我想构造一个新的数据框，将Age装箱，并将每个箱中的元素总数存储在不同的Score列中:

I want to construct a new dataframe that bins Age and stores the total number of elements in each of the bins in different Score columns:

Age       Score 1   Score 2     Score 3
19-21     2         4           3
22-24     2         2           9

这是我的处理方式，我感到非常费解(意思是，这不应该那么困难):

This is my way of doing it, which I feel is highly convoluted (meaning, it shouldn't be this difficult):

import numpy as np
import pandas as pd

data = pd.DataFrame(columns=['Age', 'Score'])
data['Age'] = [19,20,24,19,24,24,24,20,19,20,22,22]
data['Score'] = [1,2,3,2,3,1,3,1,1,3,2,1]

_, bins = np.histogram(data['Age'], 2)

labels = ['{}-{}'.format(i + 1, j) for i, j in zip(bins[:-1], bins[1:])] #dynamically create labels
labels[0] = '{}-{}'.format(bins[0], bins[1])

df = pd.DataFrame(columns=['Score', labels[0], labels[1]])
df['Score'] = data.Score.unique()
for i in labels:
    df[i] = np.zeros(3)


for i in range(len(data)):
    for j in range(len(labels)):
        m1, m2 = labels[j].split('-') # lower & upper bounds of the age interval
        if ((float(data['Age'][i])>float(m1)) & (float(data['Age'][i])<float(m2))): # find the age group in which each age lies
            if data['Score'][i]==1:
                index = 0
            elif data['Score'][i]==2:
                index = 1
            elif data['Score'][i]==3:
                index = 2

            df[labels[j]][index] += 1

df.sort_values('Score', inplace=True)
df.set_index('Score', inplace=True)
print(df)

这产生

             19.0-21.5      22.5-24.0
Score                      
1            2.0            2.0
2            4.0            2.0
3            3.0            9.0

是否有更好，更清洁，更有效的方法来实现这一目标?

Is there a better, cleaner, more efficient of achieving this?

推荐答案

IIUC，我认为您可以尝试以下方法之一:

IIUC, I think you can try one of these:

1.如果您已经知道垃圾箱:

1.If you already know the bins:

df['Age'] = np.where(df['Age']<=21,'19-21','22-24')
df.groupby(['Age'])['Score'].value_counts().unstack()

2.如果您知道垃圾箱的数量，则需要:

2.If you know number of bins you need:

df.Age = pd.cut(df.Age, bins=2,include_lowest=True)
df.groupby(['Age'])['Score'].value_counts().unstack()

3.乔恩·克莱门茨评论的想法:

pd.crosstab(pd.cut(df.Age, [19, 21, 24],include_lowest=True), df.Score)

这三者均产生以下输出:

All of the three produces following output:

Score           1   2   3
Age         
(18.999, 21.0]  3   2   1
(21.0, 24.0]    2   1   3

这篇关于在对数据框的一列进行装箱后，如何制作一个新的数据框以计算每个箱中的元素数量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！