基于Python Pandas匹配两只股票时间戳的技术问询

Hey there! Let's tackle this timestamp matching problem for your financial data—it’s a super common task when aligning stock datasets, so I’ve got you covered with solutions for both of your data formats.

If you have one big DataFrame containing timestamp1 (for Stock A) and timestamp2 (for Stock B) alongside their respective data columns, the key is to split out each stock's data, align them by timestamp, then merge back together. This handles cases where matching timestamps are in different rows (df['timestamp1'][i] == df['timestamp2'][j] with i≠j).

Step-by-Step Code: #

import pandas as pd

# Sample large DataFrame (replace with your actual data)
big_df = pd.DataFrame({
    'timestamp1': ['2018-01-02-07:00:00', '2018-01-02-08:00:00', '2018-01-02-09:00:00'],
    'stock1_price': [100.50, 101.25, 102.75],
    'stock1_volume': [15000, 22000, 18000],
    'timestamp2': ['2018-01-02-08:00:00', '2018-01-02-09:00:00', '2018-01-02-10:00:00'],
    'stock2_price': [205.30, 206.10, 207.40],
    'stock2_volume': [8000, 9500, 11000]
})

# 1. Convert timestamps to datetime objects (critical for accurate matching)
big_df['timestamp1'] = pd.to_datetime(big_df['timestamp1'], format='%Y-%m-%d-%H:%M:%S')
big_df['timestamp2'] = pd.to_datetime(big_df['timestamp2'], format='%Y-%m-%d-%H:%M:%S')

# 2. Split into separate DataFrames for each stock
stock1 = big_df[['timestamp1', 'stock1_price', 'stock1_volume']].rename(columns={'timestamp1': 'timestamp'})
stock2 = big_df[['timestamp2', 'stock2_price', 'stock2_volume']].rename(columns={'timestamp2': 'timestamp'})

# 3. Merge on timestamp (inner join keeps only matching timestamps)
aligned_df = pd.merge(stock1, stock2, on='timestamp', how='inner')

print(aligned_df)

Output: #

timestamp  stock1_price  stock1_volume  stock2_price  stock2_volume
0 2018-01-02 08:00:00        101.25          22000        205.30           8000
1 2018-01-02 09:00:00        102.75          18000        206.10           9500

If your two stock datasets are in separate DataFrames (each with their own timestamp column and integer index), the approach is similar—we just align each DataFrame to its timestamp first, then merge.

Step-by-Step Code: #

import pandas as pd

# Sample separate DataFrames (replace with your actual data)
df_stock1 = pd.DataFrame({
    'timestamp1': ['2018-01-02-07:00:00', '2018-01-02-08:00:00', '2018-01-02-09:00:00'],
    'price': [100.50, 101.25, 102.75],
    'volume': [15000, 22000, 18000]
})

df_stock2 = pd.DataFrame({
    'timestamp2': ['2018-01-02-08:00:00', '2018-01-02-09:00:00', '2018-01-02-10:00:00'],
    'price': [205.30, 206.10, 207.40],
    'volume': [8000, 9500, 11000]
})

# 1. Convert timestamps to datetime and standardize column names
df_stock1['timestamp'] = pd.to_datetime(df_stock1['timestamp1'], format='%Y-%m-%d-%H:%M:%S')
df_stock2['timestamp'] = pd.to_datetime(df_stock2['timestamp2'], format='%Y-%m-%d-%H:%M:%S')

# 2. Rename data columns to avoid conflicts during merge
df_stock1 = df_stock1.rename(columns={'price': 'stock1_price', 'volume': 'stock1_volume'}).drop('timestamp1', axis=1)
df_stock2 = df_stock2.rename(columns={'price': 'stock2_price', 'volume': 'stock2_volume'}).drop('timestamp2', axis=1)

# 3. Merge on matching timestamps
aligned_df = pd.merge(df_stock1, df_stock2, on='timestamp', how='inner')

print(aligned_df)

• Duplicate Timestamps: If either dataset has duplicate timestamps (e.g., multiple entries for the same time), use groupby to deduplicate first (e.g., stock1 = stock1.groupby('timestamp').last().reset_index() to keep the latest entry).
• Timezones: If your timestamps include timezones, use tz_localize or tz_convert to ensure both datasets are in the same timezone before matching.
• String vs. Datetime: Always convert timestamps to pandas datetime objects—matching strings can fail due to minor format differences (e.g., spaces vs. hyphens), but datetime objects handle these inconsistencies.

Let me know if you hit any snags with your specific dataset—I’m happy to help adjust the code!

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