pivot_table: precision lost

[mstanichenko]

Code Sample, a copy-pastable example if possible

In [29]: df = pd.DataFrame([[1, 2, 11111111111111111]], columns=['index', 'type', 'value'])

In [30]: df.dtypes
Out[30]: 
index    int64
type     int64
value    int64
dtype: object

In [31]: df.pivot_table(index='index', columns='type', values='value')
Out[31]: 
type                   2
index                   
1      11111111111111112

Problem description

Since value is a 64-bit integer, we should be able to present it with zero precision loss.

Expected Output

In [31]: df.pivot_table(index='index', columns='type', values='value')
Out[31]: 
type                   2
index                   
1      11111111111111111

Output of pd.show_versions()

# Paste the output here pd.show_versions() here

INSTALLED VERSIONS

commit: None
python: 3.5.2.final.0
python-bits: 64
OS: Linux
OS-release: 3.10.0-514.2.2.el7.x86_64
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8

pandas: 0.18.0
nose: 1.3.7
pip: 9.0.1
setuptools: 28.8.0.post20161110
Cython: 0.23.4
numpy: 1.11.2
scipy: 0.17.0
statsmodels: 0.6.1
xarray: None
IPython: 5.1.0
sphinx: 1.3.5
patsy: 0.4.0
dateutil: 2.6.0
pytz: 2016.2
blosc: None
bottleneck: 1.0.0
tables: 3.2.2
numexpr: 2.5
matplotlib: 1.5.1
openpyxl: 2.3.2
xlrd: 0.9.4
xlwt: 1.0.0
xlsxwriter: 0.8.4
lxml: 3.6.0
bs4: 4.4.1
html5lib: None
httplib2: 0.9.2
apiclient: 1.5.2
sqlalchemy: 1.0.15
pymysql: None
psycopg2: None
jinja2: 2.8
boto: 2.39.0

[jreback]

this is passing thru float

In [5]: np.int64(11111111111111111).astype(float).astype('int64')
Out[5]: 11111111111111112

very similar to this

In [7]: df.groupby('index').value.mean()
Out[7]: 
index
1    11111111111111112
Name: value, dtype: int64

so even though its cast back it will lose precision.

not really sure of a great way to prevent this and simulataneously deal with overflow.

[jorisvandenbossche]

You can use pivot instead of pivot_table for now to circumvent this:

In [6]: df.pivot(index='index', columns='type', values='value')
Out[6]: 
type                   2
index                   
1      11111111111111111

The thing is that pivot_table takes a mean, and does not just represent the original value, so this is the reason you see the precion error.

In [10]: df['value'].mean()
Out[10]: 1.1111111111111112e+16

So not sure there is something to do about it.

[jreback]

xref to #3707

[jorisvandenbossche]

@jreback Do we have a general issue about the precision for int64 algos like mean? As this is not specific to pivot_table, so I would close this.
Although it is specific to pivot_table/groupby that we cast back to int instead of leaving it as a float (which masks the reason for the precision error), but that is covered here: #11199 (comment)

[jreback]

oh that whole issue is better anyhow #11199

[mstanichenko]

You can use pivot instead of pivot_table for now

Unfortunately, pivot is not able to handle multiindex.
I deliberately simplified the example to present the point.

[jorisvandenbossche]

@mstanichenko pivot is specifically defined not to handle aggregations (pure reshaping function), but otherwise there should not be more restrictions in comparison to pivot_table (or otherwise this may be a bug). Can you give a small example of how you want to use multiindex? (if it is still without aggregation of course)

[mstanichenko]

@jorisvandenbossche, I don't really need a multiindex.
You are right that I need just to reshape a dataframe. Unfortunately, pivot didn't work for me.

Can you give a small example of how you want to use multiindex?

I have a set of components and subcomponents which regularly take timestamps.
The timestamps can be of various types. Therefore the dataframe looks like following.

In [41]: df_ts.columns
Out[41]: Index(['component_id', 'subcomponent_id', 'timestamp_type', 'timestamp_value'], dtype='object')

All I want to do is

df = df_ts.pivot(index=['component_id', 'subcomponent_id'], colums='timestamp_type', values='timestamp_value').reset_index()

[jorisvandenbossche]

So with a dummy example, something like this?

In [73]: df = pd.DataFrame({'A':[0,0,0,1,1,1], 'B':[0,1,0,1,0,1], 'C':[0,0,1,1,1,0], 'D':[1,2,3,4,5,6]})

In [74]: df
Out[74]: 
   A  B  C  D
0  0  0  0  1
1  0  1  0  2
2  0  0  1  3
3  1  1  1  4
4  1  0  1  5
5  1  1  0  6

In [75]: df.pivot(index=['A', 'B'], columns='C', values='D')
...
ValueError: Wrong number of items passed 6, placement implies 2

In [76]: df.pivot_table(index=['A', 'B'], columns='C', values='D')
Out[76]: 
C      0    1
A B          
0 0  1.0  3.0
  1  2.0  NaN
1 0  NaN  5.0
  1  6.0  4.0

where you would expect the output of pivot being the same as pivot_table ?