68 lines
2.5 KiB
Python
68 lines
2.5 KiB
Python
import pandas as pd
|
|
|
|
# 读取数据
|
|
df_open = pd.read_excel("opendemo1.xlsx")
|
|
df_close = pd.read_excel("closedemo1.xlsx")
|
|
|
|
# 清洗并准备数据
|
|
df_open["Trade_Shares"] = df_open["Trade_Shares"].astype(int)
|
|
df_close["Trade_Shares"] = df_close["Trade_Shares"].astype(int).abs()
|
|
|
|
# 分组
|
|
grouped_open = df_open.groupby("check_str")
|
|
grouped_close = df_close.groupby("check_str")
|
|
|
|
matched_rows = []
|
|
|
|
def match_shares(open_rows, close_rows):
|
|
open_iter = iter(open_rows.to_dict("records"))
|
|
close_iter = iter(close_rows.to_dict("records"))
|
|
current_open = next(open_iter, None)
|
|
current_close = next(close_iter, None)
|
|
|
|
while current_open and current_close:
|
|
open_shares = current_open["Trade_Shares"]
|
|
close_shares = current_close["Trade_Shares"]
|
|
matched_shares = min(open_shares, close_shares)
|
|
|
|
open_row_copy = current_open.copy()
|
|
open_row_copy["Trade_Shares"] = matched_shares
|
|
|
|
close_row_copy = current_close.copy()
|
|
close_row_copy["Trade_Shares"] = matched_shares
|
|
close_row_copy = {f"{k}_close": v for k, v in close_row_copy.items()}
|
|
|
|
combined_row = {**open_row_copy, **close_row_copy}
|
|
matched_rows.append(combined_row)
|
|
|
|
current_open["Trade_Shares"] -= matched_shares
|
|
current_close["Trade_Shares"] -= matched_shares
|
|
|
|
if current_open["Trade_Shares"] == 0:
|
|
current_open = next(open_iter, None)
|
|
if current_close["Trade_Shares"] == 0:
|
|
current_close = next(close_iter, None)
|
|
|
|
# 如果还有未匹配完的开仓
|
|
while current_open:
|
|
if current_open["Trade_Shares"] > 0:
|
|
open_row_copy = current_open.copy()
|
|
# 留下剩余数量
|
|
unmatched_row = open_row_copy.copy()
|
|
unmatched_row["Trade_Shares"] = open_row_copy["Trade_Shares"]
|
|
# 添加对应的空_close列
|
|
for col in close_rows.columns:
|
|
unmatched_row[f"{col}_close"] = None
|
|
matched_rows.append(unmatched_row)
|
|
current_open = next(open_iter, None)
|
|
|
|
# 处理所有check_str
|
|
common_keys = set(grouped_open.groups.keys()) & set(grouped_close.groups.keys())
|
|
for key in common_keys:
|
|
open_rows = grouped_open.get_group(key)
|
|
close_rows = grouped_close.get_group(key)
|
|
match_shares(open_rows, close_rows)
|
|
|
|
# 保存结果
|
|
df_matched = pd.DataFrame(matched_rows)
|
|
df_matched.to_excel("D:/wangzhiming/match1.xlsx", index=False) |