CODEWITHSUNDEEP
pythonpandasdataframepivotreshape
ctrl-alt-delete
ctrl-alt-delete

Reputation: 3852

How to preserve the index from pivoting?

I have a dataframe (fx_return):

index_date      ccy     rate
====================================
2016-06-01      CHF     1.013017272
2016-06-01      EUR     1.1178
2016-06-01      GBP     1.44255
2016-06-01      USD     1
2016-06-02      CHF     1.010560356
2016-06-02      EUR     1.11615
2016-06-02      GBP     1.44415
2016-06-02      USD     1
2016-06-03      CHF     1.022547165
2016-06-03      EUR     1.1326
2016-06-03      GBP     1.4553
2016-06-03      USD     1
2016-06-06      CHF     1.027749229
2016-06-06      EUR     1.13505
2016-06-06      GBP     1.4428
2016-06-06      USD     1
2016-06-07      CHF     1.034661148
2016-06-07      EUR     1.13585
2016-06-07      GBP     1.45725
2016-06-07      USD     1
2016-06-08      CHF     1.042318115
2016-06-08      EUR     1.1404
2016-06-08      GBP     1.4567
2016-06-08      USD     1
2016-06-09      CHF     1.038691249
2016-06-09      EUR     1.1324
2016-06-09      GBP     1.4472
2016-06-09      USD     1
2016-06-10      CHF     1.037129226
2016-06-10      EUR     1.1289
2016-06-10      GBP     1.43465
2016-06-10      USD     1

Create the index

Now we must create the index.......

fx_return.set_index('index_date', drop=True, append=False, inplace=False, verify_integrity=False)

Pivot the data

fx_return = fx_return.pivot(index='index_date', columns='ccy', values='rate')



ccy          CHF        EUR        GBP        USD
index_date
2016-06-01   1.013017   1.11780    1.44255    1.0
2016-06-02   1.010560   1.11615    1.44415    1.0
2016-06-03   1.022547   1.13260    1.45530    1.0
2016-06-06   1.027749   1.13505    1.44280    1.0 
2016-06-07   1.034661   1.13585    1.45725    1.0 
2016-06-08   1.042318   1.14040    1.45670    1.0
2016-06-09   1.038691   1.13240    1.44720    1.0 
2016-06-10   1.037129   1.12890    1.43465    1.0

Re-Shape the Data:

fx_return = pd.melt(fx_return, col_level=0)



    ccy    value
0   CHF    1.013017 
1   CHF    1.010560 
2   CHF    1.022547 
3   CHF    1.027749 
4   CHF    1.034661

Is there a way to preserve the index?

The index is lost is there a way to preserve this?

Upvotes: 1

Views: 50

Answers (1)

jezrael
jezrael

Reputation: 862511

Use stack with reset_index instead melt:

print (fx_return.stack().reset_index(name='val'))
    index_date  ccy       val
0   2016-06-01  CHF  1.013017
1   2016-06-01  EUR  1.117800
2   2016-06-01  GBP  1.442550
3   2016-06-01  USD  1.000000
4   2016-06-02  CHF  1.010560
5   2016-06-02  EUR  1.116150
6   2016-06-02  GBP  1.444150
7   2016-06-02  USD  1.000000
8   2016-06-03  CHF  1.022547
9   2016-06-03  EUR  1.132600
10  2016-06-03  GBP  1.455300
11  2016-06-03  USD  1.000000
12  2016-06-06  CHF  1.027749
13  2016-06-06  EUR  1.135050
14  2016-06-06  GBP  1.442800
15  2016-06-06  USD  1.000000
16  2016-06-07  CHF  1.034661
17  2016-06-07  EUR  1.135850
18  2016-06-07  GBP  1.457250
19  2016-06-07  USD  1.000000
20  2016-06-08  CHF  1.042318
21  2016-06-08  EUR  1.140400
22  2016-06-08  GBP  1.456700
23  2016-06-08  USD  1.000000
24  2016-06-09  CHF  1.038691
25  2016-06-09  EUR  1.132400
26  2016-06-09  GBP  1.447200
27  2016-06-09  USD  1.000000
28  2016-06-10  CHF  1.037129
29  2016-06-10  EUR  1.128900
30  2016-06-10  GBP  1.434650
31  2016-06-10  USD  1.000000

Upvotes: 1

Related Questions