Nested loop - alternative solution - Python pandas

Question

I have started learning python, and I wanted to ask if there is an alternative faster solution to the below nested loop:

for y in range(total_rows2):
    for x in range(total_rows1):
        if df2.iloc[y,0]==df1.iloc[x,0]:
            df1.iloc[x,1]=df1.iloc[x,1]+df2.iloc[y,17]

Basically, I have found the number of rows (total_rows1 and total_rows2) of two dataframes (df1 and df2). The first column of both dataframes (index=0) correspond to some IDs.

If the IDs of the two dataframes match, then I want to add the value of column 18 of df2(index 17, column name='Profit') to the second column of df1 (index=1, column name='Profit'). An id may appear twice in df2 but I will appear the sum in df1 (please see below for id 0108). So the 'Profit' column of df1 will be the sum of Profit per ID.

df2:

---	ID	Profit
0	0104	0
1	0106	0
2	0107	0
3	0108	0

df1:

---	ID	Loss	Profit
0	0104	100	230
1	0106	200	150
2	0108	150	120
3	0107	120	230
4	0109	100	400
5	0108	150	400

So I want df2 to look as followed:

---	ID	Profit
0	0104	230
1	0106	150
2	0107	230
3	0108	520

Thanks!

Does this answer your question: stackoverflow.com/questions/46896105/…? — Pygirl
– Pygirl, Commented Mar 8, 2021 at 16:42
Are df1 and df2 in the same shape? (same number of rows and column) or not at all? Have you got a small example to help us to reproduce the code and make understand faster what's your goal? — Lumber Jack
– Lumber Jack, Commented Mar 8, 2021 at 16:44
Hi, no the dataframes have different shapes, I have added some more details on my initial post! Thank you! — Natalia
– Natalia, Commented Mar 8, 2021 at 17:18

Boskosnitch · Accepted Answer · 2021-03-08 16:51:11Z

I think just merging the two dfs on that first column and then doing the addition would be fine.

frames:

>>> df1
  ID  B  C  D
0  e  3  8  1
1  d  5  1  1
2  g  6  5  1
3  e  8  8  7
4  j  9  3  6
5  i  4  0  5
6  g  0  4  1
7  a  3  7  2
8  e  0  6  9
9  b  2  9  6
>>> df2
  ID  col_17
0  j       9
1  c       3
2  d       6
3  g       4
4  h       4
5  g       5
6  e       1
7  d       8
8  b       0
9  i       6

Merge:

>>> df3 = df1.merge(df2,how='left',on='ID')
>>> df3
   ID  B  C  D  col_17
0   e  3  8  1     1.0
1   d  5  1  1     6.0
2   d  5  1  1     8.0
3   g  6  5  1     4.0
4   g  6  5  1     5.0
5   e  8  8  7     1.0
6   j  9  3  6     9.0
7   i  4  0  5     6.0
8   g  0  4  1     4.0
9   g  0  4  1     5.0
10  a  3  7  2     NaN
11  e  0  6  9     1.0
12  b  2  9  6     0.0

Add:

>>> df3['B']=np.where(df3['col_17'].notna(),df3['B']+df3['col_17'],df3['B'])
>>> df3
   ID     B  C  D  col_17
0   e   4.0  8  1     1.0
1   d  11.0  1  1     6.0
2   d  13.0  1  1     8.0
3   g  10.0  5  1     4.0
4   g  11.0  5  1     5.0
5   e   9.0  8  7     1.0
6   j  18.0  3  6     9.0
7   i  10.0  0  5     6.0
8   g   4.0  4  1     4.0
9   g   5.0  4  1     5.0
10  a   3.0  7  2     NaN
11  e   1.0  6  9     1.0
12  b   2.0  9  6     0.0

Collectives™ on Stack Overflow

Nested loop - alternative solution - Python pandas

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related