CODEWITHSUNDEEP
pythonpandas
jobbacc
jobbacc

Reputation: 23

Summing results in for loop with pandas

I am trying to write a code to read an excel file, in which I have a list of number of machines 'No' and its price and size respectively, and I am trying to sum it to obtain the total price and total size needed. This is what I have tried, but it gives me a NaN output.

ttp = 0 
tts = 0
for abcde in ws_all:
    price = fn.loc[fn['WS Name'] == abcde]['Tool Price']
    size = fn.loc[fn['WS Name'] == abcde]['Size']
    nooo = fn.loc[fn['WS Name'] == abcde]['No']
    mprice = price * nooo
    msize = size * nooo
    ttp += mprice
    print(ttp)

May i know how to troubleshoot this issue? Thank you all!

Upvotes: 2

Views: 46

Answers (1)

Corralien
Corralien

Reputation: 120391

If your dataframe fn looks like:

# df = pd.DataFrame({'WS Name': np.random.choice(['abcde', 'fghij', 'klmno', # 'pqrst'], 20),
#                    'Tool Price': np.random.randint(10, 20, 20),
#                    'Size': np.random.randint(1, 10, 20),
#                    'No': np.random.randint(1, 10, 20)})

>>> df
   WS Name  Tool Price  Size  No
0    klmno          18     4   1
1    klmno          16     7   4
2    pqrst          15     9   8
3    pqrst          17     1   3
4    klmno          10     5   9
5    pqrst          13     9   8
6    abcde          16     8   2
7    klmno          11     5   1
8    abcde          18     1   7
9    klmno          12     7   2
10   fghij          18     7   4
11   abcde          14     1   3
12   klmno          10     6   4
13   fghij          10     6   9
14   abcde          19     9   4
15   abcde          12     8   4
16   abcde          15     3   7
17   fghij          10     6   8
18   klmno          18     8   8
19   pqrst          19     3   1

You cans use the split-apply-combine pattern:

ttp = df.groupby('WS Name').apply(lambda x: sum(x['Tool Price'] * x['No']))
tts = df.groupby('WS Name').apply(lambda x: sum(x['Size'] * x['No']))

Build your combined dataframe

>>> pd.DataFrame({"Total Price": ttp, "Total Size": tts})

         Total Price  Total Size
WS Name
abcde            429         115
fghij            242         130
klmno            391         184
pqrst            294         150

Upvotes: 1

Related Questions