Using Python And Pandas To Split Excel Worksheet Into Separate Worksheets

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

2

I need a script to split a master worksheet (that contains over 50K rows) into separate worksheets that contain only 40 rows with no headings

After a bit of research I’ve managed to create a script that splits the master worksheet. However, each of the worksheets contains the original heading and the rows are not split into 40 rows per worksheet.

I believe that when you split a worksheet using panda with data frames they will always contain a heading? Any suggestions on how my python script can be modified to achieve what I need or is there a simpler way to achieve this without the need to use pandas and data frames?

Here is a link: https://github.com/lblake/sample-data to the some sample data

path = input('Enter file path to workbook name and extension, 

e.g. example.xlsx: ')

chunksize = int (input('Enter the row number you want to split the excel sheet at: ') )

destination = input('Enter folder path to where you want the split files stored. Press Enter to save in current location: ')



i = 0

df = pd.read_excel(path)

for chunk in np.array_split(df, len(df) // chunksize):

    chunk.to_excel(destination + 

'file_{:02d}.xlsx'.format(i), index=True)

i += 1 

python pandas dataframe

share|improve this question

asked Jan 3 at 13:59

pythlanxpythlanx

4214

add a comment | 

2

I need a script to split a master worksheet (that contains over 50K rows) into separate worksheets that contain only 40 rows with no headings

After a bit of research I’ve managed to create a script that splits the master worksheet. However, each of the worksheets contains the original heading and the rows are not split into 40 rows per worksheet.

I believe that when you split a worksheet using panda with data frames they will always contain a heading? Any suggestions on how my python script can be modified to achieve what I need or is there a simpler way to achieve this without the need to use pandas and data frames?

Here is a link: https://github.com/lblake/sample-data to the some sample data

path = input('Enter file path to workbook name and extension, 

e.g. example.xlsx: ')

chunksize = int (input('Enter the row number you want to split the excel sheet at: ') )

destination = input('Enter folder path to where you want the split files stored. Press Enter to save in current location: ')



i = 0

df = pd.read_excel(path)

for chunk in np.array_split(df, len(df) // chunksize):

    chunk.to_excel(destination + 

'file_{:02d}.xlsx'.format(i), index=True)

i += 1 

python pandas dataframe

share|improve this question

asked Jan 3 at 13:59

pythlanxpythlanx

4214

add a comment | 

2

2

2

I need a script to split a master worksheet (that contains over 50K rows) into separate worksheets that contain only 40 rows with no headings

After a bit of research I’ve managed to create a script that splits the master worksheet. However, each of the worksheets contains the original heading and the rows are not split into 40 rows per worksheet.

I believe that when you split a worksheet using panda with data frames they will always contain a heading? Any suggestions on how my python script can be modified to achieve what I need or is there a simpler way to achieve this without the need to use pandas and data frames?

Here is a link: https://github.com/lblake/sample-data to the some sample data

path = input('Enter file path to workbook name and extension, 

e.g. example.xlsx: ')

chunksize = int (input('Enter the row number you want to split the excel sheet at: ') )

destination = input('Enter folder path to where you want the split files stored. Press Enter to save in current location: ')



i = 0

df = pd.read_excel(path)

for chunk in np.array_split(df, len(df) // chunksize):

    chunk.to_excel(destination + 

'file_{:02d}.xlsx'.format(i), index=True)

i += 1 

python pandas dataframe

share|improve this question

asked Jan 3 at 13:59

pythlanxpythlanx

4214

I need a script to split a master worksheet (that contains over 50K rows) into separate worksheets that contain only 40 rows with no headings

After a bit of research I’ve managed to create a script that splits the master worksheet. However, each of the worksheets contains the original heading and the rows are not split into 40 rows per worksheet.

I believe that when you split a worksheet using panda with data frames they will always contain a heading? Any suggestions on how my python script can be modified to achieve what I need or is there a simpler way to achieve this without the need to use pandas and data frames?

Here is a link: https://github.com/lblake/sample-data to the some sample data

path = input('Enter file path to workbook name and extension, 

e.g. example.xlsx: ')

chunksize = int (input('Enter the row number you want to split the excel sheet at: ') )

destination = input('Enter folder path to where you want the split files stored. Press Enter to save in current location: ')



i = 0

df = pd.read_excel(path)

for chunk in np.array_split(df, len(df) // chunksize):

    chunk.to_excel(destination + 

'file_{:02d}.xlsx'.format(i), index=True)

i += 1 

python pandas dataframe

python pandas dataframe

share|improve this question

asked Jan 3 at 13:59

pythlanxpythlanx

4214

share|improve this question

asked Jan 3 at 13:59

pythlanxpythlanx

4214

share|improve this question

share|improve this question

asked Jan 3 at 13:59

pythlanxpythlanx

4214

asked Jan 3 at 13:59

pythlanxpythlanx

4214

asked Jan 3 at 13:59

pythlanxpythlanx

4214

4214

add a comment | 

add a comment | 

2 Answers
2

active

oldest

votes

1

You can use groupby and iterate. To ignore headers, specify header=False when writing to a pd.ExcelWriter object. The below example splits a dataframe of 10 rows into 2-row chunks.

df = pd.DataFrame(np.arange(100).reshape((10, 10)))



writer = pd.ExcelWriter('file.xlsx')



for key, grp in df.groupby(df.index // 2):

    grp.to_excel(writer, f'sheet_{key}', header=False)



writer.save()

share|improve this answer

edited Jan 3 at 16:48

answered Jan 3 at 14:21

jppjpp

103k2167117

add a comment | 

0

I just copied your code and added header=False

path = input('Enter file path to workbook name and extension, 

e.g. example.xlsx: ')

chunksize = int (input('Enter the row number you want to split the excel sheet at: ') )

destination = input('Enter folder path to where you want the split files stored. Press Enter to save in current location: ')



i = 0

df = pd.read_excel(path)

for chunk in np.array_split(df, len(df) // chunksize):

    chunk.to_excel(destination + 

'file_{:02d}.xlsx'.format(i), index=True, header=False)

i += 1 

and it worked for me.

share|improve this answer

answered Jan 3 at 14:34

Rodwan BakkarRodwan Bakkar

15711

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks adding the header=False option to my code fixed my issue
  
  – pythlanx
  Jan 5 at 15:51
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023779%2fusing-python-and-pandas-to-split-excel-worksheet-into-separate-worksheets%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

active

oldest

votes

active

oldest

votes

1

You can use groupby and iterate. To ignore headers, specify header=False when writing to a pd.ExcelWriter object. The below example splits a dataframe of 10 rows into 2-row chunks.

df = pd.DataFrame(np.arange(100).reshape((10, 10)))



writer = pd.ExcelWriter('file.xlsx')



for key, grp in df.groupby(df.index // 2):

    grp.to_excel(writer, f'sheet_{key}', header=False)



writer.save()

share|improve this answer

edited Jan 3 at 16:48

answered Jan 3 at 14:21

jppjpp

103k2167117

add a comment | 

1

You can use groupby and iterate. To ignore headers, specify header=False when writing to a pd.ExcelWriter object. The below example splits a dataframe of 10 rows into 2-row chunks.

df = pd.DataFrame(np.arange(100).reshape((10, 10)))



writer = pd.ExcelWriter('file.xlsx')



for key, grp in df.groupby(df.index // 2):

    grp.to_excel(writer, f'sheet_{key}', header=False)



writer.save()

share|improve this answer

edited Jan 3 at 16:48

answered Jan 3 at 14:21

jppjpp

103k2167117

add a comment | 

1

1

1

You can use groupby and iterate. To ignore headers, specify header=False when writing to a pd.ExcelWriter object. The below example splits a dataframe of 10 rows into 2-row chunks.

df = pd.DataFrame(np.arange(100).reshape((10, 10)))



writer = pd.ExcelWriter('file.xlsx')



for key, grp in df.groupby(df.index // 2):

    grp.to_excel(writer, f'sheet_{key}', header=False)



writer.save()

share|improve this answer

edited Jan 3 at 16:48

answered Jan 3 at 14:21

jppjpp

103k2167117

You can use groupby and iterate. To ignore headers, specify header=False when writing to a pd.ExcelWriter object. The below example splits a dataframe of 10 rows into 2-row chunks.

df = pd.DataFrame(np.arange(100).reshape((10, 10)))



writer = pd.ExcelWriter('file.xlsx')



for key, grp in df.groupby(df.index // 2):

    grp.to_excel(writer, f'sheet_{key}', header=False)



writer.save()

share|improve this answer

edited Jan 3 at 16:48

answered Jan 3 at 14:21

jppjpp

103k2167117

share|improve this answer

share|improve this answer

edited Jan 3 at 16:48

edited Jan 3 at 16:48

edited Jan 3 at 16:48

answered Jan 3 at 14:21

jppjpp

103k2167117

answered Jan 3 at 14:21

jppjpp

103k2167117

answered Jan 3 at 14:21

jppjpp

103k2167117

103k2167117

add a comment | 

add a comment | 

0

I just copied your code and added header=False

path = input('Enter file path to workbook name and extension, 

e.g. example.xlsx: ')

chunksize = int (input('Enter the row number you want to split the excel sheet at: ') )

destination = input('Enter folder path to where you want the split files stored. Press Enter to save in current location: ')



i = 0

df = pd.read_excel(path)

for chunk in np.array_split(df, len(df) // chunksize):

    chunk.to_excel(destination + 

'file_{:02d}.xlsx'.format(i), index=True, header=False)

i += 1 

and it worked for me.

share|improve this answer

answered Jan 3 at 14:34

Rodwan BakkarRodwan Bakkar

15711

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks adding the header=False option to my code fixed my issue
  
  – pythlanx
  Jan 5 at 15:51
  

  
  
  
  

add a comment | 

0

I just copied your code and added header=False

path = input('Enter file path to workbook name and extension, 

e.g. example.xlsx: ')

chunksize = int (input('Enter the row number you want to split the excel sheet at: ') )

destination = input('Enter folder path to where you want the split files stored. Press Enter to save in current location: ')



i = 0

df = pd.read_excel(path)

for chunk in np.array_split(df, len(df) // chunksize):

    chunk.to_excel(destination + 

'file_{:02d}.xlsx'.format(i), index=True, header=False)

i += 1 

and it worked for me.

share|improve this answer

answered Jan 3 at 14:34

Rodwan BakkarRodwan Bakkar

15711

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks adding the header=False option to my code fixed my issue
  
  – pythlanx
  Jan 5 at 15:51
  

  
  
  
  

add a comment | 

0

0

0

I just copied your code and added header=False

path = input('Enter file path to workbook name and extension, 

e.g. example.xlsx: ')

chunksize = int (input('Enter the row number you want to split the excel sheet at: ') )

destination = input('Enter folder path to where you want the split files stored. Press Enter to save in current location: ')



i = 0

df = pd.read_excel(path)

for chunk in np.array_split(df, len(df) // chunksize):

    chunk.to_excel(destination + 

'file_{:02d}.xlsx'.format(i), index=True, header=False)

i += 1 

and it worked for me.

share|improve this answer

answered Jan 3 at 14:34

Rodwan BakkarRodwan Bakkar

15711

I just copied your code and added header=False

path = input('Enter file path to workbook name and extension, 

e.g. example.xlsx: ')

chunksize = int (input('Enter the row number you want to split the excel sheet at: ') )

destination = input('Enter folder path to where you want the split files stored. Press Enter to save in current location: ')



i = 0

df = pd.read_excel(path)

for chunk in np.array_split(df, len(df) // chunksize):

    chunk.to_excel(destination + 

'file_{:02d}.xlsx'.format(i), index=True, header=False)

i += 1 

and it worked for me.

share|improve this answer

answered Jan 3 at 14:34

Rodwan BakkarRodwan Bakkar

15711

share|improve this answer

share|improve this answer

answered Jan 3 at 14:34

Rodwan BakkarRodwan Bakkar

15711

answered Jan 3 at 14:34

Rodwan BakkarRodwan Bakkar

15711

answered Jan 3 at 14:34

Rodwan BakkarRodwan Bakkar

15711

15711

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks adding the header=False option to my code fixed my issue
  
  – pythlanx
  Jan 5 at 15:51
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks adding the header=False option to my code fixed my issue
  
  – pythlanx
  Jan 5 at 15:51
  

  
  
  
  

Thanks adding the header=False option to my code fixed my issue

– pythlanx
Jan 5 at 15:51

Thanks adding the header=False option to my code fixed my issue

– pythlanx
Jan 5 at 15:51

add a comment | 

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54023779%2fusing-python-and-pandas-to-split-excel-worksheet-into-separate-worksheets%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here