BUG: Fixed incorrect type in integer conversion in to_stata
State: closed

Simple fix for bug #6327. All conversions were off by a factor of 2.
Added test for this case.


Labels:
name: Bug
name: IO Data
description: IO issues that don't fit into a more specific label
name: Dtype Conversions
description: Unexpected or buggy dtype conversions

Comments:
typo in your test...pls test locally!

This should probably be rejected.  I had a look at 

http://www.stata.com/help.cgi?dta#variabletypes

and there does not appear to be any int64 type.

What's the correct thing?
- Raise an exception and ask the user to coerce int64 to double
- Silently coerce long to double.

I probably thing the former is the correct solution.

hmm...int64 is kind of a fundamental type in pandas/numpy land.

The issue only really arises if the value cannot fit in a int32 right?
pretty easy to try to coerce and raise if it doesn't work properly.
(of course this will essentially make the round-trip non-exact, but oh well)

can u look at the current test failures?

I have been working backward on this and I'm still not really sure that a fix which actually makes `to_stata()` produce actual Stata compliant files is ever going to be acceptable since, from what I can tell, Stata does not support any type of 8-byte integers.  

As you say, this is a real problem for a DataFrame since many columns are naturally int64 (e.g. a plain index).

I tried removing all references to int64 and all that did was more-or-less break everything.

I've found another issue in the writer when it iterates across rows of the DataFrame, which produces a Series.  This is a problem with mixed numeric data since the data is first transformed using the largest data type, including coercing integer types to float if the row contains a float, before bring written.  I can't imagine that a loss of fidelity that this can induce would be desirable.

This shows what is effectively happening inside `to_stata`:

``` python
from pandas import Series,DataFrame
import numpy as np

original = np.int64(2**63-2**32)

s1 = Series(original,dtype=np.int64)
s2 = Series(0.1,dtype=np.float32)
df = DataFrame({'long':s1,'single':s2})

# No problem in data frame
print df['long']-original

# Coercing int64 to float32 creates  a problem
print df.iloc[0]['long'] - original
```

The conversion is silently happening at 

https://github.com/bashtage/pandas/blob/stata-export-datatype/pandas/io/stata.py#L1144

since the rows of the DataFrame are being iterated over.

cc @PKEuS

@bashtage IIUC this happens for values that don't fit in float32? e.g. large ints. So a warning / raising is prob appropriate. This 'works' it seems if the values fit, though? E.g. if you generate a file with `to_stata` it is read by stata ok? 

@jreback I have now tested it on Stata and the dta produced with pandas 0.13.1 is not a correct stata file.  

This simple code outputs and inputs fine in pandas, but the variables have the wrong values in Stata

``` python
s1 = pd.Series(1,dtype=np.int8)
s2 = pd.Series(2**8+1,dtype=np.int16)
s3 = pd.Series(2**16+1,dtype=np.int32)
s4 = pd.Series(2**32+1,dtype=np.int64)
df = pd.DataFrame({'s1':s1,'s2':s2,'s3':s3,'s4':s4})
df.to_stata('test.dta')
```

Running the command 

``` stata
list
```

returns 

```
index   s1  s2  s3              s4
     0   0   0   0        1.68e+07
```

I ran a couple of other experiments with other data dtypes (e.g. float64) and they also do not load the correct values into Stata 12.1.

ok, feel free to make a new version that works with out-of-size values. I was asking whether the more normal case works, e.g. regular sized values.

Those are all normal sized values.  s1 is a byte with the value 1.  s2 and int16 with value 257. Stata sees them with the correct type but wrong values.

Even the trivial dta

``` python
df = pd.DataFrame(np.array([[1.0]]),columns=['s1'])
df.to_stata('test.dta')
print df.dtypes
```

does not contain the correct values when loaded into Stata.  

so the test data files are wrong? are _they_ not valid stata files? since these can be roundtripped you are sying that the **read** is compensating somehow? (for the writing issues)

