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

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.

• What have you tried on this problem? Is there any particular reason that it can't be done be iterating over the rows and iterating over the list in the hourly column? – user12075 Sep 7 '18 at 1:13
• @user12075 I tried accessing values individually but didn't know how to get all those values at a time in a new dataframe. Can you please tell me how to iter over the entire rows and the hourly column like you suggested ? – VishwaV Sep 7 '18 at 1:27
• can you post the file somewhere please? – JahKnows Sep 7 '18 at 1:33
• To be exact, I'm trying to access the particular parameter ' CloudCover' from the list and not the entire list. Hence the confusion. – VishwaV Sep 7 '18 at 1:33
• @jahknows, yeah will post the notebook cloud link here wait. – VishwaV Sep 7 '18 at 1:39

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'])


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 = extract_col_as_df(df, 'hourly')


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')