如何将SQL SELECT语句转为Python Pandas操作并对df按条件分组

Hey there! Let's break down how to bridge SQL and Pandas, starting with general rules and then tackling your specific query.

General SQL-to-Pandas Mappings #

If you're switching between SQL and Pandas, here are the core equivalents you'll use all the time:

• SELECT specific columns: Grab them directly with df[['Producer', 'Premium']]
• WHERE conditions: Use boolean indexing (wrap each condition in parentheses and combine with & for AND, | for OR)
• GROUP BY: Chain .groupby('Column') with an aggregation like .sum() or .mean()
• Aggregation aliases: Use .agg() with a dictionary to name your aggregated columns clearly
• ORDER BY: Sort your results with .sort_values(by='Column', ascending=False) for descending order
• BETWEEN dates: Use .between(start_date, end_date) on datetime columns
• IN clause: Check membership with .isin(list_of_values)

Translating Your T-SQL Query to Pandas #

First, let's restate your T-SQL query for clarity:

select Producer, sum(Premium) as Premium from df where EffectiveDate BETWEEN '2018-01-01' and '2018-03-01' AND PolicyType = 'New Business' AND SICCode IN (4210,4211,4213) GROUP BY Producer Order by Premium DESC

(I fixed a tiny typo in Premim to Premium—assuming that's what you meant!)

Here's how to replicate this in Pandas, step by step:

1. Filter the Data First #

We need to apply all those WHERE conditions. Make sure your EffectiveDate column is a datetime type (if not, convert it with df['EffectiveDate'] = pd.to_datetime(df['EffectiveDate']) first):

filtered_data = df[
    (df['EffectiveDate'].between('2018-01-01', '2018-03-01')) &
    (df['PolicyType'] == 'New Business') &
    (df['SICCode'].isin([4210, 4211, 4213]))
]

2. Group and Aggregate #

Next, group by Producer and calculate the total premium. Using .reset_index() turns the grouped result back into a regular DataFrame:

grouped_data = filtered_data.groupby('Producer')['Premium'].sum().reset_index()

If you want to explicitly set the column alias (like your SQL as Premium), you can do it in one go with .agg():

grouped_data = filtered_data.groupby('Producer').agg(Premium=('Premium', 'sum')).reset_index()

3. Sort the Results #

Finally, sort by the total premium in descending order to match your ORDER BY clause:

final_result = grouped_data.sort_values(by='Premium', ascending=False)

All in One Line #

If you prefer concise code, you can chain all these steps together:

final_result = df[
    (df['EffectiveDate'].between('2018-01-01', '2018-03-01')) &
    (df['PolicyType'] == 'New Business') &
    (df['SICCode'].isin([4210, 4211, 4213]))
].groupby('Producer').agg(Premium=('Premium', 'sum')).reset_index().sort_values(by='Premium', ascending=False)

Quick Note on Your Attempts #

You're already on the right track with your existing code! You know how to group and sum, and you started filtering dates—this solution just adds the extra filters for PolicyType and SICCode, plus the sorting step to get the exact result your SQL query produces.

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