How to fuse a small pandas.dataframe into a larger one based on values of a column?

user_7294160 0
Viewed 688

I have two pandas.dataframe df1 and df2:

>>>import pandas as pd
>>>import numpy as np
>>>from random import random
>>>df1=pd.DataFrame({'x1':range(10), 'y1':np.repeat(0,10).tolist()})
>>>df2=pd.DataFrame({'x2':range(0,10,2), 'y2':[random() for _ in range(5)]})
>>>df1
   x1  y1
0   0   0
1   1   0
2   2   0
3   3   0
4   4   0
5   5   0
6   6   0
7   7   0
8   8   0
9   9   0
>>>df2
   x2        y2
0   0  0.075922
1   2  0.606703
2   4  0.272918
3   6  0.842641
4   8  0.576636

Now I want to fuse df2 into df1. This is to say, I want to change the values of y1 in df1 into the values of y2 in df2 when the value of x1 in df1 is equal to the value of x2 in df2. The final result I need is like the following:

>>>df1
   x1        y1
0   0  0.075922
1   1  0
2   2  0.606703
3   3  0
4   4  0.272918
5   5  0
6   6  0.842641
7   7  0
8   8  0.576636
9   9  0

Although I can use the follow codes to get the above result:

>>> for i in range(df1.shape[0]):
...     for j in range(df2.shape[0]):
...             if df1.iloc[i,0] == df2.iloc[j,0]:
...                     df1.iloc[i,1]=df2.iloc[j,1]
...

I think there must be better ways to achieve this. Do you know what they are? Thank you in advance.

pandas python
5 Answers

You can use df.update to update your df1 in place, eg:

df1.update({'y1': df2.set_index('x2')['y2']})

Gives you:

   x1        y1
0   0  0.075922
1   1  0.000000
2   2  0.606703
3   3  0.000000
4   4  0.272918
5   5  0.000000
6   6  0.842641
7   7  0.000000
8   8  0.576636
9   9  0.000000
user_12527590

Use map and then replace missing values by original values by fillna:

df1['y1'] = df1['x1'].map(df2.set_index('x2')['y2']).fillna(df1['y1'])
print (df)
   x1        y1
0   0  0.696469
1   1  0.000000
2   2  0.286139
3   3  0.000000
4   4  0.226851
5   5  0.000000
6   6  0.551315
7   7  0.000000
8   8  0.719469
9   9  0.000000
user_29010020

You can also use update after setting indices of both dataframes:

import pandas as pd
import numpy as np
from random import random
df1=pd.DataFrame({'x1':range(10), 'y1':np.repeat(0,10).tolist()})

#set index of the first dataframe to be 'x1'
df1.set_index('x1', inplace=True)

df2=pd.DataFrame({'x2':range(0,10,2), 'y1':[random() for _ in range(5)]})

#set index of the second dataframe to be 'x2'
df2.set_index('x2', inplace=True)

#update values in df1 with values in df 
df1.update(df2)

#reset index if necessary (though index will look exactly like x1 column)
df1 = df1.reset_index()
user_94139900

Update() seems to be the best option here !

import pandas as pd
import numpy as np
from random import random

# your dataframes
df1 = pd.DataFrame({'x1': range(10), 'y1': np.repeat(0, 10).tolist()})
df2 = pd.DataFrame({'x2': range(0, 10, 2), 'y2': [random() for _ in range(5)]})

# printing df1 and df2 values before update
print(df1)
print(df2)

df1.update({'y1': df2.set_index('x2')['y2']})

# printing df1 after update was performed
print(df1)
user_49156080

Another method, adding the two dataframes together:

# first give df2 the same column names as df2
df2.columns = ['x1','y1']

#now set 'x1' as the index for both dfs (since this is what you want to 'join' on)
df1 = df1.set_index('x1')
df2 = df2.set_index('x1')

print(df1)

   y1
x1    
0    0
1    0
2    0
3    0
4    0
5    0
6    0
7    0
8    0
9    0

print(df2)

          y1
x1          
0   0.525232
2   0.907628
4   0.612100
6   0.497420
8   0.656509

#now you can simply add the two df's to eachother
df_new = df1 + df2
print(df_new)

        y1
x1          
0   0.317418
1        NaN
2   0.581443
3        NaN
4   0.728766
5        NaN
6   0.495450
7        NaN
8   0.171131
9        NaN

Two problems:

  1. The dataframe has NA's where you want 0's. These are the positions where df2 was not defined. Those positions were effectively equal to NA in df2, and NA + anything = NA. This can be fixed with a fillna

  2. You want 'x1' to be a column, not the index so just reset the index

df_new=df_new.reset_index().fillna(0)


print(df_new)

x1        y1
0   0  0.118903
1   1  0.000000
2   2  0.465557
3   3  0.000000
4   4  0.533266
5   5  0.000000
6   6  0.518484
7   7  0.000000
8   8  0.308733
9   9  0.000000
user_85675550
Related