Value extraction from a python dataframe [ problem statement specific ]

Question

This is an update on one of the older posts of a friend of mine (That post stopped getting responses as I got a partial solution to my problem) and I'm reworking that problem statement. Link to that post: Coding Problem - Extracting values from a column and forming a new dataframe [edited]

While extracting the weather parameters from the 'hourly' column entries, It only extracts the first value of a particular weather parameter. Each entry in the 'hourly' column is a list of weather parameters. Each parameter has 24 different values denoting the weather parameter value for 24 hours in a particular day. So, the code I used gives me 31 different entries in the new data frame while it should have been 31*24 entries for the month of January. Can you guys please help me out ?

Notebook link (edited) is provided here.

data set link is presented here.

[code]

import json
from pprint import pprint 
import pandas as pd

with open(r'C:\Users\Murali\Desktop\Downloads\2016-1.json') as f:
    s = f.read()
jansixteen = json.loads(s)
#print(jansixteen)

a  =jansixteen['data']['weather']
b = ((pd.DataFrame(a)))
b.head()

def extract_col_as_df(df, column_name):
    data = [datum[0] for datum in df[column_name]]
    df = pd.DataFrame(data = data)
    return df
df_hourly = extract_col_as_df(b, 'hourly')
    print(df_hourly)

df_hourly.drop(['FeelsLikeF', 'HeatIndexF','HeatIndexC','weatherIconUrl','WindGustMiles','weatherDesc','weatherCode'], axis=1)

Answer 1

The problem with your code is that it considers the numerics as column names ie., 0,1,2,3, etc., upto 31 rows and 24 hours column but u must loop through each of the column name value which is stored as dictionary so u need two looping:

1. This loops and creates a new list which stores each and every attribute of the json
2. This adds the attributes to the DataFrame now perform your operation at this dataframe you might get an answer

Google drive shared location of edited ipython notebook