Python：如何按州代码分组统计各行业公司总数

Hey there! It sounds like you want to go beyond just counting total companies per state—you want to break it down by industry (like how many software firms are in California, or finance companies in New York). Let me walk you through two straightforward methods to make this happen with your pandas DataFrame.

Method 1: Use groupby() + sum() (Best for 0/1 Flagged Industries) #

Since your DataFrame uses 1s and 0s to mark which industry each company belongs to, grouping by state_code and summing the industry columns will directly give you the count of companies per industry per state.

Here's the code to implement this:

# Assume your DataFrame is named 'df'
industry_counts_by_state = df.groupby('state_code')[['software', 'finance', 'commerce']].sum()

# Print the result to see the breakdown
print(industry_counts_by_state)

What this does: #

• groupby('state_code') clusters all your data rows by their state code.
• The list ['software', 'finance', 'commerce'] tells pandas which columns to focus on (add any other industry columns you have here!).
• sum() adds up all the 1s in each industry column for every state—this gives you the total number of companies in that industry for the state.

For your sample data, this would output:

software  finance  commerce
state_code                              
CA                  1        0         0
unknown             0        0         1

Method 2: Use pd.crosstab() (Great for Visual Cross-Tabulation) #

If you prefer a more readable cross-table format, pd.crosstab() is perfect. This works best if each company belongs to exactly one industry (which your sample data suggests).

import pandas as pd

# Optional: Filter to only US states if needed
usa_df = df[df['country_code'] == 'USA']

# Create a cross-tab of states vs. industries
state_industry_crosstab = pd.crosstab(
    usa_df['state_code'],
    # Find the industry each company belongs to (the column with a 1)
    usa_df[['software', 'finance', 'commerce']].idxmax(axis=1),
    # Add a "Total" row/column if you want
    margins=True,
    margins_name="Total"
)

print(state_industry_crosstab)

What this does: #

• idxmax(axis=1) looks at each row and returns the name of the industry column that has a 1 (since that's the industry the company is in).
• pd.crosstab() then counts how many times each industry appears per state, creating a clean table view.

For your sample US data, this would output:

col_0     software  Total
state_code               
CA               1      1
Total            1      1

Quick Notes: #

• If some companies belong to multiple industries, stick with Method 1—sum() will correctly count them in each relevant industry column.
• Just add any additional industry column names (like etc... in your sample) to the list in either method to include them in the count.

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