Python export dataframe complete column as hyperlinks

Question

I am exporting dataframes to excel sheets. Two columns have https addresses. I want to make them these columns hyperlinks. So, when I open the excel sheet, I can simply click the hyperlink.

Present:

Expected solution:

My export code:

# jobsdf has two columns Job URL, Apply URL that needed to be hyperlinked
with pd.ExcelWriter('My_jobs.xlsx') as writer:
  jobsdf.to_excel(writer,sheet_name='jobs_list')

How to hyperlink the two columns Job URL, Apply URL and then export to the excel?

Update: after the accepted answer, the excel sheet shows them as a hyperlinks (with no blue underline below)

I think this is an Excel issue, not a Pandas one

Josh Friedlander
– Josh Friedlander

2022-09-05 18:15:49 +00:00
Commented Sep 5, 2022 at 18:15 — Josh Friedlander
– Josh Friedlander, Commented Sep 5, 2022 at 18:15

anuar · Accepted Answer · 2022-09-05 18:31:54Z

3

You can try editing the cells so that whenever they are converted to excel they are automatically read as a hyperlink. Provided I am correct with the excel formatting, the change would go like this.

jobsdf['Job Url'] = jobsdf['Job URL'].map('=HYPERLINK("{}")'.format)
jobsdf['Apply Url'] = jobsdf['Apply URL'].map('=HYPERLINK("{}")'.format)

Now all your cells will look like this =HYPERLINK("https://the.corresponding.link") and once converted, excel will read them as hyperlinks

answered Sep 5, 2022 at 18:31

anuar

961 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mainland Over a year ago

Nice solution. I updated my question to show the answer. Only thing is, I don't see the underlined blue lines for the hyperlink. Can I get these as well?

Collectives™ on Stack Overflow

Python export dataframe complete column as hyperlinks

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related