assume have following data
import numpy np import pandas pd import datetime table = [[datetime.datetime(2015, 1, 1), 1], [datetime.datetime(2015, 1, 27), 1], [datetime.datetime(2015, 1, 31), 1], [datetime.datetime(2015, 2, 1), 1], [datetime.datetime(2015, 2, 3), 1], [datetime.datetime(2015, 2, 15), 1], [datetime.datetime(2015, 2, 28), 1], [datetime.datetime(2015, 3, 1), 1], [datetime.datetime(2015, 3, 17), 1], [datetime.datetime(2015, 3, 28), 1], [datetime.datetime(2015, 4, 12), 1], [datetime.datetime(2015, 4, 28), 1]] df = pd.dataframe(table, columns=['date', 'id']) table2 = [datetime.datetime(2015, 3, 31), datetime.datetime(2015, 6, 30), datetime.datetime(2015, 9, 30)] is there way merge table2 onto table such elements of table2 joined on closest smallest or equal element of table, , fill table backwards? needs done groupwise on column id. example, resulting table be
date id new 0 2015-01-01 1 2015-03-31 1 2015-01-27 1 2015-03-31 2 2015-01-31 1 2015-03-31 3 2015-02-01 1 2015-03-31 4 2015-02-03 1 2015-03-31 5 2015-02-15 1 2015-03-31 6 2015-02-28 1 2015-03-31 7 2015-03-01 1 2015-03-31 8 2015-03-17 1 2015-03-31 9 2015-03-28 1 2015-03-31 10 2015-04-12 1 2015-06-30 11 2015-04-28 1 2015-06-30 thanks, tingis
you use searchsorted:
table2 = pd.to_datetime(table2) idx = table2.searchsorted(df['date'].values) this finds indices dates in df['date'] should inserted table2 while maintaining sorted order. note assumes table2 starts out in sorted order.
table2 = pd.to_datetime(table2) idx = table2.searchsorted(df['date'].values) df['new'] = table2[idx] print(df) yields
date id new 0 2015-01-01 1 2015-03-31 1 2015-01-27 1 2015-03-31 2 2015-01-31 1 2015-03-31 3 2015-02-01 1 2015-03-31 4 2015-02-03 1 2015-03-31 5 2015-02-15 1 2015-03-31 6 2015-02-28 1 2015-03-31 7 2015-03-01 1 2015-03-31 8 2015-03-17 1 2015-03-31 9 2015-03-28 1 2015-03-31 10 2015-04-12 1 2015-06-30 11 2015-04-28 1 2015-06-30 
Comments
Post a Comment