4

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?

dc7
  • 401
  • 1
  • 4
  • 13
Plutonian Fairy
  • 105
  • 9
  • if you look at the second part of my answer, I think those links will help for reading and writing hyperlinks, but some of the code sounds like it might be deprecated, but not the code relating to hyperlinks, so if you can fix the deprecated code in those answers (read comments), then you might be able to do this without manually having to create a "hyperlink list" or "hyperlink dictionary". – David Erickson Oct 11 '20 at 04:52
  • @DavidErickson ok, thanks for the tip, I will try using a dictionary as that seems like the more straightforward option. – Plutonian Fairy Oct 11 '20 at 05:30

1 Answers1

3

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

David Erickson
  • 16,433
  • 2
  • 19
  • 35