I want to set the dtype
s of multiple columns in pd.Dataframe
(I have a file that I've had to manually parse into a list of lists, as the file was not amenable for pd.read_csv
)
import pandas as pd
print pd.DataFrame([['a','1'],['b','2']],
dtype={'x':'object','y':'int'},
columns=['x','y'])
I get
ValueError: entry not a 2- or 3- tuple
The only way I can set them is by looping through each column variable and recasting with astype
.
dtypes = {'x':'object','y':'int'}
mydata = pd.DataFrame([['a','1'],['b','2']],
columns=['x','y'])
for c in mydata.columns:
mydata[c] = mydata[c].astype(dtypes[c])
print mydata['y'].dtype #=> int64
Is there a better way?
You're better off using typed np.arrays, and then pass the data and column names as a dictionary.
import numpy as np
import pandas as pd
# Feature: np arrays are 1: efficient, 2: can be pre-sized
x = np.array(['a', 'b'], dtype=object)
y = np.array([ 1 , 2 ], dtype=np.int32)
df = pd.DataFrame({
'x' : x, # Feature: column name is near data array
'y' : y,
}
)
facing similar problem to you. In my case I have 1000's of files from cisco logs that I need to parse manually.
In order to be flexible with fields and types I have successfully tested using StringIO + read_cvs which indeed does accept a dict for the dtype specification.
I usually get each of the files ( 5k-20k lines) into a buffer and create the dtype dictionaries dynamically.
Eventually I concatenate ( with categorical... thanks to 0.19) these dataframes into a large data frame that I dump into hdf5.
Something along these lines
import pandas as pd
import io
output = io.StringIO()
output.write('A,1,20,31\n')
output.write('B,2,21,32\n')
output.write('C,3,22,33\n')
output.write('D,4,23,34\n')
output.seek(0)
df=pd.read_csv(output, header=None,
names=["A","B","C","D"],
dtype={"A":"category","B":"float32","C":"int32","D":"float64"},
sep=","
)
df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 4 columns):
A 5 non-null category
B 5 non-null float32
C 5 non-null int32
D 5 non-null float64
dtypes: category(1), float32(1), float64(1), int32(1)
memory usage: 205.0 bytes
None
Not very pythonic.... but does the job
Hope it helps.
JC
you can set the types explicitly with pandas DataFrame.astype(dtype, copy=True, raise_on_error=True, **kwargs)
and pass in a dictionary with the dtypes you want to dtype
here's an example:
import pandas as pd
wheel_number = 5
car_name = 'jeep'
minutes_spent = 4.5
# set the columns
data_columns = ['wheel_number', 'car_name', 'minutes_spent']
# create an empty dataframe
data_df = pd.DataFrame(columns = data_columns)
df_temp = pd.DataFrame([[wheel_number, car_name, minutes_spent]],columns = data_columns)
data_df = data_df.append(df_temp, ignore_index=True)
In [11]: data_df.dtypes
Out[11]:
wheel_number float64
car_name object
minutes_spent float64
dtype: object
data_df = data_df.astype(dtype= {"wheel_number":"int64",
"car_name":"object","minutes_spent":"float64"})
now you can see that it's changed
In [18]: data_df.dtypes
Out[18]:
wheel_number int64
car_name object
minutes_spent float64
Another way to set the column types is to first construct a numpy record array with your desired types, fill it out and then pass it to a DataFrame constructor.
import pandas as pd
import numpy as np
x = np.empty((10,), dtype=[('x', np.uint8), ('y', np.float64)])
df = pd.DataFrame(x)
df.dtypes ->
x uint8
y float64
For those coming from Google (etc.) such as myself:
convert_objects
has been deprecated since 0.17 - if you use it, you get a warning like this one:
FutureWarning: convert_objects is deprecated. Use the data-type specific converters
pd.to_datetime, pd.to_timedelta and pd.to_numeric.
You should do something like the following:
df =
df.astype(np.float)
df["A"] =
pd.to_numeric(df["A"])
Source: Stackoverflow.com