CODEWITHSUNDEEP
pythoncsvhyperlink
Krishay R.
Krishay R.

Reputation: 2814

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

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:

assume the 3 URLs were real websites

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

Upvotes: 1

Views: 2647

Answers (2)

Ziggy
Ziggy

Reputation: 93

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

Upvotes: 0

scotscotmcc
scotscotmcc

Reputation: 3113

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)

Upvotes: 1

Related Questions