How to save a new sheet in an existing excel file using Pandas

Question

I want to use excel files to store data elaborated with python  My problem is that I can t add sheets to an existing excel file  Here I suggest a sample code to work with in order to reach this issue  import pandas as pd import numpy as np  path   r C  Users fedel Desktop excelData PhD data xlsx   x1   np random randn 100  2  df1   pd DataFrame x1   x2   np random randn 100  2  df2   pd DataFrame x2   writer   pd ExcelWriter path  engine    xlsxwriter   df1 to excel writer  sheet name    x1   df2 to excel writer  sheet name    x2   writer save   writer close     This code saves two DataFrames to two sheets  named  x1  and  x2  respectively  If I create two new DataFrames and try to use the same code to add two new sheets   x3  and  x4   the original data is lost   import pandas as pd import numpy as np  path   r C  Users fedel Desktop excelData PhD data xlsx   x3   np random randn 100  2  df3   pd DataFrame x3   x4   np random randn 100  2  df4   pd DataFrame x4   writer   pd ExcelWriter path  engine    xlsxwriter   df3 to excel writer  sheet name    x3   df4 to excel writer  sheet name    x4   writer save   writer close     I want an excel file with four sheets   x1    x2    x3    x4   I know that  xlsxwriter  is not the only  engine   there is  openpyxl   I also saw there are already other people that have written about this issue  but still I can t understand how to do that   Here a code taken from this link  import pandas from openpyxl import load workbook  book   load workbook  Masterfile xlsx   writer   pandas ExcelWriter  Masterfile xlsx   engine  openpyxl    writer book   book writer sheets   dict  ws title  ws  for ws in book worksheets   data filtered to excel writer   Main   cols   Diff1    Diff2     writer save     They say that it works  but it is hard to figure out how  I don t understand what  ws title    ws   and  dict  are in this context    Which is the best way to save  x1  and  x2   then close the file  open it again and add  x3  and  x4

User · Answer

Another fairly simple way to go about this is to make a method like this:

def _write_frame_to_new_sheet(path_to_file=None, sheet_name='sheet', data_frame=None):
    book = None
    try:
        book = load_workbook(path_to_file)
    except Exception:
        logging.debug('Creating new workbook at %s', path_to_file)
    with pd.ExcelWriter(path_to_file, engine='openpyxl') as writer:
        if book is not None:
            writer.book = book
        data_frame.to_excel(writer, sheet_name, index=False)

The idea here is to load the workbook at path_to_file if it exists and then append the data_frame as a new sheet with sheet_name. If the workbook does not exist, it is created. It seems that neither openpyxl or xlsxwriter append, so as in the example by @Stefano above, you really have to load and then rewrite to append.

User · Answer

I would strongly recommend you work directly with openpyxl since it now supports Pandas DataFrames    This allows you to concentrate on the relevant Excel and Pandas code

User · Answer

For creating a new file x1   np random randn 100  2  df1   pd DataFrame x1  with pd ExcelWriter  sample xlsx   as writer        df1 to excel writer  sheet name  x1    For appending to the file  use the argument mode  a  in pd ExcelWriter  x2   np random randn 100  2  df2   pd DataFrame x2  with pd ExcelWriter  sample xlsx   engine  openpyxl   mode  a   as writer        df2 to excel writer  sheet name  x2    Default is mode   w   See documentation

User · Answer

Thank you  I believe that a complete example could be good for anyone else who have the same issue   import pandas as pd import numpy as np  path   r C  Users fedel Desktop excelData PhD data xlsx   x1   np random randn 100  2  df1   pd DataFrame x1   x2   np random randn 100  2  df2   pd DataFrame x2   writer   pd ExcelWriter path  engine    xlsxwriter   df1 to excel writer  sheet name    x1   df2 to excel writer  sheet name    x2   writer save   writer close     Here I generate an excel file  from my understanding it does not really matter whether it is generated via the  xslxwriter  or the  openpyxl  engine   When I want to write without loosing the original data then  import pandas as pd import numpy as np from openpyxl import load workbook  path   r C  Users fedel Desktop excelData PhD data xlsx   book   load workbook path  writer   pd ExcelWriter path  engine    openpyxl   writer book   book  x3   np random randn 100  2  df3   pd DataFrame x3   x4   np random randn 100  2  df4   pd DataFrame x4   df3 to excel writer  sheet name    x3   df4 to excel writer  sheet name    x4   writer save   writer close     this code do the job

User · Answer

In the example you shared you are loading the existing file into book and setting the writer book value to be book  In the line writer sheets   dict  ws title  ws  for ws in book worksheets  you are accessing each sheet in the workbook as ws  The sheet title is then ws so you are creating a dictionary of  sheet titles  sheet  key  value pairs  This dictionary is then set to writer sheets  Essentially these steps are just loading the existing data from  Masterfile xlsx  and populating your writer with them   Now let s say you already have a file with x1 and x2 as sheets  You can use the example code to load the file and then could do something like this to add x3 and x4     path   r C  Users fedel Desktop excelData PhD data xlsx  writer   pd ExcelWriter path  engine  openpyxl   df3 to excel writer   x3   index False  df4 to excel writer   x4   index False  writer save     That should do what you are looking for

