Pandas DataFrame from list/dict/list

Question

I have some data in this form:

a = [{'table': 'a', 'field':['apple', 'pear']}, 
     {'table': 'b', 'field':['grape', 'berry']}]

I want to create a dataframe that looks like this:

    field table
0   apple     a
1   pear      a
2   grape     b
3   berry     b

When I try this:

pd.DataFrame.from_records(a)

I get this:

            field table
0   [apple, pear]     a
1  [grape, berry]     b

I'm using a loop to restructure my original data, but I think there must be a more straightforward and simpler methid.

How do you infer `berry c`? Shouldn't it be `b`. – umutto Aug 25 '17 at 01:23 — umutto, Aug 25 '17 at 01:23
@umutto is correct - I will edit question – Mike Woodward Aug 25 '17 at 02:14 — Mike Woodward, Aug 25 '17 at 02:14

piRSquared · Answer 1 · 2017-08-25T01:34:54.807

4

Option 1
comprehension

pd.DataFrame([{'table': d['table'], 'field': f} for d in a for f in d['field']])

   field table
0  apple     a
1   pear     a
2  grape     b
3  berry     b

Option 2
reconstruct

d1 = pd.DataFrame(a)
pd.DataFrame(dict(
    table=d1.table.repeat(d1.field.str.len()),
    field=np.concatenate(d1.field)
)).reset_index(drop=True)

   field table
0  apple     a
1   pear     a
2  grape     b
3  berry     b

Option 3
Rubik's Cube

pd.DataFrame(a).set_index('table').field.apply(pd.Series) \
    .stack().reset_index('table', name='field').reset_index(drop=True)

  table  field
0     a  apple
1     a   pear
2     b  grape
3     b  berry

edited Aug 25 '17 at 01:34

answered Aug 25 '17 at 01:23

piRSquared

285,575
57
475
624

I prefer option 1. Given that `table` is a scalar, I can just take its value. – Alexander Aug 25 '17 at 01:32
Option 3 is an interesting approach, although I wouldn't want to review it six months down the line and ask WTF did I write back then... (-; – Alexander Aug 25 '17 at 01:39
Amen to that. But hey! It's one line and that has to count for something. I heard one line makes things faster and cotton candy taste like unicorns. – piRSquared Aug 25 '17 at 01:40
I like option 3 :) – BENY Aug 25 '17 at 02:03

Alexander · Accepted Answer · 2017-08-25T01:35:41.967

4

You can use a list comprehension to concatenate a series of dataframes, one for each dictionary in a.

>>> pd.concat([pd.DataFrame({'table': d['table'],  # Per @piRSquared for simplification.
                             'field': d['field']})
               for d in a]).reset_index(drop=True)
   field table
0  apple     a
1   pear     a
2  grape     b
3  berry     b

edited Aug 25 '17 at 01:35

answered Aug 25 '17 at 01:29

Alexander

105,104
32
201
196

I love that! Smart! – piRSquared Aug 25 '17 at 01:36
This is the solution I used. Perfect. – Mike Woodward Aug 25 '17 at 02:18

score 0 · Answer 3 · answered Aug 25 '17 at 02:18

Or you can try using pd.wide_to_long ,I do want to use lreshape, but it is undocumented and personally not recommended ...T _ T

a = [{'table': 'a', 'field':['apple', 'pear']},
     {'table': 'b', 'field':['grape', 'berry']}]
df=pd.DataFrame.from_records(a)

df[['Feild1','Feild2']]=df.field.apply(pd.Series)
pd.wide_to_long(df,['Feild'],'table','lol').reset_index().drop('lol',axis=1).sort_values('table')

Out[74]: 
  table  Feild
0     a  apple
2     a   pear
1     b  grape
3     b  berry

Pandas DataFrame from list/dict/list

3 Answers3

Linked