Question 232380 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Convert JSON into a Pandas Dataframe

I have a below JSON object that is retrieved by a query

r = {'metaData': [{'columnAliases': ['ltp', 'mcap', 'wsma30uptrend','15 day contraction'],
     'availableLimit': 477,
     'maxRows': 1,
     'isTrend': True,
     'limit': 2,
     'groups': ['symbol'],
     'tradeTimes': [1692210600000],
     'lastUpdateTime': 1692266340000}],
     'groups': ['BALRAMCHIN', 'GHCL'],
     'time': 138,
     'groupData': [{'name': 'BALRAMCHIN',
                    'results': [{'ltp': [394.6]}, {'mcap': [7938.8328]}, {'wsma30uptrend': [1]}, {'15 day contraction': [9.478]}]},
                   {'name': 'GHCL',
                    'results': [{'ltp': [516.1]}, {'mcap': [5048.8412]}, {'wsma30uptrend': [1]}, {'15 day contraction': [9.5524]}]}]}

I am having very tough time converting this JSON into a Pandas Dataframe that looks like below. groupData is the block from above JSON that need to be converted as below.

Expected Output:

name        ltp         mcap        wsma30uptrend   15 day contraction
BALRAMCHIN  394.6       7938.83     1               9.478
GHCL        516.1       5048.84     1               9.5524

My solution is as below which seems like a very crude way of doing this.

df = pd.DataFrame(r['groupData'])
df = pd.concat([df.drop(['results'], axis=1), df['results'].apply(pd.Series)], axis=1)
for i in range(0, 4):
    df = pd.concat([df.drop([i], axis=1), df[i].apply(pd.Series)], axis=1)
df = df.explode('ltp')
df = df.explode('mcap')
df = df.explode('wsma30uptrend')
df = df.explode('15 day contraction')

Can there be a better and simpler solution than this? Thanks in advance.

Tags:

3

Answers


  1. 0

    I made a df by directly iterating (single loop) through the original JSON object and forming a dictionary that matches the desired DataFrame structure where I removed the explode, concat, and drop, I know they can sometimes be computationally expensive and increase your code complexity.

    import pandas as pd

r = {
    'groupData': [
        {'name': 'BALRAMCHIN',
         'results': [{'ltp': [394.6]}, {'mcap': [7938.8328]}, {'wsma30uptrend': [1]}, {'15 day contraction': [9.478]}]},
        {'name': 'GHCL',
         'results': [{'ltp': [516.1]}, {'mcap': [5048.8412]}, {'wsma30uptrend': [1]}, {'15 day contraction': [9.5524]}]}
    ]
}

data = []
for group in r['groupData']:
    row = {'name': group['name']}
    for result in group['results']:
        for key, value in result.items():
            row[key] = value[0]
    data.append(row)

df = pd.DataFrame(data)
df.columns = ['name', 'ltp', 'mcap', 'wsma30uptrend', '15 day contraction']
print(df)

    enter image description here

    Login or Signup to reply.
  2. 0

    Here is a solution without using the extra pandas functions like explode:

    df = pd.DataFrame()
for xx in r['groupData']: 
    d_ = {'name': xx['name']}
    for x in xx['results']:
        d_.update(x)
    df = pd.concat([df,pd.DataFrame(d_)])

    OUTPUT:

    
{'name': {0: 'GHCL'},
 'ltp': {0: 516.1},
 'mcap': {0: 5048.8412},
 'wsma30uptrend': {0: 1},
 '15 day contraction': {0: 9.5524}}
    Login or Signup to reply.
  3. 0

    Column names are in columnAliases, explode method is not needed.

    import pandas as pd
r = {...}

# Get columns name
columns = r['metaData'][0]['columnAliases']

# Get data
data = []
for group_data in r['groupData']:
    # Get name column
    row_data = [group_data['name']]
    for result in group_data['results']:
        value = list(result.values())[0][0]
        row_data.append(value)
    data.append(row_data)

# Load df
df = pd.DataFrame(data, columns=['name'] + columns)
print(df)

    Output

             name    ltp       mcap  wsma30uptrend  15 day contraction                            
0  BALRAMCHIN  394.6  7938.8328              1              9.4780                            
1        GHCL  516.1  5048.8412              1              9.5524
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search