Choose correct datatype when creating new dataframe column from old ones

[djrobust]

I am converting a Stata dataset to a dataframe, then multiply two columns to create a third one. The chosen datatype for the two columns is int8 and for some reason the new column blindly follows that.

For instance, this code

df = pd.read_stata(file)
df['w_age_educ'] = df['w_age'] * df['weduc']
print(df[['w_age', 'weduc', 'w_age_educ']].dtypes)
print(df[['w_age', 'weduc', 'w_age_educ']][:3])

would give me

w_age         int8
weduc         int8
w_age_educ    int8
dtype: object
   w_age  weduc  w_age_educ
0     44     14         104
1     34     13         -70
2     33     18          82

Is this a bug or intended behavior? If the latter, how can I get my desired product column?

[jreback]

can u show df.info() right after the read from stata

[jreback]

pd.show_versions() as well

[djrobust]

df = pd.read_stata(file)
df.info()
pd.show_versions()
df['w_age_educ'] = df['w_age'] * df['weduc']
print(df[['w_age', 'weduc', 'w_age_educ']].dtypes)
print(df[['w_age', 'weduc', 'w_age_educ']][:3])

yields

<class 'pandas.core.frame.DataFrame'>
Int64Index: 4066 entries, 0 to 4065
Data columns (total 62 columns):
state                4066 non-null int8
hhnum                4066 non-null int32
cbsa_size            4066 non-null int8
famnum               4066 non-null int8
himm                 4066 non-null int8
hgen2                4066 non-null int8
heduc                4066 non-null int8
htwage               3789 non-null float32
hannhrs              4066 non-null int16
hfamearn             4066 non-null int32
h_age                4066 non-null int8
hrace                4066 non-null int8
hwagesal             4066 non-null int32
hwage_ogr            3281 non-null float32
hselfinc             4066 non-null int32
hfarminc             4066 non-null int32
hclassly             4066 non-null int8
hhealth              4066 non-null int8
hweeksly             4066 non-null int8
hhrswkly             4066 non-null int8
hogrflag             4066 non-null int8
hrgroup              4066 non-null int8
ownkidsu6            4066 non-null int8
ownkidsu18           4066 non-null int8
wimm                 4066 non-null int8
wgen2                4066 non-null int8
weduc                4066 non-null int8
wtwage               3265 non-null float32
wannhrs              4066 non-null int16
wfamearn             4066 non-null int32
w_age                4066 non-null int8
wrace                4066 non-null int8
wwagesal             4066 non-null int32
wwage_ogr            2906 non-null float32
wselfinc             4066 non-null int32
wfarminc             4066 non-null int32
wclassly             4066 non-null int8
wweeksly             4066 non-null int8
whrswkly             4066 non-null int8
whealth              4066 non-null int8
wogrflag             4066 non-null int8
wrgroup              4066 non-null int8
mergestat            4066 non-null object
wifework             4066 non-null int8
husbandwork          4066 non-null int8
age_gap              4066 non-null int8
wagesalgap           4066 non-null int32
educgap              4066 non-null int8
hlogwage             3789 non-null float32
wlogwage             3265 non-null float32
hexp                 4066 non-null int8
hexp2                4066 non-null float32
hexp3                4066 non-null float32
hblack               4066 non-null int8
wblack               4066 non-null int8
hhispanic            4066 non-null int8
whispanic            4066 non-null int8
wife_wage_ogr        4066 non-null int8
husband_wage_ogr     4066 non-null int8
missing_wwage_ogr    4066 non-null int8
wlogwage_ogr         2906 non-null float32
any_under6           4066 non-null int8
dtypes: float32(9), int16(2), int32(10), int8(40), object(1)
INSTALLED VERSIONS
------------------
commit: None
python: 3.3.2.final.0
python-bits: 32
OS: Linux
OS-release: 3.16.3-200.fc20.i686
machine: i686
processor: i686
byteorder: little
LC_ALL: None
LANG: en_US.utf8

pandas: 0.14.1
nose: 1.3.4
Cython: 0.20.2
numpy: 1.9.0
scipy: 0.12.1
statsmodels: 0.5.0
IPython: 2.3.0
sphinx: 1.2.3
patsy: 0.3.0
scikits.timeseries: None
dateutil: 2.2
pytz: 2014.7
bottleneck: None
tables: None
numexpr: None
matplotlib: 1.3.1
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
rpy2: 2.4.4
sqlalchemy: None
pymysql: None
psycopg2: None
w_age         int8
weduc         int8
w_age_educ    int8
dtype: object
   w_age  weduc  w_age_educ
0     44     14         104
1     34     13         -70
2     33     18          82

[jreback]

cc @bashtage

do u explicitly coerce stata types ?

[bashtage]

Yes, it attempts to preserve Stata data types for two reasons. First if a Stats data set is large, blowing up int8 to int64 is expensive. Second, preserving types helps ensure data fidelity under round trips (except under some conditions, e.g. no missing values)

On Oct 10, 2014 6:20 AM, jreback notifications@github.com wrote:

cc @bashtagehttps://github.com/bashtage

do u explicitly coerce stata types ?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/8527#issuecomment-58637559.

[jreback]

@bashtage thanks - I do recall that from the docs

[bashtage]

For clarity there is no coercion since the raw data is directly read into a structured array with the Stata data types. The old version would first create Int64 and then convert to smaller types, which meant that the memory requirement could be temporarily high. This is no longer the case.

On Oct 10, 2014 6:36 AM, jreback notifications@github.com wrote:

@bashtagehttps://github.com/bashtage thanks - I do recall that from the docs

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/8527#issuecomment-58639033.

[jreback]

unfortunately this is fundamental a numpy issue (and no fidling with np.seterr could make this raise overflow).

In [3]: np.array([1,2,3],dtype='int8')*100
Out[3]: array([100, -56,  44], dtype=int8)

In [4]: np.array([1,2,3],dtype='int16')*100
Out[4]: array([100, 200, 300], dtype=int16)

so couple of options:

• easy workaround is to simply coerce to pandas standard float64/int64:

In [22]:  df = DataFrame({'A' : np.array([1,2,3],dtype='int8'), 'B' : np.array([1,2,3],dtype='float32'), 'C' : 'foo'})

In [23]: df
Out[23]: 
   A  B    C
0  1  1  foo
1  2  2  foo
2  3  3  foo

In [24]: df.dtypes
Out[24]: 
A       int8
B    float32
C     object
dtype: object

In [26]: columns = df.select_dtypes(['integer']).columns

In [27]: df[columns] = df[columns].astype('int64')

In [28]: columns = df.select_dtypes(['floating']).columns

In [30]: df[columns] = df[columns].astype('float64')

In [31]: df.dtypes
Out[31]: 
A      int64
B    float64
C     object
dtype: object

• maybe add something like this to .astype('upcast') or something which does the above more automatically
• add an option to read_stata to convert_dtypes (and I would make this True by default I think)

[bashtage]

Calling it a numpy issue is a little unfair to numpy. It is a hardware issue since int addition doesn't check for overflow, -- to do so would be a real performance killer.

Your second comment indicates that there is nothing like pd.to_safest_dtypes which will upcast all ints to int64 and all floats to float64?