Geek Answers Handbook

Pandas dataframe converting specific columns from a row to a float

I am trying to do some simple analyzes in Kenneth French industry portfolios (first time with Pandas / Python), the data is in txt format (see link in code). Before I can do the calculations, first want to load it into the Pandas framework correctly, but I struggled with this clock:

import urllib.request
import os.path
import zipfile
import pandas as pd
import numpy as np

# paths
url = 'http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/48_Industry_Portfolios_CSV.zip'
csv_name = '48_Industry_Portfolios.CSV'
local_zipfile = '{0}/data.zip'.format(os.getcwd())
local_file = '{0}/{1}'.format(os.getcwd(), csv_name)

# download data
if not os.path.isfile(local_file):
    print('Downloading and unzipping file!')
    urllib.request.urlretrieve(url, local_zipfile)
    zipfile.ZipFile(local_zipfile).extract(csv_name, os.path.dirname(local_file))

# read from file
df = pd.read_csv(local_file,skiprows=11)
df.rename(columns={'Unnamed: 0' : 'dates'}, inplace=True)

# build new dataframe
first_stop = df['dates'][df['dates']=='201412'].index[0]
df2 = df[:first_stop]

# convert date to datetime object
pd.to_datetime(df2['dates'], format = '%Y%m')
df2.index = df2.dates

All columns except dates represent financial results. However, due to file formatting, they are now strings. According to the Pandas docs, this should do the trick:

df2.convert_objects(convert_numeric=True)

But the columns remain rows. Other suggestions are to iterate over columns (see, for example, pandas convert rows to float for multiple columns in a data frame ):

for d in df2.columns:
if d is not 'dates':
    df2[d] = df2[d].map(lambda x: float(x)/100)

But this gives me the following warning:

 home/<xxxx>/Downloads/pycharm-community-4.5/helpers/pydev/pydevconsole.py:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  try:

, , , , .

Edit:

df2=df2.convert_objects(convert_numeric=True)

, ( , http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.convert_objects.html)

df2:

     dates    Agric    Food     Soda     Beer     Smoke    Toys     Fun    \
dates                                                                           
192607  192607     2.37     0.12   -99.99    -5.19     1.29     8.65     2.50   
192608  192608     2.23     2.68   -99.99    27.03     6.50    16.81    -0.76   
192609  192609    -0.57     1.58   -99.99     4.02     1.26     8.33     6.42   
192610  192610    -0.46    -3.68   -99.99    -3.31     1.06    -1.40    -5.09   
192611  192611     6.75     6.26   -99.99     7.29     4.55     0.00     1.82

Edit2: , :

df2.index = pd.to_datetime(df2['dates'], format = '%Y%m')
df2 = df2.astype(float)/100
+3
3

convert_objects, inplace:

df2=df2.convert_objects(convert_numeric=True)

rename, inplace, True.

pandas , inplace, convert_objects - , . , , , NaNs.

, , -, , . ..

0

: :

df2=df2.astype(float)
0

float ( , ),

df["column_name"] = pd.to_numeric(df["column_name"])

, pandas.convert_objects pandas 0.20.1

0

More articles:


All Articles