logo
down
shadow

Most Efficient Way to Find Closest Date Between 2 Dataframes


Most Efficient Way to Find Closest Date Between 2 Dataframes

Content Index :

Most Efficient Way to Find Closest Date Between 2 Dataframes
Tag : python , By : Caleb Ames
Date : January 11 2021, 03:34 PM

I hope this helps . You could merge the two dataframes using pd.merge_asof. You need to sort your left & right dataframe and remove duplicates.
cd['Occurred Date Time'] = pd.to_datetime(cd['Occurred Date Time'])
wd['Date and Time'] = pd.to_datetime(wd['Date and Time'])

wd.drop_duplicates(subset = ['Date and Time'], inplace = True)
wd.sort_values(by = ['Date and Time'],inplace=True)
cd.drop_duplicates(subset = ['Occurred Date Time'], inplace = True)
cd.sort_values(by = ['Occurred Date Time'],inplace=True)

df = pd.merge_asof(cd,wd, left_on = 'Occurred Date Time', right_on = 'Date and Time')

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Most efficient way to find the second closest date


Tag : mysql , By : arbeitandy
Date : March 29 2020, 07:55 AM
wish help you to fix your issue You would change the LIMIT 1 to LIMIT 1 OFFSET 1.
Note: OFFSET 0 means gets the first row. So, OFFSET 1 means get the second row.

Merge 2 dataframes on closest past date


Tag : python-2.7 , By : vdavidovski
Date : March 29 2020, 07:55 AM
I wish this helpful for you I have 2 dataframes with column 0 for 'Date'. , Here's a try with merge_asof:
df = pd.merge_asof( main.set_index('Date').sort_index(),
                    sec.set_index('Date',drop=False).sort_index(),
                    left_index=True, 
                    right_index=True,
                    direction='backward')  # backward is the default, so you
                                           # can leave this out if you prefer

df.rename(columns={'Date':'Date_sec'})\
  .sort_index(ascending=False).reset_index()
         Date   Date_sec
0  2016-07-26 2016-07-01
1  2016-04-26 2016-04-01
2  2016-01-26 2016-01-01
3  2015-10-27 2015-10-01
4  2015-07-21 2015-07-01
5  2015-04-27 2015-04-01
6  2015-01-27 2015-01-01
7  2014-10-20 2014-10-01
8  2014-07-22 2014-07-01
9  2014-04-23 2014-04-01
10 2014-01-27 2014-01-01
11 2013-10-28 2013-10-01
12 2013-07-23 2013-07-01

find closest rows between dataframes with positive timedelta


Tag : python , By : user185751
Date : March 29 2020, 07:55 AM
Any of those help write a function to get the closest index & timestamp in df_short given a timestamp
def get_closest(n):
    mask = df_short.mytime_short >= n
    ids = np.where(mask)[0]
    if ids.size > 0:
        return ids[0], df_short.mytime_short[ids[0]]
    else:
        return np.nan, np.nan
df = df_long.mytime_long.apply(get_closest)
df
# output:
0    (0, 2013-01-10 00:00:02)
1    (2, 2013-01-10 00:00:06)
2                  (nan, nan)
df = pd.merge_asof(df_long, 
              df_short.reset_index(), 
              left_on='mytime_long', 
              right_on='mytime_short', 
              direction='forward')[['index', 'mytime_short']]
df
# output:
   index        mytime_short
0    0.0 2013-01-10 00:00:02
1    2.0 2013-01-10 00:00:06
2    NaN                 NaT

Merging dataframes by closest earlier date by group


Tag : python , By : jonagh
Date : March 29 2020, 07:55 AM
should help you out I have two dataframes generated by the following code: , You can using merge_asof with by and on parameter
df_usages.date=pd.to_datetime(df_usages.date)
df_logins.date=pd.to_datetime(df_logins.date)
df_usages,df_logins=df_usages.sort_values('date').rename(columns={'date':'use_date'}),df_logins.sort_values('date').rename(columns={'date':'log_date'})
pd.merge_asof(df_usages,df_logins,left_on='use_date',right_on='log_date',by='id',direction = 'nearest')
Out[168]: 
              use_date id            log_date
