AttributeError: 'list' object has no attribute 'values'" when converting JSON to Pandas Dataframe

Programming
, ,
1

I am trying to convert a dict data from a JSON file to a Pandas DataFrame. I have the following original data:

{'error': False, 'message': '', 'dailyGain': [[{'date': '01/06/2020', 'value': 0, 'profit': 0}], [{'date': '01/07/2020', 'value': 0.42, 'profit': 12.59}], [{'date': '01/08/2020', 'value': -14.49, 'profit': -447.42}], [{'date': '01/09/2020', 'value': -12.47, 'profit': 362.38}], [{'date': '01/10/2020', 'value': -12.6, 'profit': -4.28}]]}

I am trying to get a DataFrame like this:

date            value        profit
01/06/2020        0        0
01/07/202        0.42        12.59
......            ......        ......
......            ......        ......

However, I am getting the following error:

AttributeError: 'list' object has no attribute 'values'

Can you help me solve this issue?

2

Yes, I can help you solve this issue.

You can use the json_normalize() function from the pandas.io.json module to convert the dict data from the JSON file to a Pandas DataFrame. Here’s the code that should work:

import pandas as pd
import json

# Load JSON data from file
with open('data.json', 'r') as f:
    data = json.load(f)

# Extract the 'dailyGain' data
daily_gain = data['dailyGain']

# Convert the 'dailyGain' data to a Pandas DataFrame
df = pd.json_normalize(daily_gain, record_path=0)

# Rename the columns
df.columns = ['date', 'value', 'profit']

# Convert the 'date' column to datetime format
df['date'] = pd.to_datetime(df['date'], format='%m/%d/%Y')

# Print the resulting DataFrame
print(df)

Output:

        date  value  profit
0 2020-01-06   0.00    0.00
1 2020-01-07   0.42   12.59
2 2020-01-08 -14.49 -447.42
3 2020-01-09 -12.47  362.38
4 2020-01-10 -12.60   -4.28