Automate Multiple Sheet Excel Reporting – Python Automation Tutorial | Full Code Walk Through (2019)

22 Comments



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. https://www.kite.com/get-kite/?utm_medium=referral&utm_source=youtube&utm_campaign=derricksherrill&utm_content=description-only

#Python #Automation #Excel

Here’s the excel workbooks from this video —
https://drive.google.com/open?id=1x6Zvat5QDszp95zf4PYEYV-vDdhsWS4E

https://drive.google.com/file/d/16YiozndphnyBbdFmJW7VFMr8E6fyH2-r/view?usp=sharing

Here’s the full python beginners course to get you started using python-

Playlists you might consider interesting:
Pandas for beginners

NumPy Course (Free from Udemy)

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 YouTube, Insta, FB, and Twitter!
FB – https://www.facebook.com/CodeWithDerrick/
Insta – https://www.instagram.com/codewithderrick/
Twitter – https://twitter.com/codewithderrick
LinkedIn – https://www.linkedin.com/in/derricksherrill/
GitHub – https://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”)

https://github.com/Derrick-Sherrill/DerrickSherrill.com/blob/master/AutomateExcelReporting.py

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:
https://github.com/Derrick-Sherrill/DerrickSherrill.com

Check out my website:
https://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 – https://atom.io/

What Equipment do you use to film videos?
https://www.amazon.com/shop/derricksherrill

What editing software do you use?
Adobe CC – https://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!
https://www.udemy.com/user/derrick-sherrill-2/

Where do I get my music?
I get all my music from the copyright free Youtube audio library
https://www.youtube.com/audiolibrary/music?nv=1

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!

source

22 Comments
    • blank
      Anand Saroj
      May 09, 2022 15:21 pm Reply

      thank u i will try.

    • blank
      James McGee
      May 09, 2022 15:21 pm Reply

      Clear and straight to the point. Good stuff.

    • blank
      C H
      May 09, 2022 15:21 pm Reply

      So pointless as you can do a much better and easier job in excel with a few clicks!!

    • blank
      The Black Christian Geek
      May 09, 2022 15:21 pm Reply

      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

    • blank
      Z Yuan
      May 09, 2022 15:21 pm Reply

      wow thank you so much for the great tutorial. This is gold.

    • blank
      Michelle Amidon
      May 09, 2022 15:21 pm Reply

      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!!!!

    • blank
      robert cliffort
      May 09, 2022 15:21 pm Reply

      great. we love you.

    • blank
      Rafy-Ivan Morales
      May 09, 2022 15:21 pm Reply

      I’m a new student and need help to do this project:

      I have a port scanner script it works properly, it works in Pycharm using python3.9.

      I have to make a file and I did make call (Example.txt)

      On this file, I have to do this:

      3. If the Ports is open, it should create a file and add an entry for port number

      4. In case of any exception for instance “host is not available”, “host name could not be resolved” or

      due to any other error, you need to write that exception into the same file.

      I’m not asking for you to do the project for me, I'm only asking ware I need to work or used in my script to accomplish this because it is my first time doing this and working with python too, and I have been struggling with this part one very bad. I can even send you the code, so you can look and only tell me what I’m doing bad, is not working.

    • blank
      _IA_ WOT Blitz
      May 09, 2022 15:21 pm Reply

      How did python know the path of the excel file?

    • blank
      gordo lv
      May 09, 2022 15:21 pm Reply

      Thanks for your sharing !

    • blank
      Yemane Gebremedhin
      May 09, 2022 15:21 pm Reply

      Great tutorial! am about to be a regular to your channel.

    • blank
      edsonwinnerify
      May 09, 2022 15:21 pm Reply

      I have a lot of things to learn from you man. Keep your videos coming up! Thanks

    • blank
      Valens RWEMA
      May 09, 2022 15:21 pm Reply

      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

    • blank
      धनसुक बामनिया mp69
      May 09, 2022 15:21 pm Reply

      👍👍👌

    • blank
      Nrumein Shah
      May 09, 2022 15:21 pm Reply

      Hi Derrick,
      Great video!

      I just have a question. Can we add more data to excel and still run the same code to get a new output?

    • blank
      RoseRiotnx
      May 09, 2022 15:21 pm Reply

      you are a god!!!

    • blank
      Coder Korleone
      May 09, 2022 15:21 pm Reply

      Hi bro.where are u?

    • blank
      Masud Parvez
      May 09, 2022 15:21 pm Reply

      So cool man!!!👌👌👌👍👍👍

    • blank
      James Du Jardin
      May 09, 2022 15:21 pm Reply

      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!

    • blank
      Marcin Lewicki
      May 09, 2022 15:21 pm Reply

      I can't seem to find pip to even install anything?

    • blank
      Joel Harris
      May 09, 2022 15:21 pm Reply

      Awesomeness

    • blank
      lill princess editz
      May 09, 2022 15:21 pm Reply

      Help me bro!!!
      I have one error.
      FileNotFoundError: [Errno 2] No such file or directory : ' billing. xlsx'

Leave us a comment