0

I have this df called result:

       CODE    YEAR  MONTH DAY TMAX  TMIN   PP
9984   000130  1991    1    1  32.6  23.4  0.0
9985   000130  1991    1    2  31.2  22.4  0.0
9986   000130  1991    1    3  32.0   NaN  0.0
9987   000130  1991    1    4  32.2  23.0  0.0
9988   000130  1991    1    5  30.5  22.0  0.0
      ...   ...  ...  ...   ...   ...  ...
20118  000130  2018    9   30  31.8  21.2  NaN
30028  000132  1991    1    1  35.2   NaN  0.0
30029  000132  1991    1    2  34.6   NaN  0.0
30030  000132  1991    1    3  35.8   NaN  0.0
30031  000132  1991    1    4  34.8   NaN  0.0
      ...   ...  ...  ...   ...   ...   ...
45000  000132  2019    10   5  35.5   NaN  21.1

I want to save the data in many excel files. One excel file per code. I have 371 unique codes in CODE column.

One excel file for code 000130, another excel file for code 000132, etc etc.

I've tried this code:

for code, data in result.groupby('CODE'):
    name="/PATH/TO/FILES/station"+str(code)+".xlsx"
    writer = pd.ExcelWriter(name)
    data.to_excel(writer,'Sheet2',index = False, header = False)
    writer.save()

But it takes too long and is not working. Would you mind to help me? Thanks.

Javier
  • 493
  • 3
  • 15
  • 1
    Well, pandas excel writer is very very very slow. Maybe if you try another excel writer package. Or even [save to csv and then convert to excel](https://stackoverflow.com/questions/17684610/python-convert-csv-to-xlsx). – xicocaio Mar 31 '21 at 18:50

1 Answers1

1

You're not closing the files; ExcelWriter is a contextmanager, so you should be using it with a with clause (so that the file is closed).

for code, data in result.groupby('CODE'):
    name="/PATH/TO/FILES/station"+str(code)+".xlsx"
    with pd.ExcelWriter(name) as writer:
        data.to_excel(writer,'Sheet2',index = False, header = False)

For your case, though, you could also just use DataFrame.to_excel with a filename (using ExcelWriter is more helpful if either you want to do additional modifications to the sheet like adding charts, or if you want to write multiple DataFrames to the same excel file).

for code, data in result.groupby('CODE'):
    name="/PATH/TO/FILES/station"+str(code)+".xlsx"
    data.to_excel(name, index=False, header=False)
Liam3851
  • 121
  • 5