如何基于共同日期将两个DataFrame的数据对齐合并到单个DataFrame中

现有两个分别存储Tatasteel、Tatamotors交易数据的CSV文件，已将二者数据合并为一个DataFrame，但当前合并逻辑是先追加全部Tatasteel数据，再追加全部Tatamotors数据。实际需求为按日期对齐排序：同一时间点（如9:15）先展示Tatasteel对应数据，再展示Tatamotors的同时段数据，以此类推。

现有代码 #

import pandas as pd
import datetime as dt
from datetime import timedelta
import os
import glob


exclude_days = [dt.datetime(2019, 1, 5), dt.datetime(2019, 1, 6), dt.datetime(2019, 1, 12), dt.datetime(2019, 1, 13), dt.datetime(2019, 1, 19), dt.datetime(2019, 1, 20),
dt.datetime(2019, 1, 26), dt.datetime(2019, 1, 27) ]
backtest_start = dt.datetime(2019, 1, 1)
backtest_end = dt.datetime(2019, 1, 2)

path = os.getcwd()
path = os.path.join(path,"2019/*")
dat=[]
data3 =[]
stock_list = glob.glob(path)
for stock in stock_list:
   rdata= pd.read_csv(stock, parse_dates=['Date'])
   dat.append(rdata)
data = pd.concat(dat, ignore_index=True)

   
curr_day = backtest_start

while curr_day < backtest_end:
   if curr_day not in exclude_days:
      day_start = dt.datetime(curr_day.year,curr_day.month,curr_day.day, 9, 15)
      day_end = dt.datetime(curr_day.year,curr_day.month,curr_day.day, 15, 15)
      data2 =  data[data['Date'].between(day_start,day_end)]
      data2 = data2.reset_index(drop=True)
      data2 = pd.DataFrame(data2)
      print(data2)         
        
  curr_day += timedelta(days=1)

当前输出效果 #

仅需对现有代码做两处修改即可实现需求：

1. 读取CSV时新增字段标记股票名称，用于后续排序
2. 合并数据后按「时间升序、股票名称升序」规则排序

修改后的完整代码如下：

import pandas as pd
import datetime as dt
from datetime import timedelta
import os
import glob


exclude_days = [dt.datetime(2019, 1, 5), dt.datetime(2019, 1, 6), dt.datetime(2019, 1, 12), dt.datetime(2019, 1, 13), dt.datetime(2019, 1, 19), dt.datetime(2019, 1, 20),
dt.datetime(2019, 1, 26), dt.datetime(2019, 1, 27) ]
backtest_start = dt.datetime(2019, 1, 1)
backtest_end = dt.datetime(2019, 1, 2)

path = os.getcwd()
path = os.path.join(path,"2019/*")
dat=[]
stock_list = glob.glob(path)
for stock in stock_list:
    rdata= pd.read_csv(stock, parse_dates=['Date'])
    # 新增：标记股票名称，可根据实际文件名调整匹配规则
    if 'Tatasteel' in os.path.basename(stock):
        rdata['stock_name'] = 'Tatasteel'
    else:
        rdata['stock_name'] = 'Tatamotors'
    dat.append(rdata)
data = pd.concat(dat, ignore_index=True)
# 新增：按时间升序、股票名称升序排序，同时间点Tatasteel自动排在前面
data = data.sort_values(by=['Date', 'stock_name'], ignore_index=True)

   
curr_day = backtest_start

while curr_day < backtest_end:
   if curr_day not in exclude_days:
      day_start = dt.datetime(curr_day.year,curr_day.month,curr_day.day, 9, 15)
      day_end = dt.datetime(curr_day.year,curr_day.month,curr_day.day, 15, 15)
      data2 =  data[data['Date'].between(day_start,day_end)]
      data2 = data2.reset_index(drop=True)
      print(data2)         
        
  curr_day += timedelta(days=1)

逻辑说明 #

Tatasteel的拼音序天然排在Tatamotors前面，所以按Date、stock_name两个字段升序排序后，所有同一个时间点的记录都会先展示Tatasteel的数据，再展示Tatamotors的数据，完全符合需求。

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