Combine Excel Files with Python | Beginner Friendly | Excel Python Automate with Pandas

Рет қаралды 59,944

Күн бұрын

In this video we create two scripts with python to combine excel files!
We're using fitbit sleep data to illustrate this. If you have your own fitbit, it would be cool if you used your own, but if not you can go to my github and download the files I used:
github.com/LauraMarcela505/Au...
Timestamps:
2:15 : Concatenate all excel files into one excel workbook in the same sheet
8:50 : The first script fully written out
11:06 : Multiple excel files into one excel workbook but into different sheets
15:17 : The second script fully written out
10:12 : Make sure to close the excel file you created before running or you'll get an error
13:56 : change df1 = pd.read_excel(excel_files) to df1 = pd.read_excel(excel_file) otherwise you'll get an error
15:14 : Like, Comment, Subscribe
#pandas #exceltopython #automatewithpython

Пікірлер: 171

@walkantalk 2 жыл бұрын

Thank You, Laura, for this great tutorial ! No fluff and to the point - keep it up

@thefriendlycoder_ 2 жыл бұрын

This makes me happy! Thank you :)

@cdisla98 3 жыл бұрын

Really needed to see this tutorial!! thank you so much for this!!

@bleharris4702 2 жыл бұрын

You're a star, very simple and to the point tutorial. Thank you.

@thefriendlycoder_ 2 жыл бұрын

Thank you ✨✨✨

@MrJohn2brown 2 жыл бұрын

Amazing , this saves me a ton of repetitive copy pasting every Monday. Cant wait for more vids. Many thanks

@thefriendlycoder_ 2 жыл бұрын

Thank you so much!

@michaelcrain8926 2 жыл бұрын

I am just now learning python and this is just what I needed to start with. Thank you for making this.

@thefriendlycoder_ 2 жыл бұрын

Thank you!

@johnobiomah22 Жыл бұрын

Thank you. This worked perfectly for what I needed it for

@SalehGoodarzian 2 жыл бұрын

I appreciate your helpful, concise, and very friendly video. It helped me a lot. I had spent hours to find a very short way to import all excel/csv files. Your video showed me the way. I really appreciate and I wish you all the best...I share what I did in only 5 lines, I can't thank you more (please note my code is saved in the folder with csv files...). files = glob.glob('*.csv') data = pd.DataFrame() for file in files: current = pd.read_csv(file) data = pd.concat([data, current], axis=0)

@jenniferdios 3 жыл бұрын

pandas is so awesome. Need to get more familiar with it. Thanks for this tutorial!!

@user-lp1iu4eo5s Жыл бұрын

Explained it very clearly.... Thank u 😊

@MuhammadAdeelAhmed 2 жыл бұрын

Thank you Laura, something I learnt today, very well explained, excellent work

@thefriendlycoder_ Жыл бұрын

Thank you!!

@LanceToledo 3 жыл бұрын

So proud of you Laura!!!

@thefriendlycoder_ 3 жыл бұрын

Thanks Lance :')

@rikardoledal2270 2 жыл бұрын

Thank you!! I love excel and are now learning to code Python. It was a great video. I'm subscribing :)

@thefriendlycoder_ 2 жыл бұрын

I love to hear that! thank you :)

@binarystar4947 Жыл бұрын

Welcome to you tube :) and wanted to see more of the videos related to pandas tips and tricks

@akanobody5622 2 жыл бұрын

Perfect, thanx I have to do many repetitive excel sheet cleaning and this tutorial is very helpful for me, I just hope this can handle the amounth of data I work with.

@thefriendlycoder_ 2 жыл бұрын

This makes me so happy! I hope it works for you

@SuperNunera Жыл бұрын

Gonna use that today ! Ty for sharing !

@sirajmultani5724 2 жыл бұрын

Thank You Laura, it really helped me and saved hours of work. I really appreciate! please keep up the good work !!!

@thefriendlycoder_ 2 жыл бұрын

Thank you Siraj!

@dalixolombuyane5119 Жыл бұрын

Love it. Thank you

@rmscrisostomo 2 жыл бұрын

Thank you so much, this is a very good tutorial! Congratulations!

@thefriendlycoder_ 2 жыл бұрын

Thank you! :)

@zeinplayy Жыл бұрын

Best tutorial ever, thanks🎉

@robertspalding5635 2 жыл бұрын

I am only a beginner but this was incredibly cool and so are you. thank you.

@thefriendlycoder_ 2 жыл бұрын

Thank you!

@ngrunoz Жыл бұрын

