如何在.xlsx中删除重复项并用熊猫将值移至新列

Question

我的.xlsx表有多个条目： 条目

我尝试实现的目标： 结果

因此，我真的被卡住了。我尝试过df.drop_duplicates（），但无济于事。 如果您可以分享一些想法或在哪里看，那就太好了！ Tia

P.S.对不起，图片，但由于某种原因，它无法正确渲染。

Answer 1

我的策略是创建然后将索引设置为触发列连接2个数据范围。一个数据帧仅包含触发， to 和 列的代码>类别和说明列。这里的“窍门”是使用 dataframe。 Pivot 将相应的类别和descript值中的值添加到自己的列中。

import pandas as pd
import os

filename = r'logfile.xlsx'
new_filename = r'consolidated_logfile.xlsx'

df = pd.read_excel(filename)

# Create a dataframe with the actual records we want to be left with at the end
# You might think of this as removing duplicates, in a way
records = df.loc[df['String Value'].isna()]

# Remove the 'String Value' and 'Remarks' columns
records = records.drop(columns=['String Value', 'Remarks'])

# Set the index to the 'Trigger' column, so another dataframe with the extra 
# details ('Category' and 'Description' columns) can be joined/merged later on
records = records.set_index('Trigger')

# Create a dataframe with just the extra details ('Category' and 'Description' columns)
details = df.loc[df['String Value'].notna()]

# Transform the dataframe using DataFrame.pivot so that:
# - the indexes are the values from the 'Trigger' column
# - the columns are the values from the 'String Value' column
# - the values are taken from the 'Remarks' column
details = details.pivot(index='Trigger', columns='String Value', values='Remarks')

# Combine the 2 dataframes with DataFrame.join
combined = records.join(details)

# Save to a new spreadsheet
combined.to_excel(new_filename)

# Launch the new spreadsheet
os.startfile(new_filename)

为了使其更容易理解，以下是每个步骤之后的数据框：

df = pd.read_excel(filename)

                   Trigger   From     To String Value                    Remarks
0  incident: INC111111111    Tom  Peter          NaN                        NaN
1      request: REQ123464   John   Alex          NaN                        NaN
2   incident: INC22222222  David  Maria          NaN                        NaN
3  incident: INC111111111    NaN    NaN     Category                       FYI 
4      request: REQ123464    NaN    NaN     Category               Wrong Route 
5   incident: INC22222222    NaN    NaN     Category                       FYI 
6  incident: INC111111111    NaN    NaN  Description            Wrongly routed 
7      request: REQ123464    NaN    NaN  Description  Dispatch to another team 
8   incident: INC22222222    NaN    NaN  Description     Try more troubleshoot 

# Create a dataframe with the actual records we want to be left with at the end
# You might think of this as removing duplicates, in a way
records = df.loc[df['String Value'].isna()]

                   Trigger   From     To String Value Remarks
0  incident: INC111111111    Tom  Peter          NaN     NaN
1      request: REQ123464   John   Alex          NaN     NaN
2   incident: INC22222222  David  Maria          NaN     NaN

# Remove the 'String Value' and 'Remarks' columns
records = records.drop(columns=['String Value', 'Remarks'])

                   Trigger   From     To
0  incident: INC111111111    Tom  Peter
1      request: REQ123464   John   Alex
2   incident: INC22222222  David  Maria

# Set the index to the 'Trigger' column, so another dataframe with the extra 
# details ('Category' and 'Description' columns) can be joined/merged later on
records = records.set_index('Trigger')

                          From     To
Trigger                             
incident: INC111111111    Tom  Peter
request: REQ123464       John   Alex
incident: INC22222222   David  Maria

# Create a dataframe with just the extra details ('Category' and 'Description' columns)
details = df.loc[df['String Value'].notna()]

                   Trigger From   To String Value                    Remarks
3  incident: INC111111111  NaN  NaN     Category                       FYI 
4      request: REQ123464  NaN  NaN     Category               Wrong Route 
5   incident: INC22222222  NaN  NaN     Category                       FYI 
6  incident: INC111111111  NaN  NaN  Description            Wrongly routed 
7      request: REQ123464  NaN  NaN  Description  Dispatch to another team 
8   incident: INC22222222  NaN  NaN  Description     Try more troubleshoot 

# Transform the dataframe using DataFrame.pivot so that:
# - the indexes are the values from the 'Trigger' column
# - the columns are the values from the 'String Value' column
# - the values are taken from the 'Remarks' column
details = details.pivot(index='Trigger', columns='String Value', values='Remarks')

 String Value                Category                Description
Trigger                                                        
incident: INC111111111          FYI             Wrongly routed 
incident: INC22222222           FYI      Try more troubleshoot 
request: REQ123464      Wrong Route   Dispatch to another team 

# Combine the 2 dataframes with DataFrame.join
combined = records.join(details)

                          From     To      Category                Description
Trigger                                                                      
incident: INC111111111    Tom  Peter          FYI             Wrongly routed 
request: REQ123464       John   Alex  Wrong Route   Dispatch to another team 
incident: INC22222222   David  Maria          FYI      Try more troubleshoot