Pandas Pivot table and Excel's style cells

user_3727223 0
Viewed 275

I get data measurements from instruments. These measurements depend on several parameters, and a pivot table is a good solution to represent the data. Every measurement can be associated to a scope screenshoot to be more explicit. I get all the data in the following csv format :

Data sample

The number of measurements and parameters can change.

I am trying to write a Python script (for now with Pandas lib) which allows me to create a pivot table in Excel. With Pandas, I can color the data in and out of a defined range. However, I would like also to to create a link on every cell who can send me to the corresponding screenshot. But I am stuck here.

I would like a result like the following (but with the link to the corresponding screenshot) : desired result

Actually, I found out a way to add the link thanks to the =HYPERLINK() Excel function to all the cells with the apply() Pandas function. However, I cannot apply a conditional formatting thanks to xlsxWriter anymore because the cells don't have a numerical content anymore

I can apply the conditional formatting first and then iterate through the whole sheet to add a link, but it will be a total mess to retrieve the relation between the data and the different parameters measurement

I would like your help to find ideas and efficient ways to do what I would like

xlsxwriter pandas pivot-table dataframe python
1 Answers

xlsxwriter has a function called write_url ,but first while creating new worksheet you must apply write_url and then use openyxl to insert your pandas data frame

1)create worksheet and insert write_url

2)use openyxl to write data into already formatted cells.

user_145706290
Related