Thank You Laura for the great tutorial. I have a scenario were I would like to combine only sheet with same name from various workbooks and either write each sheet for similar sheet names or a concatenated csv file for similar sheet names.

@miladghalehnoei8300 Жыл бұрын

it was very helpfull for me. thank you so mach

@theshuffle0409 Жыл бұрын

Great explanation Laura...

@arielmoraes9960 3 жыл бұрын

Wow great video!!!

@gloriachica986 2 жыл бұрын

Thank you Laura

@michaelventarola7100 2 жыл бұрын

Liked your tutorial. Would like to do lookups from one sheet to another. That would be a great tutorial.

@mariachica6913 3 жыл бұрын

Thanks, I will try ❤️

@shubhamgade7180 3 жыл бұрын

Thanks for the content!! If possible can you make a tutorial on joining 2 excel sheets on basis of one common column between the two files? So as the values in second excel with join the values in first excel.

@patrickwheeler7107 3 жыл бұрын

Thanks for putting this together! Would love to see you add some logic to look for last row of an empty data set and omit the rest of the rows, like foot notes from some reporting systems. EIther way this was very helpful!

@thefriendlycoder_ 3 жыл бұрын

Thanks Patrick, good idea!

@patrickwheeler7107 3 жыл бұрын

@@thefriendlycoder_ Thanks for replying. Here IMO would be a fantastic project to run through. You have several files. All the same column length. a couple of real world examples I run into. As mentioned looking for the last record, maybe blank, or a specific word or length of word. Delete a column or columns Delete a row Rename missing values to something to group them correct in a column. I am mainly an SQL developer and use some VBA when unable to use our ETL tool so learning Python is really helpful for me. Thanks!

@Oostenwald-s5q 27 күн бұрын

You made this look very easy. Please, please, please make more of these Python videos. I extract information from 18 different Excel files and consolidate the relevant data from each file to create a report on a single Excel sheet. There must be an easier way to accomplish this, as my laptop struggles to execute all the commands and formulas I use.

@thefriendlycoder_ 19 күн бұрын

Thank you! Great idea

@utkarshtripathi9118 Жыл бұрын

U Healed me alot Thank U Thank u so much

@HartsoBig 2 жыл бұрын

This was great! However, going to your channel only to see one upload was not! More videos! I would love to see you continue this sleep data project and automate turning the data into visuals. Thanks!

@bluchips2055 2 жыл бұрын

That was good, thanks.

@torque6389 2 жыл бұрын

Great video, thank you!

@thefriendlycoder_ 2 жыл бұрын

Thank you!

@cuneytozkurt4867 2 жыл бұрын

Hey, it's really great!!!

@gloriachica986 3 жыл бұрын

Wow ,Thanks 😘🕊🙏🏻

@sravankumar1767 Жыл бұрын

THANKS 😊 NICE EXPLANATION 👌 👍

@sureshn3951 2 жыл бұрын

Hey Laura, This tutorial is very Helpful. Can you please do another video on how to combine cols by cols. thanks

@gmagcaling Ай бұрын

Very helpful!

@thefriendlycoder_ Ай бұрын

Thank you!

@deepanshusingh6057 2 жыл бұрын

Beautiful Content

@thefriendlycoder_ Жыл бұрын

Thank you! :)

@mhmoudkhadija3839 Жыл бұрын

Thanks! This tutorial is really useful..

@thefriendlycoder_ Жыл бұрын

Thank you!

@kamrank680 3 жыл бұрын

Thanks!! It helped me

@thefriendlycoder_ 3 жыл бұрын

That's awesome! Thanks for the feedback :)

@iceiceisaac 2 жыл бұрын

Nice, please keep posting

@thefriendlycoder_ 2 жыл бұрын

Thank you!

@ahmedabdelaziem4497 Жыл бұрын

Many thanks

@deepakhaldar2983 4 ай бұрын

keep upload new videos, you have just saved my hours by this. Thankyou. Can you please make a video on sharing Auto Mail to different stakeholders with attachement of their mentioned data.

@thefriendlycoder_ 4 ай бұрын

Thank you! A great suggestion :)

@cdlowe3 Жыл бұрын

This was very helpful - Thanks for putting this together. How would you format the cells?

@marceloestrada1934 Жыл бұрын

thanks Laura! amazing tutorial! I have many excel files but with different headers. How can I match or concatenate headers with similar names or a condition that include one word for each column in each file?

@akhgh07 2 жыл бұрын

really helpful thank you

@thefriendlycoder_ 2 жыл бұрын

