to_stata results in unnecessarily long strings

[cpbl]

I'm writing some simple data, including a string of len()=5 using to_stata.
In Stata, I then have trouble with it due to its length. codebook shows that my variable as been encoded as a string of length 244:
of type "string (str244), but longest is str5"

If tthe longest string is length 5, so should be the output variable type in Stata.

[jreback]

cc @bashtage

[jreback]

@cpbl can you post a self-contained example

[cpbl]

Here's the code:

#!/usr/bin/python

fout=open('tmp.tsv','wt')
fout.write("""A B   C
dummy   32.470986000    -86.487033000
01001   32.470986000    -86.487033000
01001   32.466056000    -86.472934000
01001   32.474035000    -86.457764000
01001   32.466794000    -86.445569000
01001   32.454933000    -86.425025000
01001   32.439950000    -86.478442000
01001   32.438025000    -86.443068000
""")
fout.close()
import pandas as pd
df=pd.read_table('tmp.tsv')
df.to_stata('tmp.dta')
fout=open('tmp.do','wt')
fout.write("""
log using tmp.log, text
use tmp
codebook
log close
""")

And an excerpt from the output in stata ("tmp.log")

------------------------------------------------------------------------------------------
A                                                                              (unlabeled)
------------------------------------------------------------------------------------------

                  type:  string (str244), but longest is str5

         unique values:  2                        missing "":  0/8

            tabulation:  Freq.  Value
                             7  "01001"
                             1  "dummy"

[jreback]

can you put the stata version up as well

[bashtage]

This seems to be something related to how pandas stores strings now.

Consider this simple example

>>> data = pd.DataFrame(np.array(['a','b','c']))
>>> data.dtypes[0].type
numpy.object_

StataWriter assumes all numpy.object_ are 244 character strings since there isn't an easy way to tell its size. Note that htis is different from how numpy stores the same array

>>>arr = np.array(['a','b','c'])
>>> arr.dtype
dtype('S1')

which is a string with a fixed size.

Maybe @jreback can comment on how to force pandas to store fixed width strings as Sxx where xx is the width?

[jreback]

@bashtage

in pandas you cannot store anything string-like that is not object dtype. iow all strings are variable-width.

However, you could on storage, convert to a numpy str of fixed width. (This is done in pytables as the underlying storage mechanism ONLY stores fixed-width).

If you are writing in one go (e.g. no chunking), then this is pretty easy to do (e.g. lib.max_len_string_array(....)). With chunking pytables has a min_itemsize to make sure that the table is a minimum width.

If stata supports a fixed-width character size you could store like that.

[cpbl]

@jreback said "can you put the stata version up as well". Was that to me?
stata version is 12.1 for Unix (Linux 64-bit x86-64
Revision 23 Jan 2014

If you mean to include the stata file... I don't see how to attach files.
And this discussion is already way over my head.
Thanks.
c

[jreback]

@cpbl yes that was to you as stata has many different formats for the files, some newer than others.

[bashtage]

Dont' need a stata file - this issue is how the string dimension is determined in StataWriter which uses only the DataFrams.dtypes and not the actual data. Can probably change, although this isn't so much a bug as an omitted optimization.

[cpbl]

@jreback So did you want a file or the version? Still unsure. File is here (today): http://barringtonleigh.net/tmp.dta

@bashtage Not a bug: Oh, maybe. I was interpreting my resultant inability to merge as a result of the strlen in the dta, but I guess I had a more fundamental error.

[jreback]

@bashtage yep. I'll mark as an enhancement. To add fixed-width strings in addition/in lieu of variable-wdith. You make the call if you should simply switch this totally / or make it optional. I don't now how this would affect the stata side (I don't think a round-trip would be affected on the pandas side at all).

Though dealing with unicode could be a bit of an issue (you need to astype to U123 rather than S123)

[bashtage]

I don't think Stata handles unicode anyway - just different encodings of 1 byte strings (e.g. Latin1)