I have converted a pandas DataFrame to an Excel sheet using df.to_excel.
Now, I want to add hyperlinks to the values in one column. In other words, when a customer sees my excel sheet, he would be able to click on a cell and bring up a webpage (depending on the value in this cell).
Building on the approach by @guillaume-jacquenot we can use apply to apply this to an entire Series.
df = pd.DataFrame({'Year': [2000, 2001, 2002 , 2003]})
For cleanliness, I wrote a helper method.
def make_hyperlink(value):
url = "https://custom.url/{}"
return '=HYPERLINK("%s", "%s")' % (url.format(value), value)
Then, apply it to the Series:
df['hyperlink'] = df['Year'].apply(make_hyperlink)
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")
make_hyperlink function directly into .apply method like df['Year'].apply(make_hyperlink).
; between the arguments.You could use the HYPERLINK function
import pandas as pd
df = pd.DataFrame({'link':['=HYPERLINK("http://www.someurl.com", "some website")']})
df.to_excel('test.xlsx')
df.to_excel('test.xlsx') on the above code and try it out, i just tried an anchor link and it worked for me
" in it? Thanks" in it you need to replace the " with %7B. One can use str.replace() for that.From @maxymoo's answer, here is a full 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)
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))
I was generating text files from a excel file and wanted to link the names of the generated .txt files to a particular existing column in the Dataframe.
I was trying to push the local drive directory where the generated .txt files are stored, to the corresponding "File Name". So that on clicking the file name, it will open the .txt file.
rancheck_DF = pd.read_excel(excel_file, delim_whitespace = True, encoding = 'utf-8')
for index_df in range(len(rancheck_DF)):
Desc = rancheck_DF.loc[index_df,'Description Text']
MainFile = rancheck_DF.loc[index_df,'File Name']
fileName = r'.\Documents\TestF\TestF_{}.txt'.format(index_df)
with open(fileName, 'w', encoding='utf-8') as txtfile:
txtfile.write(Desc)
rancheck_DF.loc[index_df,'File Name'] = '=HYPERLINK("{}","{}")'.format(fileName,MainFile)
rancheck_DF.to_excel('./damn.xlsx', index=None)
