readin data as float per converter

Question

I have a csv-file called 'filename' and want to read in these data as 64float, except the column 'hour'. I managed it with the pd.read_csv - function and an converter.

df = pd.read_csv("../data/filename.csv",
                 delimiter = ';',
                 date_parser = ['hour'],
                 skiprows = 1,
                 converters={'column1': lambda x: float(x.replace   ('.','').replace(',','.'))})

Now, I have two points:

FIRST:

The delimiter works with ; ,but if I take a look in Notepad to my data, there are ',', not ';'. But if I take ',' I get: 'pandas.parser.CParserError: Error tokenizing data. C error: Expected 7 fields in line 13, saw 9'

SECOND:

If I want to use the converter for all columns, how can I get this?! What`s the right term? I try to use dtype = float in the readin-function, but I get 'AttributeError: 'NoneType' object has no attribute 'dtype'' Whats happend? Thats the reasion why I want to managed it with the converter.

Data:

,hour,PV,Wind onshore,Wind offshore,PV.1,Wind onshore.1,Wind offshore.1,PV.2,Wind onshore.2,Wind offshore.2 0,1,0.0,"12,985.0","9,614.0",0.0,"32,825.5","9,495.7",0.0,"13,110.3","10,855.5" 1,2,0.0,"12,908.9","9,290.8",0.0,"36,052.3","9,589.1",0.0,"13,670.2","10,828.6" 2,3,0.0,"12,740.9","8,886.9",0.0,"38,540.9","10,087.3",0.0,"14,610.8","10,828.6" 3,4,0.0,"12,485.3","8,644.5",0.0,"40,734.0","10,087.3",0.0,"15,638.3","10,343.7" 4,5,0.0,"11,188.5","8,079.0",0.0,"42,688.0","10,087.3",0.0,"16,809.4","10,343.7" 5,6,0.0,"11,219.0","7,594.2",0.0,"43,333.5","10,025.0",0.0,"18,266.9","10,343.7"

Answer 1

This should work:

In [40]:
# text data
temp=''',hour,PV,Wind onshore,Wind offshore,PV.1,Wind onshore.1,Wind offshore.1,PV.2,Wind onshore.2,Wind offshore.2
0,1,0.0,"12,985.0","9,614.0",0.0,"32,825.5","9,495.7",0.0,"13,110.3","10,855.5"
1,2,0.0,"12,908.9","9,290.8",0.0,"36,052.3","9,589.1",0.0,"13,670.2","10,828.6"
2,3,0.0,"12,740.9","8,886.9",0.0,"38,540.9","10,087.3",0.0,"14,610.8","10,828.6"
3,4,0.0,"12,485.3","8,644.5",0.0,"40,734.0","10,087.3",0.0,"15,638.3","10,343.7"
4,5,0.0,"11,188.5","8,079.0",0.0,"42,688.0","10,087.3",0.0,"16,809.4","10,343.7"
5,6,0.0,"11,219.0","7,594.2",0.0,"43,333.5","10,025.0",0.0,"18,266.9","10,343.7"'''
# so read the csv, pass params quotechar and the thousands character
df = pd.read_csv(io.StringIO(temp), quotechar='"', thousands=',')
df
Out[40]:
   Unnamed: 0  hour  PV  Wind onshore  Wind offshore  PV.1  Wind onshore.1  \
0           0     1   0       12985.0         9614.0     0         32825.5   
1           1     2   0       12908.9         9290.8     0         36052.3   
2           2     3   0       12740.9         8886.9     0         38540.9   
3           3     4   0       12485.3         8644.5     0         40734.0   
4           4     5   0       11188.5         8079.0     0         42688.0   
5           5     6   0       11219.0         7594.2     0         43333.5   

   Wind offshore.1  PV.2  Wind onshore.2  Wind offshore.2  
0           9495.7     0         13110.3          10855.5  
1           9589.1     0         13670.2          10828.6  
2          10087.3     0         14610.8          10828.6  
3          10087.3     0         15638.3          10343.7  
4          10087.3     0         16809.4          10343.7  
5          10025.0     0         18266.9          10343.7  
In [41]:
# check the dtypes
df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 6 entries, 0 to 5
Data columns (total 11 columns):
Unnamed: 0         6 non-null int64
hour               6 non-null int64
PV                 6 non-null float64
Wind onshore       6 non-null float64
Wind offshore      6 non-null float64
PV.1               6 non-null float64
Wind onshore.1     6 non-null float64
Wind offshore.1    6 non-null float64
PV.2               6 non-null float64
Wind onshore.2     6 non-null float64
Wind offshore.2    6 non-null float64
dtypes: float64(9), int64(2)
memory usage: 576.0 bytes

So basically you need to pass the quotechar='"' and thousands=',' params to read_csv to achieve what you want, see the docs: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html#pandas.read_csv

EDIT

If you want to convert after importing (which is a waste when you can do it upfront) then you can do this for each column of interest:

In [43]:
# replace the comma separator
df['Wind onshore'] = df['Wind onshore'].str.replace(',','')
# convert the type
df['Wind onshore'] = df['Wind onshore'].astype(np.float64)
df['Wind onshore'].dtype
Out[43]:
dtype('float64')

It would be faster to replace the comma separator on all the columns of interest first and just call convert_objects like so: df.convert_objects(convert_numeric=True)