39306

Pandas: add column with the most recent values

I have two pandas dataframes, both index with datetime entries. The df1 has non-unique time indices, whereas df2 has unique ones. I would like to add a column df2.a to df1 in the following way: for every row in df1 with timestamp ts, df1.a should contain the most recent value of df2.a whose timestamp is less then ts.

For example, let's say that df2 is sampled every minute, and there are rows with timestamps 08:00:15, 08:00:47, 08:02:35 in df1. In this case I would like the value from df2.a[08:00:00] to be used for the first two rows, and df2.a[08:02:00] for the third. How can I do this?

Answer1:

apply to rows of df1, reindex on df2 with ffill.

df1['df2.a'] = df1.apply(lambda x: pd.Series(df2.a.reindex([x.name]).ffill().values), axis=1)

Answer2:

You are describing an asof-join, which was just released in pandas 0.19.

pd.merge(df1, df2, left_on='ts', right_on='a')

Recommend

  • Istio distributed tracing with Jaeger not working
  • GridBagLayout: How to set fixed column width?
  • JPanel Won't Focus After Switching in CardLayout
  • iOS app camera orientation in captureOutput
  • SettingWithCopyWarning while using .loc
  • Aggregating based on “near” row values
  • Extracting rows from df based on multiple conditions in R
  • Multiply two data frames with similar index in python pandas
  • How to set repeating alarm using setExact and how to cancel the same?
  • How can I write a where clause in SQL to filter a DATETIME column by the time of day?
  • Timer once a minute on the minute
  • Firestore - Checking The Connection Status Of The Module To The Server
  • Angular2 & SystemJS : Cannot find module while building a moduleLoader
  • What does the TypeScript “lib” option really do?
  • Using Nested Table variables / Collections in SQL inside PL/SQL blocks
  • php script is parsing content from RTE (tt_news) but internal links are not appearing as speaking ur
  • CERN ROOT exporting data to plain text
  • Eloquent update method change created_at timestamp
  • Groovy: Unexpected token “:”
  • Replace value with Factor in r data.table
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Illegal mix of collations for operation for date/time comparison
  • vba code to select only visible cells in specific column except heading
  • Do I've to free mysql result after storing it?
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Unanticipated behavior
  • Transpose CSV data with awk (pivot transformation)
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Proper way to use connect-multiparty with express.js?
  • Angular 2 constructor injection vs direct access
  • Java static initializers and reflection
  • Android Google Maps API OnLocationChanged only called once
  • Can't mass-assign protected attributes when import data from csv file
  • Sorting a 2D array using the second column C++
  • UserPrincipal.Current returns apppool on IIS
  • Unable to use reactive element in my shiny app
  • Conditional In-Line CSS for IE and Others?
  • How can I use threading to 'tick' a timer to be accessed by other threads?