pandas :pivot 和 pivot_table 之间的区别.为什么只有 pivot_table 工作?

2022-01-22 00:00:00 python pandas pivot

问题描述

我有以下数据框.

df.head(30)

     struct_id  resNum score_type_name  score_value
0   4294967297       1           omega     0.064840
1   4294967297       1          fa_dun     2.185618
2   4294967297       1      fa_dun_dev     0.000027
3   4294967297       1     fa_dun_semi     2.185591
4   4294967297       1             ref    -1.191180
5   4294967297       2            rama    -0.795161
6   4294967297       2           omega     0.222345
7   4294967297       2          fa_dun     1.378923
8   4294967297       2      fa_dun_dev     0.028560
9   4294967297       2      fa_dun_rot     1.350362
10  4294967297       2         p_aa_pp    -0.442467
11  4294967297       2             ref     0.249477
12  4294967297       3            rama     0.267443
13  4294967297       3           omega     0.005106
14  4294967297       3          fa_dun     0.020352
15  4294967297       3      fa_dun_dev     0.025507
16  4294967297       3      fa_dun_rot    -0.005156
17  4294967297       3         p_aa_pp    -0.096847
18  4294967297       3             ref     0.979644
19  4294967297       4            rama    -1.403292
20  4294967297       4           omega     0.212160
21  4294967297       4          fa_dun     4.218029
22  4294967297       4      fa_dun_dev     0.003712
23  4294967297       4     fa_dun_semi     4.214317
24  4294967297       4         p_aa_pp    -0.462765
25  4294967297       4             ref    -1.960940
26  4294967297       5            rama    -0.600053
27  4294967297       5           omega     0.061867
28  4294967297       5          fa_dun     3.663050
29  4294967297       5      fa_dun_dev     0.004953

根据 pivot 文档，我应该能够使用 pivot 函数在 score_type_name 上重塑它.

According to the pivot documentation, I should be able to reshape this on the score_type_name using the pivot function.

df.pivot(columns='score_type_name',values='score_value',index=['struct_id','resNum'])

但是，我得到以下信息.

But, I get the following.

但是，pivot_table 函数似乎可以工作:

However, pivot_table function seems to work:

pivoted = df.pivot_table(columns='score_type_name', values='score_value', index=['struct_id','resNum'])

但至少对我来说，它不适合做进一步的分析.我希望它只将 struct_id、resNum 和 score_type_name 作为列，而不是将 score_type_name 堆叠在其他列的顶部.此外，我希望 struct_id 用于每一行，而不是像在表中那样聚合在连接行中.

But it does not lend itself, for me atleast, to further analysis. I want it to just have the struct_id, resNum, and score_type_name as columns instead of stacking the score_type_name on top of the other columns. Additionally, I want the struct_id to be for every row, and not aggregate in a joined row like it does for the table.

那么谁能告诉我如何使用 pivot 获得一个不错的 Dataframe?此外，从文档中，我无法说出为什么 pivot_table 有效而 pivot 无效.如果我查看枢轴的第一个示例，它看起来正是我所需要的.

So can anyone tell me how I can get a nice Dataframe like I want using pivot? Additionally, from the documentation, I can't tell why pivot_table works and pivot doesn't. If I look at the first example of pivot, it looks like exactly what I need.

附:我确实发布了一个关于这个问题的问题，但是我在演示输出方面做得很差，我删除了它并再次使用 ipython notebook 尝试.如果您看到两次，我提前道歉.

P.S. I did post a question in reference to this problem, but I did such a poor job of demonstrating the output, I deleted it and tried again using ipython notebook. I apologize in advance if you are seeing this twice.

这是供您参考的笔记本

编辑 - 我想要的结果看起来像这样(用 excel 制作):

EDIT - My desired results would look like this (made in excel):

StructId resNum pdb_residue_number chain_id name3 fa_dun fa_dun_dev fa_dun_rot fa_dun_semi omega p_aa_pp rama ref 4294967297 1 99 A ASN 2.1856 0.0000 2.1856 0.0648 -1.1912 4294967297 2 100 A MET 1.3789 0.0286 1.3504 0.2223 -0.4425 -0.7952 0.2495 4294967297 3 101 A VAL 0.0204 0.0255 -0.0052 0.0051 -0.0968 0.2674 0.9796 4294967297 4 102 A GLU 4.2180 0.0037 4.2143 0.2122 -0.4628 -1.4033 -1.9609 4294967297 5 103 A GLN 3.6630 0.0050 3.6581 0.0619 -0.2759 -0.6001 -1.5172 4294967297 6 104 A MET 1.5175 0.2206 1.2968 0.0504 -0.3758 -0.7419 0.2495 4294967297 7 105 A HIS 3.6987 0.0184 3.6804 0.0547 0.4019 -0.1489 0.3883 4294967297 8 106 A THR 0.1048 0.0134 0.0914 0.0003 -0.7963 -0.4033 0.2013 4294967297 9 107 A ASP 2.3626 0.0005 2.3620 0.0521 0.1955 -0.3499 -1.6300 4294967297 10 108 A ILE 1.8447 0.0270 1.8176 0.0971 0.1676 -0.4071 1.0806 4294967297 11 109 A ILE 0.1276 0.0092 0.1183 0.0208 -0.4026 -0.0075 1.0806 4294967297 12 110 A SER 0.2921 0.0342 0.2578 0.0342 -0.2426 -1.3930 0.1654 4294967297 13 111 A LEU 0.6483 0.0019 0.6464 0.0845 -0.3565 -0.2356 0.7611 4294967297 14 112 A TRP 2.5965 0.1507 2.4457 0.5143 -0.1370 -0.5373 1.2341 4294967297 15 113 A ASP 2.6448 0.1593 0.0510 -0.5011

解决方案

对于pivot和pivot_table的区别还有兴趣的朋友，主要有两个区别:

For anyone who is still interested in the difference between pivot and pivot_table, there are mainly two differences:

pivot_table 是 pivot 的概括，它可以处理一个 pivot 索引/列对的重复值.具体来说，您可以使用关键字参数 aggfunc 为 pivot_table 提供一个聚合函数列表.pivot_table 的默认 aggfunc 是 numpy.mean.
pivot_table 还支持对pivot 表的索引和列使用多列.系统会自动为您生成分层索引.

pivot_table is a generalization of pivot that can handle duplicate values for one pivoted index/column pair. Specifically, you can give pivot_table a list of aggregation functions using keyword argument aggfunc. The default aggfunc of pivot_table is numpy.mean.

pivot_table also supports using multiple columns for the index and column of the pivoted table. A hierarchical index will be automatically generated for you.

参考:pivot 和 pivot_table

相关文章