Automate Pivot Tables and Charts with Python | Excel Automation Hacks

Подписаться 19 тыс.

Просмотров 38 тыс.

50% 1

Learn to automate pivot tables and charts with python and put excel on autopilot with these excel automation hacks
👉 Patreon: patreon.com/SATSifaction
👉 Facebook Group: / theaiwarriors
👉 Instagram: @theaiwarriors
👉 Corporate Training and Up skilling: levers.ai
Netfirms (Affiliate) - bit.ly/2KdJ4Dp
Linode Server - bit.ly/2XpqGi9
Bluehost (Affiliate) - bit.ly/2GxxBh1
PythonAnywhere (Affiliate) - bit.ly/2kWORVe
Heroku - www.heroku.co
NordVPN (Affiliate) - bit.ly/2W87je0
Here is a link to my python for beginners, master python course: bit.ly/2HIZS42
What Does a Data Science Executive Carry Around in 2019?
Bag - amzn.to/2HfHkJo (will fit laptop plus one tablet and a few accessories)
Laptop - 2018 MacBook Pro with Touch Bar
Tablet - 2017 9.7” iPad or
Tablet - Amazon Fire HD 10 - amzn.to/2HjeLec
Folding Keyboard - amzn.to/2JLMbE7
Power Bank - amzn.to/2VsNASc
Bose QC35 - amzn.to/2LEpqEx
Home Setup for more intense analysis and editing:
AMD MSI Vega 56 Graphics Card - amzn.to/2HkiJU1
Razor X Core egpu with thunderbolt 3 - amzn.to/2Hix579
Two LG 29” Ultrawide Monitors - amzn.to/2YtZfSi
Other options to run Python on a budget
Raspberry Pi Starters kit - amzn.to/2Q4lzPE
Any inexpensive Android Tablet (all Fire tablets with playstore side loaded)
Minimalist Keyboard and Mouse - amzn.to/2Q2w2ek
Favourite Mobile Apps to Run Python and Code
iOS - Pythonista (paid) - apple.co/2HjRVTJ
Android - Dcoder (free) - bit.ly/2Vrjb6N
Song
Credit To Author
Lahar:
Spotify: spoti.fi/2xPW7Fb
RU-vid: / lahar
Instagram: / musicbylahar
SoundCloud: / musicbylahar
Facebook: www. musicbylahar

Хобби

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

1 июл 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 40

@ItsJorgePerez 3 года назад

I appreciate your real life use case. Thanks for sharing!

@SATSifaction 3 года назад

You’re welcome

@joey5069 4 года назад

thanks! this is awesome! I have one question tho. In my graph everything is blue. Can I gave my index input a different color in te graph? so that I can differentiate between the two?

@lshankar83 4 года назад

Thanks.. The graph is in the image format, which i cannot edit right?

@tonybelair2113 3 года назад

WOW Thank you !! It s exactely what i m looking for long time. How can i drill to the pivot table data to have the details of the table like in Excel ??

@SATSifaction 3 года назад

You can always embed the details in the data frame and use pandas search to drill down

@radams7532 Год назад

Another great video! You certainly have real life scenarios that I can use. I will certainly subscribe and continue watching. I see this works for columns that have numeric data. What if I wanted to count of each item in a column?

@radams7532 Год назад

FIgured it out! df_pt= pd.pivot_table(df1, index=piv_index, values=cnt_column, columns=piv_data, aggfunc='count')

@yooalejandro386 4 года назад

Could you please share the sampledata.xlsx

@sangdao9345 4 года назад

That's amazing. Is there any way we can edit the graph in the exported file, without changing the code? That would be more convenient for business users. For example: i want to see sales by quarter in the pivot, but after that I want to see only number of orders by quarter (I don't want to put sales and orders in the same graph).

@radams7532 Год назад

I would say just create two graphs and put them next to each other or one on top of the other. Just modify "for k" loop to accommodate another graph?

@AnirudhGarg1123 4 года назад

Do we have python libraries for Numbers app in Mac instead of MS Excel?

@SATSifaction 4 года назад

Great question. Not that I know of however it’s always a safe bet to convert all your spreadsheets to csv. Alternatively I’m sure you can write an AppleScript but I haven’t tried it myself. If you do find a library let me know and I’ll be sure to give you a shout out for doing so

@ahmedmohiuddin8225 3 года назад

How to change the name of worksheet generated for Pivot table and chart Excel? If I am having same name Excel from before the new Pivot table Excel is replacing the previous Excel with same name. Kindly, let me know how to give custom name for the newly generated workbooks

@dharmdutt1771 8 месяцев назад

Hi, any guidance how to add data labels to the chart?

@dharmdutt1771 8 месяцев назад

@radams7532- any guidance pls

@danielcojocariu8438 Год назад

Just a question, you created a chart using ExcelWriter and Matplotlib with some data. You can create a pivot table using those libraries and the same data? I can't find nothing for pivot tables.

@KgRu1eS 2 месяца назад

Yes you can. You can use a library could Openpyxl.

@shubhampaul6393 2 года назад

Line no 34: worksheet.insert_image() is throwing error "AttributeError: 'Worksheet' object has no attribute 'insert_image'" Any solution for this?

@stilianstoilov3728 4 года назад

Thanks for the video. I want to do a similar thing. To read from excel file(first sheet), create a Pivot table and save it to a new sheet. This is what i have: ################### orders = pd.read_excel('/path_to_the_file/filename.xlsx', 0) table = pd.pivot_table(orders, index='Component/s', values='Key', columns='Resolution', margins=True ,aggfunc='count') ################### I'm succesfully creating the pivot table, but can't figure out how to write it in new sheet. Can you advise me something?

@SATSifaction 4 года назад

Check out this link I’m sure it will help: xlsxwriter.readthedocs.io/tutorial03.html

@stilianstoilov3728 4 года назад

@@SATSifaction I've manage to do it using the following code. 10x anyway :) # Writes the pivot table to new sheet called Pivot. with pd.ExcelWriter(path='/path_to_the_file/filename.xlsx', engine="openpyxl", mode='a') as writer: p_table.to_excel(writer, sheet_name='Pivot')

@suneelreddy4396 4 года назад

Good Video. Sir How can we Automate Power Point.?

@SATSifaction 4 года назад

Suneel Reddy interesting comment. What part of PowerPoint would you want to automate?

@suneelreddy4396 4 года назад

@@SATSifaction let's say by using python (Pandas and Matplotlib) we are created some graphs (.png or .jgp) those graphs we are saved in some folder. By using python script we need to place those images in to PPT. Sir can you send one test mail to my mail. (Suneelreddy@live.com)

@SATSifaction 4 года назад

Ok let me give that some thought perhaps I can figure it out and post a tutorial in the future

@hellofrombd1960 4 года назад

Sir, is it possible by jupytet notebook. If so please make a video. Or if you have already video of this Process by jupyter please share the link in reply

@Ashoks1985 4 года назад

While run im getting this error "AttributeError: module 'pandas' has no attribute 'Excelfile'" is there anything todo in the code16th line above video

@radams7532 Год назад

It is case specific... It has to be "ExcelFile" not "Excelfile"