converting each column list element to JSON in a pandas dataframe

Question

I've been trying to convert a pandas dataframe column of list elements to json and push it to snowflake as a variant but I'm stuck in 1st step.

I have a pandas dataframe with ID and conversation transcript which looks in this way.

Sample dataframe:

ID   transcript
1     ['Joe([email protected]): Hey', 'Smoe([email protected]): Hey!! How are you doing?', 'Joe([email protected]): I'm doing good']

And, I have multiple rows(conversation transcripts with different ID) with same format

Expected dataframe:

    ID   transcript
    1     {'Joe([email protected]): Hey', 'Smoe([email protected]): Hey!! How are you doing?', 'Joe([email protected]): I'm doing good'}

I tried to convert each individual object to json but list object has no attribute 'to_json'

df['transcript_json'] = df['transcript_json'].apply(lambda x: x.to_json())

I also tried converting the whole column into a json object which gave me a big string object but didn't get me any further where I want to go.

transcript_list = df['transcript'].to_json()

{"0":["Joe([email protected]): Hey", "Smoe([email protected]): Hey!! How are you doing?", "Joe([email protected]): I'm doing good"]}

I know I'm missing something small here. Any ideas on how to do it would be much appreciated.

Apparently, your expected dataframe contains an invalid JSON. If you need multi-values, you need to use an array instead of dict. For instance, the expected JSON would be: ['Joe([email protected]): Hey', 'Smoe([email protected]): Hey!! How are you doing?', 'Joe([email protected]): I'm doing good'] — felipecrp
– felipecrp, Commented May 19, 2021 at 15:42
Ah... I see. That makes sense. I don't have a key for that JSON object — NAB0815
– NAB0815, Commented May 19, 2021 at 16:32

Jonathan Leon · Accepted Answer · 2021-05-19 16:49:41Z

It's not clear what are wanting the end result to be. Your expected from original only change these [] to these {}. If you want a dictionary with usable key:value pairs, here's a bastardized way to change the string to dictionary. The problem is, you lose any elements when the email address (the key) is the same.

data='''
ID   transcript
1   ['Joe([email protected]): Hey', 'Smoe([email protected]): Hey!! How are you doing?', 'Joe([email protected]): I'm doing good']
'''
df = pd.read_csv(io.StringIO(data), sep='   ', engine='python')
df['transcript'] = df['transcript'].apply(lambda x: x.replace(': ', '": "').replace("['", '{"').replace("']", '"}').replace("', '", '", "'))
print(df['transcript'].apply(lambda x: type(x)))
df['transcript'].apply(lambda x: json.loads(x))

Outptut

0    <class 'str'>
Name: transcript, dtype: object

0    {'Joe([email protected])': 'I'm doing good', 'Smoe([email protected])': 'Hey!! How are you doing?'}
Name: transcript, dtype: object

What format do you need that list object to really be in so you don't lose any data? Can it be a list of properly formatted key:value pairs?

ThePyGuy · Accepted Answer · 2021-05-19 18:28:34Z

1

Split each item in the list on : and create dictionary out of each key,value after splitting and then use json.dumps to serialize it to JSON string.

df.transcript.apply(lambda x:{key:value.strip() for key,value in [item.split(':') for item in x]}).apply(json.dumps)

OUTPUT:

'{"Joe([email protected])": "I\'m doing good", "Smoe([email protected])": "Hey!! How are you doing?"}'

edited May 19, 2021 at 18:28

answered May 19, 2021 at 17:54

ThePyGuy

18.6k5 gold badges24 silver badges55 bronze badges

Collectives™ on Stack Overflow

converting each column list element to JSON in a pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related