Automate Multiple Sheet Excel Reporting - Python Automation Tutorial | Full Code Walk Through (2019)
Vložit
- čas přidán 25. 07. 2024
- Python Automation Tutorial
In this one we'll cover the basics of how to automate your excel reports. I know I did this video previously, but I felt it was time to redo that one. In this one we'll cover pandas, NumPy, and Matplotlib for creating reports from multiple excel spreadsheets.
Kite helps fund the channel, thanks for checking them out and supporting me --
⭐ Kite is a free AI-powered coding assistant that will help you code faster and smarter. The Kite plugin integrates with all the top editors and IDEs to give you smart completions and documentation while you’re typing. www.kite.com/get-kite/?...
#Python #Automation #Excel
Here's the excel workbooks from this video --
drive.google.com/open?id=1x6Z...
drive.google.com/file/d/16Yio...
Here's the full python beginners course to get you started using python-
• Learn Python - Course ...
Playlists you might consider interesting:
Pandas for beginners
• Learn Python Pandas #1...
NumPy Course (Free from Udemy)
• Introduction to NumPy ...
Hey Everyone! In this video we're covering the basics of how to automate your multiple sheet excel reporting using python. We'll cover the basics of everything you need to get started using pandas, numpy, matplotlib and python to automate Excel.
There's a lot more in depth stuff about automating excel reporting than what is covered in this video, so subscribe to the channel and I'll have the medium and advanced level videos up soon!
Let me know any feedback or any trips you have for automating excel reporting and I'll be sure to feature you (and the tips) in an upcoming video.
Thanks so much for watching and I hope this video helps you automate excel reporting.
Join The Socials -- Picking Shoutouts Across CZcams, Insta, FB, and Twitter!
FB - / codewithderrick
Insta - / codewithderrick
Twitter - / codewithderrick
LinkedIn - / derricksherrill
GitHub - github.com/Derrick-Sherrill
Thanks so much for all the support! It's crazy that this topic catapulted my channel about a year ago and now I get to type this (and make an improved version) a year later. Thanks so much for supporting me. This one feels surreal. I appreciate you all so much!
5100+ subscribers and climbing. Thank you all.
*****************************************************************
Full code from the video:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
excel_file_1 = 'shift-data.xlsx'
excel_file_2 = 'third-shift-data.xlsx'
df_first_shift = pd.read_excel(excel_file_1, sheet_name='first')
df_second_shift = pd.read_excel(excel_file_1, sheet_name='second')
df_third_shift = pd.read_excel(excel_file_2)
print(df_first_shift)
print(df_first_shift['Product'])
df_all = pd.concat([df_first_shift, df_second_shift, df_third_shift])
print(df_all)
pivot = df_all.groupby(['Shift']).mean()
shift_productivity = pivot.loc[:,"Production Run Time (Min)":"Products Produced (Units)"]
print(shift_productivity)
#shift_productivity.plot(kind='bar')
#plt.show()
df_all.to_excel("output.xlsx")
github.com/Derrick-Sherrill/D...
Packages (& Versions) used in this video:
Python 3.7
NumPy 1.17
Pandas 0.15.0
Matplotlib
Mac OS operating system
*****************************************************************
Code from this tutorial and all my others can be found on my GitHub:
github.com/Derrick-Sherrill/D...
Check out my website:
www.derricksherrill.com/
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/shop/derricksh...
What editing software do you use?
Adobe CC - www.adobe.com/creativecloud.html
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/user/derrick-sh...
Where do I get my music?
I get all my music from the copyright free CZcams audio library
czcams.com/users/audiolibrary...
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! - Věda a technologie
This lesson would take more than 30 minutes for others to explain. Great pace & clarity. This is usually the best way to explain something for non-beginners. This is the essence! Thank you so much!
Love😍your videos... So clear and straight to the point...high quality
This guy is going places! It wasn’t too long ago when I watched one of your videos and you only had a couple thousand subs. Keep up the great work! Love your videos
Just stumbled across this. Thanks so much - a clear, easy to follow example. Great stuff!
This tutorial is great, thank you so much!
Really sweet person knows what he is doing and is not all about money and friggin sponsors. Keep it up! Makes me cry happily!
dude thank you so much for this tutorial. i can't thank you how helpful you are to the data analysis community!
Brilliant video, I have heard python mentioned numerous times but never seen it in action. Looks amazing, thanks for making the video!
Hi Derrick, I was nine when Apollo 11 was launched so, despite the difference of timelines between you and me, I feel very enthusiastic about my Python learning curve. I will utilize your instructive videos in order to implement a project on a native american language I am learning here in Central America. As things go further in my idiom classes the more rules appear, therefore I am expecting about storing different sources in different Excel files and combine them, with the proper sintactic rules, in some sort of a centralized dictionary. I have recorded the important features of this video for further use. Thanks.
Amazing Tutorial, Derrick. Thank you so much :)
Thank you for sharing your knowledge! Very useful easy to understand! 👍
You have no idea how valuable this is to me. Thank you so much. Great content!
Great content. I don’t see enough people talking about excel -> python.
Thanks, Derrick. Great job. I have worked with excel for over 15 years, manipulating data with excel multiple functions. But I still give credit to python and other programming languages for data automation. I'm transitioning.
CZcams's algo popped up your video and this the first video to watch on your channel and I am subscribed to it. Thanks, this has been a great relieve for my production planning, though I am new in python.
Great Video! Helps me alot in day to day work. Thank you Derrick.
I have a lot of things to learn from you man. Keep your videos coming up! Thanks
This is so great! Thank you Derrick!!
Great content dude. I am newbie, and learn a lot from this video! Thanks!
I'm going to drop all professionalism and just say " holy shit "". I've watched ALOT of training videos On python but WOW...it was short, clear, concise, and understandable. I know the video is 2 yrs old but I hope he is having all the success he deserves!! Well done!!!!
Sorry for the technical errors on this one (learning premiere and animations to improve the videos haha) - Medium & Advanced level techniques coming soon!
Hi, great video. Is it possible the output to MS Access file instead of Excel File? The reason is because I would like to combine thousands of excel files and the quantity of rows of the output will be more than 60mm.
Derrick Sherrill new stuff I just learned!
Where is live
Don't even worry about that man. You are doing God's work, thanks a lot.
For all of us who have administrative jobs, this kind of content is amazing and I think college, at least in my country doesn't give us that (They didn't even taught us Excel). Now that I got out of it I will try to learn by myself and also with the valuable help of people like you. I have recently found in CZcams more than all the courses of my university.
Thanks for the inspiration and being a role model too.
i guess im asking randomly but does anybody know of a tool to log back into an instagram account??
I stupidly forgot my account password. I appreciate any assistance you can give me!
You are such a blessing. Subscriber from the Philippines here
Holly.. you just convinced me of the worthiness of learning to program. I could automate 90% of my job!! Thank you
really good stuff. videos like these are great because we're doing real work and it helps me learn! thanks again.
The pacing of your videos is perfect.
Hi Derrick, that was awesome!
I'll make sure i'll check your content on Udemy as well :)
I am very glad that you are more used to be the tutor in youtube. Thanks for providing the material with link!
Thanks for your video, really liked them all.
really very clear in conveying the things
opened a new way to process my excel files
thank u so much
that was awesome video, i 'v never been seen one like that , thanks for your time my friend
Thank you for sharing your knowledge on Excels.
This is a great tutorial! Great job!
Good job. Thanks for sharing your knowledge
Thank you Derrick, great explanation!! You won a subscriber and a thumbs up. God bless you
Great video! I’m just getting into python to be able to pull data from webapi and convert json to csv. That would be a great video!
I really like your videos, like I’m purely a beginner but I feel like I learn a lot from your videos (and I already wrote some codes to automate excel tasks). Can you do more how python can be used to automate excel. Thank you and keep up your good work.
Clear and straight to the point. Good stuff.
this is simple amazing!!!! thank you for the video..
So glad I found your channel!Thanks Derrick!!
Brilliantly explained....I have been trying to learn python since 6 mounths........only this Guy made python easy for my dumb mind....
thanks for the upload this video. i wasn´t have idea how to use python for read excel. one greeting from argentina
Muchas Gracias Derrick, excelente tu video. Saludos desde Argentina.
Excellent demonstration of functionality 🤙🏽
Wow this was really straightforward and easy to understand.
Awesome! Thanks! Would greatly appreciate if you could answer my question in your ""Replace Vlookup with python" video.
Thanks for sharing this knowledge, I appreciate it too much!!!
Great Video!! Thank you for sharing your knowledge:)
Amazing video ! Really enjoy it.. done subscribe.. looking forward for new vids
very helpful tutorial bro Thank you!! love from india ❤️
I have seen power query more useful so far, I have been making interactive dashboards out of raw data from multiple excel sheets and when new data is updated in sheets just press refresh button and job done.
COPY FOLDER NAMES TO EXCEL in ONE GO
czcams.com/video/kqVjSSjVnks/video.html
"so far" ... thats' why
Amazing mate!!
Amazing content here. Auto subscribed. Cheers from Spain :D
thanks for a great vid! truly grateful being walked through this stuff
Very well explained! Thank you!!
Thanks for the tutorial! Any idea on how to read an open sheet with real time DDE (dynamic data exchange) data on the flow?
Have to agree with the other Power Query comments - automating Excel reports using Power Query is super easy, built into Excel, and no coding required! People should look at the many CZcams tutorials to familiarise themselves with it.
I'm not sure why you'd go to Python for the scenarios noted - Power Query is way more user friendly (built into Excel), less error prone, and low code to use Power Query. And apparently much quicker.
I'm surprised from looking at the rest of people commenting and the number of likes this video received! Clearly people are not familiar with Power Query! Its like using a sledge hammer to crack a nut :)
wow thank you so much for the great tutorial. This is gold.
another outstanding video on python/pandas/excel tech!
I really enjoyed the video. Concise and informative. Thank you
Interesting!! Thank u this was very helpful👍🏻
Learn a lot. Thank you.
great video, you should teach, how to make an automatic report that creates a complete report with the graph and the calculations on a PDF.
Hey Derrick thanks for taking the time to teach us. I was wondering how to put the df_all, shift_productivity & the plot all in one excel file? Any help would be great! Thanks!
Very useful, thank you
Very nice Tutorial. Thank you!
Subscribed i dont know anithing of panda, python, automatization, excel but your explaination ets so clear that i want to learn whatever u are saying.
Я новичок, но все понял. Очень классно объясняешь :)
I’m just a beginner, but understood everything. You do it great :)
Привет из России!
Really great tutorial!
This video is really helpful. Thank you.
great work, thank you!
Thanks a lot for the video!!
Great tutorial! 👍
Thanks for your sharing !
Thanks for the tips and I humbled to say that I never heard of some of these tools before this video. Accordingly, I have subscribed. Again, thanks, and God bless
Your channel is so underrated.
Thanks Derrick that was really helpfull
Derrick you are super cool
thanks for sharing your knowledge
Very good one. I liked that you cared to mention which Python Version you we using.
thank you, so helpful
thanks, i learned so much from your videos
you are the best, straight to the point
To the author. This is a great demo of an important topic. Thumbs up +1 from me.
Thank you so much Derrick
Very Good Video Derrick!
Very informative . God bless and keep posting
I never thought David Bowie was into computers!
We could be heroes!!
Lmfao
Lol, this David Bowie is a real rock star
Just for onde day...
🤣
Amazing. You made it so easy.
Very Useful Buddy Keep It Up !!
Great stuff, maybe pivot table could work for 1 or 2 different data sources, but if you got multiple spread sheets this seems like the best way to go. Maybe not as friendly as powerpivot but a good tool never the less.
Can you help me I wnna this code how I can get😢
Thanks man!
Thanks for sharing python programing knowledge, i want to learn full course from you please share all videos links
Hey Muthu Raja! So far I only have one course (that's pretty short but completely free) working on getting another one up in September about NumPy and Pandas! Here's my Udemy profile. www.udemy.com/user/derrick-sherrill-2/
Thank you for sharing your knowledge
Let’s gooooo!!!!
Bravooo, great video.
Thanks for this video.
Thanks a lot great video.
Hi Derrick, Desperately waiting for new videos from you. we haven't seen any in few days.
Thanks Derrick!
Nice to do this exercise,quite some good teaching i must admit.
Derick you are doing a great job. God bless you.
Thank you so much for this amazing work .
I have a question 🤔, if it is possible could you please make a video of how we can promote the categorical values ( like bad,good, very good, excellent)to become variables with yes or no values 😉
Thank you