a python Dataframe from a list of dictionaries with some values as lists

Question

I have a similarly formatted input to what provided below, that I'm trying to convert into a python DataFrame

my_input = [
{'comp_id': '111', 'name': 'A-Name', 'brnch_id': ['BR-AA1', 'BR-AA2']},
{'comp_id': '222', 'name': 'B-Name', 'brnch_id': ['BR-BB1', 'BR-BB2', 'BR-BB3']},
{'comp_id': '333', 'name': 'C-Name', 'brnch_id': None}
]

The desired output should be:

        comp_id     name        brnch_id
        -------     ------      --------
        111         A-Name      BR-AA1
        111         A-Name      BR-AA2
        222         B-Name      BR-BB1
        222         B-Name      BR-BB2
        222         B-Name      BR-BB3
        333         C-Name

Any suggestions?

The explode method seems useful, but I should have been more diligent with my question and suggest that the data input could have more than one column that requires expanding, so here is a the revised challenge:

[
{'comp_id': '111', 'name': 'A-Name', 'brnch_id': ['BR-1111', 'BR-1112'], 'brnch_name': ['AA1','AA2']},
{'comp_id': '222', 'name': 'B-Name', 'brnch_id': ['BR-2221', 'BR-2222', 'BR-2223'], 'brnch_name': ['BB1','BB2','BB3']},
{'comp_id': '333', 'name': 'C-Name', 'brnch_id': None, 'brnch_name': None}
]

With a desired output as follows:

        comp_id     name        brnch_id    brnch_name
        -------     ------      --------    ----------
        111         A-Name      BR-1111     AAA1
        111         A-Name      BR-1112     AAA2
        222         B-Name      BR-2221     BBB1
        222         B-Name      BR-2222     BBB2
        222         B-Name      BR-2223     BBB3
        333         C-Name

the expectation is that each comp_id should have a name and if branch exists, it should have a id (brnch_id) and brnch_name

Adding the second .explode is not producing the desired output.

   comp_id    name brnch_id brnch_name
0      111  A-Name  BR-1111        AA1
1      111  A-Name  BR-1111        AA2
2      111  A-Name  BR-1112        AA1
3      111  A-Name  BR-1112        AA2
4      222  B-Name  BR-2221        BB1
5      222  B-Name  BR-2221        BB2
6      222  B-Name  BR-2221        BB3
7      222  B-Name  BR-2222        BB1
8      222  B-Name  BR-2222        BB2
9      222  B-Name  BR-2222        BB3
10     222  B-Name  BR-2223        BB1
11     222  B-Name  BR-2223        BB2
12     222  B-Name  BR-2223        BB3
13     333  C-Name     None       None

Brnch_name are being exploded for each brnch_id. so brnch_id = BR-1111 shows as having two brnch_names: AA1 & AA2.

Just add another `explode` after the first one - second line in my answer - `df=df.explode('brnch_id').explode('brnch_name').reset_index(drop=True)` — Grzegorz Skibinski, Jun 21 '20 at 22:23
newbie at stackoverflow so initially used comment instead of editing the question post. the sentenced should have read as "a second .explode is not pro..Ducing the desired results". hit enter by mistake, and then switched to comment using the original question post. — oi68, Jun 21 '20 at 23:47
No worries, I was just curious :) see my edit for the extended example. — Grzegorz Skibinski, Jun 22 '20 at 13:53

Grzegorz Skibinski · Answer 1 · 2020-06-22T13:53:10.340

This will do:

df=pd.DataFrame(my_input)
df=df.explode('brnch_id').reset_index(drop=True)

Output:

  comp_id    name brnch_id
0     111  A-Name   BR-AA1
1     111  A-Name   BR-AA2
2     222  B-Name   BR-BB1
3     222  B-Name   BR-BB2
4     222  B-Name   BR-BB3
5     333  C-Name     None

Edit for your extended example:

from itertools import zip_longest

df=pd.DataFrame(my_input)
df["x"]=list(zip(df['brnch_id'].fillna(''), df['brnch_name'].fillna('')))

df["x"]=df["x"].apply(lambda x: list(zip_longest(*x)))
df=df.explode('x')
mask=~df['x'].isna()
df.loc[mask, 'brnch_id']=df.loc[mask, 'x'].str[0]
df.loc[mask, 'brnch_name']=df.loc[mask, 'x'].str[1]
df=df.drop('x', axis=1).reset_index(drop=True)

Outputs:

>>> df.iloc[:, -2:]

  brnch_id brnch_name
0  BR-1111        AA1
1  BR-1112        AA2
2  BR-2221        BB1
3  BR-2222        BB2
4  BR-2223        BB3
5     None       None

a python Dataframe from a list of dictionaries with some values as lists

1 Answers1