Convert JSON file into proper format using python pandas

Question

I want to convert JSON file into proper format. I have a JSON file as given below:

{
    "fruit": "Apple",
    "size": "Large",
    "color": "Red",
    "details":"|seedless:true|,|condition:New|"

},
{
    "fruit": "Almond",
    "size": "small",
    "color": "brown",
    "details":"|Type:dry|,|seedless:true|,|condition:New|"

}

You can see the data in the details can vary.

I want to change it into :

{
    "fruit": "Apple",
    "size": "Large",
    "color": "Red",
    "seedless":"true",
    "condition":"New",

},
{
    "fruit": "Almond",
    "size": "small",
    "color": "brown",
    "Type":"dry",
    "seedless":"true",
    "condition":"New",

}

I have tried doing it in python using pandas as:

import json
import pandas as pd
import re
df = pd.read_json("data.json",lines=True)

#I tried to change the pattern of data in details column as

re1 = re.compile('r/|(.?):(.?)|/')
re2 = re.compile('r\"(.*?)\":\"(.*?)\"')

df.replace({'details' :re1}, {'details' : re2},inplace = True, regex = True);

But that giving output as "objects" in all the rows of details column.

`"details":"|Type:dry,|seedless:true|,|condition:New|"` So, does the position of the comma vary? I cannot figure out a pattern for this piece of string. Btw, your Python code has a typo: re2 does not have a closing quotation mark. — vahdet, Aug 26 '20 at 14:56
If you want json as output why using pandas? do you want output as json or dataframe? — deadshot, Aug 26 '20 at 15:06
@KunalChaudhari, If one of the answers below fixes your issue, you should accept it (click the check mark next to the appropriate answer). That does two things. It lets everyone know your issue has been resolved to your satisfaction, and it gives the person that helps you credit for the assist. [See here](http://meta.stackexchange.com/a/5235) for a full explanation. — sushanth, Aug 26 '20 at 16:14

score 0 · Answer 1 · answered Aug 26 '20 at 15:08

0

Try this,

for d in data:
    details = d.pop('details')
    d.update(dict(x.split(":") for x in details.split("|") if ":" in x))

print(data)

[{'color': 'Red',
  'condition': 'New',
  'fruit': 'Apple',
  'seedless': 'true',
  'size': 'Large'},
 {'Type': 'dry',
  'color': 'brown',
  'condition': 'New',
  'fruit': 'Almond',
  'seedless': 'true',
  'size': 'small'}]

answered Aug 26 '20 at 15:08

sushanth

8,275
3
17
28

can you tell, how you loaded the data? using json.load() or anything else. – Kunal Chaudhari Aug 26 '20 at 15:28
loading into a dataframe ? – sushanth Aug 26 '20 at 15:34
can you tell how you loaded the json file into data – Kunal Chaudhari Aug 26 '20 at 15:51
Here ref this, https://stackoverflow.com/a/20199213/4985099 – sushanth Aug 26 '20 at 15:54
i am getting error as 'str' object has no attribute 'pop' – Kunal Chaudhari Aug 27 '20 at 07:34

score 0 · Answer 2 · answered Aug 26 '20 at 15:24

You can convert the (list of) dictionaries to a pandas data frame.

import pandas as pd

# data is a list of dictionaries
data = [{
    "fruit": "Apple",
    "size": "Large",
    "color": "Red",
    "details":"|seedless:true|,|condition:New|"

},
{
    "fruit": "Almond",
    "size": "small",
    "color": "brown",
    "details":"|Type:dry,|seedless:true|,|condition:New|"

}]

# convert to data frame
df = pd.DataFrame(data)

# remove '|' from details and convert to list
df['details'] = df['details'].str.replace(r'\|', '').str.split(',')

# explode list => one row for each element
df = df.explode('details')

# split details into name/value pair
df[['name', 'value']] = df['details'].str.split(':').apply(lambda x: pd.Series(x))

# drop details column
df = df.drop(columns='details')

print(df)

    fruit   size  color       name value
0   Apple  Large    Red   seedless  true
0   Apple  Large    Red  condition   New
1  Almond  small  brown       Type   dry
1  Almond  small  brown   seedless  true
1  Almond  small  brown  condition   New

Convert JSON file into proper format using python pandas

2 Answers2