Automate Excel using Python | Excel Hacks with Pandas
ฝัง
- เผยแพร่เมื่อ 4 ต.ค. 2024
- This video is about Excel Hacks with Pandas, automate Excel using python and how to split an excel file into multiple sheets. Also learn to excel hacks 2019
Here is the code on github: github.com/sat...
👉 Patreon: patreon.com/SATSifaction
👉 Facebook Group: / theaiwarriors
👉 Instagram: @theaiwarriors
👉 Corporate Training and Up skilling: levers.ai
Netfirms (Affiliate) - bit.ly/2KdJ4Dp
Linode Server - bit.ly/2XpqGi9
Bluehost (Affiliate) - bit.ly/2GxxBh1
PythonAnywhere (Affiliate) - bit.ly/2kWORVe
Heroku - www.heroku.co
NordVPN (Affiliate) - bit.ly/2W87je0
Here is a link to my python for beginners, master python course: bit.ly/2HIZS42
Favourite Mobile Apps to Run Python and Code
iOS - Pythonista (paid) - apple.co/2HjRVTJ
Android - Dcoder (free) - bit.ly/2Vrjb6N
Song
Credit To Author
Support Cavardix Beats:
/ cavardix
/ cavardixbeats
/ cavardixbeats
www.youtube.co....
-----------------------------------------------------------------
Royalty Free Music - Shadows (Dark Underground Rap Beat Hip Hop Instrumental)
• Video
Coming from a business background, found myself in a situation at work where I can use excel/python to transform our processes - wish me luck!
You can do it!
For those of you asking: YES it can be done with the native Excel tools. Nevertheless, tutorials like this one are great for adding new tools to our toolbox, for certain things some programming languages are simply better suited than others, it's good to have more than 1 weapon in your arsenal.
Two ways to solve this natively:
a) If the categories are not too many, a quick pivot table with anything as values and all the categories as rows will do the trick. Simply double-click in the subtotal for each row and a new sheet will be created with all the table rows pertaining to the specific division only. Rename the sheet and it's done
b) VBA, you can use something like the following macro to get it done, it prompts for the user to provide the table range (including headers) as well as for the user to provide the column to be used as categories (as a string input). You can modify the code if you feel like, it's a bit sloppy but it will do the job.
Sub splitBy()
Dim table, header, colHeader, activeItem, activeRow As Range
Dim field As String
Dim nTimes, currentRow As Long
Dim sht As Worksheet
Set table = Application.InputBox(Prompt:="Select the table including headers", Type:=8)
field = Application.InputBox(Prompt:="Input column name", Type:=2)
Application.ScreenUpdating = False
nCols = table.Columns.Count
nRows = table.Rows.Count - 1
Set header = table.Resize(1, nCols)
Set colHeader = header.Find(field)
table.Sort key1:=colHeader, order1:=xlAscending, header:=xlYes
currentRow = 1
Do While currentRow
Dude, you’re a GOD! I just stumbled across your videos recently and I love your Excel/Pandas/SQL videos. If I study your code carefully, I could potentially adjust it and use it in my work as well, where I often have to run similar scenarios.
You got yourself a new dedicated subscriber!
Thank you 🙏
@@SATSifaction th-cam.com/video/Jg6g6foHzCs/w-d-xo.html
#TenaliRK #FreeHinduTemples
Excellent job!! I wrote a program pretty much exactly like this for a back end batch process that just dumped to a staging table in the morning. All the execs wanted to view it by market, division, and location type so I had to do a couple of groupbys in pandas on a few more columns. Nice to know I am not the only one building little tools like this for work that save so much time lol.
That’s a great video. Kindly show us how you schedule the split excel emailed to concerned owner. That will be really useful and thanks for sharing the knowledge. Your videos boast up the confidence. 🙂
Awesome, thank you! Although I would suggest casting the sheet names to strings, as I ran into type errors while trying out your script. But the rest was spot-on!
Love the video. This is my first of many videos, I want to watch to improve me using Python for Excel. Quick question though, if I wanted to sort the sheets alphabetically, where would I insert that? The column segmentation I have has 20 columns and they are all out of order. I plan on watching more of your videos later today. Thank you.
Nice work clearly explained! If we are using pandas then it would be easier to use the groupby function in pandas!
This was really helpful. Thanks a lot !!
Gonna do this for my dropshipping report...
what product did u do for dropshipping?
This is nice but you can do this with a few lines in VBA.
how so? wouldnt mind seeing it in vba too! thanks!
@@sniper6219 You can refer to my comment. Cheers!
i just wanna thank you for your code and your videos
Not able to download code from GitHub. Can u help me with the code.
Thanks in advance., 🙏
Great video. Thanks so much.
The technique used will also affect the runtime. If u try to filter and split, instead of per cell check, it will greatly improve the runtime
How would you modify it to add for filter and split instead? Curious, because I work with 300-660k row files and that could take a while.
what is the point for the first (while True ) in the last cell , and thankyou
I used to to this kind of tasks with VBA. I've started learning python and I love it, however, is there any advantadge of using python instead of VBA? I find that splitting data with the advanced filter function in Excel is already a very efficient way
Good question. When you think of python think about scaling. VBA in this context is very limited to the workbook you are in. Python enables you to scale by integrating into other platforms with your excel notebook like pure machine learning frameworks, web dev etc. VBA is not scalable like python is.
Excel has a limit in number of rows, it cannot be more than 1 million rows,
If u are handling less data, u can stick back to Excel
If u are handling data like more than 1 million of rows, let's consider using Python
I have used VBA for long time. Now I'm using Python and I love it. However, for this kind of "Office Task" it is less time consuming and more convenient using VBA. This is not about ML or AI at all, it's Application oriented script and VBA's been done for it. Reinventing the wheel, making up the process longer or just making it as a "nice-to-have" tool is not ideal
Hi Sir, this is what exactly i was looking for but finaly i found in your channel, thnak you for making this video.
i am not able to open code.
when i click on the link in discription Git hub says
"Uploads are disabled.
File uploads require push access to this repository."
please help
Thanks man, very clear. Also thanks for sharing. One doubt. The file merge follow some order for dates of that instance? or is all of the first category then all the second category and so on? of course you can sort it later on excel.
Hey man, good tut, but please zoom in/increase text size next time. Can hardly read anything.
This is great. thank you!
You’re welcome
amazing stuff.. your videos deserve more views.
Thank you
Hey, is it possible to keep the formulas when spliting to new files?
Why wasn’t the export filtered with a “WHERE “ clause?
Why are there two for loops in the sendtosheet function?
That was very useful, thank you sir
showing an error "expected string or bytes-like object" do you guys know why?
I did not understand the need for nested for loop for send to sheet function. I rewrote the code like below and it still works. Can someone tell me why we had nested for loops for the same ?
def senttosheet(cols):
copyfile(file, newfile)
writer=pd.ExcelWriter(newfile,engine='openpyxl')
#for j in cols:
for myname in cols:
mydf = df.loc[df[colpick] == myname]
mydf.to_excel(writer, sheet_name=myname, index=False)
writer.save()
print("Completed successfully")
return
Its very useful but not working for me getting invalided argument while giving input file.
Great video.
Thank you
Where u wrote this script Visual studio or spider platform.
this one i believe was pycharm tho I've since moved to sublime
Thanks a lot for sharing!!
great video, how to combine multi excel file with multi sheets
New subscriber here. Thanks for all these amazing content that you have.
JL you’re welcome
@@SATSifaction Complete newb question here but I cant get past the part where I enter the file path. The error text below keeps popping up:
IndexError Traceback (most recent call last)
in
4 pth=os.path.dirname(file)
5 newfile=os.path.join(pth,filename+'_2'+extension)
----> 6 df=pd.read_excel(file)
7 colpick=input('Select Column: ')
8 cols=list(set(df[colpick].values))
C:\Anaconda3\lib\site-packages\pandas\util\_decorators.py in wrapper(*args, **kwargs)
206 else:
207 kwargs[new_arg_name] = new_arg_value
--> 208 return func(*args, **kwargs)
209
210 return wrapper
C:\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, verbose, parse_dates, date_parser, thousands, comment, skip_footer, skipfooter, convert_float, mangle_dupe_cols, **kwds)
338 convert_float=convert_float,
339 mangle_dupe_cols=mangle_dupe_cols,
--> 340 **kwds
341 )
342
C:\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in parse(self, sheet_name, header, names, index_col, usecols, squeeze, converters, true_values, false_values, skiprows, nrows, na_values, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds)
881 convert_float=convert_float,
882 mangle_dupe_cols=mangle_dupe_cols,
--> 883 **kwds
884 )
885
C:\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in parse(self, sheet_name, header, names, index_col, usecols, squeeze, dtype, true_values, false_values, skiprows, nrows, na_values, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds)
440 sheet = self.get_sheet_by_name(asheetname)
441 else: # assume an integer if not a string
--> 442 sheet = self.get_sheet_by_index(asheetname)
443
444 data = self.get_sheet_data(sheet, convert_float)
C:\Anaconda3\lib\site-packages\pandas\io\excel\_xlrd.py in get_sheet_by_index(self, index)
44
45 def get_sheet_by_index(self, index):
---> 46 return self.book.sheet_by_index(index)
47
48 def get_sheet_data(self, sheet, convert_float):
C:\Anaconda3\lib\site-packages\xlrd\book.py in sheet_by_index(self, sheetx)
464 :returns: A :class:`~xlrd.sheet.Sheet`.
465 """
--> 466 return self._sheet_list[sheetx] or self.get_sheet(sheetx)
467
468 def sheet_by_name(self, sheet_name):
IndexError: list index out of range
Thanks for the inspiring and useful video! I appreciate you share the information on how to setup these pips as I did all action described in the video, however after I run the split script it says that pandas is not found. I have updated Python to 3.7.6 version on a Mojave OS.
Glad you liked it
@@SATSifaction sorry, I didn't type all info. So just updated the comment with a question. Appreciate the link on basic packages I need to run the script described in this video. Sorry for such noob request, I would say I almost zero in coding, but advanced in excel.
No problem. That generally means pandas is not installed properly. You can try to uninstall it and reinstall it. Alternately you can set up a virtual environment and run it all in there. It will work without issue if you run it in a virtual environment. Try this video 👉🏻 How to use a Python Virtual Environment the RIGHT way with Jupyter Notebook
@@SATSifaction thanks! Will dig into that!
It's Awesome Loved it
Hi There. Great Tutorial. However if I use Atom editor to run this program I get this error after the file path is entered: Alexanders-Air:Excel_Splitter_Combiner-master lexi$ python combinefiles.py
File Path: /Users/lexi/Downloads/Excel_Splitter_Combiner-master/N_Power_Split_Data/
Traceback (most recent call last):
File "combinefiles.py", line 5, in
file=input('File Path: ')
File "", line 1
/Users/lexi/Downloads/Excel_Splitter_Combiner-master/N_Power_Split_Data/
^
SyntaxError: invalid syntax
In Jupyter notebook it works well, just cant find a way to get the combined excel out.
Please help.
thanks for sharing.. i am not able to find the combined file ...
Love it!!
awesome
so any good resources on when there are : and & in the column titles and the Segment data?
Thanks!
After a little playing around I just realized that I cannot use \ / * [ ] : ? in sheet names or file names... ...which does make sense... out of our database we have : identifying parts... so i need a way to change this in the sheet before splitting....
okay so been fighting with this for a little while... ...does anyone know how to remove the special characters before the split?
You can always use a replace command to remove special characters. You can either replace special characters with an underscore which I believe is acceptable in excel or just remove it all together. There are ways to do this is code with python by using a str.replace (‘old’, ‘new’) command or you can do it a replace within excel.....so you can easily pull the data from your DB into python and use the replace method. There are more sophisticated ways of doing this with NLP as well....a quick google search on the above should set you in the right direction.
it would be great if explain the code inside the .py files.
Hi Darwin, if you watch towards to the end of the video I explain the .py file
Useful tutorial!
while executing the splitfile the last part of the code gives an error saying "TypeError: sequence item 2: expected str instance, float found" kindly help.
Means that is found a number (int) and was expecting a string (text)
You can potentially solve this by finding the "sequence item 2" the error references and try to cast the float as a string using "(str)sequence_item_2" for example. This will force the float value to become a string in the scope of wherever you cast it. Hope this helps!
@@phantasm3207 Be aware that float and int are two very different data types. This comment is still valid in explanation albeit slightly flawed in accuracy. Great tips tho!
Is it same process for PC?
Yes it should be. Python is agnostic of OS
Your git hub link is kinda messed up!
Do you have a Python class for beginners?
Yes of course check it out 👉 Master Python: Complete Beginners Course - th-cam.com/play/PLM30lSIwxWOijsnA2Fr1PPGkOiCaDaRod.html
is it also for beginner with no programming knowledge ?
You should probably understand the basics before jumping in. Check out my beginner python tutorial
Amazing work. just A fan passing through. xD
Could u please share the sample Excel file?
Where's the code!?
You do not sound like "Sahitya Sehgal".
This is a great tutorial! Got a sub in me now :D
Is there a link for that excel file for us to follow along? Don't see it on your Github
Thanks and appreciate the sub. As for the file if I’m quite honest that was posted a while back and I dont have it on my computer anymore. I thought i had uploaded it with the other files but i didnt unfortunately. My tutorials are now triple checked for the original files :) sorry about that
But with PBI it’s easier to do
Please answer as soon as possible.I want your help.How to append pandas dataframe below an existing Excel file with the help of any library other than openpyxl. I have used openpyxl but openpyxl removes pivot table from my excel sheet. Please provide me code .How to achieve it?
You would need to use something like df.loc[-1] to find the last row in your excel file and start the writing process from there. My advise is to import the data as a df and append it within python and write back to excel as a new tab. If you have too many rows then i'd consider a database like sqlite3 where appending the data is easier
Cannot macro be used to do the same
Yes, you can do this with VBA
Which is the easiest
@@Vision267 The same really. But Python gives you more options
@@infared14 what kind of additional options
i don´t understand all at the moment, but i hope to get a job with this knowledge
You surely will dude! Be strong!
👌
nice one! However, I am not able to find the code in the github link metnioned above : github.com/satssehgal/Excel_S...
Thanks for sharing, though the GitHub link missed the source code. I've made my own repository based on your code shown in the video. The link github.com/kailichou/SplitExcelbyColumnValue. Kudos to you.
can you help me? I get the error: Traceback (most recent call last):
File "split.py", line 42, in
print("Your data will split based on these values {} and create {} files or sheets based on next selection. If you are ready to proceed please type 'Y' and hit enter. Hit 'N' to exit".format(', '.join(cols), len(cols)))
TypeError: sequence item 0: expected str instance, float found
@@joey5069 Hi, please change join(cols) into join(str(cols)) in your line 42.
Thanks for letting me know. I already changed that in my github as well.
@@learnmandarinwithkaili1102 thank you! I still learning python and I could not figure out why it was not workin. How did you find the problem?
@@learnmandarinwithkaili1102 I get another error in the "split into files or sheets:
Traceback (most recent call last):
File "split.py", line 54, in
sendTofile(cols)
File "split.py", line 26, in sendTofile
df[df[colpick]==i].to_excel("{}/{}.xlsx".format(pth, i), sheet_name=i, index=False)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas/core/generic.py", line 2175, in to_excel
formatter.write(
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas/io/formats/excel.py", line 730, in write
writer.write_cells(
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas/io/excel/_xlsxwriter.py", line 206, in write_cells
wks = self.book.add_worksheet(sheet_name)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/xlsxwriter/workbook.py", line 180, in add_worksheet
return self._add_sheet(name, worksheet_class=worksheet_class)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/xlsxwriter/workbook.py", line 729, in _add_sheet
name = self._check_sheetname(name, isinstance(worksheet, Chartsheet))
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/xlsxwriter/workbook.py", line 777, in _check_sheetname
if len(sheetname) > 31:
TypeError: object of type 'float' has no len()
@@joey5069 would you mind to leave your questions at my repository? Would help others who run into the same bug.
Okay, but why use python for such an easy task? VBA is much faster.
Speaking from someone who has used both quite extensively in my career what I want to emphasize isnt just for this specific task but the automation integrations and trigger points that python offers with something like this...., its unmatched and much more superior than VBA.
@@SATSifaction Well, if someone already knows Python and don't want to learn VBA, and I totally get that, they should use Python. I'm just saying we should use the right tool for a given task. VBA is much faster if used right (being the native language), more accessible (built in IDE), bit more friendly for absolute beginners. Someone who develops Excel automations, it's worth learning both languages. If speed is important, and many times it is, VBA is mostly unbeatable.
@@danielszalok8540 There are several additional functionalities you can add to this code such as job scheduling, sending emails, etc. Although this too can be done via VBA, the coding would be much difficult compared to python. I would say python is pretty easy to learn for anyone with no prior programming and IT experience. Utilizing the features of python and excel power query, a lot of tasks can be automated.
@@danielszalok8540 VBA would soon be outdated the way things are going ahead..
@@twasif outdated doesn't mean inefficient, or difficult to use, or slow
thanks! when I run the code I get the error: Traceback (most recent call last):
File "split.py", line 42, in
print("Your data will split based on these values {} and create {} files or sheets based on next selection. If you are ready to proceed please type 'Y' and hit enter. Hit 'N' to exit".format(', '.join(cols), len(cols)))
TypeError: sequence item 0: expected str instance, float found
joeymeijers@MBP-van-Joey-2 split %
how can I fix this?
You might want to check the value of cols and len(cols), confirm the .format syntax.
Haven't run the code yet.
@@markanderson8066 I honestly can't find the problem. I am very new to programing.
You can do all this in 10 sec with power query
Here is the code on github: github.com/satssehgal/Excel_S...
You haven't heard about GDPR, lol?
Look forward to letters from you european clients))))