How to write to an existing excel file without overwriting data using pandas

Question

I use pandas to write to excel file in the following fashion   import pandas  writer   pandas ExcelWriter  Masterfile xlsx     data filtered to excel writer   Main   cols   Diff1    Diff2     writer save     Masterfile xlsx already consists of number of different tabs  However  it does not yet contain  Main    Pandas correctly writes to  Main  sheet  unfortunately it also deletes all other tabs

User · Answer

I know this is an older thread  but this is the first item you find when searching  and the above solutions don t work if you need to retain charts in a workbook that you already have created  In that case  xlwings is a better option - it allows you to write to the excel book and keeps the charts chart data   simple example   import xlwings as xw import pandas as pd   create DF months     2017-01   2017-02   2017-03   2017-04   2017-05   2017-06   2017-07   2017-08   2017-09   2017-10   2017-11   2017-12   value1    x   5 5 for x in range len months    df   pd DataFrame value1  index   months  columns     value1    df  value2     df  value1   5 df  value3     df  value2   5   load workbook that has a chart in it wb   xw Book  C   data  bookwithChart xlsx    ws   wb sheets  chartData    ws range  A1   options index False  value   df  wb   xw Book  C   data  bookwithChart updated xlsx    xw apps 0  quit

User · Answer

Pandas docs says it uses openpyxl for xlsx files  Quick look through the code in ExcelWriter gives a clue that something like this might work out   import pandas from openpyxl import load workbook  book   load workbook  Masterfile xlsx   writer   pandas ExcelWriter  Masterfile xlsx   engine  openpyxl    writer book   book     ExcelWriter for some reason uses writer sheets to access the sheet     If you leave it empty it will not know that sheet Main is already there    and will create a new sheet   writer sheets   dict  ws title  ws  for ws in book worksheets   data filtered to excel writer   Main   cols   Diff1    Diff2     writer save

User · Answer

Here is a helper function  def append df to excel filename  df  sheet name  Sheet1   startrow None                         truncate sheet False                            to excel kwargs        quot  quot  quot      Append a DataFrame  df  to existing Excel file  filename      into  sheet name  Sheet      If  filename  doesn t exist  then this function will create it       Parameters        filename   File path or existing ExcelWriter                   Example    path to file xlsx         df   dataframe to save to workbook       sheet name   Name of sheet which will contain DataFrame                      default   Sheet1         startrow   upper left cell row to dump data frame                   Per default  startrow None  calculate the last row                  in the existing DF and write to the next row          truncate sheet   truncate  remove and recreate   sheet name                         before writing DataFrame to Excel file       to excel kwargs   arguments which will be passed to  DataFrame to excel                             can be dictionary       Returns  None       c   MaxU  https   stackoverflow com users 5741205 maxu tab profile       quot  quot  quot      from openpyxl import load workbook        ignore  engine  parameter if it was passed     if  engine  in to excel kwargs          to excel kwargs pop  engine        writer   pd ExcelWriter filename  engine  openpyxl          Python 2 x  define  FileNotFoundError  exception if it doesn t exist      try          FileNotFoundError     except NameError          FileNotFoundError   IOError       try            try to open an existing workbook         writer book   load workbook filename                     get the last row in the existing Excel sheet           if it was not specified explicitly         if startrow is None and sheet name in writer book sheetnames              startrow   writer book sheet name  max row            truncate sheet         if truncate sheet and sheet name in writer book sheetnames                index of  sheet name  sheet             idx   writer book sheetnames index sheet name                remove  sheet name              writer book remove writer book worksheets idx                 create an empty sheet  sheet name  using old index             writer book create sheet sheet name  idx                     copy existing sheets         writer sheets    ws title ws for ws in writer book worksheets      except FileNotFoundError            file does not exist yet  we will create it         pass      if startrow is None          startrow   0        write out the new sheet     df to excel writer  sheet name  startrow startrow    to excel kwargs         save the workbook     writer save                 NOTE  for Pandas  lt  0 21 0  replace sheet name with sheetname  Usage examples  append df to excel  d  temp test xlsx   df   append df to excel  d  temp test xlsx   df  header None  index False   append df to excel  d  temp test xlsx   df  sheet name  Sheet2   index False   append df to excel  d  temp test xlsx   df  sheet name  Sheet2   index False  startrow 25

User · Answer

writer   pd ExcelWriter  prueba1 xlsx engine  openpyxl  keep date col True    The  keep date col  hope help you

