0

I want to write the single dataframe to the same excel file. my code Updated Code:

    data = []
    r = ['1','2']
    for i in r:
      A = ['1','2','3','4','5']
      B = ["A"]
      C = ["abc234"]
      m = list(zip(cycle(B),cycle(C), A))
      data.extend(m)
    df2 = pd.DataFrame(data,columns=['Av','Bv','Cv'])
    book = load_workbook(filepath)
    writer = pandas.ExcelWriter(filepath, engine='openpyxl')
    writer.book = book
    writer.sheets = {ws.title: ws for ws in book.worksheets}

for sheetname in writer.sheets:
    df2.to_excel(writer, sheet_name=sheetname, startrow=writer.sheets[sheetname].max_row, index=False, header=False)
writer.save()

with openpyxl, if_sheet = it always replaces the old content. Can anyone help what I could use here. Expected Output:

  Av   Bv    Cv
0  A  abc234  1
1  A  abc234  2
2  A  abc234  3
3  A  abc234  4
4  A  abc234  5
5  A  abc234  1
6  A  abc234  2
7  A  abc234  3
8  A  abc234  4
9  A  abc234  5
user12
  • 19
  • 5
  • 2
    Does this answer your question? [append dataframe to excel with pandas](https://stackoverflow.com/questions/47737220/append-dataframe-to-excel-with-pandas) – Prasanna Aug 01 '22 at 19:19
  • 1
    Check the version of your pandas. Overlay will work from v1.4.0 as per [documentation](https://pandas.pydata.org/docs/dev/reference/api/pandas.ExcelWriter.html). If earlier, you can use the method provided in above comment – Redox Aug 02 '22 at 03:55
  • @Prasanna, that does work thanksome. Is there a better way to populate the row as per column value, instead of zip function. because my code runs for hrs and I want to avoid it. – user12 Aug 03 '22 at 17:13
  • @user12 It is very difficult to suggest a solution based on the current context. Can you update the question with more context? For instance, regarding the data source and schema, what exactly are the columns Av, Bv, and Cv? Also, can you elaborate more on what you mean by "populate the row as per column value"? – Prasanna Aug 03 '22 at 17:20
  • @Prasanna, I updated the final code. it works as expected. Av is just some column that holds strings. – user12 Aug 04 '22 at 13:45
  • @user12 from what your code shows, you are creating a list of lists based on fixed values. Can't you create just one big list with everything on it? If yes, then that would be faster than the zip function as you would just create a list. If not, then you must have a source of data based on what you are creating that list. This is what my previous comment was directed towards. Unless you share the data source (or atleast the schema and some more information regarding that), it is difficult to suggest good approaches. For this instance of the code, zip is more than enough it seems. – Prasanna Aug 04 '22 at 16:58

0 Answers0