Say I have two DataFrames, where one is conceptually a subset of the other. How can I efficiently transfer data from the subset to the superset? Here is some data to work with:
import pandas as pd
sup = pd.DataFrame({'row': [0, 0, 0, 1, 1, 1, 2, 2],
'col': [0, 1, 2, 0, 1, 2, 1, 2], 'val': 1.3})
# col row val
# 0 0 0 1.3
# 1 1 0 1.3
# 2 2 0 1.3
# 3 0 1 1.3
# 4 1 1 1.3
# 5 2 1 1.3
# 6 1 2 1.3
# 7 2 2 1.3
sub = pd.DataFrame({'Row': [2, 0, 1], 'Column': [2, 1, 0], 'Value': [1.1, 4.4, 2.4]})
# Column Row Value
# 0 2 2 1.1
# 1 1 0 4.4
# 2 0 1 2.4
I know I can efficiently merge the two DataFrames:
sup.merge(sub, left_on=['row', 'col'], right_on=['Row', 'Column'])
# col row val Column Row Value
# 0 1 0 1.3 1 0 4.4
# 1 0 1 1.3 0 1 2.4
# 2 2 2 1.3 2 2 1.1
But how could I overwrite the values in sup['val'] with those matching from sub['Value']? In my real world situation, sup is about 40k rows, and sub is only 1k rows.
The desired result in this example would be:
# col row val
# 0 0 0 1.3
# 1 1 0 4.4
# 2 2 0 1.3
# 3 0 1 2.4
# 4 1 1 1.3
# 5 2 1 1.3
# 6 1 2 1.3
# 7 2 2 1.1
Use set_index and change values using loc and reset_index, Also you don't need merge here:
sub.rename(columns={'Row':'row', 'Column':'col', 'Value':'val'}, inplace=True)
#alternative sub.columns = sup.columns
sub.set_index(['row','col'], inplace=True)
sup.set_index(['row','col'], inplace=True)
sup.loc[sub.index,:] = sub['val']
sup.reset_index(inplace=True)
print(sup)
row col val
0 0 0 1.3
1 0 1 4.4
2 0 2 1.3
3 1 0 2.4
4 1 1 1.3
5 1 2 1.3
6 2 1 1.3
7 2 2 1.1