如何在Pandas中创建忽略重复值的数据透视表？

When working with your consolidado DataFrame, there are two straightforward approaches to ensure your pivot table ignores duplicate values—depending on whether you want to remove duplicates entirely first, or handle them during the pivot aggregation.

1. Remove Duplicates Before Creating the Pivot Table #

If you have duplicate rows (or duplicate entries in the columns you care about for the pivot), start by deduplicating your DataFrame. This ensures only unique records are included in the pivot.

Example: #

Since cnpj is a unique identifier for companies, you can use it to remove duplicate company entries:

# Deduplicate using cnpj as the unique key
consolidado_dedup = consolidado.drop_duplicates(subset=['cnpj'], keep='first')

# Create your pivot table from the deduplicated DataFrame
pivot_table = pd.pivot_table(
    consolidado_dedup,
    index='CNAE_principal',  # Replace with your desired index column
    values='nome_empresa',    # Replace with your value column
    aggfunc='count'           # Use other functions like 'sum' if needed
)

• Use the subset parameter to specify which columns define a duplicate (e.g., ['cnpj', 'CNAE_principal'] if duplicates are based on both fields).
• keep='first' retains the first occurrence of each duplicate; use keep='last' for the last entry, or False to drop all duplicate rows entirely.

2. Use Aggregation Functions to Handle Unique Values Directly #

If you don’t want to modify the original DataFrame, you can use an aggregation function that inherently ignores duplicates in the pivot table itself.

Example 1: Count Unique Values #

To count unique cnpj entries per CNAE_principal:

pivot_unique_count = pd.pivot_table(
    consolidado,
    index='CNAE_principal',
    values='cnpj',
    aggfunc='nunique'  # This counts only distinct values
)

Example 2: Retrieve Unique Values as a List #

If you want to see all unique company names grouped by their main CNAE:

pivot_unique_list = pd.pivot_table(
    consolidado,
    index='CNAE_principal',
    values='nome_empresa',
    aggfunc=lambda x: list(x.unique())  # Returns a list of unique company names
)

Key Notes #

• Choose the first approach if duplicates are irrelevant to your analysis and you want a clean dataset for all subsequent operations.
• Choose the second approach if you need to preserve the original DataFrame but want the pivot to focus on unique values for specific columns.

内容的提问来源于stack exchange，提问作者Reinaldo Chaves