英文:
Full left outer join with less/more than or equal conditions
问题
我有2个数据框，我想执行一个简单的全外连接左连接，条件是小于或等于。
d1 = {'object': ['A1', 'B2', 'C1'],'start_date': ['2019-01-19', '2009-07-03', '2021-11-23'],'end_date': ['2019-03-15', '2009-10-28', '2024-12-22']}df1 = pandas.DataFrame(data=d1)另一个数据框只是一个日历：```pythondf2 = pd.DataFrame({"date": pd.date_range('1990-01-01', '2050-12-31')})df["first_day_of_month"] = df["date"].to_numpy().astype('datetime64[M]')
我想要的结果类似于以下SQL：
SELECT * FROM df1 LEFT JOIN df2ON df1.start_date <= df2.date AND df1.end_date >= df2.dateWHERE df2.first_day_of_month = 1
因此，df1中的每一行都会与所有在df1.start_date和df2.end_date之间的月份的df2.first_day_of_month重复。
如何实现这个结果？
编辑：结果数据框将如下所示：
| Object | start_date | end_date | df2.date |
|---|---|---|---|
| A1 | 2019-01-19 | 2019-03-15 | 2019-02-01 |
| A1 | 2019-01-19 | 2019-03-15 | 2019-03-01 |
| B2 | 2009-07-03 | 2009-10-28 | 2009-08-01 |
| B2 | 2009-07-03 | 2009-10-28 | 2009-09-01 |
| B2 | 2009-07-03 | 2009-10-28 | 2009-10-01 |
<details><summary>英文:</summary>I have 2 dataframes that I want to perform a simple full outer left join with condition less than or equal.d1 = {'object': ['A1', 'B2', 'C1'],'start_date': ['2019-01-19', '2009-07-03', '2021-11-23'],'end_date': ['2019-03-15', '2009-10-28', '2024-12-22']}df1 = pandas.DataFrame(data=d1)The other dataframe is simply a calendar:df2 = pd.DataFrame({"date": pd.date_range('1990-01-01', '2050-12-31')})df["first_day_of_month"] = df["date"].to_numpy().astype('datetime64[M]')I would like to get results that are similar with this type of sql:SELECT * FROM df1 LEFT JOIN df2ON df1.start_date <= df2.date AND df1.end_date >= df2.dateWHERE df2.first_day_of_month = 1So every row in df1 repeated with all the ```df2.first_day_of_month```of the months that are between ```df1.start_date``` and ```df2.end_date```.How can I achieve this result?Edit: the resulted dataframe would be like:| Object | start_date | end_date | df2.date || -------- | -------------- |--------------|--------------|| A1 | 2019-01-19 | 2019-03-15 | 2019-02-01 || A1 | 2019-01-19 | 2019-03-15 | 2019-03-01 || B2 | 2009-07-03 | 2009-10-28 | 2009-08-01 || B2 | 2009-07-03 | 2009-10-28 | 2009-09-01 || B2 | 2009-07-03 | 2009-10-28 | 2009-10-01 |</details># 答案1**得分**: 1你可以使用 [`janitor`](https://pyjanitor-devs.github.io/pyjanitor) 的 [`conditional_join`](https://pyjanitor-devs.github.io/pyjanitor/api/functions/#janitor.functions.conditional_join) 方法：```python# pip install janitorimport janitordf1['start_date'] = pd.to_datetime(df1['start_date'])df1['end_date'] = pd.to_datetime(df1['end_date'])df2['date'] = pd.to_datetime(df2['date'])out = df1.conditional_join(df2[df2['first_day_of_month'].dt.day.eq(1)],('start_date', 'date', '<='),('end_date', 'date', '>='),how='left')
输出：
object start_date end_date date first_day_of_month0 A1 2019-01-19 2022-12-15 2019-01-19 2019-01-011 A1 2019-01-19 2022-12-15 2019-01-20 2019-01-012 A1 2019-01-19 2022-12-15 2019-01-21 2019-01-013 A1 2019-01-19 2022-12-15 2019-01-22 2019-01-014 A1 2019-01-19 2022-12-15 2019-01-23 2019-01-01... ... ... ... ... ...5435 C1 2021-11-23 2024-12-22 2024-12-18 2024-12-015436 C1 2021-11-23 2024-12-22 2024-12-19 2024-12-015437 C1 2021-11-23 2024-12-22 2024-12-20 2024-12-015438 C1 2021-11-23 2024-12-22 2024-12-21 2024-12-015439 C1 2021-11-23 2024-12-22 2024-12-22 2024-12-01[5440 rows x 5 columns]
英文:
You can use janitor's conditional_join:
# pip install janitorimport janitordf1['start_date'] = pd.to_datetime(df1['start_date'])df1['end_date'] = pd.to_datetime(df1['end_date'])df2['date'] = pd.to_datetime(df2['date'])out = df1.conditional_join(df2[df2['first_day_of_month'].dt.day.eq(1)],('start_date', 'date', '<='),('end_date', 'date', '>='),how='left')
Output:
object start_date end_date date first_day_of_month0 A1 2019-01-19 2022-12-15 2019-01-19 2019-01-011 A1 2019-01-19 2022-12-15 2019-01-20 2019-01-012 A1 2019-01-19 2022-12-15 2019-01-21 2019-01-013 A1 2019-01-19 2022-12-15 2019-01-22 2019-01-014 A1 2019-01-19 2022-12-15 2019-01-23 2019-01-01... ... ... ... ... ...5435 C1 2021-11-23 2024-12-22 2024-12-18 2024-12-015436 C1 2021-11-23 2024-12-22 2024-12-19 2024-12-015437 C1 2021-11-23 2024-12-22 2024-12-20 2024-12-015438 C1 2021-11-23 2024-12-22 2024-12-21 2024-12-015439 C1 2021-11-23 2024-12-22 2024-12-22 2024-12-01[5440 rows x 5 columns]
答案2
得分: 0
以下是翻译好的代码部分：
import pandas as pdimport numpy as npd1 = {'object': ['A1', 'B2', 'C1'],'start_date': ['2019-01-19', '2009-07-03', '2021-11-23'],'end_date': ['2022-12-15', '2017-05-28', '2024-12-22']}df1 = pd.DataFrame(data=d1)df2 = pd.DataFrame({"date": pd.date_range('1990-01-01', '2050-12-31')})df2_filtered = df2[df2["date"].dt.day == 1]# 创建一个用于连接的公共键df1['key'] = 1df2_filtered['key'] = 1# 执行交叉连接merged_df = pd.merge(df1, df2_filtered, on='key', how='outer')# 删除键列merged_df = merged_df.drop(columns='key')# 应用条件filtered_df = merged_df[(merged_df['start_date'] <= merged_df['date']) & (merged_df['end_date'] >= merged_df['date'])]filtered_df# 重置索引filtered_df = filtered_df.reset_index(drop=True)# 打印结果数据框print(filtered_df)
希望这对你有所帮助。
英文:
Hello there you can use the following logic 
import pandas as pdimport numpy as npd1 = {'object': ['A1', 'B2', 'C1'],'start_date': ['2019-01-19', '2009-07-03', '2021-11-23'],'end_date': ['2022-12-15', '2017-05-28', '2024-12-22']}df1 = pd.DataFrame(data=d1)df2 = pd.DataFrame({"date": pd.date_range('1990-01-01', '2050-12-31')})df2_filtered = df2[df2["date"].dt.day == 1]# Create a common key for joiningdf1['key'] = 1df2_filtered['key'] = 1# Perform the cross joinmerged_df = pd.merge(df1, df2_filtered, on='key', how='outer')# Drop the key columnmerged_df = merged_df.drop(columns='key')# Apply the conditionsfiltered_df = merged_df[(merged_df['start_date'] <= merged_df['date']) & (merged_df['end_date'] >= merged_df['date'])]filtered_df# Reset the indexfiltered_df = filtered_df.reset_index(drop=True)# Print the resulting dataframeprint(filtered_df)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论