Search code examples
pythonexcelpandasxlwt

Using Pandas to write a certain df to the next empty column

I am trying to write a certain df which pertains total values calculated from two cells and then write a new column "total" with the values written on the next empty column.

Excel sheet consists of :

Jan |Feb

10000   |62000

95000   |45000

91000   |120000

45000   |120000

162000  |120000

What I would like is:

Jan |Feb  |Total

10000   |62000| 72000

95000   |45000|140000

91000   |120000 |211000

45000   |120000 | 165000

162000  |120000 | 282000

Instead of the totals column being written to the next column like I would like, it just overwrites the whole entire file with just the totals column being show. How would I go about writing my df_totals to the next empty column like I would like?

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["Mar"]
df.head()

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


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

contents inside xlsx after running the code: 

Total
72000
140000
211000
165000
282000

Thanks

Solution

  • df_total is a Series -- the total column of df:

    df_total = df["total"]

    If you want to save the DataFrame, df, then

    df_total.to_excel(writer,'Sheet1',index=False)

    should be

    df.to_excel(writer,'Sheet1',index=False)