Join two dataframe and expand the dataframe with assign the ID

Question

I want to insert a dataframe into another dataframe with Time key with ID appear to follow the Time. How to do it?

my data looks like this:

df1
ID  Time
ID1 10:40
ID1 11:00
ID2 11:10
ID2 11:30

df2
Time    X   Y
10:40   45  50
10:50   55  55
11:00   65  60
11:10   75  65
11:20   85  70
11:30   95  75

my expected result looks like this:

ID  Time    X   Y
ID1 10:40   45  50
ID1 10:50   55  55
ID1 11:00   65  60
ID2 11:10   75  65
ID2 11:20   85  70
ID2 11:30   95  75

thank you for your help

jezrael · Accepted Answer · 2019-04-04T10:14:39.953

1

Use merge_asof with datetimes columns, last use Series.dt.strftime for convert it to HH:MM strings:

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

df = pd.merge_asof(df2, df1, on='Time').assign(Time = lambda x: x['Time'].dt.strftime('%H:%M'))
print (df)
    Time   X   Y   ID
0  10:40  45  50  ID1
1  10:50  55  55  ID1
2  11:00  65  60  ID1
3  11:10  75  65  ID2
4  11:20  85  70  ID2
5  11:30  95  75  ID2

edited Apr 04 '19 at 10:14

answered Apr 04 '19 at 09:28

jezrael

822,522
95
1,334
1,252

is it possible without transform to datetime? – Arief Hidayat Apr 04 '19 at 09:35
Another solution should be `df2.merge(df1, how='outer').ffill()`, but a bit dangerous - is necessary match always first rows, else bad assigned IDs. It depends of data. – jezrael Apr 04 '19 at 09:41
thank you for your advice. I got `KeyError: 'Time'`. how about that? – Arief Hidayat Apr 04 '19 at 12:08
@Arief What is `print (df1.columns.tolist())` and `print (df2.columns.tolist())` ? – jezrael Apr 04 '19 at 12:12
`['ID', 'Time']` and `['Time ', 'X', 'Y']`. Maybe because the type is `non-null object` after I did `df.info()` – Arief Hidayat Apr 04 '19 at 12:14
1

@Arief - If chec second, then is traling space `'Time '`, so use `df2.columns = df2.columns.str.strip()` – jezrael Apr 04 '19 at 12:15

Join two dataframe and expand the dataframe with assign the ID

1 Answers1

Linked