Home > other >  Python export dataframe complete column as hyperlinks
Python export dataframe complete column as hyperlinks

Time:09-06

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: enter image description here

Expected solution: enter image description here

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) enter image description here

CodePudding user response:

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

  • Related