You're just amazing. I spent a week on internet trying to find that solution and you made it so simple and easy. A big subscribe with a big like plus a heart 🥰

Brother , thankyou for the video , u have no idea how helpful ur content is. 😁

Thanku so much Derrick, automation with python is the primary part of my job, please keep the videos coming....

Hey Derrick, Great video. Keep doing your Job without Quitting.

Derrick simply one word. Your awesome. Many thanks for the videos. Its really very helpful. Kindly upload more videos related to excel automation.

Super sick. This is very helpful. I still havent figured out how to get python to list out the files in a folder and then do this. Ill crack it!

You Pandas Videos are awesome keep working on them.

Fantastic, this is what I exactly looking for

Hey Derrick, thanks for the awesome video. I could do the merging as shown by you, but i had query whether we could retain the format of the original excel file in the merged file?

Great lesson Derrick, thanks!

Great Thanks! Is there a way to make the spreadsheet look nicer without overlapping..Lets say I have a larger string fields which overlap, I have to open the excel and manually format each column.

You truly are a life saver! If you’re ever free to help with a query please let me know. It could be good content for you as well!

If you need to compare the data between the files, is there an option to put the data not down but beside?, like an offset or something like that

Thanks so much for this. I wonder if there are any other method if you`re combing up to 12 Excel files

Thank you very much for your videos; they are helping me a lot lately. But i've got another question: Is it possible to add the data to the next column instead of row?

Great Video Derrick. In your example, if the field names in the second file had a spelling mistake, is there a way to programmtically rename and merge.

Hi I am a begginer in python. Could you please show me a video how to remove or correct the index field in the video which u cited at the end? Thank you in advance

Thanks Derrick !! Can we create it dynamic, like we do .End(xlup), .End(xlToLeft) in VBA in place of mentioning specific columns... if we would like to merge all columns from one to another sheet...also how to drag the formula..

Works like a charm!

Thank you for this!

Awesome work Derrick 👍 Quick Question: I would like to *Compare* , *Match* and *Join* two dataframes (with multiple and different column names) with a column that have an IP address column in both dataframes. What I did: *Join* : Join = pd.concat(dataframes) *Compare* : df3= df1('IP-list').isin(df2(ip-list2) I would like to have Df3 that joins both df1 and df2 with matching values of IP columns that match along with IPs that do not match. Note: df1 and df2 should be joined side by side.

Nice job. Thank you very much.

Hi Derrick, is there way where we can move the sheets to new workbook in values but preserving the layouts and formats or charts, I mean like automating the 'move or copy' step using python

Awesome video! If you had 50 xlsx file how would you concatenate without specifying the names of each?

How to keep headers of only the first file and ignore headers of subsequent files in the output excel, could anyone help here. thanks in advance.

Is it possible to share the sample excel files as well?

Why did you use square brackets twice in Values1 and Values2?

How can I sum the data in selected columns whilst concatenating the files?

Hello! Thank you for this great information and valuable content. I’m not a pro in Python but I try to learn it. I have a problem to get this script to work, or it halv-works. I try to generate combinations and print those to an excel file. When I run the script only the Combinations are shown on screen, but no excel file is created for those combinations. What I’m doing wrong here? Strange thing is I got no error messsge either. Can you please give me any advice, I appreciate any suggestion or help. Thank you Here’s the code: import itertools import os import xlsxwriter row = 0 col = 0 combin = itertools.combinations(range(1,15),7) count_combin = [] count = 0 for i in combin: count_combin.append(i) print(count_combin.index(i)+1, i) workbook = xlsxwriter.Workbook( os.getcwd() + '\\' + 'combination.xlsx') worksheet = workbook.add_worksheet('combs') worksheet.write(row,col,i[0]) worksheet.write(row,col+1,i[1]) worksheet.write(row,col+2,i[2]) worksheet.write(row,col+3,i[3]) worksheet.write(row,col+4,i[4]) worksheet.write(row,col+5,i[5]) worksheet.write(row,col+6,i[6]) row += 1 workbook.close print('number of combinations', len(count_combin)) Hello! Thank’s for a all this valuable content. I’m not a pro in Python but I try to learn it. I have trouble to get this script to work, or it halv-works, something is missing or incorrect. I try to generate combinations and print those to an excel file. When I run the script only the Combinations are shown on screen, but no excel file is created for those combinations. What I’m doing wrong here? Strange thing is I got no error messsge either. Can you please give me any advice, I appreciate any suggestion or help. Thank you Here’s the code: import itertools import os import xlsxwriter row = 0 col = 0 combin = itertools.combinations(range(1,15),7) count_combin = [] count = 0 for i in combin: count_combin.append(i) print(count_combin.index(i)+1, i) workbook = xlsxwriter.Workbook( os.getcwd() + '\\' + 'combination.xlsx') worksheet = workbook.add_worksheet('combs') worksheet.write(row,col,i[0]) worksheet.write(row,col+1,i[1]) worksheet.write(row,col+2,i[2]) worksheet.write(row,col+3,i[3]) worksheet.write(row,col+4,i[4]) worksheet.write(row,col+5,i[5]) worksheet.write(row,col+6,i[6]) row += 1 workbook.close print('number of combinations', len(count_combin))

Very helpful thank you mate

i need to take data from users through a gui and based on that input, I need to take data from multiple excel sheets and also edit the data abit like split few of it. I dont know how to proceed. Can you help me with it?

thank you Derrick!!!

how to join two excel sheet if you dont know what attribute they have inside then you just want to join them like here location ,employee etc was known what if we have 2 excel files and 1 have emp,id 2 ecxel file have emp,cd,dvd so we now want an excel file with emp,id ,cd,dvd all 4 attribute in 3 excel file and the emp values should not be reduplicated ?

Thank you!!

Do you have the link to the excel data so I can practice too

Notice! If you have trouble with compability reading an excel file, save the file as a 2003 - excel or ".xls" instaed of a "xlsx" file.

How about if your Excel sheets all have multiple tabs? Is there a way to combine all of the tabs from all of the sheets into one Master File?

Amazing series Derrick! Love them! :D Quick question. Do you know how to merge cells from a starting cell ("test") to the next non empty cell("test1")? For example I would like to merge from every cell that is empty. Exp: Col A; Col B; Col C; Col D "test"; Empty cell; Empty cell; "test1" Final result should be: Col A; Col B; Col C; Col D "test"; "test1" Thank you!!

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

Thank you so much!

Let say you are combining excel 1 and 2... Instead of predefining... the source file name or the location... can you prompt user to type it in? does it have to be... string literral? Can it be a input box where users can search and select like opening up a word file?

AttributeError: module 'pandas' has no attribute 'read_excel' Has anyone had this kind of issue? Thank You!

I want to merge three different excel sheets. All of them have two year hourly average data, i want to merge them in such a way that the data doesn't get shrink because all three parameters in three different excel sheets have some missing data.

This 8s great, but how would you filter rows here

Hi, how can append new data in existing excel from another excel file without losing data in existing excel file.

How to add values to next columns?

Hi, i want to merge multiple Excel sheets into 1 sheet but in different tabs, so how we can we do that? And also headers are not in the first row. Thanks in advance and pls do make a video on it.

Hey Derrick first I would like to thank you for this tutorial and I have q for you: I followed r=the same steps you mentioned and I keep getting the following error= there is no file or dir. even thogh I have defined the escel files as variable as you did and made sure the names are correct. is there a problem with defining the path ? Thank you in advance

Excellent

Great video! I'm sure you've noticed that I'm binging your content. How would you go about taking data from spreadsheet and putting it in another with different column headers?

Great!

this code doesn't work on jupyter notebook df1 = pd.read_excel(excel1)

how about multiple sheets in one workbook and concat it into one long sheet?

Derrick...great video! Your video is very clear on the selection of fields. However, if there were many standardised excel files (i.e. 100 +) in the one directory . How could you select all the files in that directory?

WTF you are amazing!

Thank u brother

Bro ,,,if excel file is updating every 1 min so how to connect that in python .....

Hi Derrick I have tried to do an exercise to concatenate two excel sheets using pandas in pycharm interpreter but once i run the python script there is error message saying the excel sheets don't exist ??? how can i sort this issue ? Thanks

Hello Actually i have two excel with some data Sheet 1 SHEET_2 Country COUNTRY PLACES AUSTRALIA AUSTRALIA GARDEN FRANCE ZOO JAPAN PARK FINAL _SHEET_NEEDED COUNTRY PLACES AUSTRALIA GARDEN,ZOO,PARK FRANCE JAPAN I want to merge in the above way so how can i achieve that.

How we can choose to remove it in the python script that values 0,1,2.....

Thank bro, I have a question, - I dont want to choose exactly excel file because i have to consolidate many files (all files in a folder-I dont know how many file) - I want to consolidate all files in this folder so what I have to do?

more content please thank you

Beginner here: where is the excel file? i dont see it on github? happy to hear from anybodyyyyy thank you

Hey Derrick, Great video. Simple yet perfect. But i do have 1 question. How can you combine 2 excel sheets in the row? like {left} {right} ? Thank you.

Super..

but what is the best way of combining .. lets say, sheets from 400 excel files into one?

Shouldn't I write the path of the excel sheet 🤔

Hi I have an Excel sheet with Countries and Items as headings - I want to select 'France' from the Counties column AND 'Household' from the Items column and run out to new WB. I am using profile_region = df[df['Country']== 'France'] profile_region.to_excel('FRANCE.xlsx') but cant seem to include the Items query - any help would be great.

great.

Thanks

good work Derrik but it's not recommended to use same dataset for many videos. Try to solve some more real type of problems. Ex: DF1=>2 columns ItemCode and Preferred(Yes/No) . DF2=multiple columns including ItemCode, DF3= multiple columns including ItemCode. Now we want to fetch the Itemcodes & Prefered=Yes and any of 2 columns(among many) from DF2 (sharing column would be ItemCode) and Itemcodes & Prefered=No and any of 2 columns(among many) from DF3.

I wanna learn how to build automatic websites by python. And how to publish multiple post once in websites by using python. If you know about it please make a to z step by step video

Code run but when i run merge.to_excel("output.xlsx") this then show me ---- [from openpyxl.workbook import Workbook ModuleNotFoundError: No module named 'openpyxl']--- merge.to_excel("output.xlsx") not performs and also not filled ouput.xlsx data. i am using Windows10 . Sent me solutions pls

I got some query in excel file can u advise me how to contact me. Thanks

Hi Derrick - if I have 200 columns that are identical, is there a shortcut to include them all instead of typing them all?? Many thanks! You rock! -Cash

=AVERAGEIFS(A2:A10,Z2:Z10,">=70",Z2:Z10,"

U haven't mentioned the path of files . Which file python will read?

what if its 2 different sheets on same file?

Nyc

hey how to remove index values from the script?

What if those are xlsm files instead? :)

Hey Brother, awesome awesome video, i subbed!!!! Currently, I am having a huge issue with my excel deliverable at work. Client uses this OLD software called passport, and we have to populate the master excel sheet and uploaded onto Passport. In this master excel sheet, there are hundreds and thousands of items!!! The problem is, PassPort only recognizes certain format, for example: all letters must be capital, each cell cannot exceed 20 characters, special characters...etc....at the moment i have to check item by item to make sure all those conditions are met before uploading the Master spread sheet onto PassPort, there has to be an easy way!!!! (I watched this video because each department has their own spread sheet, in the end, I have to combine all over 10 spread sheet into the Master spread sheet, which is why this video is helpful for combining). Please give me some tips, I am spending weeks on this BS :(

cool

Code to delete the repeating sequence column

How to combine more than 10000 Excel sheets?

Why would you not just copy and paste the data from spreadsheet 2 into spreadsheet 1? Not really seeing how this saves time. By the time you’re done writing this script you could’ve just copied and pasted the data… and each time you need to run the script you’ll have to go in and manually change the file names and columns to import…

:My Written Code is: import pandas as PD Excel1="Book1.xlsx" Excel2="Book2.xlsx" df1=PD.read_excel(Excel1) df2=PD.read_excel(Excel2) Value1=df1['Name','Location'] Value2=df2['Name','Location'] dataframes=[Value1,Value2] join=pd.concate(dataframes) join.to_excel("output.xlsx") #print(dataframes)