Are You Still Using Excel? AUTOMATE it with PYTHON
ฝัง
- เผยแพร่เมื่อ 4 ต.ค. 2024
- If you do the same thing over and over in excel at work you should seriously consider automating it with Python and Pandas! I'll show you how to easily import several csv files into a dataframe, and create some summary pivote tables of the data to share.
code here: github.com/jhn...
Support Me:
Patreon: / johnwatsonrooney
Proxies: iproyal.club/J...
Hosting: Digital Ocean: m.do.co/c/c7c9...
Gear I use: www.amazon.co....
Twitter / jhnwr
Using python in daily corporate tasks would be a great idea for a series. Thanks for this vid, I will use it in my project later :)
Agree! 👍
Oh yes please
Think the same.
Thank you John! More pandas and Excel/Google Sheets related content please ☺ love your videos and long term subscriber ♥
Thank you!
Thanks for this video John!
I've been enjoying your data scraping videos A LOT.
And this new video about local data automation is very interesting too!
Being able to grab data from all kinds of sources and then produce something valuable with that is just amazing.
The CTRL+D / CMD+D PyCharm duplicate shortcut was the cherry on top.
You always create a video about a subject I'm working on. Perfect timing. Keep them coming!
Thanks Kyo appreciate it!
Excel covers full stack i.e. UI, automation and Database. What you demonstrated is only for python to cover automation - you will need additional learning curve on Python UI and Database read write to replace excel use cases with python.
A super useful follow up vid would be xlwings. After all the data manipulation and calculations, to output it in a way properly formatted and looking nice, xlwings is a pretty good choice to add on to the automation.
Good idea thank you!
I do like Python a lot and a few year ago that might have been the more effective way to do it. But since Excel include PowerQuery doing the same thing inside PowerQuery might be more easy for most people to reach the same result all without Excel and with a saved recipe that can be scheduled to have automated refreshed report.
Sure I totally understand that for this simplified example using power query would be much easier for most people. I wanted to show people what can be achieved using Python so those that are learning might find new personal projects to create to grow their learning and understanding. Thanks for your comment I appreciate good honest feedback like this
That's my first thought! Thanks for saying it!
VBA and Power Query are already there to be used, with an enormous amount of customisable power
R or Python require further installations that would have to be OK'd by the IT security dept
@@snipelite94 that true, many organisation have strict policies that restrict usage or installation of software like Python
I love PowerQuery and find it really userfriendly but it's still nice to know how it would work in Python so I absolutely appreciate the tutorials.
I also have this same sentiment. Though, if you look at the bigger picture, depending on what you are doing, using Excel's ecosystem including VBA, PowerQuery, etc, can only do so much. A simple example would be, if you improve your workflow little by little, using VBA, PowerQuery, etc, say for a year or so or however long it take, you'd definitely hit a wall that it gets so complicated. It's easier to implement each time you want to improve something compared to Python. But, had you started using Python from the start, yes it would maybe take more time to implement at first, but in the long run, using a proper programming language can do so much more.
But yeah, I'm not saying using Excel's ecosystem is bad, it's actually really good. Arguably the best thing Microsoft has ever done. But if you want to make your workflow, or better, your team's workflow, better, IMHO, using a proper programming language such as Python is the right choice in the long run.
Hey John , first time coming across your video and I'm really impressed on how flexible you are with pandas on data..and just discovering the glob method also ..hopefully I get to learn more from your videos ....Thank you and have a wonderful day
Thank you very kind I’m glad you enjoyed it!
Brilliant as usual 😉 i have scrapped a property website and it was containing many prop types( apartment, villa..etc every type in a single csv) and I wanted to concatenate them.
Thanks! I’m glad you liked it!
I noticed that your previous videos were mostly done on VS code. Did you move away from VS Code to PyCharm?
By the way, your videos are very nicely done! All are straight to the point and without the fluff that are often found in other channels. :)
Thanks! Yes I mostly use PyCharm now, however I do still use vs code sometimes
I thought I knew Pandas well, and I thought I knew PyCharm well.
CTRL+D, and pd.pivot_table were knew to me....
Good job.
PS. SHIFT+F10, or CMD+R in Mac is the run shortcut in PyCharm.
CtrlD was new to me too I guess you don’t know if no one shows you! Thanks for the nice comment
Always happy to be a subscriber. This is quality
I was thinking about this yesterday, thank you !
Hi John, Thankyou
Can you make more videos on excel automation using python
Nice video, really like pandas. Hope we will see more pandas videos from you.
Thanks for this type of Content please keep updating us
Thanks!
Thank you for the video.
Thanks for watching!
Brilliant Video!! Please make moree videos on excel automation please
Title is misleading . The excel files I see are monsters in terms of complexity . Your example is seriously simplistic . 😊
Thank you John.. Please add visualization of the data as well
Great, as usually
Interesting, I was using groupby function to do the same, I'll start using pivottable!. Regarding datetime conversion, I prefer to use parse_dates from read_csv parameter, specially when my setup is in spanish and I'm opening csv in US/UK date format. I would have find interesting also to show how you can create calculated fields with lambda functions. Also creating highlighting rules in the excel format is very interesting. Thanks!
It seems like what you described could be performed just as easily in an Excel workbook (called, say, "Recurring Report Summary") using VBA. Do you disagree?
What advantages do you see Python has over Excel VBA?
Another great video
Thanks glad you think so
Randomly came across your vids and think it was the best thing ever, one thing I would like to ask though I have to match point of sale receipts (banked) to a the transactions(sales) the references are in some cases the same and some not( human intervention), could I create this matching using python? It kills me everyday I have to do it manually.
Hey thanks for watching! When you say in some cases they are not the same - that could cause some issues. You could try fuzzy matching in Python and see if that could help but generally I’d say it could be done!
Exactly what I'm searching for. Where can I get the csv's for practice?
Great thanks! I get all my fake data from mockaroo - I don’t think I have these exact ones saved I’m afraid sorry!
@@JohnWatsonRooney Thanks for the quick response. i didn't macharoo before. thanks for the tipp, so I can create my own fake date.
Thank you John it was awesome .Also can you tell how we can automate product schema in python
*can I use garden snake*
Let's say you had a report with a column that has buy or sold value, how would you go about splitting that into multiple reports, one for buy and one for sell?
Hi Alan, sure you could split it up, create the data frame with all then create one for buy and one for sell - assuming there is some way to determine which is which
@@JohnWatsonRooney As an example let's say there was a column called 'type' with a value of either Buy or Sell.
@@alan_tucker if you want 2 dataframes
df_buy = df[df['Type'] == 'Buy' ]
df_sell = df[df['Type'] == 'Sell' ]
I'm a python web scraper and I have good grip on python, so, Should I go for VBA or continue with the python to handle excel problems?
For everyday stuff I still use excel but for things that I need to do over and over, and for larger datasets I always use pandas. I always put my scraped data into a database, then pull it into pandas for analysis. I never really used much VBA though
I don't think you need it. But for curiosity's sake, you might want to learn it. Or just for the purpose of having a conversation with someone who does VBA. But for solely using VBA for tasks, maybe not.
@@JohnWatsonRooney thank you I will continue with python
Thank You John once again.
Just one question, if i were to merge lets say a 100 files, the processing time would be considerable. Can we apply the logic of conc.futures here. If Yes, a little help will be appreciated. Im not even sure if thats possible so if not please avoid my stupid question.
Would love to hear from yoh in either scenarios John.
Happy Teaching us❤
Thanks for sharing
Can you provide the CSV data files?
That's not really a pivot table though. In a pivot you would have countries as columns in the example.
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
Hey man. There is a store I m trying to scrape, but you have to scroll in order to see all the products. When I scroll, I can t see any get requests in the network xhr tab. What should I do to make the program go to the bottom and take all the information? I m using scrapy
Hi John , can you explain ways to bypass captha?
You generally have to use a captcha solving service, if you google you’ll find some that will work with the captchas you are encountering (recaptcha for example) it has a cost though, the captcha itself is really there to provide a “cost” to getting the data
Hi There. I have used cloudscraper to bypass captcha in some cases.
question for you, for a site that returns "you need to enable javascript to experience this site", is it still possible to use python 'requests' to do this? What's the work around? Or am I forced to switch to 'requests-html' for this task. Would prefer staying with 'requests'. Thanks.
Hey, I’m afraid not, JavaScript is run in the browser so you’ll need to have the page rendered out, using something like requests-html, playwright or splash
Use a headless browser to load the page.
hello john can you make a video on scrapping world population data website please i tried but failed beacuse the span tag is constandly changing
Which python Software you use for coding
PyCharm almost exclusively now, occasionally vs code but rarely!
when i can get this data ?
Or just use power query, way simpler… and user friendly.
Yeah. Use the right tool for the right task. Though, if you want a very scalable system, use a proper programming language such as Python.
What they won’t share here is that pandas and python corrupts excel sheets …
To be honest I don't see the theme of the video being interesting for a lot of people, compare to you previous ones. I mean "are you doing repetitive tasks on the excel? Automate it with python! " isn't really a revelation. Maybe there is a lot of people who isnt like me and they didn't try this from the start, but from my point of view - not as helpful or interesting theme of the video compare to your other videos.
This is the hard part of content creation. I need to appeal to a wider audience which means that some people aren’t going to find certain videos as interesting or useful. Just like if I were to put out more technical content it alienates those who aren’t as proficient. It’s a tough balance. The idea of this video is to show people who are new and learning what can be achieved to get them more interested and excited to learn more Python. This one doesn’t appeal to you specifically I get that, thanks for your comment though I appreciate the feedback
yikes; this is so much more verbose than data.table syntax in R!
Yeah R is specifically designed for this sort of thing