python - 如何根据条件合并两个数据集

Question

我正在尝试根据 3 个条件在 python 中合并两个数据集。它们必须具有相同的经度、纬度和特定年份的月份。一个数据集的大小约为 16k，另一个数据集的大小约为 1.7k。输入和预期输出的一个简单示例如下:

>df1
 long  lat  date        proximity
 5      8   23/06/2009    Near
 6      10  05/10/2012    Far
 8      6   19/02/2010    Near
 3      4   30/04/2014    Near
 5      8   01/06/2009    Far

 >df2
 long  lat  date          mine
 5      8   10/06/2009     1
 8      6   24/02/2010     0
 7      2   19/04/2014     1 
 3      4   30/04/2013     1

如果任何条件为假，合并时“我的”中的值为 0。我将如何合并得到:

 long  lat  date        proximity  mine
 5      8   23/06/2009    Near      1
 6      10  05/10/2012    Far       0
 8      6   19/02/2010    Near      0
 3      4   30/04/2014    Near      0
 5      8   01/06/2009    Far       1

如果这样更容易，则输出中不需要日期列。

Answer 1

给你:

df1['year-month'] = pd.to_datetime(df1['date'], format='%d/%m/%Y').dt.strftime('%Y/%m')
df2['year-month'] = pd.to_datetime(df2['date'], format='%d/%m/%Y').dt.strftime('%Y/%m')

joined = df1.merge(df2,
          how='left',
          on =['long', 'lat', 'year-month'],
          suffixes=['', '_r']).drop(columns = ['date_r', 'year-month'])
joined['mine'] = joined['mine'].fillna(0).astype(int)
print(joined)

输出

   long  lat        date proximity  mine
0     5    8  23/06/2009      Near     1
1     6   10  05/10/2012       Far     0
2     8    6  19/02/2010      Near     0
3     3    4  30/04/2014      Near     0
4     5    8  01/06/2009       Far     1