python - 将行值转换为列，并计算所有可能值的重复次数mysql

Question

我有一个表(来自日志文件)，其中包含电子邮件和其他三个包含该用户与系统交互状态的列，一个电子邮件(用户)可能有 100 或 1000 个条目，每个条目包含这三个值的组合，对于同一封电子邮件和其他电子邮件，可能会不断重复。看起来像这样:

+---------+---------+---------+-----+
| email |  val1   |  val2 |  val3   |
+---------+---------+---------+-----+
|jal@h  |  cast   | core  |   cam   |
|hal@b  |little ja| qar   |  ja sa  |
|bam@t  |  cast   | core  |   cam   |
|jal@h  |little ja| qar   |  jaja   | 
+---------+---------+---------+-----+

因此，电子邮件会重复，所有值都会重复，并且每一列(所有字符串)都有 40 多个可能的值。所以我想对不同的电子邮件 email 进行排序，然后将所有可能的值作为列名，并在其下计算该值对特定电子邮件重复的次数，如下所示:

+-------+--------+--------+------+----------+-----+--------+-------+
| email | cast   |   core |  cam | little ja| qar |  ja sa | blabla |
+-------+--------+--------+------+----------+-----+--------+--------|
|jal@h  |  55    |   2    | 44   |   244    | 1   | 200    | 12     |
|hal@b  |  900   |  513   | 101  |   146    |  2  |  733   | 833    |
|bam@t  |  1231  |   33   | 433  |   411    | 933 | 833    | 53     |
+-------+--------+--------+------+----------+-----+--------+---------

我已经尝试过 mysql，但我设法为每封电子邮件计算某个值的总出现次数，但没有计算每列中的所有可能值:

SELECT 
  distinct email,

  count(val1) as "cast"
FROM table1
where val1 = 'cast'
group by email

这个查询显然没有这样做，因为它只输出第一列“val1”中的值“cast”，我正在寻找的是第一、第二和第三列中的所有不同值都被转换为对于某个电子邮件“用户”，列标题和行中的值将是该值的总和。有一个枢轴表的东西，但我无法让它工作。我将这些数据作为 mysql 中的一个表来处理，但它在 csv 文件中可用，因此如果查询不可能，python 将是一个可能的解决方案，并且在 sql 之后首选。

更新在 python 中，是否可以将数据输出为:

+-------+--------+---------+------+----------+-----+--------+-------+
|       |     val1         |      val2       |     val3              |
+-------+--------+---------+------+----------+-----+--------+-------+
| email | cast   |little ja|core  | qar      |cam  | ja sa  | jaja   |
+-------+--------+---------+------+----------+-----+--------+--------|
|jal@h  |  55    |   2     | 44   |   244    | 1   | 200    | 12     |
|hal@b  |  900   |  513    | 101  |   146    |  2  |  733   | 833    |
|bam@t  |  1231  |   33    | 433  |   411    | 933 | 833    | 53     |
+-------+--------+--------+------+----------+-----+--------+---------

我对python不是很熟悉。

Answer 1

如果您使用 pandas，您可以在通过电子邮件对数据框进行分组后执行 value_counts，然后将其unstack/pivot 为宽格式:

(df.set_index("email").stack().groupby(level=0).value_counts()
   .unstack(level=1).reset_index().fillna(0))

---

要获得更新后的结果，您可以在 stack 之后按 email 和 val* 列进行分组:

(df.set_index("email").stack().groupby(level=[0, 1]).value_counts()
   .unstack(level=[1, 2]).fillna(0).sort_index(axis=1))