我正在开发一个Python解决方案来应对数据转换挑战，但遇到了一个我似乎无法解决的小障碍.I want to merge the two data frames (i.e., df1 & df2) to create a new data frame that has inserted df2['value2'] into df1['value1'] while considering date and 'Type' alignment.有人解决过类似的问题吗？Pandas 是解决这个问题的最佳图书馆吗？
请参阅下面我在jupyter笔记本中用于解决方案测试的代码片段.
import pandas as pd
df1 = pd.DataFrame({ 'Type': ['Labor', 'Material', 'Labor', 'Material' , 'Labor', 'Material', 'Labor', 'Material'],'date1': ['2021-01-01', '2021-01-01', '2021-02-01', '2021-02-01', '2021-03-01', '2021-03-01', '2021-04-01', '2021-04-01'], 'value1': [0,0,0,0,0,0,0,0]})
df2 = pd.DataFrame({ 'Type': ['Labor', 'Material', 'Labor', 'Material'],'date2': ['2021-01-
11', '2021-02-22', '2021-02-05', '2021-03-15'], 'value2': [10,20,25,45]})
display(df1)
display(df2)
df1['date1'] = pd.to_datetime(df1['date1'])
df2['date2'] = pd.to_datetime(df2['date2'])
df1 = df1.sort_values(['date1', 'Type'])
df2 = df2.sort_values(['date2', 'Type'])
merge_df = pd.merge_asof(df2, df1, left_on= 'date2', right_on= 'date1' , by = 'Type',
direction = 'nearest')
display(merge_df)
我试图实现的输出如下所示.
| Type | Date | Value |
|---|---|---|
| Labor | 2021-01-01 | 10 |
| Material | 2021-01-01 | 0 |
| Labor | 2021-02-01 | 25 |
| Material | 2021-02-01 | 20 |
| Labor | 2021-03-01 | 0 |
| Material | 2021-03-01 | 45 |
| Labor | 2021-04-01 | 0 |
| Material | 2021-04-01 | 0 |