I have two dataframes: df1 and df2. I want to take the values of a column in df2 and add it to df1.
df1:
Title = ['Aeroplane', 'Ships', 'Houses']
Term = ['Computers', 'Flasks', 'Mouse']
counts_1 = [200, 30, 45, 66, 33, 450, 60, 100, 150]
df_1 = pd.DataFrame({"Title": Title, "Terms": Term})
product_terms = product(term_list, cap_list)
df_1 = pd.DataFrame(product_terms, columns=['Term', 'Title'])
df_1['C1'] = counts_1
Term Title C1
0 Computers Aeroplane 200
1 Computers Ships 30
2 Computers Houses 45
3 Flasks Aeroplane 66
4 Flasks Ships 33
5 Flasks Houses 450
6 Mouse Aeroplane 60
7 Mouse Ships 100
8 Mouse Houses 150
df2 (smaller one)
terms = ['Computers', 'Flasks', 'Flasks', 'Mouse']
title = ['Aeroplane', 'Aeroplane', 'Ships', 'Houses']
count_2 = [3, 6, 13, 15]
df_2 = pd.DataFrame({'Term': terms, 'Title': title, 'C2': count_2})
Term Title C2
0 Computers Aeroplane 3
1 Flasks Aeroplane 6
2 Flasks Ships 13
3 Mouse Houses 15
I want to combine the two dfs into one df like below: add the column C2 from df_2 to df_1 (based on the Term and Title col match) and insert 0 where ever there is no corresponding matching Term and Title cols.
Term Title C1 C2
0 Computers Aeroplane 200 3
1 Computers Ships 30 0
2 Computers Houses 45 0
3 Flasks Aeroplane 66 6
4 Flasks Ships 33 13
5 Flasks Houses 450 0
6 Mouse Aeroplane 60 0
7 Mouse Ships 100 15
8 Mouse Houses 150 0
df2's terms and titles are always a subset of those in df1.
Here is what I tried:
df_1.set_index(['Term', 'Title'], inplace=True)
df_2.set_index(['Term', 'Title'], inplace=True)
Then, iterate over the rows and assign values.
for idx, row in df_1.iterrows():
try:
c2_value = df_2.loc[idx, 'C2']
except:
df_1.loc[idx, 'C2'] = 0
else:
df_1.loc[idx]['C2'] = c2_value
df_final = df_1.reset_index()
Is there a better way to achieve what I want? I feel iterrows may not be an efficient way. My dataframes have millions of rows.
