Coding Problem - Extracting values from a column and forming a new dataframe [edited]

Question

The problem statement requires extracting certain weather parameters for every hour in a particular date as denoted in the dataframe. The column 'hourly' consists of 24 lists in each entry, denoting weather parameters for each hour on that particular date. Is there a way that I can extract the parameter 'CloudCover' for all those 24 hours and form a new dataframe whose columns denote the hours in the day and the corresponding CloudCover value for a single date ?

Edit: Based on the suggestions given below, I tinkered my code and a new problem has come up. While the code suggested by @jahKnows works perfectly fine, It gives only the first value of any parameter in the hourly column entries. For eg. in the date 01-01-2016, the corresponding hourly column entry has 24 values of cloudCover. But the code suggested below gives only the first CloudCover value and moves on to the next date leaving out the other 23 CloudCover values in that particular date. Can you suggest me changes to tackle this problem ? I have attached the updated notebook link and the original dataset link below.

Notebook link(edited): https://anaconda.org/vishwa989796/okayishtrial/notebook

data set link: https://drive.google.com/drive/folders/1wMNOZapHib9AyYFaLdA1jdEdx9DdLnXx

Answer 1

I wrote you two small functions which you can use to unpack a dataframe.

The original dataframe looks like this

import pandas as pd
df = pd.DataFrame(data = temp['data']['weather'])
df.head()

The first one is simple, it takes a dataframe and the name of a column, and it will extract the column into a new dataframe.

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_astronomy = extract_col_as_df(df, 'astronomy')
df_astronomy.head()

df_astronomy = extract_col_as_df(df, 'hourly')
df_astronomy.head()

The fact that the extracted tables sometimes have a useless list of dictionaries with a single value bothered me. Of course you can use the same function as above to extract that column as another dataframe, but then you would have a dataframe with a single column, why not just unpack it in place. So I wrote another function which cleans up the extracted dataframe by unpacking a list of dictionaries with a single value.

A better version

This version also takes a dataframe and a column name to extract a dataframe from it. But from the extracted dataframe if a column contains a list of dictionaries with only a single value it unpacks it.

def extract_col_as_df(df, column_name):
    data = [datum[0] for datum in df[column_name]]

    data = []
    for datum in df[column_name]:
        record = {}
        for i in datum[0]:
            # If the entry in the record is comprised of a list with a 
            # dictionary containing a single value then unpack it
            if type(datum[0][i]) is list:
                if len(datum[0][i]) == 1:
                    key_name = list(datum[0][i][0].keys())[0]
                    record.update({i: datum[0][i][0][key_name]})
                else:
                    record.update({i: datum[0][i]})
            else:
                record.update({i: datum[0][i]})
        data.append(record)

    df = pd.DataFrame(data = data)
    return df

df_astronomy = extract_col_as_df(df, 'hourly')
df_astronomy.head()