is this a version issue? I recall very specific versions of formats that this supports (or doesn't support)

@jseabold are you seeing the same thing here?

That seems to be the case.  I need to do some more systematic investigation which may take a little while.  

It appears that in some cases the reader and writer are internally compatible, but not always compatible with what Stata wants.  

ok compat is obviously important 
do pls let is know

I can have a look if needed. I didn't write the writer, so I'm less familiar with it. I can also point you to (or provide you with) the technical documentation if needed. It comes with stata since v. 11.x or so.

You are correct though that Stata doesn't have an int64. Only (type, min, max, bytes)

byte, -127, 100, 1
int, -32,767, 32,740, 2
long, -2,147,483,647, 2,147,483,620, 4
float, -1.70141173319 x 10^38, 1.70141173319 x 10^38, 4
double, -8.9884656742 x 10^307, 8.9884656743 x 10^307, 8

Adding my comment from #6327 here. Ripping out the int64 stuff is the way to go (except for handling it appropriately on the writer side of things). The reader shouldn't know about it, and it shouldn't be in dtype_maps. Those aren't suggestions, but determined from the stata binary format. Cf. the statsmodels implementation without int64.

https://github.com/statsmodels/statsmodels/blob/master/statsmodels/iolib/foreign.py#L289

It is making progress, and could probably use some criticism.  Three major changes:
1. No more `int64` anywhere
2. Casting columns with an attempt to preserve data fidelity at the expense of possibly larger files.  For example, an `int8` with a value of 101 is not upcast to an `int16`, and similarly an `int16` which is valid but outside the range of a Stata variable is upcast to `int32`.  `int64` is now case to either
   - down to `int32`, if all data is a valid `int32` 
   - sideways to float64, and a warning given if the conversion might be lossy (if the `int64` values i larger than 2**53).  Even this might be lossless, although I don't test these cases.
3. Removed the `iterrows` and replaced it with `itertuples`.  `itertuples` preserves data types while `iterrows` does not.

@jreback Thanks for the suggestion.

see here: https://github.com/pydata/pandas/wiki

you will want to rebase on master

`git rebase -i origin/master` then fix any conflicts

you can squash if you would like now (or at the end). (some people like to records _every_ commit), then squash it down once they are done. personal preference.

how does stata handle datetimes? pandas keeps them as int64's (which is the number of ns after epoch time). These are much bigger than int32's. How do you want to handle this?

not sure exactly how it is handled now, but here are some possibilites:
- convert to string reprs
- use int32's bu represent as seconds from epoch (you can reverse transform these rather easilly
  by doing `pd.to_datetime(..., unit='s')` (anything else won't fit in an int32).

not sure how the type of a column is recorded though (e.g. how do you know its a date/time column?)

Stata has its own internal representation for time (of course), Stata Internal Form (SIF). It's some numeric format (int, long, float, double), depending.

http://www.stata.com/help.cgi?datetime

ok...great...so that is already handled correctly then...gr8

Should be. It was in statsmodels... (though this possibly pre-dates pandas datetime stuff. Not sure whether I messed with that now that I think about it).

I haven't looked, but I hope that the tests for at least `read_stata` are more than round-tripping. I.e., the hard-coded data that I created in stata, vs. hard-coded results that I output from stata. I'm starting to sweat about some of my collaborative projects that are using `read_stata` from pandas extensively.

@jreback I rebased on upstream/maste, which I hope is right (not origin/master).

@jseabold  Dates seem to be fine.  Before this gets pulled I'd like to make some non-trivial Stata dta files to add -- ones which push the numerical boundaries of the dta format would probably be most useful.  The three tests that use Stata data are `nottest` so these are not helping.

@bashtage yep whatever your upstream is named (usually but not always origin)

yes pls add whatever test files u need

This has become more involved, and I have added test cases and bug fixes for the largest values permitted in Stata.  

I spent some time with the docs and have added correct values for missing types using bytes for floats and doubles. Unfortunately these have uglified the code.

@bashtage this could explain why I disabled some of the tests on certain architectures (e.g. sparc which is big-endian). The NaT comparisons were failing.

So pls comment those disablings if you would (don';t delete them just yet...who knows may need to turn them back on!)

@jreback The only disabled tests I see in test_stata are due to missing data files (which I assume are proprietary Stata, and so excluded).  Am I missing something?

https://github.com/pydata/pandas/blob/master/pandas/io/tests/test_stata.py#L160
https://github.com/pydata/pandas/blob/master/pandas/io/tests/test_stata.py#L174

its the 'skip_if_not_little_endian()` (e.g. its a big-endian arch)

I can't speak to your question, but I believe those datasets are excluded because they're old formats output from old versions of Stata. Stata didn't publish the binary format spec anywhere that I was able to find before ~109 in Stata 10 (or 11, not sure.).