Converting each row into a dataframe and concatenate results

user_14165730 0
Viewed 535

I have a dataframe df in the format below, where content is a string column.

                                                               content
0  {'api': ['api_1', 'api_1', 'api_1'],'A': [1,2,3], 'B': [4,5,6] }
1  {'api': ['api_2', 'api_2', 'api_2'],'A': [7,8,9], 'B': [10,11,12] }

and I want to convert it to the format:

     api  A   B
0  api_1  1  4
1  api_1  2  5
2  api_1  3  6
3  api_2  7  10
4  api_2  8  11
5  api_2  9  12

I tried doing this,

pd.concat([pd.DataFrame(eval(row['content'])) for (rownum, row) in df.iterrows()])

which works, but it doesn't look nice. Is there any better way to do this?

pandas dataframe python
2 Answers

Let us try

out = pd.concat(pd.DataFrame(x) for x in df.content.map(literal_eval))
     api  A   B
0  api_1  1   4
1  api_1  2   5
2  api_1  3   6
0  api_2  7  10
1  api_2  8  11
2  api_2  9  12
user_79645270

One approach is to use literal_eval to evaluate the strings in column content as python dectionaries, then create a new dataframe from this evaluated column and use apply + pd.Series.explode:

from ast import literal_eval

r = df['content'].map(literal_eval).tolist()
pd.DataFrame(r).apply(pd.Series.explode).reset_index(drop=True)

Another approach which uses defaultdict to merge all the dictionaries in content column:

from collections import defaultdict

dct = defaultdict(list)
for d in df['content']:
    for k, v in literal_eval(d).items():
        dct[k] += v

pd.DataFrame(dct)

     api  A   B
0  api_1  1   4
1  api_1  2   5
2  api_1  3   6
3  api_2  7  10
4  api_2  8  11
5  api_2  9  12
user_128331660
Related