pandas 数据透视表ValueError:索引包含重复的条目，无法重塑 [英] Pandas pivot table ValueError: Index contains duplicate entries, cannot reshape

问题描述

我有一个如下所示的数据框(前3行):

I have a dataframe as shown below (top 3 rows):

Sample_Name Sample_ID   Sample_Type IS  Component_Name  IS_Name Component_Group_Name    Outlier_Reasons Actual_Concentration    Area    Height  Retention_Time  Width_at_50_pct Used    Calculated_Concentration    Accuracy
Index                                                               
1   20170824_ELN147926_HexLacCer_Plasma_A-1-1   NaN Unknown True    GluCer(d18:1/12:0)_LCB_264.3    NaN NaN NaN 0.1 2.733532e+06    5.963840e+05    2.963911    0.068676    True    NaN NaN
2   20170824_ELN147926_HexLacCer_Plasma_A-1-1   NaN Unknown True    GluCer(d18:1/17:0)_LCB_264.3    NaN NaN NaN 0.1 2.945190e+06    5.597470e+05    2.745026    0.068086    True    NaN NaN
3   20170824_ELN147926_HexLacCer_Plasma_A-1-1   NaN Unknown False   GluCer(d18:1/16:0)_LCB_264.3    GluCer(d18:1/17:0)_LCB_264.3    NaN NaN NaN 3.993535e+06    8.912731e+05    2.791991    0.059864    True    125.927659773487    NaN

尝试生成数据透视表时:

When trying to generate a pivot table:

pivoted_report_conc = raw_report.pivot(index = "Sample_Name", columns = 'Component_Name', values = "Calculated_Concentration")

我收到以下错误:

ValueError: Index contains duplicate entries, cannot reshape

我尝试重置索引，但没有帮助.我在索引"列中找不到任何重复的值.有人可以帮忙在这里找到问题吗?

I tried resetting the index but it did not help. I couldn't find any duplicate values in the "Index" column. Could someone please help identify the problem here?

预期输出将是一个经过重整的数据框，其中只有唯一的组分名称为列，并且每个样品名称分别具有浓度:

The expected output would be a reshaped dataframe with only the unique component names as columns and respective concentrations for each sample name:

Sample_Name    GluCer(d18:1/12:0)_LCB_264.3    GluCer(d18:1/17:0)_LCB_264.3    GluCer(d18:1/16:0)_LCB_264.3
20170824_ELN147926_HexLacCer_Plasma_A-1-1    NaN    NaN    125.927659773487

为澄清起见，我不是要汇总数据，而只是重塑数据.

To clarify, I am not looking to aggregate the data, just reshape it.

推荐答案

您可以使用groupby()和unstack()来解决在pivot()中看到的错误.

You can use groupby() and unstack() to get around the error you're seeing with pivot().

以下是一些示例数据，其中添加了一些边缘情况，并删除了某些列值或将其替换为 MCVE :

Here's some example data, with a few edge cases added, and some column values removed or substituted for MCVE:

# df
      Sample_Name  Sample_ID     IS Component_Name Calculated_Concentration Outlier_Reasons
Index                                                                    
1             foo        NaN   True              x                  NaN              NaN  
1             foo        NaN   True              y                  NaN              NaN 
2             foo        NaN   False             z            125.92766              NaN 
2             bar        NaN   False             x                 1.00              NaN  
2             bar        NaN   False             y                 2.00              NaN  
2             bar        NaN   False             z                  NaN              NaN  

(df.groupby(['Sample_Name','Component_Name'])
   .Calculated_Concentration
   .first()
   .unstack()
)

输出:

Component_Name    x   y          z
Sample_Name                       
bar             1.0 2.0        NaN
foo             NaN NaN  125.92766

这篇关于 pandas 数据透视表ValueError:索引包含重复的条目，无法重塑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！