User · Answer

With openpyxlversion 2 4 0 and pandasversion 0 19 2  the process  ski came up with gets a bit simpler   import pandas from openpyxl import load workbook  with pandas ExcelWriter  Masterfile xlsx   engine  openpyxl   as writer      writer book   load workbook  Masterfile xlsx       data filtered to excel writer   Main   cols   Diff1    Diff2     That s it

User · Answer

Solution by  MaxU worked very well  I have just one suggestion  If truncate sheet True is specified than  quot startrow quot  should NOT be retained from existing sheet  I suggest          if startrow is None and sheet name in writer book sheetnames              if not truncate sheet    truncate sheet would use startrow if provided  or zero below                  startrow   writer book sheet name  max row

User · Answer

def append sheet to master self  master file path  current file path  sheet name       try          master book   load workbook master file path          master writer   pandas ExcelWriter master file path  engine  openpyxl           master writer book   master book         master writer sheets   dict  ws title  ws  for ws in master book worksheets          current frames   pandas ExcelFile current file path  parse pandas ExcelFile current file path  sheet names 0                                                                  header None                                                                 index col None          current frames to excel master writer  sheet name  index None  header False           master writer save       except Exception as e          raise e   This works perfectly fine only thing is that formatting of the master file file to which we add new sheet  is lost

User · Answer

Starting in pandas 0 24 you can simplify this with the mode keyword argument of ExcelWriter   import pandas as pd  with pd ExcelWriter  the file xlsx   engine  openpyxl   mode  a   as writer        data filtered to excel writer

User · Answer

Old question  but I am guessing some people still search for this - so     I find this method nice because all worksheets are loaded into a dictionary of sheet name and dataframe pairs  created by pandas with the sheetname None option   It is simple to add  delete or modify worksheets between reading the spreadsheet into the dict format and writing it back from the dict   For me the xlsxwriter works better than openpyxl for this particular task in terms of speed and format   Note  future versions of pandas  0 21 0   will change the  sheetname  parameter to  sheet name      read a single or multi-sheet excel file    returns dict of sheetname s   dataframe s   ws dict   pd read excel excel file path                          sheetname None     all worksheets are accessible as dataframes     easy to change a worksheet as a dataframe  mod df   ws dict  existing worksheet      do work on mod df   then reassign ws dict  existing worksheet     mod df    add a dataframe to the workbook as a new worksheet with   ws name  df as dict key  value  ws dict  new worksheet     some other dataframe    when done  write dictionary back to excel      xlsxwriter honors datetime and date formats    only included as example     with pd ExcelWriter excel file path                      engine  xlsxwriter                       datetime format  yyyy-mm-dd                       date format  yyyy-mm-dd   as writer       for ws name  df sheet in ws dict items            df sheet to excel writer  sheet name ws name    For the example in the 2013 question   ws dict   pd read excel  Masterfile xlsx                           sheetname None   ws dict  Main     data filtered   Diff1    Diff2     with pd ExcelWriter  Masterfile xlsx                       engine  xlsxwriter   as writer       for ws name  df sheet in ws dict items            df sheet to excel writer  sheet name ws name

User · Answer

Method   Can create file if not present Append to existing excel as per sheet name  import pandas as pd from openpyxl import load workbook  def write to excel df  file       try          book   load workbook file          writer   pd ExcelWriter file  engine  openpyxl            writer book   book         writer sheets   dict  ws title  ws  for ws in book worksheets          df to excel writer    kwds          writer save       except FileNotFoundError as e          df to excel file    kwds   Usage  df a   pd DataFrame range 10   columns   quot a quot    df b   pd DataFrame range 10  20   columns   quot b quot    write to excel df a   quot test xlsx quot   sheet name  quot Sheet a quot   columns   a    index False  write to excel df b   quot test xlsx quot   sheet name  quot Sheet b quot   columns   b

User · Answer

There is a better solution in pandas 0 24   with pd ExcelWriter path  mode  a   as writer      s to excel writer  sheet name  another sheet   index False    before     after     so upgrade your pandas now   pip install --upgrade pandas

User · Answer

book   load workbook xlsFilename  writer   pd ExcelWriter self xlsFilename  writer book   book writer sheets   dict  ws title  ws  for ws in book worksheets  df to excel writer  sheet name sheetName  index False  writer save

[python] How to write to an existing excel file without overwriting data (using pandas)?

Examples related to python

Examples related to excel

Examples related to python-2.7

Examples related to pandas