Python：按各唯一user_id统计location字段值的出现频次（非整列）

Got it, let's break down how to solve this problem! You need to calculate how often each location shows up for every unique user_id, then add that frequency as a new column to your dataset. Below are practical solutions using two common tools:

Pandas (Python) #

This is the go-to approach if you're working with tabular data in Python. We'll use groupby() paired with transform() to keep the original row structure while computing per-user location frequencies.

Example Code #

import pandas as pd

# Sample dataset to test with
data = {
    'user_id': [1, 1, 1, 2, 2, 3, 3, 3],
    'location': ['NY', 'NY', 'LA', 'Chicago', 'Chicago', 'NY', 'LA', 'LA']
}
df = pd.DataFrame(data)

# Add a new column with location frequency per user
df['location_freq_per_user'] = df.groupby(['user_id', 'location'])['location'].transform('count')

# View the result
print(df)

How It Works #

• groupby(['user_id', 'location']) groups the data by each unique user-location pair.
• transform('count') calculates the number of rows in each group, then "broadcasts" this value back to every row in the original dataset. This means every row gets the count of how many times its location appears for its user_id.

SQL #

If you're working directly with a database (like PostgreSQL, MySQL, etc.), window functions are the way to go—they let you compute aggregations without collapsing rows.

Example Query #

SELECT
    user_id,
    location,
    COUNT(*) OVER (PARTITION BY user_id, location) AS location_freq_per_user
FROM your_table_name;

How It Works #

• PARTITION BY user_id, location splits the data into groups where each group is a unique user-location combination.
• COUNT(*) OVER (...) counts the number of rows in each partition, and adds this count as a new column to every row in the original table.

Bonus: Calculate Frequency as Percentage #

If you want the proportion (instead of raw count) of each location per user, here's how to adjust:

• Pandas:

  # Get total rows per user
  df['total_user_rows'] = df.groupby('user_id')['location'].transform('count')
  # Calculate percentage
  df['location_pct_per_user'] = (df['location_freq_per_user'] / df['total_user_rows']).round(2)
  

• SQL:

  SELECT
      user_id,
      location,
      COUNT(*) OVER (PARTITION BY user_id, location) AS location_freq_per_user,
      ROUND(
          COUNT(*) OVER (PARTITION BY user_id, location)::NUMERIC /
          COUNT(*) OVER (PARTITION BY user_id),
          2
      ) AS location_pct_per_user
  FROM your_table_name;
  

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