Format string with values from column using pandas

Question

Using Python 3, I'm trying to replace a certain word in a URL that I've put into a Dataframe with 732 rows of the same URL. This is the URL: http://dbarchive.biosciencedbc.jp/kyushu-u/hg19/eachData/bed20/**ID**.bed.

I have another Dataframe that contains 732 rows of different experimental IDs. I want to be able to replace the word 'ID' in the URL with each of the experimental IDs so that I get an updated Dataframe with every single one of the 732 URLs I need to download a .bed file into Python.

As a side note - from there, is it possible to download a .bed file into Python without having to first save it through my browser and then upload it into Python?

cs95 · Accepted Answer · 2019-05-31 05:01:27Z

1

Use map with str.format.

import random

# Setup
url = 'http://.../bed20/{}.bed' 

np.random.seed(0)
df = pd.DataFrame({'ID': np.random.choice(100, 5).astype(str)})

df['ID'].map(url.format)

0    http://.../bed20/44.bed
1    http://.../bed20/47.bed
2    http://.../bed20/64.bed
3    http://.../bed20/67.bed
4    http://.../bed20/67.bed
Name: ID, dtype: object

Replace with your own URL and ID dataframes.

Alternatively, use a list comprehension (should be around the same in terms of performance) ...

[url.format(x) for x in df['ID']]    
# ['http://.../bed20/44.bed', 
#  'http://.../bed20/47.bed', 
#  'http://.../bed20/64.bed', 
#  'http://.../bed20/67.bed', 
#  'http://.../bed20/67.bed']

df.assign(ID=[url.format(x) for x in df['ID']])

                        ID
0  http://.../bed20/44.bed
1  http://.../bed20/47.bed
2  http://.../bed20/64.bed
3  http://.../bed20/67.bed
4  http://.../bed20/67.bed

edited May 31, 2019 at 5:01

answered Jul 20, 2017 at 1:27

cs95

406k106 gold badges744 silver badges797 bronze badges

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

2 Comments

Lauren Over a year ago

The actual experimental ID is made up of numbers and letters and can't be randomized. How do I do that?

cs95 Over a year ago

@Lauren It works the same way no matter what IDs you have. I can't solve your problem for you because I don't have your data. But I've shown you how you can solve your problem.

piRSquared · Accepted Answer · 2017-07-20 01:46:30Z

0

I'd use apply and format

fmt = 'http://dbarchive.biosciencedbc.jp/kyushu-u/hg19/eachData/bed20/{}.bed'
df.ID.apply(fmt.format)

answered Jul 20, 2017 at 1:46

piRSquared

296k68 gold badges509 silver badges654 bronze badges

1 Comment

Lauren Over a year ago

How do I insert the IDs from my other dataframe though? Keeping in mind that there's 732 unique IDs so I have to create 732 unique URLs?

Collectives™ on Stack Overflow

Format string with values from column using pandas

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related