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

[@NinadNakhwa]

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

[@victor_yahoo]

Excellent, the best on the subject. Good job!

[@ghalifahmi1220]

Excelent video. Id like to do the same but split it by year. The only problem is that the format of my column is day/month/year. Do I create another column with the year only or is there a more efficient way?. Thanks again your content helped me a lot.

[@ProgrammingIsFunn]

No need to create a new column for extracting year. You can extract year from that column directly and filtered. You have to use strftime and strptime functions you have to utilised.

[@jasonturingan3279]

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

[@himanshupandya84ify]

Nicely Explained, well detailing

[@waveekewl27]

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

[@idopshik]

That's super. Very clear.

[@Intellectual_House]

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

[@ProgrammingIsFunn]

Hi, xlsxwriter mainly for writing excel files and it doesn’t support for reading and modifying existing excel files, so it is for writing excel files. Where as openpyxl for reading and writing excel files. So if you are looking for modifications the existing excel file then I would recommend for openpyxl

[@avinashkendre711]

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??

[@dilipinamdar5523]

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

[@nandinivyas3295]

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

[@ProgrammingIsFunn]

Initially, you need to filter the data according to specific criteria and store it in a dataframe. Afterward, export that dataframe in the form of an Excel workbook. For an example: import pandas as pd # Sample data data = {'Name': ['Nandini', 'Eric', 'Modi', 'Alice'], 'Age': [28, 35, 22, 30], 'Salary': [50000, 60000, 45000, 70000]} # Creating a DataFrame df = pd.DataFrame(data) # Filtering data based on age criteria (e.g., selecting individuals older than 25) filtered_df = df[df['Age'] > 25] # Exporting the filtered DataFrame to an Excel workbook filtered_df.to_excel('filtered_data.xlsx', index=False) I hope this example will help you. Thanks for watching and Keep Learning.

[@kevinthomas4911]

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

[@mahmoudihocine2837]

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

[@ProgrammingIsFunn]

Hi Mahmoudi, thanks for your comment, using df['Location'] == State by this method we are applying the filter and it will return the Boolean series (True or False) and the Boolean series we applied within the dataframe 'df' : df[df['Location'] == State]. For more information please watch this entire video it will help you. kzbin.info/www/bejne/oGW3Z4KtntKZfLs

[@mahmoudihocine2837]

@@ProgrammingIsFunn Thanks man. you're the best

[@ProgrammingIsFunn]

@mahmoudi hocine Thanks for motivate me. 👍😊

[@RafaelaW-x6t]

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?

[@ProgrammingIsFunn]

Yeah why not, you can remove the state word and rename the excel sheet by only states name

[@sezerc.4355]

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

[@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.

[@ProgrammingIsFunn]

Share your error then I can give my input on it.

[@datascience1036]

Nice Concept.👍

[@nevilledoke6683]

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

[@SreekumarNair]

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

[@ProgrammingIsFunn]

Yes it is possible, simple you need to extract the month from date and split based on month name.

[@testsample1005]

from date you should try to get month name using strptime and strftime then you can split the data based on month. like datetime.datetime.strptime("25 Dec 2020","%B"), or this video help you to split the data based on any column.

[@santoshgujar5237]

Thank you, Sir

[@teoshaoheng4879]

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?

[@ProgrammingIsFunn]

Hi use append method so that your column will auto arrange when you will combine the data.

[@SaurabhGupta-ro1th]

@@ProgrammingIsFunn can you provide me assistance to learn data science

[@karthigeyana1807]

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()

[@atulsingh6463]

Not sure if you already got the solution but writer.save() needs to be out of the for loop.

[@TevfikBagcivan]

Thank you!

[@arupbanerjee8132]

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

[@ProgrammingIsFunn]

Means you have not applied the correct loop, can you share your code here whatever you have written

[@Intellectual_House]

i didn't underrstand what df[[df['Location'] ] == state

[@ProgrammingIsFunn]

This line will not work, df[df[“Location”] == state] : This is correct statement. However, it is filtering the data or dataframe based on state