"转行值为列，并计算所有可能值的重复次数mysq"

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 | 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”，我要查找的是第一、第二和第三列中所有不同的值都被转换为列标题，并且行中的值将是特定电子邮件“user”的值的总和。 有一个数据透视表的东西，但我不能让它工作。 我在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

我将重建数据帧，然后分组并用pd.value_counts取消堆栈

v = df.values
s = pd.Series(v[:, 1:].ravel(), v[:, 0].repeat(3))

s.groupby(level=0).value_counts().unstack(fill_value=0)

       cam  cast  core  ja sa  jaja  little ja  qar
bam@t    1     1     1      0     0          0    0
hal@b    0     0     0      1     0          1    1
jal@h    1     1     1      0     1          1    1

Answer 2

如果您使用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))

Answer 3

如果知道列表，可以使用group by计算：

SELECT email,
       sum(val1 = 'cast') as `cast`,
       sum(val1 = 'core') as `core`,
       sum(val1 = 'cam') as `cam`,
       . . .
FROM table1
GROUP BY email;

. . .用于填充剩余的值。你知道吗