CODEWITHSUNDEEP
pythonjson
Learner
Learner

Reputation: 393

Extract part of a json keys value and combine

I have this json dataset. From this dataset i only want "column_names" keys and its values and "data" keys and its values.Each values of column_names corresponds to values of data. How do i combine only these two keys in python for analysis

{"dataset":{"id":42635350,"dataset_code":"MSFT","column_names":
["Date","Open","High","Low","Close","Volume","Dividend","Split",
 "Adj_Open","Adj_High","Adj_Low","Adj_Close","Adj_Volume"],
"frequency":"daily","type":"Time Series",
"data":[["2017-12-28",85.9,85.93,85.55,85.72,10594344.0,0.0,1.0,83.1976157998082,
83.22667201021558,82.85862667838872,83.0232785373639,10594344.0],
["2017-12-27",85.65,85.98,85.215,85.71,14678025.0,0.0,1.0,82.95548071308001,
83.27509902756123,82.53416566217294,83.01359313389476,14678025.0]

for cnames in data['dataset']['column_names']:
print(cnames)

for cdata in data['dataset']['data']:
print(cdata)

For loop gives me column names and data values i want but i am not sure how to combine it and make it as a python data frame for analysis.

Ref:The above piece of code is from quandal website

Upvotes: 0

Views: 96

Answers (3)

Aditya Agrawal
Aditya Agrawal

Reputation: 445

The following snippet should work for you

import pandas as pd
df = pd.DataFrame(data['dataset']['data'],columns=data['dataset']['column_names'])

Check the following link to learn more https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html

Upvotes: 0

Vishnudev Krishnadas
Vishnudev Krishnadas

Reputation: 10960

cols = data['dataset']['column_names']
data = data['dataset']['data']

It's quite simple

labeled_data = [dict(zip(cols, d)) for d in data]

Upvotes: 0

ludovjb
ludovjb

Reputation: 66

data = {
  "dataset": {
      "id":42635350,"dataset_code":"MSFT",
      "column_names": ["Date","Open","High","Low","Close","Volume","Dividend","Split","Adj_Open","Adj_High","Adj_Low","Adj_Close","Adj_Volume"],
      "frequency":"daily",
      "type":"Time Series",
      "data":[
          ["2017-12-28",85.9,85.93,85.55,85.72,10594344.0,0.0,1.0,83.1976157998082, 83.22667201021558,82.85862667838872,83.0232785373639,10594344.0], 
          ["2017-12-27",85.65,85.98,85.215,85.71,14678025.0,0.0,1.0,82.95548071308001,83.27509902756123,82.53416566217294,83.01359313389476,14678025.0]
      ]
  }
}

Should the following code do what you want ?

import pandas as pd
df = pd.DataFrame(data, columns = data['dataset']['column_names'])
for i, data_row in enumerate(data['dataset']['data']):
    df.loc[i] = data_row

Upvotes: 1

Related Questions