I have two DataFrames of 20 rows and 4 columns. The names and value types of the columns are the same.
One of the columns is the title
, the other 3 are values.
JavaScript
x
12
12
1
df1
2
title col1 col2 col3
3
apple a d g
4
pear b e h
5
grape c f i
6
7
df2
8
title col1 col2 col3
9
carrot q t w
10
pumpkin r u x
11
sprouts s v y
12
Now I would like to create 3 separate tables/lists subtracting each value of df1.col1 - df2.col1
| df1.col2 - df2.col2
| df1.col3 - df2.col3
. For df1.col1 - df2.col1
I expect an output that looks something among the lines of:
JavaScript
1
11
11
1
df1.title df2.title score
2
apple carrot (a - q)
3
apple pumpkin (a - r)
4
apple sprouts (a - s)
5
pear carrot (b - t)
6
pear pumpkin (b - u)
7
pear sprouts (b - v)
8
grape carrot (c - w)
9
grape pumpkin (c - x)
10
grape sprouts (c - y)
11
I tried to create a for loop using the following code:
JavaScript
1
7
1
for i in df1.iterrows():
2
score_col1 = df1.col1[[i]] - df2.col2[[j]]
3
score_col2 = df1.col2[[i]] - df2.col2[[j]]
4
score_col3 = df1.col3[[i]] - df2.col3[[j]]
5
score_total = score_col1 + score_col2 + score_col3
6
i = i + 1
7
In return, I received an output for score_col1
looking like this:
JavaScript
1
5
1
df1.title df2.title score
2
apple carrot (a - q)
3
pear carrot (b - t)
4
grape carrot (c - w)
5
Can someone help me to obtain the expected output?
Advertisement
Answer
JavaScript
1
24
24
1
a1 = ['apple','pear', 'banana']
2
b1 = [56,32,23]
3
c1 = [12,34,90]
4
d1 = [87,65,23]
5
6
a2 = ['carrot','pumpkin','sprouts']
7
b2 = [16,12,93]
8
c2 = [12,32,70]
9
d2 = [81,55,21]
10
11
df1 = pd.DataFrame({'title':a1, 'col1':b1, 'col2':c1, 'col3':d1})
12
df2 = pd.DataFrame({'title':a2, 'col1':b2, 'col2':c2, 'col3':d2})
13
14
res_df = pd.DataFrame([])
15
cols = ['col1','col2','col3']
16
17
for c in cols:
18
res_df = pd.DataFrame([])
19
for i,j in df1.iterrows():
20
for k,l in df2.iterrows():
21
res_df = res_df.append(pd.DataFrame({'title_df1':j.title, 'title_df2':l.title, 'score':j[str(c)] - l[str(c)]},index=[0]), ignore_index=True)
22
23
print(res_df)
24