How to split text inside a pandas dataframe into new dataframe columns

Question

I have a list

list1= ['{"bank_name": null, "country": null, "url": null, "type": "Debit", "scheme": "Visa", "bin": "789452"}\n',
 '{"prepaid": "", "bin": "123457", "scheme": "Visa", "type": "Debit", "bank_name": "Ohio", "url": "www.u.org", "country": "UKs"}\n']

I passed it into a dataframe:

df = pd.DataFrame({'bincol':list1})
print(df)
                                               bincol
0  {"bank_name": null, "country": null, "url": nu...
1  {"prepaid": "", "bin": "123457", "scheme": "Vi...

I am trying to split bincol columns into new columns using this function

def explode_col(df, column_value):
    df = df.dropna(subset=[column_value])
    if isinstance(df[str(column_value)].iloc[0], str):
        df[column_value] = df[str(column_value)].apply(ast.literal_eval)
    expanded_child_df = (pd.concat({i: json_normalize(x) for i, x in .pop(str(column_value)).items()}).reset_index(level=1,drop=True).join(df, how='right', lsuffix='_left', rsuffix='_right').reset_index(drop=True))
    expanded_child_df.columns = map(str.lower, expanded_child_df.columns)

    return expanded_child_df

df2 = explode_col(df,'bincol')

But i am getting this error, am i missing something here ?

raise ValueError(f'malformed node or string: {node!r}')
ValueError: malformed node or string: <_ast.Name object at 0x7fd3aa05c400>

jezrael · Accepted Answer · 2021-03-31 06:02:53Z

3

For me working in your sample data json.loads for convert data to dictionaries, then is used json_normalize for DataFrame:

import json

df = pd.json_normalize(df['bincol'].apply(json.loads))
print(df)

  bank_name country        url   type scheme     bin prepaid
0      None    None       None  Debit   Visa  789452     NaN
1      Ohio     UKs  www.u.org  Debit   Visa  123457

answered Mar 31, 2021 at 6:02

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to split text inside a pandas dataframe into new dataframe columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related