Skip to main content
 首页 » 编程设计

python之Pandas :使用枢轴函数进行复杂列转置的更快方法

2024年11月24日7cmt

简单地说,我需要将下面的输入数据帧转换为下面的输出。

经过几个小时努力弄清楚如何通过结合之前的多个 stackoverflow 问题,我可以转换数据帧,但是由于我使用了 pivot 和 apply 方法,转换大型数据帧需要花费很多时间。

import numpy as np 
import pandas as pd 
df = pd.DataFrame({"id":[1,2,3,4,5], 
                  "day":pd.Timestamp('20190529'), 
                  "subject":"math,english,economics", 
                  "score":pd.Categorical(["68,62,49","58,72,87","28,32,46","48,72,66","46,25,93"]), 
                  "Department":pd.Categorical(["Economics","Computer Science","Sociology","Business","Math"])}) 
 
 
---Input DataFrame--- 
 
    id    day           subject                score       Department 
0   1   2019-05-29  math,english,economics    68,62,49    Economics 
1   2   2019-05-29  math,economics             58,87       Computer Science 
2   3   2019-05-29  philosophy,english,business 28,32,46    Sociology 
3   4   2019-05-29  physics,sociology           72,66      Business 
4   5   2019-05-29  Math                          93          Math 

输出如下图

---Output DataFrame--- 
 
id  day         Department          Math business economics english math philosophy physics sociology 
1   2019-05-29  Economics           NaN NaN 49  62  68  NaN NaN NaN 
2   2019-05-29  Computer Science    NaN NaN 87  NaN 58  NaN NaN NaN 
3   2019-05-29  Sociology           NaN 46  NaN 32  NaN 28  NaN NaN 
4   2019-05-29  Business            NaN NaN NaN NaN NaN NaN 72  66 
5   2019-05-29  Math                93  NaN NaN NaN NaN NaN NaN NaN 

我的做法是

  1. 我用“,”拆分主题和分数列。
  2. 分解主题和分数列中的元素列表以将行分隔为 pandas.Series
  3. 加入每个 pandas.Series 以制作新的数据框
  4. 透视第 3 步中创建的新数据框
  5. 在原始数据框中删除主题和分数列
  6. 加入第 4 步和第 5 步中制作的每个数据框

而我的代码如下

df["subject"] = df["subject"].str.split(",") 
df["score"] = df["score"].str.split(",") 
 
subject = df.apply(lambda x: pd.Series(x['subject']),axis=1).stack().reset_index(level=1, drop=True) 
score = df.apply(lambda x: pd.Series(x['score']),axis=1).stack().reset_index(level=1, drop=True) 
subject.name = 'subject' 
score.name = 'score' 
 
subject_score = pd.concat([subject, score],join='outer', axis=1) 
pdf = df.drop('subject', axis=1).drop("score", axis=1).join(subject_score) 
 
pivot = pdf.pivot(columns="subject",values="score") 
concate_table = df.drop("subject",axis = 1).drop("score", axis=1) 
output = concate_table.join(pivot) 

我最近才开始学习 pandas,我确信这不是列转置的最佳方式。

如果您能给我一些如何优化此代码的建议,我将不胜感激。

提前谢谢你。

请您参考如下方法:

我会定义一个自定义函数 stack_str使用 expand=True 将字符串列解压到数据框和 stack和 reset_index到一个系列。

申请stack_str到 2 列字符串来制作 df1 2列。

接下来,执行 pivot在 df1制作subject值为 columns和 scores作为values .最后加入回df已经删除了 2 个包含字符串的列。

def stack_str(x): 
        s = x.str.split(',', expand=True).stack().reset_index(level=-1, drop=True) 
        return s 
 
df1 = df[['subject', 'score']].apply(stack_list) 
 
Out[984]: 
      subject score 
0        math    68 
0     english    62 
0   economics    49 
1        math    58 
1   economics    87 
2  philosophy    28 
2     english    32 
2    business    46 
3     physics    72 
3   sociology    66 
4        Math    93 
 
df2 = df.drop(['subject', 'score'], axis=1).join(df1.pivot(columns='subject', values='score')) 
 
Out[986]: 
   id         day        Department Math business economics english math  \ 
0   1  2019-05-29         Economics  NaN      NaN        49      62   68 
1   2  2019-05-29  Computer_Science  NaN      NaN        87     NaN   58 
2   3  2019-05-29         Sociology  NaN       46       NaN      32  NaN 
3   4  2019-05-29          Business  NaN      NaN       NaN     NaN  NaN 
4   5  2019-05-29              Math   93      NaN       NaN     NaN  NaN 
 
  philosophy physics sociology 
0        NaN     NaN       NaN 
1        NaN     NaN       NaN 
2         28     NaN       NaN 
3        NaN      72        66 
4        NaN     NaN       NaN