如何按指定列汇总单元格并实现多维度频率计算（含复合等式）？

Great question! Let's break this down step by step using two common tools: Python's pandas library (for in-memory data) and SQL (for database-stored data). Both approaches will fulfill your requirements: aggregating by specified columns and generating a result table where Daily_freq = men + women = Freq_UK + Freq_SP.

Assumptions About Source Data #

First, let's define a sample source table to make the examples concrete. I'll assume your data has these columns:

• day: Date of the posts
• hashtag: The topic tag associated with the post
• location: Either 'UK' or 'SP' (as referenced in your equation)
• gender: Either 'male' or 'female' (mapped to 'men'/'women' in the result)
• post_count: Number of posts matching the row's dimensions

Sample source data:

Approach 1: Using Python Pandas #

Step 1: Aggregate by Specified Columns #

First, we group the data by day, hashtag, location, and gender to sum up the post counts:

import pandas as pd

# Load your source data into a DataFrame (example uses sample data)
data = [
    ("2024-01-01", "#python", "UK", "male", 50),
    ("2024-01-01", "#python", "UK", "female", 30),
    ("2024-01-01", "#python", "SP", "male", 20),
    ("2024-01-01", "#python", "SP", "female", 15),
    ("2024-01-02", "#data", "UK", "male", 40),
    ("2024-01-02", "#data", "SP", "female", 25),
]
df = pd.DataFrame(data, columns=["day", "hashtag", "location", "gender", "post_count"])

# Aggregate by the 4 dimensions
aggregated_df = df.groupby(["day", "hashtag", "location", "gender"])["post_count"].sum().reset_index()

Step 2: Calculate Frequencies and Build the Result Table #

Next, we compute the gender and location totals per day and hashtag, then combine them with the overall daily frequency:

# Calculate gender totals (men/women) per day + hashtag
gender_totals = aggregated_df.groupby(["day", "hashtag", "gender"])["post_count"].sum().unstack(fill_value=0)
gender_totals = gender_totals.rename(columns={"male": "men", "female": "women"})

# Calculate location totals (Freq_UK/Freq_SP) per day + hashtag
location_totals = aggregated_df.groupby(["day", "hashtag", "location"])["post_count"].sum().unstack(fill_value=0)
location_totals = location_totals.rename(columns={"UK": "Freq_UK", "SP": "Freq_SP"})

# Combine totals and compute Daily_freq
result_df = pd.concat([gender_totals, location_totals], axis=1).reset_index()
result_df["Daily_freq"] = result_df["men"] + result_df["women"]

# Verify the equation (optional, for correctness check)
result_df["equation_check"] = (result_df["Daily_freq"] == result_df["Freq_UK"] + result_df["Freq_SP"])

Final Result #

The result_df will look like this:

Approach 2: Using SQL #

If your data is stored in a database, you can achieve the same result with SQL using common table expressions (CTEs):

Step 1: Aggregate by Specified Columns (CTE) #

WITH aggregated_data AS (
    SELECT 
        day,
        hashtag,
        location,
        gender,
        SUM(post_count) AS total_posts
    FROM source_table
    GROUP BY day, hashtag, location, gender
)

Step 2: Calculate Frequencies and Build Result Table #

SELECT 
    day,
    hashtag,
    SUM(CASE WHEN gender = 'male' THEN total_posts ELSE 0 END) AS men,
    SUM(CASE WHEN gender = 'female' THEN total_posts ELSE 0 END) AS women,
    SUM(CASE WHEN location = 'UK' THEN total_posts ELSE 0 END) AS Freq_UK,
    SUM(CASE WHEN location = 'SP' THEN total_posts ELSE 0 END) AS Freq_SP,
    SUM(total_posts) AS Daily_freq
FROM aggregated_data
GROUP BY day, hashtag;

This query returns the same result as the pandas approach, and the equation Daily_freq = men + women = Freq_UK + Freq_SP will hold true because SUM(total_posts) is the grand total for each day and hashtag.

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