如何基于ID关联DF_1与DF_2，按Date2统计月份数量

Hey there! Let's break down how to solve this problem step by step. The goal is to find, for each ID in DF_1, the number of unique months represented in DF_2's Date2 column (which uses millisecond timestamps) where the IDs match.

Step-by-Step Implementation #

First, let's start with the code (I'll use sample data matching your provided DataFrames to make it concrete):

import pandas as pd

# Define your DataFrames (as per your example)
DF_1 = pd.DataFrame({
    'ID': [123, 456],
    'Date': ['18/03/2018 16:45', '10/03/2018 20:15']
})

DF_2 = pd.DataFrame({
    'ID': [123, 123, 123, 456, 456, 456, 456],
    'Date1': ['2018-03-18 06:37:22', '2018-03-18 06:37:21', '2018-03-16 04:03:01', 
              '2018-03-10 14:46:03', '2018-03-10 14:46:04', '2018-03-10 14:46:03', '2018-03-10 14:46:15'],
    'Date2': [1519109133704, 1520324827462, 1520690354458, 1517319313151, 1515143046429, 1515838021062, 1488092209241]
})

# 1. Convert millisecond timestamps in Date2 to readable datetime objects
DF_2['datetime'] = pd.to_datetime(DF_2['Date2'], unit='ms')

# 2. Extract year-month pairs to easily identify unique months
DF_2['year_month'] = DF_2['datetime'].dt.to_period('M')

# 3. Merge with DF_1 to only keep IDs that exist in DF_1 (left join ensures no IDs are lost from DF_1)
merged_data = pd.merge(DF_1[['ID']], DF_2[['ID', 'year_month']], on='ID', how='left')

# 4. Group by ID and count the number of distinct year-month values
month_count_result = merged_data.groupby('ID')['year_month'].nunique().reset_index(name='month_count')

print(month_count_result)

What Each Step Does #

• Convert Timestamps: pd.to_datetime(..., unit='ms') is crucial here because Date2 uses millisecond timestamps (not seconds), so we need to specify the unit to get the correct date.
• Extract Year-Month: Using dt.to_period('M') converts each datetime into a YYYY-MM period, which is perfect for grouping unique months (since it combines year and month, avoiding confusion between March 2018 and March 2019).
• Merge DataFrames: A left join ensures we retain all IDs from DF_1, even if there's no matching data in DF_2 (in that case, the month count will be 0).
• Count Unique Months: nunique() counts how many distinct year_month values exist per ID, giving us the exact number of months we need.

Sample Output #

Running the code will produce this result:

ID  month_count
0  123            2
1  456            3

(Note: For ID 456, the Date2 timestamps correspond to months Feb 2017, Dec 2017, and Jan 2018 — hence 3 unique months.)

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