1

I have the below JSON format, I need to convert this to a dataframe in python. Please let me know, how to go about it.

JSON :

 User Patterns
[{"Jane": [{"Thermostat": 20, "Days": [1, 2], "Hour": 6, "Minute": 43}],  
"John": [{"Thermostat": 18, "Days": [1, 2], "Hour": 0, "Minute": 15}], 
"Jen": [{"Thermostat": 22, "Days": [1, 2], "Hour": 10, "Minute": 1}]}]

I want my dataframe to look like :

User    Thermostat   Days     Hour   Minute
Jane    20           [1,2]    6      43  
John    18           [1,2]    0      15 
Jen     22           [1,2]    10     1
Improve this question
2
  • 1
    You can use pd.read_json(userpatterns) Commented Jan 11, 2017 at 6:40
  • the variable where the JSON is stored is "th_pat". If I do pd.read_jso(th_pat). Its throwing me an error Commented Jan 11, 2017 at 6:49

1 Answer 1

Reset to default
4 
jstr = """[{"Jane": [{"Thermostat": 20, "Days": [1, 2], "Hour": 6, "Minute": 43}],  
"John": [{"Thermostat": 18, "Days": [1, 2], "Hour": 0, "Minute": 15}], 
"Jen": [{"Thermostat": 22, "Days": [1, 2], "Hour": 10, "Minute": 1}]}]"""

pd.DataFrame.from_dict(
    {k: v[0] for k, v in json.loads(jstr)[0].items()}, 'index'
).rename_axis('User').reset_index()

enter image description here

If you want to split out the Days column

df = pd.DataFrame.from_dict(
    {k: v[0] for k, v in json.loads(jstr)[0].items()}, 'index'
).rename_axis('User').reset_index()

df.drop('Days', 1).join(
    pd.DataFrame(df.Days.tolist()).rename(columns='Day{}'.format))

enter image description here

You can functionalize this:

def read_my_json(jstr):
    return pd.DataFrame.from_dict(
        {k: v[0] for k, v in json.loads(jstr)[0].items()}, 'index'
    ).rename_axis('User').reset_index()
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you, but this JSON is stored in a variable called "th_pat". And for each experiement, the values of thermostat, day etc would change. Hence, how do I go about it
Sure, it worked. Thank you. I have a related question, can this be returned inside a function?. If I do so, I'm getting the following error: NotImplementedError: Python Bridge conversion table not implemented for type [<type 'list'>]
I'm facing a minor issue again. For the column "Days", it has two values which is [1,2]. Hence while returning, its throwing an error as its unable to parse that list. How can I modify the code to make each of those values in a separate column, and also, the values might get increased, say [1,2,3] etc. So Is there a generic code, so each of those values, can be treated as a different column
@Anagha no problem. Glad I could help.
How would we iterate through the JSON if each User had multiple records?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.