1
$\begingroup$

dataframeimageThe 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

$\endgroup$
  • $\begingroup$ 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? $\endgroup$ – user12075 Sep 7 '18 at 1:13
  • $\begingroup$ @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 ? $\endgroup$ – VishwaV Sep 7 '18 at 1:27
  • $\begingroup$ can you post the file somewhere please? $\endgroup$ – JahKnows Sep 7 '18 at 1:33
  • $\begingroup$ To be exact, I'm trying to access the particular parameter ' CloudCover' from the list and not the entire list. Hence the confusion. $\endgroup$ – VishwaV Sep 7 '18 at 1:33
  • $\begingroup$ @jahknows, yeah will post the notebook cloud link here wait. $\endgroup$ – VishwaV Sep 7 '18 at 1:39
1
$\begingroup$

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

$\endgroup$
  • 1
    $\begingroup$ This is pretty useful and should help me out perfectly. Thanks @JahKnows :) $\endgroup$ – VishwaV Sep 7 '18 at 2:39
  • $\begingroup$ Good luck to you! $\endgroup$ – JahKnows Sep 7 '18 at 2:43
  • $\begingroup$ Hello brother @JahKnows, please do look into the problem that has arised now, Thanks in advance. $\endgroup$ – VishwaV Sep 7 '18 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.