LancelotHolmes LancelotHolmes - 1 month ago 5
Python Question

python get and process value between certain characters in csv

Actually it's related to pandas, I have several records(about 40000) in a csv file, and one of the column value is

market_value
,for example:

Original records

player_id,player_name,club,age,market_value,date
3713,massimo-maccarone,UC Sampdoria,31,£2.55m,2011-01-10
3713,massimo-maccarone,UC Sampdoria,31,£1.70m,2011-06-21
3713,massimo-maccarone,FC Empoli,32,£850k,2012-01-01
3713,massimo-maccarone,FC Empoli,32,£680k,2012-06-15
...


here I'd like to extract the value of the
market_value
and convert it into some format like

player_id,player_name,club,age,market_value,date
3713,massimo-maccarone,UC Sampdoria,31,2550000,2011-01-10
3713,massimo-maccarone,UC Sampdoria,31,1700000,2011-06-21
3713,massimo-maccarone,FC Empoli,32,850000,2012-01-01
3713,massimo-maccarone,FC Empoli,32,680000,2012-06-15
...


which means I'd like to convert it into a numeric form and then import to database.And I have written a regex method to implement this like:

import re

def regex_format(test_str):
if re.search('^£.+k$',test_str):
return float(re.search('^£(.+)k$',test_str).group(1))*1000
elif re.search('^£(.+)m$',test_str):
return float(re.search('^£(.+)m$',test_str).group(1))*1000000
else:
return 0.0


it works for those string,but when I tried to apply it in csv file by pandas,I got a problem,my code is like:

import pandas as pd
import currency_format

df=pd.read_csv('the_market_value_o.csv')
df['market_value']=currency_format.regex_format(df['market_value'])
print df


and I got an exception like:

TypeError:expected string or buffer


and then I tried to convert the series to string by methods like:

df['market_value'].astype(basestring)


but it doesn't work.

BTW,my python version is
Python 2.7.12 :: Anaconda 4.0.0 (64-bit)
with pandas 0.18.0

Any help is appreciated,thank you.

Answer

I think the problem is that df[market_value] is a Series (of strings) not a singleton string. Think of it as a slice/column of your data -- like a list of values. So instead try something like:

df['market_value'].map(currency_format.regex_format)

This answer here has more such functions/examples.

Comments