Sorted entries from dictionary to excel using python Pandas

Question

I am using pandas to write data to an excel file. I want to dump data as it is in one sheet and in second sheet I want it in sorted way. Layers is the key and weights are the values in the dictionary.

for e.g. sheet1 should have the table as below (unsorted) :

and sheet2 should have sorted entries:

I already tried OrderedDict

    df1 = pd.DataFrame.from_dict(dict_weights, orient="index")
    df1.columns = ['weights']

    df2 = pd.DataFrame.from_dict(collections.OrderedDict(dict_weights), orient="index")
    df2.columns = ['weights']
    df1.to_excel(writer, sheet_name='sheet1')
    df2.to_excel(writer, sheet_name='sheet2', startcol=3)

    writer = pd.ExcelWriter(filename, engine='xlsxwriter')
    writer.save()

The problem is it do the sorting but on both sheets. I just want data to be sorted in sheet2 and in sheet1 it should remain unsorted.

Expected Output in :

Sheet1

Layer;  weights
T1_max_pool;    4
activation_9;   1
sum_9;  3
Merge_2;    4
activation_2;   1
T2_max_pool;    4

Sheet2

Layer;  weights
activation_2;   1
activation_9;   1
Merge_2;    4
sum_9;  3
T1_max_pool;    4
T2_max_pool;    4

Any suggestions?? :) Thanks!

Always helps to have copy-able data in the question instead of images. — Zero
– Zero, Commented Feb 7, 2017 at 13:46
Thanks for suggestion! I have updated question with ; separated values — blackbug
– blackbug, Commented Feb 7, 2017 at 13:52
OrderedDict just remembers the order of insertion however it is not sorted (at least not in an intuitive way). You can create the OrderedDict first and then insert items one after another in a sorted manner. I.e. instead of OrderedDict(dict_weights) you can do sorted_dict = OrderedDict(); for key in sorted(dict_weights): sorted_dict[key] = dict_weights[key]. — a_guest
– a_guest, Commented Feb 7, 2017 at 13:57

Zero · Accepted Answer · 2017-02-07 13:57:39Z

2

IIUIC, you need to use sort_values on Layers column.

In [503]: writer = pd.ExcelWriter('file.xlsx')

In [504]: df.to_excel(writer, 'sheet1')

In [505]: df.sort_values(by='Layers', ascending=True).to_excel(writer, 'sheet2')

In [506]: writer.save()

If you don't want index, use index=False while writing to excel.

answered Feb 7, 2017 at 13:57

Zero

77.4k22 gold badges153 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

blackbug Over a year ago

I have update and query on the proposed solution. It does the sorting but output is a bit different than expected : T1_max_pool | T2_max_pool | activation_2 | activation_9 | merge_2 | sum_9. Is it possible it has to do something with Capital and small alphabets?

zipa · Accepted Answer · 2017-02-07 14:01:22Z

1

Change dict into lists and use:

df1 = pd.DataFrame({'weights': list2}, index=list1)
df2 = df1.sort_index()

Dict has its own logic for sorting keys and if you are to print it you would see it is not printed as you expect it.

answered Feb 7, 2017 at 14:01

zipa

28k6 gold badges45 silver badges62 bronze badges

Collectives™ on Stack Overflow

Sorted entries from dictionary to excel using python Pandas

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related