1

I am trying to sum two columns on my excel sheet. when I print df.[total] the values are correct. but it does not write a new column on the excel spreadsheet. how can I go about doing this?

Here is my excel sheet consists of :

Jan |Feb

10000   |62000

95000   |45000

91000   |120000

45000   |120000

162000  |120000

Goal: to sum the two values and write a new sum column of the resulting sums. and get on my excel spreadsheet:

Jan |Feb  |Sums

10000   |62000| 72000

95000   |45000|140000

91000   |120000 |211000

45000   |120000 | 165000

162000  |120000 | 282000

Here is my code:

import pandas as pd
import numpy as np
from pandas import ExcelWriter

df = pd.read_excel("samplesheet.xlsx")
df["total"] = df["Jan"] + df["Feb"]
df.head()
#print(df["total"])

df_total = df["total"]
print(df_total)


df_total = pd.DataFrame(df_total)
writer = ExcelWriter('samplesheet.xlsx')
df_total.to_excel(writer,'Sheet1')
writer.save()

After printing df["total"] it results in: 72000, 140000, 211000, 165000, 282000. As you can see the summing is actually working but i am not sure write a new column to the excel sheet with the column name "sums" with the pertaining sums for the specific rows.

Thanks

Techno04335
  • 1,365
  • 6
  • 22
  • 43
  • You can check https://openpyxl.readthedocs.org/en/latest/ and http://stackoverflow.com/questions/13437727/python-write-to-excel-spreadsheet and many other examples. Your code is incomplete so can just move you to related questions. – Techidiot Sep 16 '15 at 13:29
  • You have a data frame now if you want to write back to excel you have to use the df.to_excel() method. remember to install the appropriate excel addins as well. tarcebacks should tell you which ones are missing. df.to_excel("samplesheet.xlsx" should do the job. depending on version of pandas you might have to use df.to_excel("samplesheet.xls") – Joop Sep 16 '15 at 13:35
  • @Joop I edited my code above, but using that I get this error: copy got an unexpected keyword argurment "font". Do you know how a can fix this? Thanks! – Techno04335 Sep 16 '15 at 13:51
  • @Joop, Also how can I ensure that this data frame will be wirrten to the next empty column, not replacing any of the current data values? – Techno04335 Sep 16 '15 at 13:52
  • if you did nochange any of existing columns it will wite back just changes. Any formatting that you had will be lost though... try using .xls extention cant remember but repending on pandas version I remember having lost ability to write to xlsx formats – Joop Sep 16 '15 at 14:50
  • I would alos suggest that you use df.to_excel() method. Using the writer gives you some flexibility, but method gives much more concise code. – Joop Sep 16 '15 at 14:51
  • @ Joop I downloadeed the package xlwt and xlsxwriter. I am able to write into an excel sheet but it completely replaces the values and writes the df of the totals. but how to i just make it write the speicfic total values. for instance the data frame has index. like: 0|72000, 1|140000, 2|211000, 3|165000, 4|282000 is what results when you print the df. if I just want to print the totals and not the index how would i do that? Thanks – Techno04335 Sep 16 '15 at 15:37
  • @Joop, Nevermind I got it to print without the index, but I am still having issues to print the totals column into the next empty column of the existing data sheet columns. – Techno04335 Sep 16 '15 at 15:43

0 Answers0