主页 / coding / 问题 / 79317617
Accepted
Ishigami
Asked: 2024-12-30 20:39:15 +0800 CST

将 Pandas 数据框从长格式转换为宽格式

  • 772

我有一个 Pandas 数据框，用于记录不同班级学生的考试表现，它如下所示：

Class_ID   Class_size   Student_Number   IQ   Hours   Score   Place
1          3            1                101  10      98      1
1          3            2                99   19      80      3
1          3            3                130  3       95      2
2          5            1                93   5       50      5
2          5            2                103  9       88      3
2          5            3                112  12      99      2
2          5            4                200  10      100     1
2          5            5                90   19      78      4
3          2            1                100  12      84      2
3          2            2                102  13      88      1

我想使用Student_Numberascolumns和Class_ID, Class_sizeas将上述长格式转换为宽格式index，最后一列Top是该班第一名的学生，因此期望的结果如下：

Class_ID Class_size IQ_1 IQ_2 IQ_3 IQ_4 IQ_5 Hours_1 Hours_2 Hours_3 Hours_4 Hours_5 Score_1 Score_2 Score_3 Score_4 Score_5 Top
1        1          101  99   130  NaN  NaN  10      19      3       NaN     NaN     98      80      95      NaN     NaN     1
2        5          93   103  112  200  90   5       9       12      10      19      50      88      99      100     78      4
3        2          100  102  NaN  NaN  NaN  12      13      NaN     NaN     NaN     84      88      NaN     NaN     NaN     2

以下是我尝试过的：

out = df.pivot_table(index=['Class_ID', 'Class_size'],
                     columns='Student_Number',
                     values=['IQ', 'Hours', 'Score'])
out.columns = [f'{x}_{y}' for x,y in out.columns]
out_dummy = out.reset_index()
df_wide = out_dummy

但是，我不知道如何创建最后一列Top。此外，上面的代码似乎相当慢，而且由于我的原始数据框非常大（约 300,000 行），我想问一下有没有更快的方法可以做到这一点。提前谢谢您。

pandas

2 个回答

  1. 你可以通过首先过滤 df 来仅包含 place 列等于 1 的行，并将 class_ID 值从 pivoted_df 映射到 top_students，如下所示，找到班级中的顶尖学生

    top_students = df[df['Place'] == 1].set_index('Class_ID')['Student_Number']
    pivoted_df['Top'] = pivoted_df['Class_ID'].map(top_students)

    代码

    # first create a pivot table
pivoted_df = df.pivot(index=['Class_ID', 'Class_size'], columns='Student_Number', values=['IQ', 'Hours', 'Score'])

# and then flatten the columns
pivoted_df.columns = [f'{i}_{j}' for i, j in pivoted_df.columns]
pivoted_df = pivoted_df.reset_index()

#find the Top students
top_students = df[df['Place'] == 1].set_index('Class_ID')['Student_Number']
pivoted_df['Top'] = pivoted_df['Class_ID'].map(top_students)

    输出

       Class_ID  Class_size   IQ_1   IQ_2   IQ_3   IQ_4  IQ_5  Hours_1  Hours_2  \
0         1           3  101.0   99.0  130.0    NaN   NaN     10.0     19.0   
1         2           5   93.0  103.0  112.0  200.0  90.0      5.0      9.0   
2         3           2  100.0  102.0    NaN    NaN   NaN     12.0     13.0   

   Hours_3  Hours_4  Hours_5  Score_1  Score_2  Score_3  Score_4  Score_5  Top  
0      3.0      NaN      NaN     98.0     80.0     95.0      NaN      NaN    1  
1     12.0     10.0     19.0     50.0     88.0     99.0    100.0     78.0    4  
2      NaN      NaN      NaN     84.0     88.0      NaN      NaN      NaN    2
    • 2
  2. Best Answer

    使用apply的一个简单方法：

    out["Top"] = out.apply(lambda row: 1 + np.argmax(row["Score_1":"Score_5"]) , axis=1)
    • 1

相关问题