convert list of lists in dataframe

Question

I have a following data

0      [[-0.932, 2.443, -1....
1      [[-1.099, 2.140, -1.4...
2      [[-0.985, -1.654, -1....
3      [[-1.339, 2.070, -0....
4      [[-1.119, 2.788, -2....
                             ...                        
494    [[-0.023, 2.688, -1...
495    [[1.897, 0.0, -2.249,...
496    [[1.538, 2.349, -0.6...
497    [[-0.141, 2.320, -0...
498    [[-0.483, 1.587, -1....
Length: 499, dtype: object

In each row are about 80 lists consisted (list of lists) and I would like to turn them into columns and to get the data:

          ID  col1      col2   ...   col80
1.1.2020  0   -0.932    ...
2.1.2020  0   2.443     ...
3.1.2020  0   -1        ...       
1.1.2020  1   -1.099
2.1.2020  1   2.140
3.1.2020  1   -1.4      ...

where the column ID is from the lists indicator (0,1,..,498). The index column (1.1.2020 2.1.2020..) is saved as another object (date). Is this possible and how?

Chrysophylaxs · Accepted Answer · 2023-01-03 16:37:13Z

2

Let's say you had data like:

import numpy as np
import pandas as pd

ser = pd.Series(np.arange(90).reshape(10, 3, 3).tolist())

0             [[0, 1, 2], [3, 4, 5], [6, 7, 8]]
1     [[9, 10, 11], [12, 13, 14], [15, 16, 17]]
2    [[18, 19, 20], [21, 22, 23], [24, 25, 26]]
3    [[27, 28, 29], [30, 31, 32], [33, 34, 35]]
4    [[36, 37, 38], [39, 40, 41], [42, 43, 44]]
5    [[45, 46, 47], [48, 49, 50], [51, 52, 53]]
6    [[54, 55, 56], [57, 58, 59], [60, 61, 62]]
7    [[63, 64, 65], [66, 67, 68], [69, 70, 71]]
8    [[72, 73, 74], [75, 76, 77], [78, 79, 80]]
9    [[81, 82, 83], [84, 85, 86], [87, 88, 89]]
dtype: object

then I think you can do the bulk of the work like so:

out = ser.explode().apply(pd.Series).reset_index(names="ID")

    ID   0   1   2
0    0   0   1   2
1    0   3   4   5
2    0   6   7   8
3    1   9  10  11
4    1  12  13  14
5    1  15  16  17
6    2  18  19  20
7    2  21  22  23
8    2  24  25  26
9    3  27  28  29
10   3  30  31  32
11   3  33  34  35
12   4  36  37  38
13   4  39  40  41
14   4  42  43  44
15   5  45  46  47
16   5  48  49  50
17   5  51  52  53
18   6  54  55  56
19   6  57  58  59
20   6  60  61  62
21   7  63  64  65
22   7  66  67  68
23   7  69  70  71
24   8  72  73  74
25   8  75  76  77
26   8  78  79  80
27   9  81  82  83
28   9  84  85  86
29   9  87  88  89

but you'll need to rename the columns and change the index yourself (how are you determining those dates?)

answered Jan 3, 2023 at 16:37

Chrysophylaxs

6,5933 gold badges13 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Beginner_01 Over a year ago

Thanks, this is what I was looking for. The data that I presented are after the standard scaler is applied, so I have the dates as the index in the original data ('data.index')

Chrysophylaxs Over a year ago

Glad it worked, keep in mind that in this example the ID column stems from having the default RangeIndex before exploding.

Collectives™ on Stack Overflow

convert list of lists in dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related