Separate Excel Data into Workbooks by Column Values - Python Pandas Tutorial

Hey Everyone! In this one we'll talk a look at how we can break down a workbook into multiple other workbooks depending ont he value inside a column.

We do it here for all unique values in one of our columns, but you could do the conditional indexing any way you like. I hope this shows you the baseline of how to do it!

Support the Channel on Patreon --
Join The Socials --
*****************************************************************
Workbook -

Full code from the video:
import pandas as pd

# print(df)

split_values = df['Shift'].unique()
# print(split_values)

for value in split_values:
df1 = df[df['Shift'] == value]

Packages (& Versions) used in this video:
Pandas 0.25.0
Python 3.8

*****************************************************************
Code from this tutorial and all my others can be found on my GitHub:

Check out my website:

If you liked the video - please hit the like button. It means more than you know. Thanks for watching and thank you for all your support!!

--- Channel FAQ --

What text editor do you use?

What Equipment do you use to film videos?

What editing software do you use?
Premiere Pro for video editing
Photoshop for images
After Effects for animations

Do I have any courses available?
Yes & always working on more!

Where do I get my music?
I get all my music from the copyright free Youtube audio library

Let me know if there's anything else you want answered!

-------------------------

Always looking for suggestions on what video to make next -- leave me a comment with your project! Happy Coding!

[taylorhonda]

Randomly clicked on this video and this was the EXACT problem I needed to solve at work. You're the best!!

[antoniosantos]

You are the best!!! Thanks a lot. Many people try, but only a few can make it simple and sweet.

[Tigasever]

Oh my god! Getting into Data Analysis and your tutorials are so nice and smooth! Everything one needs! Thank you so much! :D

[patrickwheeler]

Thanks for providing the dataset in your newer tutorials.

[marlowstamm]

Hey Derrick,

Great short and well explained video with real working life issues! Thanks!

[annakvachko]

oh my god, this was the best thing I found yesterday.

[berkster]

Great video and clear explanation. With your help I was able to separate my worksheet that has 20000 rows. Thank you

[kenannan]

This was great! I finally got this to work. In my instance my data frame only had one value so instead of split_values = df['Shift'].unique(), I did split_values = df['Shift'] and then created the file name. I also used datetime to use in the title. Now to create a tkinter exe so that my staff can use this.

[imperiopts]

Hey Derrick, thanks a lot for the video. It was very usefull for me.

In my case, the process took too long, with 162k records and 78 unique spreadsheets as output. But, it is another thing.
Thanks again.

[mahmoudgamal]

Man you are great please keep it up - I'm learning so much from you

[jinnah]

I am a very beginner in Python. never used Pandas, I still got that 100%

[TheYasinsonu]

Thanks a lot Derrick. This saves a lot of manual hours for me.

[roywilson]

Hi Derrick, thanks for the video, one of most useful yet.

[martin-xqte]

Great video Derrick. Have you thought about putting all these excel-python videos into a PDF? Great work makes it look so simple

[Mustafawi]

Awesome, you make it look like a piece of cake! Great video as always!!! Appreciate you!!!

[vaishnavimalini]

Really very great tutorial thank you so much for this 👏🏻

[randalltaylor]

This is exactly what I needed; thank you

[karan-aulakh]

Hi Derrick,

Thanks for an amazing guide.

I tried to extend the concept to make a more generic solution whereby I have some inputs for each file i.e. the columns i wanna split on, Root directory where I want the results etc. However, while the code works fine, the data is not getting split in the workbooks, I just have headers in the splitted workbooks.

Here's the code for your reference :

root_folder = 'C:/Users/XYZ'/Downloads
excel_file_path =
variable = 'Shift'


df =
# print(df)

split_value = df[variable].unique()
#print(split_value)


for value in split_values:
df1 = df[df[str(variable)] == value]
output_file_name = str(root_folder) + "Data_" + str(value) + ".xlsx"
df1.to_excel(output_file_name, index=False)

print ("Done")

[voodoochili]

Thanks for this, well explained and functional. Much appreciated

[gussta]

This is a great channel...you make it look so easy...nice work. I learn a lot here...