Read a multi nested Dictionary value into column in Pandas

user_10137468 0
Viewed 247

I have some hardware that outputs data as nested dictionaries.

data_out = {'unimportant_dict': [{'dateTime': '2019-12-01', 'value': '183'}], 'secondary_dict': {'sub_dict': [{'time': '00:00:00', 'value': 0}, {'time': '00:01:00', 'value': 0}, {'time': '00:02:00', 'value': 0}], 'datasetInterval': 1}}

I am interested in the 'value' data in 'sub_dict' which I want to read into a Pandas df, but I have multiple files to collate into a single dataframe.

I can read one file in which works fine 

tempdict = data_out['secondary_dict']
testdf = pd.DataFrame(tempdict['sub_dict'])

Which gives

       time        value
     0  00:00:00      0
     1  00:01:00      0
     2  00:02:00      0
     3  00:03:00      0
     4  00:04:00      0

Now I want to add a second file but ONLY the value data (because the timestamps will always be same). Assuming a second file with the same structure as above my approach is wrong.

tempdict2 = data_out2['secondary_dict']['value']   
testdf['new data'] = tempdict2
TypeError: list indices must be integers or slices, not str

I think this is because it is a long list of dicts (I assume its a list to preserve the time order). I thought I could just add it and then delete the additional time column but it adds the whole dict as a single column

          time  value                              fle2
   0  00:00:00      0  {'time': '00:00:00', 'value': 0}
   1  00:01:00      0  {'time': '00:01:00', 'value': 0}
   2  00:02:00      0  {'time': '00:02:00', 'value': 0}
   3  00:03:00      0  {'time': '00:03:00', 'value': 0}
   4  00:04:00      0  {'time': '00:04:00', 'value': 0}

As Im writing, I'm wondering if it would be easier to extract the 'value' values to a list and then add that to the df?

I don't think this is a duplicate of Capture python value from multi-nested dictionary since that is still a single dict as a list, whereas this is lots of dicts in a list.

any help appreciated.

pandas python-3.x dictionary python
1 Answers

First, read all your data into a list named all_nested_dicts. I am assuming you have same number of records in all your files and the time values are same for all the files. Without this two assumptions in place, the solution below is not going to work as I simply merge the dataframes by concatenating them.

dfs = []
for i, nested_dict in enumerate(all_nested_dicts):
    df = pd.DataFrame(nested_dict['secondary_dict']['sub_dict']).rename(columns = {'value': f'value_{i}'})
    df.sort_values("time", inplace = True)
    # drop the `time` column from all subsequent files after sorting
    if i >= 1:
        df.drop("time", axis = 1, inplace = True)
    dfs.append(df)

final_df = pd.concat(dfs, axis = 1)
user_51506900
Related