List of list to pandas dataframe

Question

I have a dataset that follows this format:

data =[[[1, 0, 1000], [2, 1000, 2000]],
        [[1, 0, 1500], [2, 1500, 2500], [2, 2500, 4000]]]
var1 = [10.0, 20.0]
var2 = ['ref1','ref2']

I want to convert it to a dataframe:

dic = {'var1': var1, 'var2': var2, 'data': data}

import Pandas as pd
pd.DataFrame(dic)

The result:

Dataframe initial

However I'm trying to get something like this:

enter image description here

I've been trying to flatten the dictionary/list but with no success:

pd.DataFrame([[col1, col2] for col1, d in dic.items() for col2 in d])

See the result:

try again

The different sizes of the list made the 'unpacking' complicated for another level. I'm not sure if pandas could take care of this of it needs to be done before importing into pandas.

Mike Müller · Accepted Answer · 2015-05-19T20:33:58.410

2

Creating an appropriate list works:

new_data = []
for x, v1, v2 in zip(data, var1, var2):
    new_data.extend([y + [v1] + [v2] for y in x])
pd.DataFrame(new_data, columns=['data', 'min', 'max', 'var1', 'var2'])

gives:

   data   min   max  var1  var2
0     1     0  1000    10  ref1
1     2  1000  2000    10  ref1
2     1     0  1500    20  ref2
3     2  1500  2500    20  ref2
4     2  2500  4000    20  ref2

edited May 19 '15 at 20:33

answered May 19 '15 at 20:09

Mike Müller

82,630
20
166
161

1

There is a bug in your code (and the output doesn't match). You repeated the `var1`. – chrisaycock May 19 '15 at 20:17
1

@chrisaycock Thanks for the pointer. Had a copy-paste problem. Must be `var1` and `var2` not `var1` twice in `zip`. Fixed this. – Mike Müller May 19 '15 at 20:23
The output still doesn't match what you've pasted here. Hint: check the indices. – chrisaycock May 19 '15 at 20:25
1

@chrisaycock Pasted my current output. – Mike Müller May 19 '15 at 20:34
1

Nice use of extend, it is really handy in this case. – Yoann_R May 19 '15 at 21:08

score 1 · Answer 2 · answered May 19 '15 at 20:01

1

I can iterate over the rows in your temporary DataFrame.

df = pd.DataFrame(dic)
result = []
for i,d in df.iterrows():
    temp = pd.DataFrame(d['data'], columns=['data', 'min', 'max'])
    temp['var1'] = d['var1']
    temp['var2'] = d['var2']
    result += [temp]
pd.concat(result)

This produces

   data   min   max  var1  var2
0     1     0  1000    10  ref1
1     2  1000  2000    10  ref1
0     1     0  1500    20  ref2
1     2  1500  2500    20  ref2
2     2  2500  4000    20  ref2

answered May 19 '15 at 20:01

chrisaycock

36,470
14
88
125

The picture for the desired result in the question shows indices `0, 1, 2, 3, 4,`. You have `0, 1, 0, 1, 2` as indices. – Mike Müller May 19 '15 at 20:37
1

For my use the indices are not essential, in my dataset I will pass a specific column as index. – Yoann_R May 19 '15 at 21:06

List of list to pandas dataframe

2 Answers2