Home > Software design >  How to pandas explode a column of a string escaped list into a pandas column of int
How to pandas explode a column of a string escaped list into a pandas column of int

Time:11-30

I referenced the pandas explode doc :#https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.explode.html

This code works with strings.

import pandas as pd
import numpy as np
df = pd.DataFrame({'A': [["1058","1057","1056","1055","1054"], np.nan, np.nan, ["10","57","56","55","54"]],
                   'B': 1,
                   'C': [['a', 'b', 'c'], np.nan, [], ['d', 'e']]})
df.explode('A')

Gives

A   B   C
0   1058    1   [a, b, c]
0   1057    1   [a, b, c]
0   1056    1   [a, b, c]
0   1055    1   [a, b, c]
0   1054    1   [a, b, c]
1   NaN 1   NaN
2   NaN 1   []
3   10  1   [d, e]
3   57  1   [d, e]
3   56  1   [d, e]
3   55  1   [d, e]
3   54  1   [d, e]

How can I get the same Column A exploded results above with this dataframe that includes quotes?:

df = pd.DataFrame({'A': [['\"1058\",\"1057\",\"1056\",\"1055\",\"1054\"'], np.nan, np.nan, ['\"10\",\"57\",\"56\",\"55\",\"54\"']],
                   'B': 1,
                   'C': [['a', 'b', 'c'], np.nan, [], ['d', 'e']]})

CodePudding user response:

Use pd.eval before explode:

>>> df.assign(A=df['A'].apply(lambda x: pd.eval(x) if pd.notna(x) and x else x)) \
      .explode('A')

      A  B          C
0  1058  1  [a, b, c]
0  1057  1  [a, b, c]
0  1056  1  [a, b, c]
0  1055  1  [a, b, c]
0  1054  1  [a, b, c]
1   NaN  1        NaN
2   NaN  1         []
3    10  1     [d, e]
3    57  1     [d, e]
3    56  1     [d, e]
3    55  1     [d, e]
3    54  1     [d, e]

CodePudding user response:

Use ast which is more prefered like eval:

import ast

df['A'] = df.A.apply(lambda x: ast.literal_eval(x[0]) if isinstance(x, list) else x)
df = df.explode('A')
print (df)
      A  B          C
0  1058  1  [a, b, c]
0  1057  1  [a, b, c]
0  1056  1  [a, b, c]
0  1055  1  [a, b, c]
0  1054  1  [a, b, c]
1   NaN  1        NaN
2   NaN  1         []
3    10  1     [d, e]
3    57  1     [d, e]
3    56  1     [d, e]
3    55  1     [d, e]
3    54  1     [d, e]
  • Related