Replace pandas dataframe columns with another dataframe based on specific column

Question

I have two dataframes with many columns df1, df2, and i want to replace all df1 values (except the time columns) with the data from df2 columns where the time values is the same:

df1:

index time   x y   ......many other columns ( the same as df2)
0       1    1 1
1       1.1  2 2
2       1.1  3 3
3       1.1  4 4
4       1.4  5 5
5       1.5  6 6
6       1.5  7 7


df2:

index time  x   y   ....many other columns (the same as df1)
0       1   10  10
1       1.1 11  11
2       1.2 12  12
3       1.3 13  13
4       1.4 14  14
5       1.5 15  15
6       1.6 16  16



the result for df1 should be:

index time  x   y   ....many other columns 
0       1    10 10
1       1.1  11 11
2       1.1  11 11
3       1.1  11 11
4       1.4  14 14
5       1.5  15 15
6       1.5  15 15

Answer 1

I think I was able to get my thinking in order and hopefully have reached a solution that will work for you.

Try this, you can get your answer with using combine_first, and doing some tweaking:

1. combine_first fills null values from another dataframe, so first you can replace all values (except in 'time' column) with np.nan. Note that I use 'time' column as the index.

2. As combine_first will return the union of the two dataframes, you can use isin to get only the time values from df1 in your final output.

import numpy as np
import pandas as pd

df1[df1.columns.difference(['time'])] = np.nan
res = df1.set_index('time').combine_first(df2.set_index('time')).reset_index()
li = [i for i in df1['time'].unique()]

final= res[res['time'].isin(li)]

Which will get you:

   time     x     y
0   1.0  10.0  10.0
1   1.1  11.0  11.0
2   1.1  11.0  11.0
3   1.1  11.0  11.0
6   1.4  14.0  14.0
7   1.5  15.0  15.0
8   1.5  15.0  15.0

Try it on your actual dataset, and let me know if it works.

Answer 2

You need to merge:

df1 = df1.merge(df2, left_index = True, right_index = True)

then you need to remove the columns you do not need

Answer 3

Edit: Misread the question the first time. This should help:

df1[['time']].merge(df2, on='time')