Inspect a string to create a new column in spark dataframe

Question

I have a Spark dataframe column with trading pairs that I need to use to create a new column with the name of the coin populated in it.

The first column "bot" contains "Polkadot/USD", I need a new column called "coin" that contains only the substring "Polkadot" of the bot column. Same for all other rows. Basically the new column needs to have the substring "/USD" removed.

How would the code look like to accomplish this. I'm a crypto trader not a coder, so the more coding detail in the answer the better. Thank you.

Note: The notebook is a Python Notebook

Please add text instead of images to your question. Also, add anything that you have tried and issues, if you are facing any. — philantrovert
– philantrovert, Commented Jul 26, 2021 at 5:29

Ric S · Accepted Answer · 2021-07-26 07:47:45Z

1

You can use regexp_replace to substitute a substring with another substring

df.withColumn('coin', F.regexp_replace(F.col('bot'), '/USD', ''))

Example

# sample dataframe
df3 = spark.createDataFrame([
    ('BamBridge/USD', ),
    ('CLV/USD', ),
    ('ETH/USD', ),
    ('Polkadot/USD', ),
], ['bot'])

df3 = df3.withColumn('coin', F.regexp_replace(F.col('bot'), '/USD', ''))

df3.show()

+-------------+---------+
|          bot|     coin|
+-------------+---------+
|BamBridge/USD|BamBridge|
|      CLV/USD|      CLV|
|      ETH/USD|      ETH|
| Polkadot/USD| Polkadot|
+-------------+---------+

answered Jul 26, 2021 at 7:47

Ric S

9,3184 gold badges30 silver badges57 bronze badges

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

2 Comments

Christopher Pfeifer Over a year ago

Your example worked great. However I did have to remove the "F." from the two places in the command. After importing org.apache.spark.sql.functions.regexp_replace. the code line that worked is df3 = df3.withColumn('coin', regexp_replace(col('bot'), '/USD', '')) just had to remove the "F." thanks for your help.

Ric S Over a year ago

It depends on whether you import the whole functions module as the shortcut F (as I did), or you import the necessary functions one by one (as you did). Please consider also upvoting the answer (in addition to accepting it) if you found it useful!

Collectives™ on Stack Overflow

Inspect a string to create a new column in spark dataframe

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