Automate Excel Work with Python and Pandas

Excel tasks are repetitive and boring! Automate them and make your life easier using Python and Pandas. Opening CSV and XLSX files into a Pandas Dataframe is super easy, and setting up to do some basic editing and manipulation with that data can save you hours off your day job.

I will show you how automating tasks such as combining CSV files into one, moving columns around, creating pivot tables and vlookups is easy in Pandas, as well as exporting into all the popular file formats.

Learning how to use Pandas is also highly recommended for anyone interested in data as it is Pythons go to for Data Science, so starting small with some basic dataframe manipulation will set you off down the right path.

Support Me:

-------------------------------------
Disclaimer: These are affiliate links and as an Amazon Associate I earn from qualifying purchases
-------------------------------------

# timestamps
00:00 Intro
00:38 Data
01:45 read_csv
03:46 Change Columns
08:23 Edit Column Data
09:56 Add Multiple Files Together
12:56 Pivot Tables
17:00 vlookups (merging)
20:00 Exporting
21:10 Outro

[RS-Amsterdam]

Yesterday, before this video, I was testing this subject and I got an error and it took me a while to figure it out.
The problem was that my Excel conversion to CSV gave a ; as delimiter instead of a comma (, )
The solution was df = pd.read_csv('data.csv', *sep=';'* )
Thanks for sharing your wisdom ;-)

[maryamzarabian]

Thank you very much for the gentle introduction of Panda library . It was very useful for me

[ruffhouse]

Easy to follow tutorial, appreciate it john!!!!

[training]

Masterly content and presentation, thanks. The ideal pace for making the viewer understand what is being done and how.

[kprenbq]

Really useful from start till the end. Highly appreciated.

[jisuresh]

Hi John Watson! I'm watching your channel regularly and updating my skills. You are my real teacher. Thanks!

[senthilsds]

I learned a lot about web scrapping and data handling methods from you in very short time. Thank you

[tubelessHuma]

Very useful common operations for data science projects. 👍💖

[AlvinRyellPrada]

I am getting started with touching python for my excel files and glad i found this video.
I can easily follow this thru
Looking forward for more :)

[OBPagan]

Just wanted to say a huge THANKYOU as you have taught me so much!

[TheeMatrixUno]

Hi John, Thank you for the informative video! Top job

[wladcapiekla]

Thanks a lot for your videos. These are really helpful and easy to understand.

[priya-okur]

This video is really helpful. Thank you so much.

[mariuscheek]

As someone more used to VBA, I'm starting to learn Python as my employer is going to deprecate VBA due to security concerns, so this is really useful.
However, I have yet to find any tutorials at all, anywhere on youtube, that show how to actually deploy for end users within an Excel environment. My users aren't going to have an IDE, they need to be able to click a button and set code running.

[TheeMatrixUno]

John, Can you target a specific sheet within an excel workbook to create a new db?

[yannhk]

It's a very useful tip to get a list of df column names

[ceknowledge]

Great.awesome video...great learning time

[roybuchler]

Hi - what do you do in the case that you want to merge the data but some of the data in the references tab does find a corresponding match in the original merged spreadsheet?

[absoluteRandom]

Hello, John can you make a video about the VScode Debugger about that how to setup a debugger, how to use it and it's setting and all that stuff. Thanks in Advance

[Destide]

Nice to get tutorials from an actual work flow rather than a reinterpretation of the manual.