Create multiple sheets in excel using pandas to loop through sheet names

Question

I am trying to create multiple sheets in excel, using pandas integration with xlsxwriter in Python, based on data represented in categories

Categories ="Autoimmune Diseases","Blood","Cancer: Head & Neck","Cancer: Hematological","Cancer: Other","Cardiovascular","Dermatological Treatment","Eyes","Immune Deficiencies","Infectious Diseases","Liver Disease","Neurological","Neurotology","Pain Management","Rare Diseases","Respiratory Diseases","Women's Health"

Right now my code looks like:

writer = pd.ExcelWriter(Individualreport, engine='xlsxwriter')
Categories ="Autoimmune Diseases","Blood","Cancer: Head & Neck","Cancer: Hematological","Cancer: Other","Cardiovascular","Dermatological Treatment","Eyes","Immune Deficiencies","Infectious Diseases","Liver Disease","Neurological","Neurotology","Pain Management","Rare Diseases","Respiratory Diseases","Women's Health"

Overview = pd.read_excel(READ,sheet_name='Overview', header=None)
OverviewCols = pd.read_excel(READ, sheet_name='Overview', header=None,nrows=1).values[0]
Overview.columns = OverviewCols

auto = Overview.loc[(Overview['Category']=="Autoimmune Diseases")]
auto.to_excel(writer, sheet_name='Auto', startrow= over_row, startcol=over_col, header=False, index=False)

and I would have to repeat for all 17 categories to make each sheet like:

blood = Overview.loc[(Overview['Category']=="Blood")]
blood.to_excel(writer, sheet_name='Blood', startrow= over_row, startcol=over_col, header=False, index=False)

Is there a way to loop through the list to simplify the code? I know this isn't right, but something like:

for i in Categories:
    i = Overview.loc[(Overview['Category']==i)]
    i.to_excel(writer, sheet_name=i, startrow= over_row, startcol=over_col, header=False, index=False)

smex · Accepted Answer · 2021-07-25 19:37:34Z

2

referring to your code:

for i in Categories:
    i = Overview.loc[(Overview['Category']==i)]
    i.to_excel(writer, sheet_name=i, startrow= over_row, startcol=over_col, header=False, index=False)

The variable "Categories" is a tuple of strings - you are trying to use a string "i" as a variable e.g.:

"Blood" = Overview.loc[(Overview['Category']=="Blood")

which won t work.

Have your tried to use a free variable name?

for cat in Categories:
    test = Overview.loc[(Overview['Category']==cat)]
    test.to_excel(writer, sheet_name=cat, startrow=over_row, startcol=over_col, header=False, index=False)

answered Jul 25, 2021 at 19:37

smex

463 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashley Adams Over a year ago

I did try that, thanks for having me try it again! Silly mistake, I guess I didn't read my error very closely earlier. It wasn't working because I had : in some of the category names and xlsxwriter doesn't like that character.

smex Over a year ago

as an alternative to xlsxwriter you could try openpyxl - dunno why but i prefer it

Collectives™ on Stack Overflow

Create multiple sheets in excel using pandas to loop through sheet names

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related