Excel Python Appending Data Frames From Multiple Worksheets - Episode 2621
Microsoft Excel Tutorial: Appending data from multiple worksheets using Python in Excel
To download today's workbook: https://www.mrexcel.com/youtube/3rAJP...
Today, a question about creating a Python data frame from multiple Excel sheets. I use the CONCAT function in Python but then realize that the headings are repeated.
So I show how to use .tail(-1) to remove the top row from each data frame except the first.
Welcome to another episode of the MrExcel podcast, where we tackle your toughest Excel questions. Today's question comes from a viewer who wants to know if it's possible to define a data frame from multiple worksheets with the same column titles. The answer is yes, and in this video, we'll show you how to do it using Python.
First, we'll open up our Excel workbook and take a look at the three sheets we'll be working with: "One Year," "Other Year," and "Part of Next Year." Then, we'll jump into Python by pressing Control + Alt + Shift + P and extend the formula bar by pressing Control + Shift + U. From there, we'll create a data frame for each sheet by selecting the data and using the control and shift keys to highlight the entire table.
Next, we'll create a list of these data frames and use the pandas function "Concat" to join them together. This function is specifically designed for combining data frames with the same column titles, making it perfect for our situation. After pressing Control + Enter to execute the code, we'll see the combined data frame with all of the rows from each sheet.
But wait, there's a problem. The headings from each sheet have been included in the data frame, which is not what we want. In Excel, we could simply use the "Drop" function to get rid of these headings, but in Python, we have a few different options. In this video, we'll show you how to use the "Tail" function to drop the top row of each data frame, effectively removing the headings. And just like that, we have a clean, combined data frame with no extra rows.
So there you have it, a simple and efficient way to append data frames from multiple worksheets using Python. If you enjoyed this video, be sure to like, subscribe, and ring the bell to be notified of future episodes. And as always, feel free to leave any questions or comments down below. Thanks for watching and we'll see you next time on the MrExcel podcast.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents
(0:00) Problem Statement
(0:29) Defining 3 data frames
(1:32) Python CONCAT function
(2:20) Python Tail Function
(3:10) Wrap-up
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
In questa pagina del sito puoi guardare il video online Excel Python Appending Data Frames From Multiple Worksheets - Episode 2621 della durata di ore minuti seconda in buona qualità , che l'utente ha caricato MrExcel.com 01 settembre 2023, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 5,848 volte e gli è piaciuto 158 spettatori. Buona visione!
