Python Pandas Tutorial: Split Excel Data into Worksheet by Column Values #5

In this video we will learn how to Split Excel Data into Worksheet by Column Values. You will explore how to split excel column's data into multiple worksheet in the same workbook using Python Pandas.

[vimalchawla]

Excellent, the best on the subject. Good job!

[NinadNakhwa]

This video deserves more Likes
Gonna share it with my Friends
Thank You Sir

[jasonturingan]

Thank you sir, What yo have done is easier than VBA. please create more automation technique like this one.. Ill be following all your tutorials. thanks again

[kevinthomas]

Hello, thanks for the video!
Is there a way to create multiple tabs that include copies of certain variables from the main dataset?

[avinashkendre]

How to use diifferent environmnets for python like jupyter notbook nad other idles or how to extract .exe file for one of the smal automation project??

[sezerc.]

If as you write writer.save() and got error, try writer.close() instead of it.

[nandinivyas]

What if we want to split data in different workbooks instead of sheets?

[dilipinamdar]

Nice Concept.. i am getting
In [ * ] due to * (star) not getting result please suggest

[nevilledoke]

for example u have 1 excel sheet and it consist of 10000 data in it. Later when we import that excel file in pycharm or jupiter notebook. if i run that file i will get an Index range also know as Row labels. my python code should be able to read that ten thousand row labels and should be able to separate / split into 10 different excel sheet files which will have 1000 data in each of the 10 saperated sheet.
other example is, if there is 9999 data in 1 sheet then my python code should divide 9000 data in 9 sheet and other 999 in other sheet without any mistakes.





i am asking this because in my data there is not any unique values for my code to split the files using .unique




plz help i have search the whole YT, stackoverflow, and github tooo from 3 days

[Intellectual_House]

i have also another question
what's the difference between engine= xlsxWriter and openpyxl ??

[teoshaoheng]

Hi, Thanks for the video, but I am having problem programming a format for header and row columns, could you provide me with some tips?

[imhqxxe]

Thanks for that great tutorial! It seems to work but one of my "state" entries has >31 chars and cannot be used as sheetname. All entries follow the same pattern like "State_NewYork", "State_NewJersey", ... Is there a way to get rid of the "State_" before creating the Excel sheets, so it can be used as a sheetname?

[waveekewl]

Why cant we save the split data in the same workbook, just asking!

[daillengineer]

my file isn't created. followed everything letter for letter. at the top of the Jupiter notebook I get a little red box that says error, but I have no idea what the error is. frustrated.

[SreekumarNair]

If we have start date and end date, Is it possible to split the data into rows for each month

[karthigeyana]

Hi, I ran the code for my data set. But I can get only one sheet .Please find the below code I ran.

writer=pd.ExcelWriter("FT_data.xlsx", engine='xlsxwriter')
for Pro in df['product'].unique():
newdf=df[df['product']==Pro]
newdf.to_excel(writer, sheet_name=Pro, index=False)
writer.save()

[mahmoudihocine]

Would you explain this line: df[df['Location'] == state]
Why 2 df not only one?
thanks very much

[Intellectual_House]

i didn't underrstand what

df[[df['Location'] ] == state

[arupbanerjee]

I have ran the code for state but it has splited one state only