LancelotHolmes LancelotHolmes - 9 months ago 48
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

,for example:

Original records

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
and convert it into some format like

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):
return float('^£(.+)k$',test_str).group(1))*1000
return float('^£(.+)m$',test_str).group(1))*1000000
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

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:


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 Source

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:


This answer here has more such functions/examples.