Тёмный

Excel Python Appending Data Frames From Multiple Worksheets - Episode 2621 

MrExcel.com
Подписаться 158 тыс.
Просмотров 5 тыс.
50% 1

Опубликовано:

 

4 окт 2024

Поделиться:

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист
Посмотреть позже
Комментарии : 31   
@vinamrachandra9611
@vinamrachandra9611 Год назад
DataFrameName = xl("cell range", headers=True/False) should exclude the header row. This can be used in second and third data-frames. eg. df2 = xl(OtherYear!A1:I293,headers=False)
@MrXL
@MrXL Год назад
There have been heated discussions with the developers on this point. To actually exclude the headers, I would have to start the range at A2. This seems to annoy a lot of people. Using Headers=False makes sure they are part of the data and not treated as headers. Sort of like pressing Ctrl+T and choosing Headers=False.
@vinamrachandra9611
@vinamrachandra9611 Год назад
@@MrXL Thanks for letting me know. I just assumed it will work without testing and allowing for the fact that it is still in Beta. Your solution is elegant as usual.
@philippebrillault1140
@philippebrillault1140 Год назад
You can do this : df = xl("Table_name", headers=True) df.values will give you the data without the headers. the option headers=(True|False|None) doesn't seem to remove the headers.
@big1975E
@big1975E Год назад
This is awesome! Just experimenting I wanted to see if there was a way to dynamically get the ranges for the data frames. I created two dynamic na.ed ranges using the OFFSET function then put the names in the python expression instead of the cell range and it worked!! New data was automatically added to the named range and to the concatenated data frame.
@MrXL
@MrXL Год назад
Great tip! Thanks for trying it.
@nadermounir8228
@nadermounir8228 Год назад
Thank you Mr Excel for introducing all these new features :)
@richardhay645
@richardhay645 Год назад
Very helpful! Thanks.
@SyedHassanAbbasNaqvi-p6i
@SyedHassanAbbasNaqvi-p6i 7 часов назад
Hi can you make a video to show how to unpivot data using python code
@rickswineberg
@rickswineberg Год назад
Mr. Excel will it accept a table name instead of the range? Finally, can covert the output to table? Thanks
@MrXL
@MrXL Год назад
You can point to tables for input. But the output can not be a table.
@pkup10
@pkup10 Год назад
If I update the data in one of the data frames, say add a row to df2, will it update in the concat?
@suatmozgur
@suatmozgur Год назад
If you are referring to a range (not table) then it will only update if you "insert" the row "in" the range. For example, xl("Sheet1!A1:C4", headers=True) will be updated to xl("Sheet1!A1:C5", headers=True) in this case. However, adding a new row to the next available row won't trigger the range update in the PY formula. However, if you used a table to create the dataframe then it will work even if you added a new row. Basically, it works just like any other formula in Excel.
@OzduSoleilDATA
@OzduSoleilDATA Год назад
Thanks for this answer. I was also wondering how dynamic this is.
@user-mv3ek7jh8g
@user-mv3ek7jh8g Год назад
Hi, Thank you very much for this time warp :). Is there a way to access those Python objects from VBA? (e.g. data frames, or even using Python functions) Or on the other hand, accessing VBA from Python?
@readistreet9383
@readistreet9383 Год назад
Hi, with CTRL+E you can select al the table at once
@RonDavidowicz
@RonDavidowicz Год назад
These Python videos would be more useful if they came out after the roll out to us regular users. A little frustrating seeing this without being able to try.
@MrXL
@MrXL Год назад
My goal is to have a book out by the time it rolls out to regular M365. These videos are a way to “remember” the building blocks for the book.
@richardhay645
@richardhay645 Год назад
Really easy ti sign up for Beta channel. If you can't at work then 365 home subscription quite cheap. Besides these videos will stll be around whenever you do get access to Excel Python.
@vinamrachandra9611
@vinamrachandra9611 Год назад
@@MrXL I would love to help with the book in any way I can. Proof reading, testing, screenshots...
@MrXL
@MrXL Год назад
@@vinamrachandra9611 send me an email to pub at MrExcel dot com.
@GeertDelmulle
@GeertDelmulle Год назад
…episode 2621-and-a-half… haha, that was funny! :-)
@MrXL
@MrXL Год назад
I hoped someone would laugh at that!
@philippebrillault1140
@philippebrillault1140 Год назад
This is great for data within the same workbook but how do you consolidate data from external workbooks ? I tried the pandas read_csv() or read_excel() functions but failed. Apparently, Python+Excel does not seem to recognize the path + file combination. I tried both options in native Python and it works perfectly.
@MrXL
@MrXL Год назад
It’s a huge limitation. Python in Excel can’t read from anything external to the workbook. I can’t get any image compare code to work because I can’t get the images loaded. I am creating a new video on abandoning Python in Excel and moving to a local install of Python.
@philippebrillault1140
@philippebrillault1140 Год назад
@@MrXL Thanks for your reply. I tried several versions of the xl() function but it works only for workbooks already loaded. Re: your video, the local version of Python is working very well for consolidating multiple files from various sources (Excel, csv, json, etc.). The code is simple and very readable. On top of this, the Jupyterlab environment is very good for documenting the code. Thanks for your videos. Eager to see your work on Python !
@AdminGRG
@AdminGRG Год назад
Is python only for windows excel? whatabout mac excel?
@suatmozgur
@suatmozgur Год назад
Of course not on Mac yet :) It is a little bit disappointing, because it doesn't even require a local third party installation and as far as I understand, the function is simply interpreted on a Microsoft cloud server. So, I don't understand why they didn't include this update in beta on Mac, but it is what it is. If you are interested testing it without having it running in your Excel, you might want to check my detailed reply to @RonDavidowicz for the Jupyter Notebook workaround. It is not the exact same thing but very close to understand and learn it before it arrives to Mac.
@ricos1497
@ricos1497 Год назад
We all know that the Mac is a poisonous machine that should be placed on a rocket and fired at the sun anyway, so that's probably why Microsoft didn't offer this for such users.
@jerrydellasala7643
@jerrydellasala7643 Год назад
With all due respect Mr.E, it makes NO sense to Zip a single Excel file. If you're concerned about file size, saving as an XLSB is even smaller than today's Zip file and still supports Macros. A Zip file makes sense for multiple files, and maybe that's just how you're set up, but otherwise zipping a single Excel file is just a hassle - repeated the hundreds or thousands of times the file is downloaded.
@MrXL
@MrXL Год назад
Point taken. The web utility for creating the page was built with the thought that I would be including multiple workbooks. And, by using a zip file, the zip file name can match the RU-vid Video ID which will help with database management. For 16 years, I refused to post the workbook because it was too much of a hassle. I finally paid to have a utility made to build the web page and have a place to hold the workbooks. For now, the files will keep getting zipped because that's how the system is built.
Далее
КОГДА НАКРОШИЛ НА ПОЛ #shorts
00:19
Пчёлы некроманты.
00:46
Просмотров 19 тыс.
The Excel Trick I Use EVERY DAY (and you should too!)
11:21
Are These Pivot Table Mistakes Costing You?
9:45
Просмотров 51 тыс.
КОГДА НАКРОШИЛ НА ПОЛ #shorts
00:19