pydata/pandas — pull request #9061 by behzadnouri (2014-12-12 02:10:36+00:00)
Title: pivot & unstack with nan in the index

closes https://github.com/pydata/pandas/issues/7466
on branch:

>>> df
a b c
0 R1 C1 10
1 R2 C2 15
2 NaN C3 17
3 R4 C4 20
>>> df.pivot('a', 'b', 'c').fillna('.')
b C1 C2 C3 C4
a
NaN . . 17 .
R1 10 . . .
R2 . 15 . .
R4 . . . 20
>>> df.pivot('b', 'a', 'c').fillna('.')
a NaN R1 R2 R4
b
C1 . 10 . .
C2 . . 15 .
C3 17 . . .
C4 . . . 20

the `pivot` function requires the `unstack` function to work with `nan`s in the index:

>>> df
4th 5th
1st 2nd 3rd
d y 67 d.y.67 67.y.d
39 d.y.39 39.y.d
w 53 d.w.53 53.w.d
NaN w 72 .w.72 72.w.
57 .w.57 57.w.
NaN 80 . .80 80. .
31 . .31 31. .
18 . .18 18. .
a z 11 a.z.11 11.z.a
30 a.z.30 30.z.a
c z 59 c.z.59 59.z.c
50 c.z.50 50.z.c
NaN 62 c. .62 62. .c
e NaN 59 e. .59 59. .e
76 e. .76 76. .e
b x 52 b.x.52 52.x.b
14 b.x.14 14.x.b
53 b.x.53 53.x.b
NaN 60 b. .60 60. .b
51 b. .51 51. .b
the `4th` and `5th` column are so that it is easy to verify the frame:

>>> df.unstack(level=1).fillna('-')
4th 5th
2nd NaN w x y z NaN w x y z
1st 3rd
NaN 18 . .18 - - - - 18. . - - - -
31 . .31 - - - - 31. . - - - -
57 - .w.57 - - - - 57.w. - - -
72 - .w.72 - - - - 72.w. - - -
80 . .80 - - - - 80. . - - - -
a 11 - - - - a.z.11 - - - - 11.z.a
30 - - - - a.z.30 - - - - 30.z.a
b 14 - - b.x.14 - - - - 14.x.b - -
51 b. .51 - - - - 51. .b - - - -
52 - - b.x.52 - - - - 52.x.b - -
53 - - b.x.53 - - - - 53.x.b - -
60 b. .60 - - - - 60. .b - - - -
c 50 - - - - c.z.50 - - - - 50.z.c
59 - - - - c.z.59 - - - - 59.z.c
62 c. .62 - - - - 62. .c - - - -
d 39 - - - d.y.39 - - - - 39.y.d -
53 - d.w.53 - - - - 53.w.d - - -
67 - - - d.y.67 - - - - 67.y.d -
e 59 e. .59 - - - - 59. .e - - - -
76 e. .76 - - - - 76. .e - - - -

`nan` values are kept out of levels and are handled by labels:

>>> df.unstack(level=1).index
MultiIndex(levels=[['a', 'b', 'c', 'd', 'e'], [11, 14, 18, 30, 31, 39, 50, 51, 52, 53, 57, 59, 60, 62, 67, 72, 76, 80]],
labels=[[-1, -1, -1, -1, -1, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4], [2, 4, 10, 15, 17, 0, 3, 1, 7, 8, 9, 12, 6, 11, 13, 5, 9, 14, 11, 16]],
names=['1st', '3rd'])
>>> df.unstack(level=1).columns
MultiIndex(levels=[['4th', '5th'], ['w', 'x', 'y', 'z']],
labels=[[0, 0, 0, 0, 0, 1, 1, 1, 1, 1], [-1, 0, 1, 2, 3, -1, 0, 1, 2, 3]],
names=[None, '2nd'])