Difference between revisions of "Merge files based on ID, then do calculation"
Jump to navigation
Jump to search
(Created page with "<pre> import pandas as pd import plotly.express as px ## pip install plotly excel_book1a = 'Purchases - Home A.xlsx' excel_prices = 'PriceBook.xlsx' df_prices = pd.read_excel...") |
|||
Line 28: | Line 28: | ||
</pre> | </pre> | ||
''Excel files are in: G:\Dropbox\All Folders\python\python_desktop\automate_excel'' | |||
==[[#top|Back To Top]] - [[Python|Main Category]]/[[Python_Excel_Related| Excel Category]]== | ==[[#top|Back To Top]] - [[Python|Main Category]]/[[Python_Excel_Related| Excel Category]]== | ||
[[Category:Python]] | [[Category:Python]] |
Latest revision as of 21:25, 9 September 2020
import pandas as pd import plotly.express as px ## pip install plotly excel_book1a = 'Purchases - Home A.xlsx' excel_prices = 'PriceBook.xlsx' df_prices = pd.read_excel(excel_prices) df_homea = pd.read_excel(excel_book1a) #print(df_prices, df_homea) ## merge the spreadsheet on the id df_total = df_homea.merge(df_prices, on='ID') ## Create a new column df_total['Total Price'] = df_total['PURCHASED AMOUNT'] * df_total['Price'] df_total.to_excel('totl_combined.xlsx') print(df_total) fig = px.pie(df_total[['MATERIAL', 'Total Price']], values='Total Price', names='MATERIAL') fig.show()
Excel files are in: G:\Dropbox\All Folders\python\python_desktop\automate_excel