Pivoting but Index Mismatch Issue leading to NaN

Question

I have the following DataFrame.

print df

    Period   Hour
0       22   6665
1       22   8290
2       22   8589
3       22   9808
4       22  10010
5       22   9152
6       22   7701
7       23   6658
8       23  10039
9       23   8396
10      23   9130
11      23   9029
12      23   7985
13      23   6784

Originally what I wanted to achieve via some one line code:

   22      23  
  6665    6658
  8290    10039
  8589    8396
  9808    9130
  10010   9029
  9152    7985
  7701    6784

Tried pivoting but got NaN due to indexes being different.

print(df.pivot(columns='Period'))

           Hour         
Period       22       23
0        6665.0      NaN
1        8290.0      NaN
2        8589.0      NaN
3        9808.0      NaN
4       10010.0      NaN
5        9152.0      NaN
6        7701.0      NaN
7           NaN   6658.0
8           NaN  10039.0
9           NaN   8396.0
10          NaN   9130.0
11          NaN   9029.0
12          NaN   7985.0
13          NaN   6784.0

Output achieved via the following apparently unnecessarily long route:

a = df[df['Period'] == 22]['Hour'].values
b = df[df['Period'] == 23]['Hour'].values
print(pd.DataFrame({22:a, 23:b}))

      22     23
0   6665   6658
1   8290  10039
2   8589   8396
3   9808   9130
4  10010   9029
5   9152   7985
6   7701   6784

There must be a shorter way of doing this via Pivot/Pivot_Table or in some other shorter code. Please suggest.

See Q10 in the linked question. – Quang Hoang Dec 11 '19 at 19:22 — Quang Hoang, Dec 11 '19 at 19:22

score 2 · Answer 1 · answered Dec 11 '19 at 19:19

Try this:

Option 1:

df.set_index([df.groupby('Period').cumcount(),'Period'])['Hour'].unstack()

Output:

Period     22     23
0        6665   6658
1        8290  10039
2        8589   8396
3        9808   9130
4       10010   9029
5        9152   7985
6        7701   6784

Option 2:

df.pivot(df.groupby('Period').cumcount(), 'Period')['Hour']

Pivoting but Index Mismatch Issue leading to NaN

1 Answers1