Thank you!

@noahinaus Жыл бұрын

Laura, you seem like a night owl! XD Thanks for the great tutorial and I really like your energy all along 😁

@d515144d Жыл бұрын

Thank You

@aleksandrekacharava2318 Жыл бұрын

Thank you for the great info! One more question: can we keep cells' format while combining the files? For example, if there is some color in certain files' cells and we need to keep them in those colors. Thank you.

@thegracegong 2 жыл бұрын

thanks queen

@thefriendlycoder_ 2 жыл бұрын

💓

@samahmedalone6573 2 жыл бұрын

Thanks, 👍👍👍

@anujsaxena8268 3 жыл бұрын

Really Appreciable, Much helpful 💕 Please do make more videos on Numpy and Pandas facing much errors in this

@thefriendlycoder_ 3 жыл бұрын

Thanks for the encouragement Anuj!

@anujsaxena8268 3 жыл бұрын

@@thefriendlycoder_ Most Welcome

@mohamadhabibhomeidi5634 3 жыл бұрын

Great....

@sainbayarzorig 2 жыл бұрын

thans you very much

@anselmeouohi7914 Жыл бұрын

Really good tutorial ! How about if we want to compare data in dataframe before concatenate?

@fafa5841 2 жыл бұрын

thank you so much. can you make a video on how to read specific cell value/column/ raw from multiple workbook or sheets and combine it into a new excel file?

@neeljain5169 2 жыл бұрын

Thanks for the great tutorial ! I am trying to combine two excel files using your code. But its not reading the hyperlink values and I am getting N/A in that column. Could u pls suggest what changes can be done to have the hyperlink values as well.

@Frank-si2jd Жыл бұрын

Hi Laura, thanks for your very well done tutorial and using this helpful example! Question though; I understand having Python and adding/including libraries like Pandas and Glob and the necessity to have them installed on your computer to be able to program. But what if I hand this compiled executable program over to a friend, does he needs to have Python, Glob and Pandas installed on his computer? Or is running the executable enough for him? I do understand I also need to fix the location (path) of his Excel file on his computer in the script.

@PONCEJE 6 ай бұрын

You are fantastic thank you for video.

@thefriendlycoder_ 6 ай бұрын

Thank you!!

@PONCEJE 6 ай бұрын

Waiting for more videos.

@heythemhamlaoui2392 3 жыл бұрын

thank you

@beginho2454 2 жыл бұрын

Nice and pretty teacher.

@miltondiaz2531 2 жыл бұрын

Hola Laura, tu video hizo que resuma mi codigo a de mas de 10 filas a 4. Saludos desde Perú. Gracias

@thefriendlycoder_ 2 жыл бұрын

Gracias! ☺️

@mattviverette 2 жыл бұрын

Can you think of a way to reorder the sheets so that the month names are in the correct chronological order without numbering the month names?

@temukaify 2 жыл бұрын

Really nice. I wish I also had a video how to delete all the rows that I don't need and combine files in a column fashion - join by columns.

@temukaify 2 жыл бұрын

+1 request - Please zoom in on codes, because it's difficult when they're so tiny.

@thefriendlycoder_ 2 жыл бұрын

Thanks for the feedback :)

@siyakholwakhiphi1556 2 жыл бұрын

That's great staff, I emulated the code and it worked only on your spreadsheets but on the ones I want to use, kind to explain what could be the problem? Thanks in advance

@blankseventydrei 2 жыл бұрын

nice work and I learned alot, and will put it to use, but why do this in python when it can be done with VBA? what would be the advantage?

@kabirgive9356 2 жыл бұрын

Really helpfull could you please tell me could I append each excel file and then save all the data generated into a single excel file please it would be really helpfull

@Smolenishev 2 жыл бұрын

Thanks!

@thefriendlycoder_ 2 жыл бұрын

Thank you!

@fernandoortiz6069 2 жыл бұрын

great job

@thefriendlycoder_ 2 жыл бұрын

Thank you!

@kudakwashejena1667 Жыл бұрын

Thank you for this. Can I combine sheets into one if I’m dealing with more than 3million rows of data?

@komangsengku133 2 жыл бұрын

thanks

@sravankumar1767 Жыл бұрын

How can we convert each xlsx sheet as single csv to load to destination. Can you please explain this scenario

@call-me-potato. 11 ай бұрын

thanks madam for such a nice tutorial , I just started learning programming. no previous back ground . kindly please make more easy videos and please increase size of your screen its very small. and wall paper on your screen is amazing. kindly any one who is reading my comments , advice me on python , what things should i learn initially.

