3

I have a dictionary that looks like this: 

{'4F703:00013:02038': {'100.000, Oryza rufipogon x Oryza sativa', '100.000, Oryza rufipogon', '100.000, Oryza sativa', '100.000, Oryza sativa Japonica Group', '100.000, Oryza sativa f. spontanea'}}

And I want to write this dictionary into an xlsx file, so I did this code: 

workbook=xlsxwriter.Workbook('demo.xlsx') worksheet=workbook.add_worksheet() row=0 col=0 for key in new_dict.keys(): row += 1 worksheet.write(row,col, key) for item in new_dict[key]: print(item,row,col+1) worksheet.write(row,col + 1,item) col += 1 col = 0 workbook.close()

However I get this output:

enter image description here

And I would like to have it this way:

enter image description here

what am I doing wrong?

Improve this question
3
  • The values in the pictures and the given dictionary doesn't match. Is this a mistake or are you leaving out some bit of code? Commented Dec 6, 2019 at 17:56
  • there is no much difference in expected and actual . except the precision of middle column Commented Dec 6, 2019 at 18:09
  • you are getting in that way because set element is '100.000, Oryza rufipogon x Oryza sativa' . Notice that comma (,) is present inside single quotes. Commented Dec 6, 2019 at 18:12

4 Answers 4

Reset to default
2

Using pandas.ExcelWriter greatly simplifies this task:

import pandas as pd data = {'4F703:00013:02038': {'100.000, Oryza sativa', '100.000, Oryza rufipogon x Oryza sativa', '100.000, Oryza rufipogon', '100.000, Oryza sativa f. spontanea', '100.000, Oryza sativa Japonica Group'}} new_data = [[a, [i.split(', ') for i in b]] for a, b in data.items()] with pd.ExcelWriter('spreadsheet.xlsx') as writer: df = pd.DataFrame([i for a, [c, *d] in new_data for i in [[a, *c], *[['', *k] for k in d]]]) df.to_excel(writer, sheet_name='sheet1', startrow=0, index=False) writer.save()

enter image description here

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

2 Comments

Sorry, but with your code I don't get the values separated, they stay together...
@Sofia Is your data delimeter always , ?
1 
import xlsxwriter workbook=xlsxwriter.Workbook('demo.xlsx') worksheet=workbook.add_worksheet() row=0 col=0 new_dict ={'4F703:00013:02038': {'100.000 , Oryza rufipogon x Oryza sativa', '100.000 , Oryza rufipogon', '100.000 , Oryza sativa', '100.000 , Oryza sativa Japonica Group', '100.000 , Oryza sativa f. spontanea'}} for key in new_dict.keys(): row += 1 worksheet.write(row,col,key) for item in new_dict[key]: print(item, row, col+1) item = item.split(',') worksheet.write(row, col + 1,item[0]) col += 1 worksheet.write(row,col + 1,item[1]) row += 1 col = 0 workbook.close()

I think you are considering '100.000, Oryza rufipogon x Oryza sativa' as multiple values but its a single value and would be filled in a single column.Above solution may clear some things.

Improve this answer

Comments

1

An item is a string that contains a number and a text separated by a comma. That string is added to the column in the worksheet.

In order to have the number and the text separated another column is required.

You could do an item.split(',') and put the first part in the second column and the second part in a third one.

num, text = item.split(',') worksheet.write(row,col + 1, num) worksheet.write(row,col + 2, text)
Improve this answer

Comments

0

there is no wrong in you code.

Notice that '100.000, Oryza rufipogon x Oryza sativa' is one value. If you want to be as separate columns in excel. get the string and split it by , and then write it to the sheet.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.