expand array in pandas

Question

I have the following data:

data = [{
  'color': ['red','green'],
  'name': 'obj1' 
}, {
  'color': ['blue','brown','pink'],
  'name': 'obj2'
}]

and when I use pandas, it gives me an output like this:

    color                 name
0   [red, green]          obj1
1   [blue, brown, pink]   obj2

but I need an output like this:

    color.0  color.1  color.2  name
0   red      green    NaN         obj1
1   blue     brown    pink     obj2

I have tried json_normalize but unable to get the desired output.

Thanks in advance.

Answer 1

You can preprocessing list of dicts and then call DataFrame constructor:

out = []
for x in data:
    d = {}
    for k, v in x.items():
        if isinstance(v, list):
            for i, y in enumerate(v):
                d['{}.{}'.format(k,i)] = y
        else:
            d[k] = v
    out.append(d)
print (out)
[{'color.0': 'red', 'color.1': 'green', 'name': 'obj1'}, 
 {'color.0': 'blue', 'color.1': 'brown', 'color.2': 'pink', 'name': 'obj2'}]

df = pd.DataFrame(out).sort_index(axis=1)
print (df)
  color.0 color.1 color.2  name
0     red   green     NaN  obj1
1    blue   brown    pink  obj2

You can create DataFrame and then expand list column to multiple ones:

df = pd.DataFrame(data)
df1 = pd.DataFrame(df.pop('color').values.tolist(), index=df.index)

df = df.join(df1.add_prefix('color.')).sort_index(axis=1)
print (df)
  color.0 color.1 color.2  name
0     red   green    None  obj1
1    blue   brown    pink  obj2