Array of values depending on column of Pandas Dataframe

Question

I have the following Pandas Dataframe:

Id         Values     Hour  
ES0024     4000       01:00
ES0024     5000       03:00
ES0024     6000       04:00
ES0024     7000       05:00
ES0024     8000       06:00
ES0024     9000       08:00

Want I want to do is to construct a json doc with the following output:

{"Id": "ES0024", "Values": [4000, None, 5000, 6000, 7000, 8000, None, 9000]}

So that if the corresponding position of the hour in the array of values is empty, a None value is entered.

Thank you very much!

Does this answer your question? [pandas groupby to nested json](https://stackoverflow.com/questions/24374062/pandas-groupby-to-nested-json) — pissall, Nov 15 '19 at 07:49
you could simply fill in the dataframe so it contains the values you need. you can resample using something like df.resample().fillna() — Chrisvdberge, Nov 15 '19 at 07:55

score 3 · Accepted Answer · answered Nov 15 '19 at 10:39

First try converting your DataFrame into required format and then convert it into dict. Maybe this will help you somewhere.

Get list of Hour with this first :

hour = list(pd.date_range("01:00", "08:00", freq="H").strftime('%H:%M'))

Then set Hour and index and re-index with new Hour list obtained and finally reset index to get new DataFrame.

new_df = df.set_index("Hour").reindex(hour).reset_index()

new_df["Id"].fillna( method ='ffill', inplace = True)

And then convert it to dict to obtain similar results.

print(new_df.to_dict(orient='list'))

Though this is not the exact format you wanted, I think you should try it on your own after this.This will help you achieve it.

Array of values depending on column of Pandas Dataframe

1 Answers1