@thefriendlycoder_ 7 ай бұрын

Thank you so much! Will take all of this into consideration.

@terrytolbert3098 2 жыл бұрын

i am a heavy VBA (Excel/ Access/PowerPoint) user and always wondered how Python could also do the same things. can you also please show me how Python can do these. - auto find the last row/ column of data, if the data has gaps. - find a sheet in a excel workbook find (multi sheets, and you only want data from 1).

@tranngocquynh94 2 жыл бұрын

Hi Laura, I have just following your tutorial. However, i have duplicate headlines that need to be removed, can you share how can we delete it? Thank you so much

@jessesmith2250 2 жыл бұрын

when running the script at 6:31 I get the following error message: AttributeError(f"module 'pandas' has no attribute '{name}'")

@nasserabdal523 5 ай бұрын

Thank you very much on this explan But i ask about copy of format excel file as color and bolt text for each file when i combine in one

@thefriendlycoder_ 5 ай бұрын

Hi! For formatting (e.g, adding color, making text bold) I suggest using the library openpyxl instead of pandas.

@lordprocessor7253 2 жыл бұрын

You leave me no choice….”subscribed”

@thefriendlycoder_ 2 жыл бұрын

😂😂😂 thank you!

@noeldoller2958 2 жыл бұрын

great work Laura. Please do more excel automation specially showing how to copy range of cells from multiple excel files to a single master file, please

@aseemtripathi3555 2 жыл бұрын

How Laura can you please guide me how to fetch only current date excel files from specific time frame

@TajMikel 10 ай бұрын

Why no more tutorials? This one was really helpful!

@thefriendlycoder_ 7 ай бұрын

Thank you for this comment! I posted another video

@hirengajjar4159 2 жыл бұрын

i want to marge multiple excel, all excel have common 3 sheets. please help to get code for the same, i want all data merge in new blank excel sheet wise

@FahmiFashaApridiansyah 2 жыл бұрын

I always get ValueError: Invalid file path or buffer object type:

@allanrasmussen5789 2 жыл бұрын

it's fine what you're doing, but you need to set the font size up

@ryanrumsey2830 Жыл бұрын

Hello Laura .. loved your video. Can you do a video on combining data from multiple excel files using a "Vlookup" to search for certain data?

@thefriendlycoder_ Жыл бұрын

Good idea thank you!

@Karimahmedsaleh Жыл бұрын

I`d like to thank you for your great video, but I also tried and didn`t work with me, I don`t know what`s the reason

@williamstanes1381 Жыл бұрын

Hello Laura , i have a doubt about combining different files to single files but in a different sheet

@williamstanes1381 Жыл бұрын

Is there any platform so i can post my query and you can help me with it

@jonelatendido9836 6 ай бұрын

Could I use Visual Studio, Instead of IDE?

@thefriendlycoder_ 6 ай бұрын

Yes! Visual studio is a type of IDE

@Saurabhdeepsinghdeopuri 3 жыл бұрын

i cannot be able to run at 6.45, error show (warning: Workbook contains no default style, apply openpyxl's default) help[ me to resolve issue

@thefriendlycoder_ 3 жыл бұрын

Hey Saurabhdeep, does the code run before 6.45 for you?

@pankajgaurr 2 жыл бұрын

How to do it for csv files??????

@thefriendlycoder_ 2 жыл бұрын

1) change the extension from .xls or .xlsx to .csv 2) change pd.read_excel to pd.read_csv 3) change .to_excel to .to_csv

@robertreed4337 11 ай бұрын

excellent +++++++

@thefriendlycoder_ 7 ай бұрын

Thank you!!

@alikhanak123 2 жыл бұрын

Hi can you make a video on this scenario Multiple files with multple sheets, merged to one file, with all sheets in thoes multple files. If sheet name in all files are same data should concat.

@ngrunoz Жыл бұрын

Hi Randoms. Did you manage to find solution on this one. I am also trying to find solution on it.

@alikhanak123 Жыл бұрын

@@ngrunoz half way... I wanted, if File 1 2 & 3 all have sheets A B C.. And output file should have Sheets A B C with all data from files 1 2 &3 under respective sheets name

@ngrunoz Жыл бұрын

@@alikhanak123 that is great. It looks like the exact same solution I am looking for. If you comfortable sharing the solution you have.

@alikhanak123 Жыл бұрын

@@ngrunoz I will share the work I have done half way, in a while. It is not successful, may be you can make it successful.