replace dataframe values based on another dataframe

Question

I have a pandas dataframe which is structured as follows:

             timestamp       y
0 2020-01-01 00:00:00   336.0
1 2020-01-01 00:15:00   544.0
2 2020-01-01 00:30:00   736.0
3 2020-01-01 00:45:00   924.0
4 2020-01-01 01:00:00  1260.0
...

The timestamp column is a datetime data type

and I have another dataframe with the following structure:

                     y
timestamp
00:00:00    625.076923
00:15:00    628.461538
00:30:00    557.692308
00:45:00    501.692308
01:00:00    494.615385
...

I this case, the time is the pandas datetime index.

Now what I want to do is replace the values in the first dataframe where the time field is matching i.e. the time of the day is matching with the second dataset.

@NoobVB I have updated the question a bit regarding the types. In first case, it is a column with datetime[ns] type and in the second case it is an index. — Luca
– Luca, Commented Aug 29, 2022 at 21:42

SomeDude · Accepted Answer · 2022-08-29 21:56:13Z

2

IIUC your first dataframe df1's timestamp is datetime type and your second dataframe (df2) has an index of type datetime as well but only time and not date.

then you can do:

df1['y'] = df1['timestamp'].dt.time.map(df2['y'])

answered Aug 29, 2022 at 21:56

SomeDude

14.3k5 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luca Over a year ago

This really is some next level voodoo!

scotscotmcc · Accepted Answer · 2022-08-29 21:39:50Z

I wouldn't be surprised if there is a better way, but you can accomplish this by working to get the tables so that they can merge on the time. Assuming your dataframes will be df and df2.

df['time'] = df['timestamp'].dt.time
df2 = df2.reset_index()
df2['timestamp'] = pd.to_datetime(df2['timestamp'].dt.time
df_combined = pd.merge(df,df2,left_on='time',right_on='timestamp')
df_combined
    timestamp_x           y_x   time      timestamp_y   y_y
0   2020-01-01 00:00:00 336.0   00:00:00    00:00:00    625.076923
1   2020-01-01 00:15:00 544.0   00:15:00    00:15:00    628.461538
2   2020-01-01 00:30:00 736.0   00:30:00    00:30:00    557.692308
3   2020-01-01 00:45:00 924.0   00:45:00    00:45:00    501.692308
4   2020-01-01 01:00:00 1260.0  01:00:00    01:00:00    494.615385
# This clearly has more than you need, so just keep what you want and rename things back.

df_combined = df_combined[['timestamp_x','y_y']]
df_combined = df_combined.rename(columns={'timestamp_x':'timestamp','y_y':'y'})

scotscotmcc · Accepted Answer · 2022-08-29 21:48:27Z

1

New answer I like way better: actually using .map()

Still need to get df2 to have the time column to match on.

df2 = df2.reset_index()
df2['timestamp'] = pd.to_datetime(df2['timestamp'].dt.time
df['y'] = df['timestamp'].dt.time.map(dict(zip(df2['timestamp',df2['y'])))

answered Aug 29, 2022 at 21:48

scotscotmcc

3,1631 gold badge10 silver badges35 bronze badges

Collectives™ on Stack Overflow

replace dataframe values based on another dataframe

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related