Advertisement
xtiansimon

pandas shared date merge

Sep 4th, 2017
283
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 1.92 KB | None | 0 0
  1.  
  2. # Create Pandas DF with one week of random dates...
  3. dates1 = pd.date_range(start='2017-09-01', end='2017-09-07', freq='5s')
  4. # Create DataFrame...
  5. Z = [('Date',dates1)]
  6. ZZ = pd.DataFrame.from_items(Z)
  7. # Add random ID column for later reference...
  8. ZZ['ID'] = np.random.choice(range(1,500), ZZ.shape[0])  
  9.  
  10. # Take first sample...
  11. df1 = ZZ.sample(200)
  12. # Create a list of Unique dates...
  13. uniquedates1 = list(df1['Date'].dt.normalize().unique())
  14.  
  15. # Take second sample...
  16. df2 = ZZ.sample(200)
  17. uniquedates2 = list(df2['Date'].dt.normalize().unique())
  18.  
  19. # Find the intersection (shared dates)
  20. sd = set(uniquedates1) & set(uniquedates2)
  21. shareddates = list(sd)
  22.  
  23. # Merge on Date
  24. merged = {}
  25. for date in shareddates:
  26.     print(date)
  27.     # Select whole day
  28.     # Create date selection mask using one date and timedelta addition
  29.     mask = (df1['Date'] > date) & (df1['Date'] <= date + np.timedelta64(24, 'h'))
  30.     df11 = df1.loc[mask]
  31.     mask = (df2['Date'] > date) & (df2['Date'] <= date + np.timedelta64(24, 'h'))
  32.  
  33.     df22 = df2.loc[mask]
  34.     merged[date] = df11.merge(df22, how='outer', left_on='Date', right_on='Date')
  35.  
  36. merged = {}
  37. for date in shareddates:
  38.     print(date)
  39.     # Select whole day
  40.     # Create date selection mask using one date and timedelta addition
  41.     mask = (df1['Date'] > date) & (df1['Date'] <= date + np.timedelta64(24, 'h'))
  42.     df11 = df1.loc[mask]
  43.  
  44.     # Resample to minutes
  45.     df11.index = df11['Date']
  46.     del df11['Date']
  47.     df111 = df11.resample('T')
  48.    
  49.     mask = (df2['Date'] > date) & (df2['Date'] <= date + np.timedelta64(24, 'h'))
  50.     df22 = df2.loc[mask]
  51.  
  52.     # Resample to minutes
  53.     df22.index = df22['Date']
  54.     del df22['Date']
  55.     df222 = df22.resample('T')
  56.    
  57.     merged[date] = df1111.merge(df222, how='outer', left_index=True, right_index=True)
  58.  
  59. # ValueError: can not merge DataFrame with instance of type <class 'pandas.core.resample.DatetimeIndexResampler'>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement