pandas merging based on a timestamp which do not match exactly

by

I would try using this method in pandas:

pandas.merge_asof()

The parameters of interest for you would be direction,tolerance,left_on, and right_on

Building off @Igor answer:

import pandas as pd
from pandas import read_csv
from io import StringIO

# datetime column (combination of date + start_time)
dtc = [['date', 'start_time']]

# index column (above combination)
ixc="date_start_time"

df1 = read_csv(StringIO(u'''
date,start_time,employee_id,session_id
01/01/2016,02:03:00,7261824,871631182
01/01/2016,06:03:00,7261824,871631183
01/01/2016,11:01:00,7261824,871631184
01/01/2016,14:01:00,7261824,871631185
'''), parse_dates=dtc)

df2 = read_csv(StringIO(u'''
date,start_time,employee_id,session_id
01/01/2016,02:03:00,7261824,871631182
01/01/2016,06:05:00,7261824,871631183
01/01/2016,11:04:00,7261824,871631184
01/01/2016,14:10:00,7261824,871631185
'''), parse_dates=dtc)



df1['date_start_time'] = pd.to_datetime(df1['date_start_time'])
df2['date_start_time'] = pd.to_datetime(df2['date_start_time'])

# converting this to the index so we can preserve the date_start_time columns so you can validate the merging logic
df1.index = df1['date_start_time']
df2.index = df2['date_start_time']
# the magic happens below, check the direction and tolerance arguments
tol = pd.Timedelta('5 minute')
pd.merge_asof(left=df1,right=df2,right_index=True,left_index=True,direction='nearest',tolerance=tol)

output

date_start_time date_start_time_x   employee_id_x   session_id_x    date_start_time_y   employee_id_y   session_id_y

2016-01-01 02:03:00 2016-01-01 02:03:00 7261824 871631182   2016-01-01 02:03:00 7261824.0   871631182.0
2016-01-01 06:03:00 2016-01-01 06:03:00 7261824 871631183   2016-01-01 06:05:00 7261824.0   871631183.0
2016-01-01 11:01:00 2016-01-01 11:01:00 7261824 871631184   2016-01-01 11:04:00 7261824.0   871631184.0
2016-01-01 14:01:00 2016-01-01 14:01:00 7261824 871631185   NaT NaN NaN

More Related Contents:

  1. converting a text file to csv file with out index
  2. pandas: How do I split text in a column into multiple rows?
  3. Fast Haversine Approximation (Python/Pandas)
  4. Pandas Groupby Range of Values
  5. Drop rows containing empty cells from a pandas DataFrame
  6. How to form tuple column from two columns in Pandas
  7. Convert pandas Series to DataFrame
  8. Pandas selecting by label sometimes return Series, sometimes returns DataFrame
  9. Pandas: resample timeseries with groupby
  10. Pandas groupby.size vs series.value_counts vs collections.Counter with multiple series
  11. How to keep leading zeros in a column when reading CSV with Pandas?
  12. Comparing two pandas dataframes for differences
  13. Read XLSB File in Pandas Python
  14. Remove name, dtype from pandas output of dataframe or series
  15. How to calculate correlation between all columns and remove highly correlated ones using pandas?
  16. Lambda including if…elif…else
  17. Create bool mask from filter results in Pandas [duplicate]
  18. Getting a list of indices where pandas boolean series is True
  19. Pandas DataFrame sort by categorical column but by specific class ordering
  20. How can I remove extra whitespace from strings when parsing a csv file in Pandas?
  21. convert entire pandas dataframe to integers in pandas (0.17.0)
  22. TfidfVectorizer in scikit-learn : ValueError: np.nan is an invalid document
  23. Pandas Correlation Groupby
  24. pandas, melt, unmelt preserve index
  25. Conditional row read of csv in pandas
  26. Using regex matched groups in pandas dataframe replace function
  27. converting list of header and row lists into pandas DataFrame
  28. How to return a csv file/Pandas DataFrame in JSON format using FastAPI?
  29. How to get number of groups in a groupby object in pandas?
  30. Python generating a list of dates between two dates

Leave a Comment