df.to_dict() only get one row of original dataframe (df)

Question

I have the following dataframe:

Note: date is the index

city    morning afternoon   evening midnight
date                    
2014-05-01  YVR 2.32    4.26    -4.87   6.58
2014-05-01  YYZ 24.78   2.90    -50.55  6.64
2014-05-01  DFW 24.78   2.90    -50.55  6.64
2014-05-01  PDX 2.40    4.06    -4.06   6.54
2014-05-01  SFO 30.35   9.96    64.24   6.66

I try to save this df to a dict by df.to_dict() but I only get one row:

df.to_dict():

{'city': {Timestamp('2014-05-01 00:00:00'): 'SFO'},
 'morning': {Timestamp('2014-05-01 00:00:00'): 9.9600000000000009},
 'afternoon': {Timestamp('2014-05-01 00:00:00'): 6.6600000000000001},
 'evening': {Timestamp('2014-05-01 00:00:00'): 30.350000000000001},
 'midnight': {Timestamp('2014-05-01 00:00:00'): 64.239999999999995}}

Shouldn't the entire dataframe be output in the dict?

Answer 1

The output you're getting is using the index as a key in a dictionary. You have a repeated index, and dictionary keys must be unique, so it's not going to work.

You'll have to choose another output format. I find records useful, as in

In [65]: df.reset_index().to_dict("records")
Out[65]: 
[{'afternoon': 4.26,
  'city': 'YVR',
  'date': '2014-05-01',
  'evening': -4.87,
  'midnight': 6.58,
  'morning': '2.32'},
 {'afternoon': 2.9,
  'city': 'YYZ',
  'date': '2014-05-01',
  'evening': -50.55,
  'midnight': 6.64,
  'morning': '24.78'},
 {'afternoon': 2.9,
  'city': 'DFW',
  'date': '2014-05-01',
  'evening': -50.55,
  'midnight': 6.64,
  'morning': '24.78'},
 {'afternoon': 4.06,
  'city': 'PDX',
  'date': '2014-05-01',
  'evening': -4.06,
  'midnight': 6.54,
  'morning': '2.40'},
 {'afternoon': 9.96,
  'city': 'SFO',
  'date': '2014-05-01',
  'evening': 64.24,
  'midnight': 6.66,
  'morning': '30.35'}]