python-3.x - 提高 pandas 的迭代、子集和合并性能

Question

我有一个包含用户和角色的数据框以及另一个具有不兼容角色的 df。挑战在于找出谁的角色不相容。我找到了一个带有 df.apply 的解决方案，它具有对用户和角色进行细分并将其合并的功能。结果很好，但性能需要改进：处理 500.000 条记录需要 1.5 小时。是否可以减少此过程的时间？

df_usr = pd.DataFrame({"user": ('A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C', 'C'),
                   "role": ('role_1', 'role_2', 'role_3', 'role_3', 'role_4', 'role_5', 'role_6', 'role_5', 'role_6', 'role_7', 'role_1', 'role_2'),
                  })
df_inc = pd.DataFrame({"role": ('role_1', 'role_1', 'role_3', 'role_3', 'role_5', 'role_7', 'role_7'),
                   "incomp": ('role_2', 'role_3', 'role_2', 'role_4', 'role_6', 'role_5', 'role_6')
                  })

class get_incomp():
    def __init__(self):
        self.df_final = pd.DataFrame()

    def get_incomp(self, user, role):
        df_u = df_usr[df_usr["user"] == user]
        df_i = df_inc[df_inc["role"] == role]
        df_r = pd.merge(left=df_u, right=df_i, left_on="role", right_on="incomp", suffixes=["_u", "_i"])
        self.df_final = pd.concat([self.df_final, df_r])

    def fin(self):
        self.df_final = self.df_final[["user", "role_u", "role_i"]]
        return self.df_final

inc = get_incomp()
df_usr.apply(lambda row: inc.get_incomp(row["user"], row["role"]), axis=1)
df_fin = inc.fin()

标签： python-3.xpandasperformance

Answer 1

tmp = pd.merge(df_usr, df_inc, on="role")
df_fin = pd.merge(
    tmp, df_usr, left_on=["user", "incomp"], right_on=["user", "role"]
)[["user", "incomp", "role_x"]].rename(
    columns={"incomp": "role_u", "role_x": "role_i"}
)
print(df_fin)

印刷：

  user  role_u  role_i
0    A  role_2  role_1
1    A  role_2  role_3
2    A  role_3  role_1
3    C  role_2  role_1
4    B  role_4  role_3
5    B  role_6  role_5
6    C  role_6  role_5
7    C  role_6  role_7
8    C  role_5  role_7

---

然后你可以.sort_values()：

print(df_fin.sort_values("user"))

  user  role_u  role_i
0    A  role_2  role_1
1    A  role_2  role_3
2    A  role_3  role_1
4    B  role_4  role_3
5    B  role_6  role_5
3    C  role_2  role_1
6    C  role_6  role_5
7    C  role_6  role_7
8    C  role_5  role_7