Make list values to columns in DataDrama

Question

I've stumbled upon this problem and can't find a solution for hours. Basically I have the following two list, which I wish to transform to my final solution: A data frame, which has dates as the index and each instrument as a column with the respective values in that column.

In my first list I have the following structure:

[  Instrument                  Date
 0     IWVL.L  2017-12-29T00:00:00Z
 1     IWVL.L  2017-12-28T00:00:00Z
 2     IWVL.L  2017-12-27T00:00:00Z
 3     IWVL.L  2017-12-22T00:00:00Z
 4     IWVL.L  2017-12-21T00:00:00Z
 5     IWVL.L  2017-12-20T00:00:00Z,   Instrument                  Date
 0     IWMO.L  2017-12-29T00:00:00Z
 1     IWMO.L  2017-12-28T00:00:00Z
 2     IWMO.L  2017-12-27T00:00:00Z
 3     IWMO.L  2017-12-22T00:00:00Z
 4     IWMO.L  2017-12-21T00:00:00Z
 5     IWMO.L  2017-12-20T00:00:00Z,

and so on (a total of 100 entries).

My second list has the following structure:

[  Instrument  Total Return
 0     IWVL.L      0.405743
 1     IWVL.L     -0.031201
 2     IWVL.L      0.046824
 3     IWVL.L     -0.140274
 4     IWVL.L      0.375469
 5     IWVL.L      0.156691,   Instrument  Total Return
 0     IWMO.L      0.294196
 1     IWMO.L      0.080300
 2     IWMO.L     -0.080235
 3     IWMO.L     -0.213504
 4     IWMO.L      0.321285
 5     IWMO.L     -0.120337,

Now I want a structure which puts dates as a index and the Instruments as separate columns (First Column: IWVL.K, Second Column: IWMO.L, and so on). The values of Total Return are then listed columnwise for the specific date. It looks like this (snippet for first two instruments):

               IWVL.L    IWMO.L  ...
Date                        
2017-12-29  0.405743    0.294196 ...    
2017-12-28  -0.031201   0.080300 ...    
2017-12-27  0.046824    -0.080235 ...

Would be glad if somebody can help out here.

Oh and happy New Years eve!

Does the second list also have 100 entries that have a 1-1 correspondence with the first list? — cs95, Dec 31 '17 at 11:33

cs95 · Accepted Answer · 2017-12-31T11:56:23.047

If your two lists are l1 and l2, and if the elements are 1-1 corresponding, then your solution simplifies, using concat and a subsequent pivot operation.

i = pd.concat(l1, ignore_index=True)
j = pd.concat(l2, ignore_index=True)

df = pd.concat([i, j[['Total Return']]], axis=1)\
       .pivot('Date', 'Instrument', 'Total Return')
df

Instrument              IWMO.L    IWVL.L
Date                                    
2017-12-20T00:00:00Z -0.120337  0.156691
2017-12-21T00:00:00Z  0.321285  0.375469
2017-12-22T00:00:00Z -0.213504 -0.140274
2017-12-27T00:00:00Z -0.080235  0.046824
2017-12-28T00:00:00Z  0.080300 -0.031201
2017-12-29T00:00:00Z  0.294196  0.405743

Details

First, we concatenate each dataframe in each list -

i = pd.concat(l1, ignore_index=True)
i.head()

  Instrument                  Date
0     IWVL.L  2017-12-29T00:00:00Z
1     IWVL.L  2017-12-28T00:00:00Z
2     IWVL.L  2017-12-27T00:00:00Z
3     IWVL.L  2017-12-22T00:00:00Z
4     IWVL.L  2017-12-21T00:00:00Z

j = pd.concat(l2, ignore_index=True)
j.head()

  Instrument  Total Return
0     IWVL.L      0.405743
1     IWVL.L     -0.031201
2     IWVL.L      0.046824
3     IWVL.L     -0.140274
4     IWVL.L      0.375469

Now, since each entry corresponds directly, just combine them by joining them horizontally. We only want the 'Total Return' column from the second one, so slice a dataframe column out accordingly -

k = j[['Total Return']]
k.head()

   Total Return
0      0.405743
1     -0.031201
2      0.046824
3     -0.140274
4      0.375469

Combine k with i -

v = pd.concat([i, k], axis=1)

   Instrument                  Date  Total Return
0      IWVL.L  2017-12-29T00:00:00Z      0.405743
1      IWVL.L  2017-12-28T00:00:00Z     -0.031201
2      IWVL.L  2017-12-27T00:00:00Z      0.046824
3      IWVL.L  2017-12-22T00:00:00Z     -0.140274
4      IWVL.L  2017-12-21T00:00:00Z      0.375469
5      IWVL.L  2017-12-20T00:00:00Z      0.156691
6      IWMO.L  2017-12-29T00:00:00Z      0.294196
7      IWMO.L  2017-12-28T00:00:00Z      0.080300
8      IWMO.L  2017-12-27T00:00:00Z     -0.080235
9      IWMO.L  2017-12-22T00:00:00Z     -0.213504
10     IWMO.L  2017-12-21T00:00:00Z      0.321285
11     IWMO.L  2017-12-20T00:00:00Z     -0.120337

Now, the last step is the "pivot" operation. How do we know to pivot? That's just a fancy name for reshaping your data. You want one column becoming the index in the result, another column becoming the columns in the result, and the last column becoming the values. This is the very description of a pivoting operation (and is very similar to the SQL pivot idiom).

v.pivot(index='Date', columns='Instrument', values='Total Return')

Instrument              IWMO.L    IWVL.L
Date                                    
2017-12-20T00:00:00Z -0.120337  0.156691
2017-12-21T00:00:00Z  0.321285  0.375469
2017-12-22T00:00:00Z -0.213504 -0.140274
2017-12-27T00:00:00Z -0.080235  0.046824
2017-12-28T00:00:00Z  0.080300 -0.031201
2017-12-29T00:00:00Z  0.294196  0.405743

More information can be found here - How to pivot a dataframe.

Since im new to python can you briefly explain what the \ .pivot('Date', 'Instrument', 'Total Return') exactly does ? — Arthur Vott, Dec 31 '17 at 11:49

Make list values to columns in DataDrama

1 Answers1