0  2013-09-20 13:02:00  1 2013-09-20 13:01:00
1  2013-09-20 13:02:00  2 2013-09-20 13:01:00
2  2013-09-20 13:05:00  1 2013-09-20 13:05:00
3  2013-09-20 13:05:00  2 2013-09-20 13:05:00
4  2013-09-20 13:06:00  1 2013-09-20 13:05:00
5  2013-09-20 13:06:00  2 2013-09-20 13:05:00
6  2013-09-20 13:35:00  1 2013-09-20 13:37:00
7  2013-09-20 13:35:00  2 2013-09-20 13:37:00
8  2013-09-20 13:38:00  1 2013-09-20 13:37:00
9  2013-09-20 13:38:00  2 2013-09-20 13:37:00
10 2013-09-20 13:45:00  1 2013-09-20 13:45:00
11 2013-09-20 13:45:00  2 2013-09-20 13:45:00
12 2013-09-20 13:57:00  1 2013-09-20 13:59:00
13 2013-09-20 13:57:00  2 2013-09-20 13:59:00

Find closest point in Pandas DataFrames


Tag : python , By : christiandsg
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • How To Fix: RuntimeError: size mismatch in pyTorch
  • Concatenating two Pandas DataFrames while maintaining index order
  • Why does this not run into an infinite loop?
  • Python Multithreading no current event loop
  • Element Tree - Seaching for specific element value without looping
  • Ignore Nulls in pandas map dictionary
  • How do I get scrap data from web pages using beautifulsoup in python
  • Variable used, golobal or local?
  • I have a regex statement to pull all numbers out of a text file, but it only finds 77 out of the 81 numbers in the file
  • How do I create a dataframe of jobs and companies that includes hyperlinks?
  • Detect if user has clicked the 'maximized' button
  • Does flask_login automatically set the "next" argument?
  • Indents in python 3
  • How to create a pool of threads
  • Pandas giving IndexError on one dataframe but not on another similar dataframe
  • Django Rest Framework - Testing client.login doesn't login user, ret anonymous user
  • Running dag without dag file in airflow
  • Filling across a specified dimension of a numpy array
  • Python populating dataframe in pandas from text files
  • How to interpolate a single ("non-piecewise") cubic spline from a set of data points?
  • Divide 2 integers (leetcode 29) - recursion issue
  • Can someone explain why do I get this output in Python?
  • How do I scrape pdf and html from search results without obvious url
  • Is there a way to automatically make a "collage" of plots with matplotlib?
  • How to combine multiple rows in pandas with shared column values
  • How do I get LOAD_CLASSDEREF instruction after dis.dis?
  • Django - How to add items to Bootstrap dropdown?
  • Linear Regression - Does the below implementation of ridge regression finding coefficient term using gradient method is
  • How to drop all rows in pandas dataframe with negative values?
  • Execution error when Passing arguments to a python script using os.system. The script takes sys.argv arguments
  • Looping through a function
  • Create a plot for each unique ID
  • a thread python with 'while' got another thread never start
  • Solution from SciPy solve_ivp contains oscillations for a system of first-order ODEs
  • trigger python events driven by selenium controlled browser
  • Passing line-edits to a contextmanager to set validators
  • Python: globals().items() iterations try to change a dict
  • Is it possible to specify starting values for each parameter (instead of bounds) for scipy's differential evolution?
  • why datetime.now() and constructed datetime using all fields(like year,month...) of now has big timedelta?
  • MySQL multiple table UPDATE query using sqlalchemy core?
  • find if a semantic version is superset of of another version python
  • Type checking against dynamically created objects
  • Struggling with simple reverse function
  • Is there a function for finding the midpoint of n points on sklearn.neighbors.NearestNeighbors?
  • How to set max number of tweets to fetch
  • PYTHON 3.7.4 NOT USING SQLITE 3.29.0
  • How to replace Nan value with zeros in a numpy array?
  • How to speed up calculating variance among sparse matrix
  • cupy code is not fast enough compared with numpy
  • How to count frequency of select values in Python pandas dataframe
  • Scrape Span Text from Google
  • Python watchdog, watch a directory and rename file on event.modification
  • Filtering rows in DataFrame with dependent conditions
  • How to check if a character is a not a part of number or URL in string?
  • Compare corresponding elements of a list
  • Python misinterprets 3 character string as UTF-8 continuation byte
  • Merge two columns in Pandas
  • Side Effect error in Python in an online compiler
  • How to convert a navigation list with depth levels to a parent-child flat list?
  • Retrieving values from a paired key dictionary in Python
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com