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?
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 ??
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?
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
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).
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
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.
@@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)
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 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')
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
I appreciate your real life use case. Thanks for sharing!
You’re welcome
Thanks.. The graph is in the image format, which i cannot edit right?
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?
FIgured it out!
df_pt= pd.pivot_table(df1, index=piv_index, values=cnt_column, columns=piv_data, aggfunc='count')
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 ??
You can always embed the details in the data frame and use pandas search to drill down
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?
Hi, any guidance how to add data labels to the chart?
@radams7532- any guidance pls
Do we have python libraries for Numbers app in Mac instead of MS Excel?
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
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).
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?
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
Line no 34: worksheet.insert_image() is throwing error "AttributeError: 'Worksheet' object has no attribute 'insert_image'" Any solution for this?
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.
Yes you can. You can use a library could Openpyxl.
Good Video.
Sir How can we Automate Power Point.?
Suneel Reddy interesting comment. What part of PowerPoint would you want to automate?
@@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)
Ok let me give that some thought perhaps I can figure it out and post a tutorial in the future
Could you please share the sampledata.xlsx
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?
Check out this link I’m sure it will help: xlsxwriter.readthedocs.io/tutorial03.html
@@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')
In case we're making a video on Python we should share the code ...
Most python tutorials have code on github. Feel free to browse my github 👉🏼 github.com/satssehgal
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
what if your column name has spaces??
just put quotes around it... lol nvm
for df in file_dfs:
df.columns = df.columns.str.replace(' ', '_')
While run im getting this error "AttributeError: module 'pandas' has no attribute 'Excelfile'" is there anything todo in the code16th line above video
It is case specific... It has to be "ExcelFile" not "Excelfile"
Please share code for practice
Over a third of this video is dedicated to sharing and explaining the code.
you dont know that Excel can automate table in just 1 click only. repeatative data/
You can easily do that with Excel Power Query. 😂😂
You wouldn't be able to split the files like he did. Also customizing the pivots would have to be done manually.