How to output excel files with multiple urls in one cell with pandas?

Question

One of my dataframe element contains text in html format : This is <a href="https://www.google.com">google</a> and this is <a href="https://www.yahoo.com">yahoo</a>

I want to save this dataframe in excel file.

Can the excel file show the string as This is google and this is yahoo with two urls in one cell?

Thanks

Anwarvic · Accepted Answer · 2020-06-22 16:37:16Z

1

You can do something like this:

import re
import pandas as pd

df = pd.DataFrame({"text": ['This is <a href="https://www.google.com">google</a> and this is <a href="https://www.yahoo.com">yahoo</a>']})

df["links"] = df.text.apply(lambda x: re.findall(r'<a href="(.+?)".+?', x))
df.text = df.text.str.replace(r"<a.+?>(.+?)</a>", r'\1', regex=True)
print(df)
#                               text                                            links
#0  This is google and this is yahoo  [https://www.google.com, https://www.yahoo.com]

edited Jun 22, 2020 at 16:37

answered Jun 22, 2020 at 10:51

Anwarvic

13.2k5 gold badges60 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Evgeny Over a year ago

Followed by df.to_excel() probably

Chan Over a year ago

Thank you, Anwarvic. However, I want to produce the effect: when I export df by df.to_excel, that cell in excel will contain 2 clickable urls in one cell.

Collectives™ on Stack Overflow

How to output excel files with multiple urls in one cell with pandas?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related