CODEWITHSUNDEEP
pythonpandas
fred.schwartz
fred.schwartz

Reputation: 2155

Python splitting strings with prefix

If I have a dataframe column full of text and prices.

 0  £75 BT Reward Card
1   £125 BT Reward Card 
2   £50 Retail Voucher
3   £100 BT Reward Card 
4   £150 BT Reward Card 
5   £50 Cashback
6   Fibre Connection Fee (£50 Credit
7   £75 BT Reward Card  
8   £125 BT Reward Card 
9   £50 Cashback
10  £0 Fibre Connection Fee (£50 Credit

I just want to return the number directly after the £ sign.

I've got this so far but that falls apart for index 6 and 10

df['col']=df['col'].apply(lambda x: x.split(' ')  [0])

I've also tried this:

df['col']=df['col'].apply(lambda x: x.split('£')  [1])

Upvotes: 1

Views: 605

Answers (1)

jezrael
jezrael

Reputation: 862611

If need first value only use extract and cast to integers if necessary:

df['new'] = df['col'].str.extract('£(\d+)').astype(int)
print (df)
                                      col  new
0                      £75 BT Reward Card   75
1                    £125 BT Reward Card   125
2                      £50 Retail Voucher   50
3                    £100 BT Reward Card   100
4                    £150 BT Reward Card   150
5                            £50 Cashback   50
6        Fibre Connection Fee (£50 Credit   50
7                    £75 BT Reward Card     75
8                    £125 BT Reward Card   125
9                            £50 Cashback   50
10    £0 Fibre Connection Fee (£50 Credit    0

And if all values in lists use str.findall:

#values are strings
df['new'] = df['col'].str.findall('£(\d+)')
#values are integers
#df['new'] = df['col'].str.findall('£(\d+)').apply(lambda x: [int(y) for y in x])
print (df)
                                      col      new
0                      £75 BT Reward Card     [75]
1                    £125 BT Reward Card     [125]
2                      £50 Retail Voucher     [50]
3                    £100 BT Reward Card     [100]
4                    £150 BT Reward Card     [150]
5                            £50 Cashback     [50]
6        Fibre Connection Fee (£50 Credit     [50]
7                    £75 BT Reward Card       [75]
8                    £125 BT Reward Card     [125]
9                            £50 Cashback     [50]
10    £0 Fibre Connection Fee (£50 Credit  [0, 50]

And if need them in new columns use extractall with unstack, add_prefix and join:

df = df.join(df['col'].str.extractall('£(\d+)')[0].unstack().astype(float).add_prefix('new'))
print (df)
                                      col   new0  new1
0                      £75 BT Reward Card   75.0   NaN
1                    £125 BT Reward Card   125.0   NaN
2                      £50 Retail Voucher   50.0   NaN
3                    £100 BT Reward Card   100.0   NaN
4                    £150 BT Reward Card   150.0   NaN
5                            £50 Cashback   50.0   NaN
6        Fibre Connection Fee (£50 Credit   50.0   NaN
7                    £75 BT Reward Card     75.0   NaN
8                    £125 BT Reward Card   125.0   NaN
9                            £50 Cashback   50.0   NaN
10    £0 Fibre Connection Fee (£50 Credit    0.0  50.0

Upvotes: 6

Related Questions