I'm looking to try to sort some "tables" in Excel via Python as if I were doing it manually (that is selecting the range and then clicking sort). These are the tables I'm talking about:
Each of these cells in the tables excluding the names contains a formula such as a vlookup into a different sheet holding all of the data such as how many sales John Doe has made today and how many has John Doe made overall.
I would like to sort these tables via Python by the condition of sales from highest to lowest and then if sales numbers are the same then by conversion rate highest to lowest.
This is what I want to end up with:
I would like to keep the conditional formatting if possible or if possible add it into the code.
I have experimented sorting with Pandas however it has deleted the conditional formatting and has changed my conversion percentages into decimals.
This is the code I experimented with Pandas.
Sheet1 is where the tables are located, Sheet2 is the where the call numbers are pulling from, Sheet3 is where the sales numbers are being pulled from.
import pandas as pd
xl = pd.ExcelFile('file.xlsx')
df = xl.parse("Sheet1")
df = df.sort_values(by=["Sales", "Conversion"])
df2 = xl.parse("Sheet2")
df3 = xl.parse("Sheet3")
writer = pd.ExcelWriter('file.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)
df3.to_excel(writer, sheet_name='Sheet3', index=False)
writer.save()