Clever Geek Handbook

Add hyperlink to excel sheet created by pandas dataframe to_excel method

I converted pandas DataFrame to Excel sheet using df.to_excel

.

Now I want to add hyperlinks to values ​​in one column. In other words, when a customer sees my Excel sheet, they will be able to click in a cell and open a web page (depending on the value in that cell).

+3


source to share


4 answers


You can use the function HYPERLINK 



import pandas as pd
df = pd.DataFrame({'link':['=HYPERLINK("http://www.someurl.com", "some website")']})
df.to_excel('test.xlsx')
+3


source


From @maxymoo answer, here is a complete example



import pandas as pd
df = pd.DataFrame({'Year': [2000, 2001, 2002 , 2003]})
df['link'] = '-'
df.set_value(0, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2000", 2000)')
df.set_value(1, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2001", 2001)')
df.set_value(2, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2002", 2002)')
df.set_value(3, 'link', '=HYPERLINK("https://en.wikipedia.org/wiki/2003", 2003)')
df.to_excel('test.xlsx', index = False)
0


source


You can use:

df = pd.DataFrame(list(range(5)), columns=['a'])

df['a'] = df['a'].apply(lambda x: '<a href="http://youtube.com/{0}">link</a>'.format(x))

HTML(df.to_html(escape=False))
0


source


Based on @ guillaume-jacquenot's approach, we can use apply

to apply it to a whole series.

df = pd.DataFrame({'Year': [2000, 2001, 2002 , 2003]})

For the sake of cleanliness, I wrote a helper method.

def make_hyperlink(value):
    url = "https://custom.url/{}"
    return '=HYPERLINK("%s", "%s")' % (url.format(value), value)

Then apply

this to the series:

df['hyperlink'] = df['Year'].apply(lambda x: make_hyperlink(x))
<P → 
    Year    hyperlink
0   2000    =HYPERLINK("https://custom.url/2000", "2000")
1   2001    =HYPERLINK("https://custom.url/2001", "2001")
2   2002    =HYPERLINK("https://custom.url/2002", "2002")
3   2003    =HYPERLINK("https://custom.url/2003", "2003")
0


source






More articles:


All Articles