Create hyperlink for each item in column in python(csv)

Question

I am trying to create a Hyperlink for each item in a column based on another column.

Here is an image to help you understand better:

Each title should hyperlink to the corresponding URL. (when you click apple, it should go to apple.com, when you click banana it should go to banana.com, so on) Is there a way to do this to a CSV file in python?

(Let's say my data is 2000 rows)

Thanks in advance

you say your final thing is to have a hyperlink, but what does one open to have a hyperlink at all? Like, do we have an excel file or a word doc where they click it? A CSV as the end-product won't work, I don't think, because a CSV won't have hyperlinks in it. You could start with a CSV and use Python to reformat/re-save it as an Excel file where column A is hyperlinks. Is that what you want? — scotscotmcc
– scotscotmcc, Commented May 24, 2021 at 23:23
Yes exactly, I'm planning to re-save it as an excel file. Thanks — Krishay Rastogi
– Krishay Rastogi, Commented May 24, 2021 at 23:24

scotscotmcc · Accepted Answer · 2021-05-25 02:12:38Z

You can use (I used) the pandas library to read the data from csv and later write it to excel, and leverage the Excel function HYPERLINK to make the cell a, well, Hyperlink. The Excel function for HYPERLINK requires the url we are going to (with http:// or https:// at the beginning) and then the visible text, or friendly-name.

One thing the Excel file will not have when you open it is blue underlined text for the Hyperlinked cells. If that is needed, you can probably leverage this solution somewhat and also use the XlsxWriter module.

import pandas as pd

df = pd.read_csv("path\test.csv") #put your actual path here
# this will read the file and save it is a 'dataframe', basically a table.
df['title'] = '=HYPERLINK("https://' + df["url"] +'","' + df["title"]+'")' 
"""the df that we originally pulled in has the columns 'title' and 'url'. This line re-writes the values in 
'title' to use the HYPERLINK formula in Excel, where the link is the value from the URL column with 'https://'
added to the beginning, and then uses the value from 'title' as the text the user will see in Excel"""
df.to_excel("save_location\test2.xlsx",index=False) #this saves the new file as an Excel. index=False removes the index column that was created when you first make any dataframe

If you want your output to just be one column, the one they click, your final line will be slightly different: df['title'].to_excel("save_location\test2.xlsx",index=False)

Ziggy · Accepted Answer · 2021-12-15 17:20:38Z

0

Did not work or probably you have included too many 's

import pandas as pd
df = pd.read_csv('test.csv')
df['URL'] = 'https://url/'+df['id'].astype(str)
keep_col = ['URL']
newurl = df[keep_col]
newurl.to_csv("newurl.csv", index=False)

This code is working but the output file does not show a clickable url

answered Dec 15, 2021 at 17:20

Ziggy

939 bronze badges

1 Comment

Ravi Kumar Gupta Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Create hyperlink for each item in column in python(csv)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related