sum columns from excel in python

Question

I have an excel and an 'if' inside my code.

if telos_min == False:

    data = pd.DataFrame(data =all_together)
    data=data.transpose()
    writer= pd.ExcelWriter('ΕΟΠΥΥ.xlsx',engine='openpyxl', index=False)
    writer.book = load_workbook('ΕΟΠΥΥ.xlsx')
    writer.sheets=dict((ws.title,ws) for ws in writer.book.worksheets)
    reader = pd.read_excel(r'ΕΟΠΥΥ.xlsx',sheet_name=minas)
    data.to_excel(writer,sheet_name=minas,header=False, index = None,startrow=len(reader)+1)

    writer.save()

else:       
    writer=pd.ExcelWriter('pythonexcel.xlsx',engine='openpyxl',index=False)
    writer.book=load_workbook('pythonexcel.xlsx')
    writer.sheets=dict((ws.title,ws) for ws in writer.book.worksheets)
    reader=pd.read_excel(r'pythonexcel.xlsx',sheet_name=month,skipcols=[1,12],
                         skiprows=[1])

    sum_month=['SUM']
    for col in reader:
        for row in reader:
            asum=sum(float(col))
            sum_month.append(asum)

    data=pd.DataFrame(data=sum_month)
    data.to_excel(writer,sheet_name=month,header=False, index = None,startrow=len(reader)+1)

    writer.save()

but I am having the following issue, I can't sum, the first row of the excel is text as well as the first column, also the last column is percentage so I wouldn't want to sum that as well EDIT That is why I am having the skiprows and skipcols in the reader, but it doesn't seem to work. /EDIT.

With the code above I am getting the following error:

ValueError: could not convert string to float: 'some item'

*EDIT * Sample Data:

kind (skip row and col | num1 | num2 | num3 | num4 | num5 | num 6 | %(skip) |
some item              | 123  | 213  | 555  |  44  | 5555 | 4444  | 32%     |
some item  1           | 123  | 213  | 555  |  44  | 5555 | 4444  | 32%     |     
some item  2           | 123  | 213  | 555  |  44  | 5555 | 4444  | 32%     |
some item  3           | 123  | 213  | 555  |  44  | 5555 | 4444  | 32%     |
some item  4           | 123  | 213  | 555  |  44  | 5555 | 4444  | 32%     |
some item  5           | 123  | 213  | 555  |  44  | 5555 | 4444  | 32%     |
The Sum is (end result)| sum1 | sum2 | sum3 | sum4 | sum5 | sum6  | empty   |

Please provide a small set of sample data as text that we can copy and paste. Include the corresponding desired result. Check out the guide on [how to make good reproducible pandas examples](https://stackoverflow.com/a/20159305/3620003). — timgeb, Jun 13 '20 at 06:30
if you need any more info I will add it with an edit to the main question. — JohnnyD, Jun 13 '20 at 06:31
Is excel important for this problem in any way? You can just remove all the code that loads or saves the file if not. — timgeb, Jun 13 '20 at 06:31
but all the program is made in order to save and use data from excel. — JohnnyD, Jun 13 '20 at 06:35
We need the sample data as text that can be copied and pasted and the desired result for the sample data. — timgeb, Jun 13 '20 at 06:35
yes it's a read error, that is why I posted the issue. And the data that I need to be read and sum-ed are from an excel file. In the first and Core scale of the program you can add prices and get all the data you see in the excel picture. In the second Scale of the program I need to sum the columns to get the final sum results. — JohnnyD, Jun 13 '20 at 06:41
No, it's a `ValueError` because you are trying to use a string as a float. If you posted an example as shown in the guide I linked to then we could show you how to arrive at the result with vectorized operations and without `ValueError`s. — timgeb, Jun 13 '20 at 06:43
But the issue is that I am trying to skip the first row and the first column and I can not do it hence the value error I do know that the first row is STR and the first column is also a STR made of characters and no numbers so that I can use it as float. That is why I am trying to skip with the skiprows and skipcols but it seems it does not work. — JohnnyD, Jun 13 '20 at 06:52

score 3 · Accepted Answer · answered Jun 13 '20 at 09:43

import numpy as np
import pandas as pd

df = pd.read_excel(file_path, header=0)

df

            kind     num1    num2    num3    num4    num5    num 6    Percent
0   some item        123     213     555      44    5555     4444     0.32
1   some item  1     123     213     555      44    5555     4444     0.32
2   some item  2     123     213     555      44    5555     4444     0.32
3   some item  3     123     213     555      44    5555     4444     0.32
4   some item  4     123     213     555      44    5555     4444     0.32
5   some item  5     123     213     555      44    5555     4444     0.32

Sum the columns, (python will concatenate the strings in the 'kind' column when sum is applied). Then convert the summed results to a list, edit the list so that the first value is 'The sum is' and then append it to the original dataframe.

last_row = ['The sum is'] + list(df.sum())[1:]
df2 = pd.DataFrame(data=[last_row], columns=df.columns)
df = df.append(df2, ignore_index=True)

df

            kind     num1    num2    num3    num4    num5    num 6    Percent
0   some item        123     213     555      44    5555     4444     0.32
1   some item  1     123     213     555      44    5555     4444     0.32
2   some item  2     123     213     555      44    5555     4444     0.32
3   some item  3     123     213     555      44    5555     4444     0.32
4   some item  4     123     213     555      44    5555     4444     0.32
5   some item  5     123     213     555      44    5555     4444     0.32
6   The sum is       738    1278    3330     264   33330    26664     1.92

save to file

df.to_excel(file_path, index=False)

Thanks mate, it is really helpful. Is there a way to stop the last column from being sum-ed? — JohnnyD, Jun 15 '20 at 06:17
I figured it out, last_row=['The sum is']+list(df.sum())[1:12]+[' '] — JohnnyD, Jun 15 '20 at 10:15

sum columns from excel in python

1 Answers1