Search code examples
pythonpandasdataframecsvhyperlink

I want one column of Panadas dataframe which is a URL and which I save as CSV to be directly clickable

I want to write this data frame to csv file where column B is shown as clickable link in Excel on the first go and not clicking it with enter. Following code should have worked but I still donot get a underlined clickable hyperlink in Excel csv file.

def main():
    pass

    if __name__ == '__main__':
        main()
    import requests
    import pandas as pd
    import io
    import sys

    df = pd.DataFrame(columns = ["A", "B"])
    df.at[0, 'A'] =  "Link1"
    df.at[0, 'B'] =  "https://nseindia.com"

    def make_clickable(val):
    df.style.format({'B': make_clickable})

    print(df)

#now I want to write this to csv file where column B is shown as clickable link in ExceL

with open('C:\\dropbox\\apy\\test.csv', "w", encoding='utf-8') as f:
df.to_csv(f, index=False, lineterminator='\n')
f.close()
Solution

  • I could solve it like this

    df.loc[(df.B!= ""),'B']= '=HYPERLINK("'+ df['B']+ '")'