如何在Python中生成带计数与占比的类Proc Freq频数表？

Absolutely! You can totally replicate the Proc Freq functionality you use in SAS right in Python using pandas—and even automate the process for multiple collection variables with loops. Let’s walk through how to do this step by step.

Step 1: Single Collection Variable Frequency Table #

First, let’s generate the exact table you showed from your sample dataset. We’ll group by your categorical columns (Code, National, Regional, Local) and calculate both counts and group-level percentages for the Collection Variable.

Here’s the code:

import pandas as pd

# Your sample dataset
data = {'Code': [719, 719,719,719,719,719], 
        'National': [1001, 1001,1001,1001,1001,1001], 
        'Regional': [3005,3005,3005,3005,3005,3005], 
        'Local': [2002,2002,2002,2002,2002,2002], 
        'Collection Variable': [1,1,2,3,3,3]}
df = pd.DataFrame(data)

# Calculate counts for each group + collection variable combination
freq_table = df.groupby(['Code','National', 'Regional', 'Local', 'Collection Variable']).size().reset_index(name='Count')

# Calculate group-level percentages (percent of total within each Code/National/Regional/Local group)
freq_table['Percent'] = freq_table.groupby(['Code','National', 'Regional', 'Local'])['Count'].transform(lambda x: x / x.sum())

# Print the result (matches your SAS output)
print(freq_table)

This will output exactly the table you shared:

Code  National  Regional  Local  Collection Variable  Count   Percent
0   719      1001      3005   2002                    1      2  0.333333
1   719      1001      3005   2002                    2      1  0.166667
2   719      1001      3005   2002                    3      3  0.500000

Quick Explanation: #

• groupby(...).size() counts how many times each combination of your grouping columns + collection variable appears.
• reset_index(name='Count') turns the grouped index into regular columns and names the count column.
• The transform method calculates the percentage relative to the total count of each top-level group (Code/National/Regional/Local), so percentages add up to 1 within each group.

Step 2: Loop Through Multiple Collection Variables #

If you have multiple collection variables you want to process (just like in SAS), you can wrap the logic in a loop. Let’s expand the dataset with a second collection variable to demonstrate:

# Add a second collection variable to the dataset
df['Collection Variable 2'] = [2,2,1,1,1,2]

# List of collection variables to process
collection_vars = ['Collection Variable', 'Collection Variable 2']

# Loop through each variable and generate its frequency table
for var in collection_vars:
    print(f"\n=== Frequency Table for: {var} ===")
    # Calculate counts
    freq_table = df.groupby(['Code','National', 'Regional', 'Local', var]).size().reset_index(name='Count')
    # Calculate percentages
    freq_table['Percent'] = freq_table.groupby(['Code','National', 'Regional', 'Local'])['Count'].transform(lambda x: x / x.sum())
    # Optional: Format percentages as strings (e.g., 33.33%)
    # freq_table['Percent'] = freq_table['Percent'].apply(lambda x: f"{x:.2%}")
    print(freq_table)

This will print a separate frequency table for each collection variable in your list—just like running Proc Freq on multiple variables in SAS.

Bonus: Formatting Percentages #

If you prefer percentages formatted as strings (like "33.33%" instead of 0.3333), uncomment the optional line in the loop. It uses an apply function to format the decimal as a percentage with two decimal places.

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