Separate Excel Data into Workbooks by Column Values - Python Pandas Tutorial
HTML-код
- Опубликовано: 6 сен 2024
- 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 --
/ derricksherrill
Join The Socials --
Reddit - / codewithderrick
FB - / codewithderrick
Insta - / codewithderrick
Twitter - / codewithderrick
LinkedIn - / derricksherrill
GitHub - github.com/Der...
*****************************************************************
Workbook -
drive.google.c...
Full code from the video:
import pandas as pd
excel_file_path = 'training_status.xlsx'
df = pd.read_excel(excel_file_path)
print(df)
split_values = df['Shift'].unique()
print(split_values)
for value in split_values:
df1 = df[df['Shift'] == value]
output_file_name = "Shift_" + str(value) + "_Trainings.xlsx"
df1.to_excel(output_file_name, index=False)
github.com/Der...
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:
github.com/Der...
Check out my website:
www.derrickshe...
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?
Atom - atom.io/
What Equipment do you use to film videos?
www.amazon.com...
What editing software do you use?
Adobe CC - www.adobe.com/...
Premiere Pro for video editing
Photoshop for images
After Effects for animations
Do I have any courses available?
Yes & always working on more!
www.udemy.com/...
Where do I get my music?
I get all my music from the copyright free RUclips audio library
www.youtube.co...
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!
Randomly clicked on this video and this was the EXACT problem I needed to solve at work. You're the best!!
Dont know if you guys gives a damn but if you are stoned like me during the covid times then you can watch pretty much all of the new movies and series on Instaflixxer. Have been streaming with my girlfriend these days xD
@Marcel Casey Yea, have been using Instaflixxer for since november myself :D
Thanks for providing the dataset in your newer tutorials.
Oh my god! Getting into Data Analysis and your tutorials are so nice and smooth! Everything one needs! Thank you so much! :D
Great video Derrick. Have you thought about putting all these excel-python videos into a PDF? Great work makes it look so simple
Hey Derrick,
Great short and well explained video with real working life issues! Thanks!
You are the best!!! Thanks a lot. Many people try, but only a few can make it simple and sweet.
Nice! Could you please illustrate these activities in SQL datasets too please?
Hi Derrick, thanks for the video, one of most useful yet.
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 = 'C:/Users/XYZ/Downloads/training_status.xlsx'
variable = 'Shift'
df = pd.read_excel(excel_file_path)
# 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")
Your tutorial is very clear. Love it
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.
Man you are great please keep it up - I'm learning so much from you
Thanks a lot Derrick. This saves a lot of manual hours for me.
oh my god, this was the best thing I found yesterday.
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.
I am a very beginner in Python. never used Pandas, I still got that 100%
Great video and clear explanation. With your help I was able to separate my worksheet that has 20000 rows. Thank you
Hi derrick Sherrill,
you are expert in excel with python, and your way of teaching is really awesome. just yesterday i was searching excel with python for my problem and i found your channel, immediate I start following your channel.
I already posted this problem in your excel automated video, but I thought this is the right place to rise this question
but boss, I am in a problem... and I am pretty much sure it will be easy for you. if you could guide me to do so.
or make a video for this issue. for example.. I am having a
list "mango, apple, orange, apple, banana orange, mango, orange, apple, banana, orange, apple, pineapple, apple, banana, apple, orange, apple, mango, apple, banana, strawberry, orange, mango, banana, orange, mango, orange, guava,"
my task is to create a excel sheet and count each fruit and assign their count for example.. mango = , banana = , orange = , pineapple = , apple = ,
but I want each fruit in first column of rows and their count in second column of rows.
and in addition how to sort second column of rows to it's ascending or descending order.
my email id is rashidyasin089@@t
hope you will solve this problem. and guide for the same and if you will like to make a video on this topic.
Thanking you in advance
Rashid Yasin
Really very great tutorial thank you so much for this 👏🏻
Hey Derrick, Thanks for all you are doing.. Can you please make a video on deleting excel rows based on some conditions, I've tried but failing continuesly...
Thank you so much! You just saved me a lot of work. I want to share these files in an email to the respective column value(let's say they are different people). How can we do that?
Hi Derrick,
Many thanks for this video, and for all of your videos! On this one, how would you output each df to separate sheets in the same workbook?
Hi Kieran, I watch Derricks videos too because they demonstrate some really good ideas :).
I was thinking of the same question, so I adapted Derricks solution to the below - should be close to what you need. Hope this helps.
import pandas as pd
df = pd.read_excel('training_status.xlsx')
split_values = df['Shift'].sort_values().unique()
with pd.ExcelWriter('train_status_split.xlsx'
) as writer:
for value in split_values:
(df.query(f'Shift == {value}')
.drop(columns='Shift').copy()
.to_excel(excel_writer=writer, sheet_name=f'Shift{value}', index=False))
cordula raecke
Hey Cordula,
Thanks for the reply! I’ll give that a try. Appreciate you taking the time to post this.
@@walnut2112 welcome! I have learned so much from Derrick and trying things out... sorry for my previous messy solution ... just revised see below - hope it makes sense, nice evening! :).
import pandas as pd
with pd.ExcelWriter('train_status_split.xlsx') as writer:
df = pd.read_excel('training_status.xlsx')
split_values = df['Shift'].sort_values().unique()
for value in split_values:
(df.query(f'Shift == {value}')
.drop(columns='Shift').copy()
.to_excel(excel_writer=writer,
sheet_name=f'Shift{value}',
index=False))
You all are too kind! Great work Cordula, your solution looks awesome.
Could you do a video about Pandas Indexing. I’m used to Indexing for relational databases, so I’m confused by what Python means by indexing...
Awesome, you make it look like a piece of cake! Great video as always!!! Appreciate you!!!
Just another helpful video as usual...Thx 👍👍👏👏👏
Hello Derrick. i really like the way you communicate and explain the python language. However, I ve seen that you are using only pandas for reading excel workbooks. I would like to ask you why you are not using xlrd at all? I am building a database with data exported from multiple workbooks and it is my understanding that xlrd offers the capability to insert data to a database hosted in MySql. Furthermore, based on your deep know-how in data sets, which functionality of python would you use to export DATES data from excel to a MySQl database? Thanks for sharing your knowledge!
This is exactly what I needed; thank you
This is a great channel...you make it look so easy...nice work. I learn a lot here...
Thank you so much. This video is what i need right now. But i have a question: how we can choose where the file will be saved after separate? my file auto save to D:
hello derrick, please make videos on basic tutorials, I'm a beginner i desperately need basic guide. I think no one can guide us better than you
Hi Derrick, I was wondering would it be possible to implement mkdir before defining the path and name for each new excel output file ? In other words, I'm handling here a lot of data and I would like to have each file saved in its own folder. And that folder naming would be perfect if I can set the name like you did in the example + HAMD5 generated identified in the folder name ends. I would be eternally grateful if you could give me a tip or direct me to something specific.
Thanks for this, well explained and functional. Much appreciated
Your videos are amazing..thanks bro!
Hi Derrick...can you please do some videos with datasets from kaggle as it will be easier for us to download the same dataset and practice whatever you code with them
Hi Derrick I need to know how to make multiple sheets in a single workbook depending on the values of the columns.
Hello. Thank you so much.
Can you help me with this.
I want to search a cell value in a range from a column say A1 and compare the value to another excel.
If cell value is found in a range, then save the cell value of B1 to another excel file
Excel file #1 contains "test" at A1
Excel file#2 contains range values at A and B contains range values too
Search the value from Excel file#2 if it is equal to the value of excel file#1 which is "test"
If they are equal, display the value of column B and save it to another excel file
Hey Derrick, great videos! I was wondering if you know a way to move multiple graph analysis conducted in python and moved to a html report of those graphs. Would be great to see a video on it. I'm using holoviews at the moment!
Hello Derrick. Thanks for the video. Can you explain (in the example you presented) how in above to create different sheets in same excel file instead of creating 3 different excel files. Thank you.
thank you for the video. Any idea on how to seperate multiple file in single file with multiple tabs(like file one in 1st sheet, 2nd in 2nd sheet etc)
Thank You. great andeasy explanation
Thanks Derrick. Very clear.
How can you split columns like name into First and Last name. Or if city,st, zip is in one column and you need them in their own columns?
soooo grateful!
If we don't need that shift column in the sheets, what to do for that
Hello 👋 do the new files sync or cabeable to refresh in another shared folder for different users
Perfect!
Hi Derrick, Many thanks for sharing this, really very helpful. Can you also do something on how to work on time formats? How to convert time format, fix missing format etc.? Thanks again,
Thank You for this wonderful and simple video
Helpful! Thanks!
Hi
How we can match data of multiple columns( as per column headers) with a base file ? I Meant, instead of making so many Vlookups...
hy Derik, I need helpin this :-
I want to use(" for and if "loop) to iterrow value one by one & also check empty cell if exists and put error message on notepad if empty cell found with the cell index. Hope you see my comment. thank u!
Thanks. Very useful and simple display 🙏👍
If i want to save files using askdirectory(), using tkinter, can u help
thank you! helped me a lot.
Question: what if I need to filter in two columns?
Great idea, thanks you
Hi Derrick Sherrill,
Some error. Where I followed the same steps.
split_values=df["Shift"].unique()
print(split_values)
[1 3 2]
for value in split_values
File "", line 1
for value in split_values:
SyntaxError: unexpected EOF while parsing
Please can you help with how to split a single excel sheet into multiple sheets using python
Hello do you have a tutorial like this but getting only 1 separate Excel Data
Hi Derrick, Thanks for the code! I tried it, it is working to print one dataframe, when i tried to print one more dataframe from XX rows, it is printing 2nd or last dataframe only. Can you please help how to resolve it? code used in for loop: df.to_excel(output_file_name,'Sheet1', header=False,index=True, startrow=0)
df2.to_excel(output_file_name,'Sheet1', header=False,index=True, startrow=2)
Hi, can we output the data to the same file but to separate sheets?
this is awesome, thank you
The program finished with no errors. But didn't create the files. I am using open office. Sorry about that I just looked up at the pycharm files listing in and there they were. Shifts 1,2,3 listings
Thank you Derrick.
OMG, this would have taken a lot more code using VBA. Amazing!!
Hi Derrick... Good Video. May i know how to name the excel sheets as shift [1,2,3] along with the filenames. Also I need only one column value mapped to this shift.
Thank you very much Man
Derrick, what is the best way to combine (sum of ) multiple lines into another workbook? I have a data dump of AP information with many of the same vendor and would like to combine the total amount into another spreadsheet (specific column).
HII DERRICK
Thanks for the video
In excel sheet I have 12 months data I want to plot for only one month data
how can can I do it
PLZZ REPLY
Thanks for sharing, Also i have a list of phone numbers in excel column, i need python code to extract those numbers to the notepad sepetared with comma like 087xxxxxx,087xxxxxx
very helpful thank you
Hi Derrick, I have watched all your videos in this Excel automation with python series. Ur videos are awesome especially for total beginner like me.Can u make a video on how to filter a specific float values (within a range) from column of a dataframe and print it's corresponding values
My Excel file has 9 columns and each has float values .I hv to filter it then hv to make another sheet
Hi, Thanks for Video, However I need to supply another column with fix value in filter, How can I do that?
I have a database of employee shift pattern and the shifts can be unique (5 shifts max as the employee can work 5 days a week). Below is my code which fetch results for an employee and update the tKinter boxes with those values ; however, the issue is that it adds curli braces({}) before and end of the string/answers... My question is that how can I remove those braces...? Someone told me to convert the list into set(), but a set consists of unique data and in my case data can be unique for a max of 5 days... Please help me how to remove those brackets. I have been working on this for last 6 hrs but no luck.
def showRecord(self):
connection = sqlite3.connect("../employee.db")
connection.text_factory = sqlite3.OptimizedUnicode
cursor = connection.cursor ()
cursor.execute ( '''SELECT "Scheduled Shift" FROM employee_details WHERE Ecode = "5568328"''' )
items = cursor.fetchall ()
self.Employee1_FirstDay_ActualShift.set(items[0])
self.Employee1_SecondDay_ActualShift.set(items[1])
self.Employee1_ThirdDay_ActualShift.set(items[2])
self.Employee1_FourthDay_ActualShift.set(items[3])
self.Employee1_FifthDay_ActualShift.set(items[4])
self.Employee1_SixthDay_ActualShift.set(items[5])
self.Employee1_SeventhDay_ActualShift.set(items[6])
connection.commit ()
connection.close ()
i.stack.imgur.com/wvoPZ.png (link to pic)
Hey Derrick, Great work buddy. I have scenario for you : let's say you have multiple excel files with same type of data but the column names are not standardized, sequenced properly. How can we use pandas to standardize them and load them into a different excel file stacked vertically
Please make a video of excel file in python for calculation of stock inventory with profit & loss i
Could you do a video about programming an excel sheet by python to read 2 numbers from 2 ceratin cells then add them then drag the answer in another certain cell ?
Hi Derrick,
I am using pivot table to get a report from a excel. If I can replace it with python since using the pivot I am arriving nearly 4 to five reports. Mainly in one report I need to count values in one column and sum the numerical values in different columns and get the percentage of two columns and out put to be in one column. Can this be done using python?
how to extract only few columns and cells from a input excel and obtain a new format excel...please make a video related to this...
hi, can we split a value in a cell eg(2 A Z 4) into 4 different rows?
not column ---- Rows
Hey Derrick, Thanks for this awesome concept. But i have one doubt, can you please confirm me where we have to locate this file i mean in that same folder where python is installed or python will automatically detect this file from system?
Thank You :)
why is it that when I use the code, ot doesnt read or write xlsx only xls. What can I do to change that? please help!
I need to know whether this can be automated or not: I get one raw data details on excel sheet on mail with attachment, this attached excel sheet gets automatically downloaded to certain location and then macro runs on that excel file as per schedule and then that excel sheet is send to several email addresses as per schedule without human intervention? What is required to do this automation?
I have been working on a text file which i open in excel and it gives 5 values in a single cell and for entire colum i am getting such results how to sivide that sigle column in 5 columns?
Hi Derrick, thanks for your videos!
How to use pandas and probably numpy min function to compare 3 excel columns with different values and find out the minimum value in each row? I use these code as a starting point:
filename = 'Supplier_prices_comparison.xlsx'
df1 = pd.read_excel(filename)
a = df1.iloc[1:-1, 7]
b = df1.iloc[1:-1, 10]
c = df1.iloc[1:-1, 13]
# I don't know what to do next. If you can please help me!
what if i want to split the work book by (number of rows) , example , I want to split an workbook with 10k rows into 5 other workbooks each 2k rows
Thanks Derrick, please also help us how to load this output file in SQL Server as Tables.
how do i do if iwant to put the rsults of the filtering in the same excel files swith multiple workbook
Hi Derrick! Your videos are excellent. I have subscribed to your channel. I've watched a few and searched in the web but I am still not sure about the following problem. Comparing two excel files that have different rows and columns and the number of columns and rows are different too. I've tried merge but I have not been able to fully understand the output. One file has 1,013 rows, the other 411. But when I merged the two of them using outer (as per one of your videos using a common column), the output gives me 1,035. I know that python creates dup rows under certain conditions but I have not found why and, more importantly, how to find them.
In essence, I want to compare the two files and clearly breakdown all the rows that are equal, and clearly identify the ones that are different for reconciliation.....There are a few videos that deal with this by even adding the two different values in the same cells which will solve my problem. However, they are constrained by having to have the exact same number of columns and rows as well as column names. Your help is appreciated!
how can i save it in a specific folder?
Hi ,Derrick can you share me how to remove connection busy issue in pyodbc
Hi Derrick, that is great
please can you a new excel file contain all shift in second excel work book and multiple sheets on same example
Please help 😃
Hi Derick, can we write a code to separate a cell value which has delimiter into different columns.
eg : cell A contains asds.dffd.234.12re
Can this be split into 4 different columns using pandas? can we have a video on that?
base on the code, is there any possibility to specify the output directory, the name is generated base on cell and so I can not put the full directory on it, can you pls teach me how. THx.
df1.to_excel("D:\",(output_file_name), index=False), i put something like this and it seems not workable., pls help
great.
import pandas as pd
ModuleNotFoundError: No module named 'pandas'
how can I fix this?
AWESOME DERRICK AND THANK YOU!! Is there a way we can auto size the columns. It works like a charm. I'm think a for loop to interate each column but seems long way.
Have had same issue in the past using xlsxwriter (which is an awesome library) ... perhaps this stackoverflow might give you some ideas how to approach issue stackoverflow.com/questions/29463274/simulate-autofit-column-in-xslxwriter
Fyi check out xlwings to autosize worksheets. It has an autofit function. I ended up doing the below. To autosize all worksheets in a workbook.
df = pd.Excel(file_name)
wb = xw.Book(file_name)
sheetNames = df.sheet_names
for sheet in sheetNames:
wb.sheets[sheet].autofit()
wb.save(file_name)
wb.close()
Hi Derrick, can you help me to automate copying table from Excel and paste it into mail and send
I can print specific excell sheets on pycharm(pandas) with no problem. But when trying to count rows inside column(Sample):
import pandas as pd
x = pd.read_excel(r"C:\Users
an\PycharmProjects\giraffe\GENERA.xlsx", sheet_name=["Sample Info"], engine="openpyxl")
split_values = x["Sample"].unique()
print(split_values)
I get:
warn(msg) Traceback (most recent call last): File "C:/Users/nan/PycharmProjects/giraffe/genera1.py", line 8, in split_values = x["Sample"].unique() KeyError: 'Sample'
What am I doing wrong? PLEASE HEELLP!!!
How do you do the same output but instead of workbooks you do it by sheets?
did you know How ?
Amazing. Saved my day(S)
How I make this using Openpyxl??? Help please!!!