Myujth

I have data that I've left in a format that will allow me to pivot on dates that look like:

Region 0 1 2 3
Date 2005-01-01 2005-02-01 2005-03-01 ....
East South Central 400 500 600
Pacific 100 200 150
.
.
Mountain 500 600 450

I need to pivot this table so it looks like:

0 Date Region value
1 2005-01-01 East South Central 400 
2 2005-02-01 East South Central 500 
3 2005-03-01 East South Central 600 
.
. 
4 2005-03-01 Pacific 100 
4 2005-03-01 Pacific 200
4 2005-03-01 Pacific 150
.
.

Since both Date and Region are under one another I'm not sure how to melt or pivot around these strings so that I can get my desired output.

How can I go about this?

I think this is the solution you are looking for. Shown by example.

import pandas as pd
import numpy as np
N=100
regions = list('abcdef')
df = pd.DataFrame([[i for i in range(N)], ['2016-'.format(i) for i in range(N)], 
 list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N)), 
 list(np.random.randint(0,500, N)), list(np.random.randint(0,500, N))])
df.index = ['Region', 'Date', 'a', 'b', 'c', 'd']
print(df)

This gives

 0 1 2 3 4 5 6 7 
Region 0 1 2 3 4 5 6 7 
Date 2016-0 2016-1 2016-2 2016-3 2016-4 2016-5 2016-6 2016-7 
a 96 432 181 64 87 355 339 314 
b 360 23 162 98 450 78 114 109 
c 143 375 420 493 321 277 208 317 
d 371 144 207 108 163 67 465 130

And the solution to pivot this into the form you want is

df.transpose().melt(id_vars=['Date'], value_vars=['a', 'b', 'c', 'd'])

which gives

 Date variable value
0 2016-0 a 96
1 2016-1 a 432
2 2016-2 a 181
3 2016-3 a 64
4 2016-4 a 87
5 2016-5 a 355
6 2016-6 a 339
7 2016-7 a 314
8 2016-8 a 111
9 2016-9 a 121
10 2016-10 a 124
11 2016-11 a 383
12 2016-12 a 424
13 2016-13 a 453
...
393 2016-93 d 176
394 2016-94 d 277
395 2016-95 d 256
396 2016-96 d 174
397 2016-97 d 349
398 2016-98 d 414
399 2016-99 d 132