Microsoft Excel Tutorial: Appending data from multiple worksheets using Python in Excel
To download today's workbook: Excel Python Appending Data Frames From Multiple Worksheets - Episode 2621 Sample Files - MrExcel Publishing
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.
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
To download today's workbook: Excel Python Appending Data Frames From Multiple Worksheets - Episode 2621 Sample Files - MrExcel Publishing
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.
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
Transcript of the video:
Appending data frames from multiple worksheets. Great question sent in this morning.
Can a DF table be defined from several sheets, having the same column titles, but located on two or more sheets?
Thanks.
So in Power Query, we call this an append query.
Here, let's just try it right here. So what I have is I have three sheets.
I have the sheet called one year, a sheet called other year and a sheet called part of next year.
So we'll come back here and control alt shift P to get into Python.
Control shift U to extend the formula bar.
And I'm going to create a data frame called DF1, equal to, and just using my mouse, I click on one year.
Go to the top, control shift down, control shift right, and then DF2 equals, using my mouse.
I click on other year, go to the top, control shift down, control shift right.
Third data frame.
And wouldn't it be cool if we could just say, "Hey, here's the whole list of sheets, go get them all"?
But that would require something like knowing how to do control down, or in VBA, or use start at A1 and do end, XL down, XL right.
Okay, so now we have these three data frames and I want to join them together.
I'm going to create a list here of those D1, DF2, DF3.
And then the magic is a function in pandas called Concat.
So PD, for pandas, .Concat. And we're going to Concat the frames.
Now there's a whole bunch of other options here in Concat, but right now, because all the columns are the same, we should just be good to go. Control enter.
All right, it brings it back as a data frame.
Let's do control alt shift M to convert to bring back the data.
All right, what we see here, let's make column M a little bit wider so I can see those dates.
I should have dates from 2020, and then, moving on to, oh shoot, they bring in the headings.
That's not what I want.
Power Query would've been smart enough to get rid of those headings.
Learn Excel From MrExcel Podcast.
Episode 26, 21 and a half, how to drop the top row of a data frame. All right, three different choices.
A iLoc, drop, or tail.
Tail is the easiest one in this case, so I modified this.
I kept the entire data frame one, so I get those headings. But then DF2.tail -1, it says get rid of the top row and then DF3.tail -1. Get rid of the top row.
Control enter.
We should have the same number of rows with the exception of the heading rows that occur right there after 1231.
All right, there you go.
A twofer, how to use Concat to put multiple frames together and then tail, which is kind of equivalent to the Excel drop function.
All right. Well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please, down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
Can a DF table be defined from several sheets, having the same column titles, but located on two or more sheets?
Thanks.
So in Power Query, we call this an append query.
Here, let's just try it right here. So what I have is I have three sheets.
I have the sheet called one year, a sheet called other year and a sheet called part of next year.
So we'll come back here and control alt shift P to get into Python.
Control shift U to extend the formula bar.
And I'm going to create a data frame called DF1, equal to, and just using my mouse, I click on one year.
Go to the top, control shift down, control shift right, and then DF2 equals, using my mouse.
I click on other year, go to the top, control shift down, control shift right.
Third data frame.
And wouldn't it be cool if we could just say, "Hey, here's the whole list of sheets, go get them all"?
But that would require something like knowing how to do control down, or in VBA, or use start at A1 and do end, XL down, XL right.
Okay, so now we have these three data frames and I want to join them together.
I'm going to create a list here of those D1, DF2, DF3.
And then the magic is a function in pandas called Concat.
So PD, for pandas, .Concat. And we're going to Concat the frames.
Now there's a whole bunch of other options here in Concat, but right now, because all the columns are the same, we should just be good to go. Control enter.
All right, it brings it back as a data frame.
Let's do control alt shift M to convert to bring back the data.
All right, what we see here, let's make column M a little bit wider so I can see those dates.
I should have dates from 2020, and then, moving on to, oh shoot, they bring in the headings.
That's not what I want.
Power Query would've been smart enough to get rid of those headings.
Learn Excel From MrExcel Podcast.
Episode 26, 21 and a half, how to drop the top row of a data frame. All right, three different choices.
A iLoc, drop, or tail.
Tail is the easiest one in this case, so I modified this.
I kept the entire data frame one, so I get those headings. But then DF2.tail -1, it says get rid of the top row and then DF3.tail -1. Get rid of the top row.
Control enter.
We should have the same number of rows with the exception of the heading rows that occur right there after 1231.
All right, there you go.
A twofer, how to use Concat to put multiple frames together and then tail, which is kind of equivalent to the Excel drop function.
All right. Well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please, down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
