How to parse data from column in python pandas

Question

I have a csv file contain the following:

original

and need to parse it and the expected results:

result

Data in text:

id,product_id
    
1,[{'p_id': 59, 'p_name': 'IPF'}, {'p_id': 63, 'p_name': 'RBC'}, {'p_id': 47, 'p_name': 'CSP'}]

2,[{'p_id': 25, 'p_name': 'LPP'}, {'p_id': 86, 'p_name': 'CRS'}, {'p_id': 47, 'p_name': 'CSP'}]

3,[{'p_id': 73, 'p_name': 'OCC'}, {'p_id': 63, 'p_name': 'RBC'}]

4,[{'p_id': 63, 'p_name': 'RBC'}, {'p_id': 31, 'p_name': 'SUT'}, {'p_id': 73, 'p_name': 'OCC'}]

5,[{'p_id': 63, 'p_name': 'RBC'}]

It's recommended to add your data as text as we can not copy the contents from image; Copy first couple of rows from CSV file, and add it to the question — ThePyGuy
– ThePyGuy, Commented Oct 28, 2021 at 3:58
Please copy as it is in CSV, currently, you have everything in a single line — ThePyGuy
– ThePyGuy, Commented Oct 28, 2021 at 4:15
Thank you for your response. I just copied it from my text editor. — SeekKnowledge
– SeekKnowledge, Commented Oct 28, 2021 at 4:20
The data you have doesn't have string values enclosed inside quote, for e.g. : in [{'p_id': 59, 'p_name': IPF}, the value IPF is not enclosed by quote, so you can not use any direct method — ThePyGuy
– ThePyGuy, Commented Oct 28, 2021 at 4:23
My bad, it should have the quote. Edited the data with the quote. Could you help me? Thank you @ThePyGuy — SeekKnowledge
– SeekKnowledge, Commented Oct 28, 2021 at 4:49

ThePyGuy · Accepted Answer · 2021-10-28 04:30:48Z

As I have already mentioned in the comment, the data you have doesn't have string values enclosed inside quote, for e.g. : in [{'p_id': 59, 'p_name': IPF}, the value IPF is not enclosed by quote, so you can not use any direct method.

Out of several way, the easiest way is to use yaml (pip install pyyaml) package to parse those string values as Python object, then explode and apply pd.Series:

import pandas as pd
import yaml

filePath = 'file.csv'
df = pd.read_csv(filePath, index_col=0)
out = (df['product_id'].apply(lambda x: yaml.load(x, yaml.Loader))
        .explode()
        .apply(pd.Series)
       )

OUTPUT

>>> out
    p_id p_name
id             
1     59    IPF
1     63    RBC
1     47    CSP
2     25    LPP
2     86    CRS
2     47    CSP
3     73    OCC
3     63    RBC
4     63    RBC
4     31    SUT
4     73    OCC
5     63    RBC

Collectives™ on Stack Overflow

How to parse data from column in python pandas

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