User · Answer

A simple example for writing multiple data to excel at a time  And also when you want to append data to a sheet on a written excel file  closed excel file       When it is your first time writing to an excel   Writing  df1  and  df2  to  1st sheet  and  2nd sheet     import pandas as pd  from openpyxl import load workbook  df1   pd DataFrame   1   1    columns   a    df2   pd DataFrame   2   2    columns   b    df3   pd DataFrame   3   3    columns   c     excel dir    my excel dir   with pd ExcelWriter excel dir  engine  xlsxwriter   as writer          df1 to excel writer   1st sheet          df2 to excel writer   2nd sheet          writer save         After you close your excel  but you wish to  append  data on the same excel file but another sheet  let s say  df3  to sheet name  3rd sheet      book   load workbook excel dir  with pd ExcelWriter excel dir  engine  openpyxl   as writer      writer book   book     writer sheets   dict  ws title  ws  for ws in book worksheets              Your dataframe to append       df3 to excel writer   3rd sheet          writer save          Be noted that excel format must not be xls  you may use xlsx one

User · Answer

You can read existing sheets of your interests  for example   x1    x2   into memory and  write  them back prior to adding more new sheets  keep in mind that sheets in a file and sheets in memory are two different things  if you don t read them  they will be lost   This approach uses  xlsxwriter  only  no openpyxl involved   import pandas as pd import numpy as np  path   r C  Users fedel Desktop excelData PhD data xlsx     begin  lt    read selected sheets and write them back df1   pd read excel path  sheet name  x1   index col 0    or sheet name 0 df2   pd read excel path  sheet name  x2   index col 0    or sheet name 1 writer   pd ExcelWriter path  engine  xlsxwriter   df1 to excel writer  sheet name  x1   df2 to excel writer  sheet name  x2     end    gt     now create more new sheets x3   np random randn 100  2  df3   pd DataFrame x3   x4   np random randn 100  2  df4   pd DataFrame x4   df3 to excel writer  sheet name  x3   df4 to excel writer  sheet name  x4   writer save   writer close     If you want to preserve all existing sheets  you can replace above code between begin and end with     read all existing sheets and write them back writer   pd ExcelWriter path  engine  xlsxwriter   xlsx   pd ExcelFile path  for sheet in xlsx sheet names      df   xlsx parse sheet name sheet  index col 0      df to excel writer  sheet name sheet

User · Answer

This program is to read from excel workbook to fetch only the URL domain names and write to the existing excel workbook in a different sheet    Developer - Nilesh K import pandas as pd from openpyxl import load workbook  for writting to the existing workbook  df   pd read excel  urlsearch test xlsx     You can use the below for the relative path    r C  Users xyz Desktop Python   l       To make a list in for loop   begin  loop starts here for fetching http from a string and iterate thru the entire sheet  You can have your own logic here  for index  row in df iterrows        try           str    row  TEXT     string to read and iterate         y    index          str pos   str index  http    fetched the index position for http         str pos1   str index      str index      2   fetched the second 3rd position of   starting from http         str op   str str pos str pos1   Substring the domain name         l append str op   append the list with domain names       Error handling to skip the error rows and continue      except ValueError              print  Error    print l  l   list dict fromkeys l    Keep distinct values  you can comment this line to get all the values df1   pd DataFrame l columns   URL     Create dataframe using the list  end   Write using openpyxl so it can be written to same workbook book   load workbook  urlsearch test xlsx   writer   pd ExcelWriter  urlsearch test xlsx  engine    openpyxl   writer book   book df1 to excel writer sheet name    Sheet3   writer save   writer close     The below can be used to write to a different workbook without using openpyxl  df1 to excel r C  Users xyz Desktop Python urlsearch1 test xlsx  index  false  sheet name  sheet1

User · Answer

Can do it without using ExcelWriter  using tools in openpyxl This can make adding fonts to the new sheet much easier using openpyxl styles  import pandas as pd from openpyxl import load workbook from openpyxl utils dataframe import dataframe to rows   Location of original excel sheet fileLocation  r C  workspace data xlsx    Location of new file which can be the same as original file writeLocation r C  workspace dataNew xlsx   data     Name    Tom   Paul   Jeremy    Age   32 43 34   Salary   20000 34000 32000     The dataframe you want to add df   pd DataFrame data    Load existing sheet as it is book   load workbook fileLocation   create a new sheet sheet   book create sheet  Sheet Name     Load dataframe into new sheet for row in dataframe to rows df  index False  header True       sheet append row    Save the modified excel at desired location     book save writeLocation

[python] How to save a new sheet in an existing excel file, using Pandas?

Examples related to python

Examples related to pandas

Examples related to openpyxl

Examples related to xlsxwriter