panda dataframe to ordered dictionary

Question

There is a post in which a panda dataframe is converted in to a dictionary for further processing.

The code to do this is:

df = pd.read_excel(open('data/file.xlsx', 'rb'), sheetname="Sheet1")
dict = df.set_index('id').T.to_dict('dict')

which yields something like this: {column -> {index -> value}}

Is there a quick way to instead of this {column -> {index -> value}} get this: OrderedDict(column, value) as a return value?

Currently, I am using the dictionary generated from pandas and assign those values in to an Ordered Dictionary, one by one. This is not the optimum way, as the order is scrambled

Example input: An Excel file like this:

Unique_id | column1 | column2 | column3 | column 4
1         | 3       | 4       | 43      | 90
2         | 54      | 6       | 43      | 54

and the output should be an ordered dictionary like this:

{1:[3,4,43,90], 2:[54,6,43,54]}

Answer 1

You can get the dictionary in the desired order by using an OrderedDict with keys from the Unique_id column. The following should serve as an illustration:

from collections import OrderedDict

# Get the unordered dictionary
unordered_dict = df.set_index('Unique_id').T.to_dict('list')

 # Then order it
ordered_dict = OrderedDict((k,unordered_dict.get(k)) for k in df.Unique_id)
# OrderedDict([(1, [3, 4, 43, 90]), (2, [54, 6, 43, 54])])

Thanks!