Python: pandas groupby to dictionary based on index

Question

df_devices = df.groupby(['from_time', 'device', 'type'])['power'].mean()

I have a pandas series containing the following content after calculating the mean() of groupby on a column

from_time  device         type   
00:00:00   AC             indoor     1362.214286
                          outdoor    3470.705882
           Computer       indoor      399.000000
                          outdoor     412.400000
           Heater         indoor     2258.375000
                          outdoor    2274.666667
           Lights         indoor     1535.000000
                          outdoor    3475.473684
           Microwave      indoor     1420.000000
                          outdoor    1489.933333
           Refridgerator  indoor      192.388889
                          outdoor     195.076923
           Television     indoor      243.666667
                          outdoor     261.500000
01:00:00   AC             indoor     1493.071429
                          outdoor    3724.352941
           Computer       indoor      416.461538
                          outdoor     413.555556
           Heater         indoor     2143.277778
                          outdoor    2286.461538
           Lights         indoor     1447.950000
                          outdoor    3092.454545
           Microwave      indoor     1536.857143
                          outdoor    1429.294118
           Refridgerator  indoor      207.416667
                          outdoor     234.684211
           Television     indoor      251.000000
                          outdoor     249.904762

I would like to convert this into a dictionary of the following format:

{'00:00:00' : {'AC': {'indoor': 1362.2142857142858, 'outdoor': 3470.705882352941}, 
'Computer': {'indoor': 399.0, 'outdoor': 412.4}, 'Heater': {'indoor': 
2258.375, 'outdoor': 2274.6666666666665}, 'Lights': {'indoor': 1535.0, 
'outdoor': 3475.4736842105262}, 'Microwave': {'indoor': 1420.0, 'outdoor': 
1489.9333333333334}, 'Refridgerator': {'indoor': 192.38888888888889, 
'outdoor': 195.07692307692307}, 'Television': {'indoor': 243.66666666666666, 
'outdoor': 261.5}},
'00:00:01' : {'AC': {'indoor': 1493.0714285714287, 'outdoor': 3724.3529411764707}, 
'Computer': {'indoor': 416.46153846153845, 'outdoor': 413.55555555555554}, 
'Heater': {'indoor': 2143.277777777778, 'outdoor': 2286.4615384615386}, 
'Lights': {'indoor': 1447.95, 'outdoor': 3092.4545454545455}, 'Microwave': 
{'indoor': 1536.857142857143, 'outdoor': 1429.2941176470588}, 
'Refridgerator': {'indoor': 207.41666666666666, 'outdoor': 
234.68421052631578}, 'Television': {'indoor': 251.0, 'outdoor': 
249.9047619047619}}}

I did try the .agg() and .unstack() on the frame but ended up with different formats The .to_dict() is also not giving promising results. It returns a different format

The best I am able to do using the following code

df_devices.unstack(level=2).unstack().to_dict('index')

Does not give the output intended

{'00:00:00': {('indoor', 'AC'): 1362.2142857142858,
('indoor', 'Computer'): 399.0,
('indoor', 'Heater'): 2258.375,
('indoor', 'Lights'): 1535.0,
('indoor', 'Microwave'): 1420.0,
('indoor', 'Refridgerator'): 192.38888888888889,
('indoor', 'Television'): 243.66666666666666,
('outdoor', 'AC'): 3470.705882352941,
('outdoor', 'Computer'): 412.4,
('outdoor', 'Heater'): 2274.6666666666665,
('outdoor', 'Lights'): 3475.4736842105262,
('outdoor', 'Microwave'): 1489.9333333333334,
('outdoor', 'Refridgerator'): 195.07692307692307,
('outdoor', 'Television'): 261.5}

check this one, https://stackoverflow.com/questions/39067831/dataframe-with-multiindex-to-dict — user96564, Jun 22 '18 at 06:31
this doesn't work since the fourth column of values (from the series) are values in series and not index — Sudhanva Narayana, Jun 22 '18 at 06:42

score 0 · Answer 1 · answered Jun 22 '18 at 06:18

I'm assuming that dataframe is comma separated dataframe and is stored with name a.csv. Following is one approach, that you can iterate through each row and form dict from dataframe.

df = pd.read_csv('a.csv')
df.rename(columns={'Unnamed: 3': 'value'}, inplace=True)

from_time = None
device = None
type=None
output = {}
for idx, row in df.iterrows():
    if not pd.isnull(row['from_time']):
        from_time = row['from_time']
    if not pd.isnull(row['device']):
        device = row['device']
    if from_time not in output:
        output[from_time] = {}
    if device not in output[from_time]:
        output[from_time][device] = {}
    output[from_time][device][row['type']] = row['value']

Do let me know if you find something better than this. Thanks. Hope it helps.

I solved it using a different approach. See here [my answer](https://stackoverflow.com/a/50982868/4640237) — Sudhanva Narayana, Jun 22 '18 at 07:32

score 0 · Accepted Answer · edited Jun 22 '18 at 07:46

I was able to solve using the same dict I created

dicto = df_devices.unstack(level=2).unstack().to_dict('index')

>>> dicto

{'00:00:00': {('indoor', 'AC'): 1362.2142857142858,
('indoor', 'Computer'): 399.0,
('indoor', 'Heater'): 2258.375,
('indoor', 'Lights'): 1535.0,
('indoor', 'Microwave'): 1420.0,
('indoor', 'Refridgerator'): 192.38888888888889,
('indoor', 'Television'): 243.66666666666666,
('outdoor', 'AC'): 3470.705882352941,
('outdoor', 'Computer'): 412.4,
('outdoor', 'Heater'): 2274.6666666666665,
('outdoor', 'Lights'): 3475.4736842105262,
('outdoor', 'Microwave'): 1489.9333333333334,
('outdoor', 'Refridgerator'): 195.07692307692307,
('outdoor', 'Television'): 261.5}

Looping through the created dictionary,

df_dict = {}
df_type = {}

for i, j in dicto.items():
    for key, value in j.items():
        df_type[key[1]] = value
        df_dict[key[0]] = df_type.copy()
    dicto[i] = df_dict.copy()

.copy() is to create a copy instead of a reference

>>> dicto
{'00:00:00': {'AC': {'indoor': 1362.2142857142858,
 'outdoor': 3470.705882352941},
 'Computer': {'indoor': 399.0, 'outdoor': 412.4},
 'Heater': {'indoor': 2258.375, 'outdoor': 2274.6666666666665},
 'Lights': {'indoor': 1535.0, 'outdoor': 3475.4736842105262},
 'Microwave': {'indoor': 1420.0, 'outdoor': 1489.9333333333334},
 'Refridgerator': {'indoor': 192.38888888888889,
 'outdoor': 195.07692307692307},
 'Television': {'indoor': 243.66666666666666, 'outdoor': 261.5}}.........

Python: pandas groupby to dictionary based on index

2 Answers2