我有一个使用for循环实现的cross-join-like operation.我需要让它变得更快，最好是优雅的.它每天创建一个带有日期范围条件的块条目.
这对于small datasets可以很好地工作，但对于larger datasets则完全停滞到非常慢的运行时间.我知道它可以被矢量化.我的实现非常糟糕.
I have looked at the other posts关于如何向量化DataFrames中的循环.我按照这篇文章How to iterate over rows in a DataFrame in Pandas的建议阅读了10 minutes to pandas，并try 使用lambda函数.搞砸了Cython.我就是搞不懂.
我试着实现了[pandas.MultiIndex.to_frame]，我有一种强烈的感觉，这是一个很好的方法，或者它的表亲之一.我还try 了一大堆其他的东西，但一无所获.
我想学会优雅地编写代码.欢迎所有的建议，对解决方案的变化和意见.
from datetime import datetime
import pandas as pd
beginning = pd.to_datetime('14/09/2021', dayfirst=True)
today = pd.to_datetime(datetime.today())
date_range = pd.date_range(start=beginning, end=today) # .tolist()
frame = pd.DataFrame(columns=['Record_Date', 'Identifier', 'start_date', 'end_date', 'color'])
block = pd.DataFrame(
{'Identifier': ['4913151F', 'F4E9124A', '31715888', 'D0C57FCA', '57B4D7EB', 'E46F1E5D', '99E0A2F8', 'D77E342E',
'C596D233', 'D0EED63F', 'D0C57FCA'],
'start_date': ['03/11/2020', '05/07/2022', '22/12/2016', '17/03/2024', '14/10/2022', '08/08/2022', '04/11/2020',
'13/03/2023', '05/11/2021', '12/27/2022', '13/06/2022'],
'end_date': ['11/07/2023', '11/04/2023', '14/12/2018', '20/01/2025', '15/06/2023', '09/01/2023', '16/07/2022',
'19/05/2024', '24/09/2022', '17/11/2023', '13/06/2023'],
'color': ['red', 'green', 'magenta', 'yellow', 'light_blue', 'dark_blue', 'black', 'white', 'pink', 'orange',
'yellow']})
block.start_date = pd.to_datetime(block.start_date, dayfirst=True, format='mixed')
block.end_date = pd.to_datetime(block.end_date, dayfirst=True, format='mixed')
block_uniques = block.drop_duplicates(['Identifier', 'start_date'])
for x in date_range:
temp_df = block_uniques[(block_uniques.start_date <= x) & (block_uniques.end_date >= x)]
temp_df.insert(0, 'Record_Date', x)
frame = pd.concat([frame, temp_df])
frame = frame.sort_values(['Record_Date', 'Identifier'])
frame = frame.reset_index().drop('index', axis=1)
print(frame)
输出和解决方案:
Record_Date Identifier start_date end_date color
0 2021-09-14 4913151F 2020-11-03 2023-07-11 red
1 2021-09-14 99E0A2F8 2020-11-04 2022-07-16 black
2 2021-09-15 4913151F 2020-11-03 2023-07-11 red
3 2021-09-15 99E0A2F8 2020-11-04 2022-07-16 black
4 2021-09-16 4913151F 2020-11-03 2023-07-11 red
... ... ... ... ... ...
2641 2023-07-05 D0EED63F 2022-12-27 2023-11-17 orange
2642 2023-07-05 D77E342E 2023-03-13 2024-05-19 white
2643 2023-07-06 4913151F 2020-11-03 2023-07-11 red
2644 2023-07-06 D0EED63F 2022-12-27 2023-11-17 orange
2645 2023-07-06 D77E342E 2023-03-13 2024-05-19 white
[2646 rows x 5 columns]