how do I process an excel file with hyperlink/url in pandas?

Question

I have an excel file that has one column filled with Hyperlinks, I read it using df = pd.read_excel() then filtered it and saved it to a new excel file with df.to_excel().

The problem is that I have now lost the clickable hyperlinks, instead, there's just the text(not a hyperlink)

Can I use pandas for this? or should I be using some other library?

Answer 1

You can use the import xlsxwriter library to add hyperlinks. Speaking of hyperlinks, the example here shows some examples such as:

worksheet.write_url('A5', 'http://www.python.org/', tip='Click here')

But, if you don't want to manually write a line of code for each cell, then you can loop through and add the hyperlinks dynamically if you have a list of all the hyperlinks.

hyperlinks = ['a.com', 'b.com', 'c.com' ... etc.]

for i in range(1, len(hyperlinks)):
    worksheet.write_url(f'A{i}', hyperlinks[i-1], tip=df['column string'][i-1])

Your hyperlinks would obviously have to be in the correct order in the list, or you could create a dictionary that makes the text and hyperlink a key-value pair and use .map to bring the hyperlink into your dataframe as a column. Then you could sort the values and send the hyperlink to a list with hyperlinks = df['hyperlink'].to_list(). Then you could run the for-loop.

But, I think you will have to create a list or dictionary first.

---

Also, check out this answer for reading in date with hyperlinks using openpyxl:

Pandas read_excel with Hyperlink

And, this one for writing data with hyperlinks using pandas:

add hyperlink to excel sheet created by pandas dataframe to_excel method