writing to excel sheet in python

Question

I was trying to write to excel sheet by using panda which I successfully did. I have to questions: First I have tried to sort by column but I couldn't. I used df.sort_index, df.sortlevel both did not get what I want. this my code:

df = DataFrame({'Seq Label':['if >= 1','if >= 2','if >= 3','if >= 4','if >= 5','if >= 6','if >= 7'],
            'TP':[countTP, twocountTP, threecountTP, fourcountTP, fivecountTP, sixcountTP, sevencountTP], 
            'TN':[countTN, twocountTN, threecountTN, fourcountTN, fivecountTN, sixcountTN, sevencountTN], 
            'FP':[countFP, twocountFP, threecountFP, fourcountFP, fivecountFP, sixcountFP, sevencountFP], 
            'FN':[countFN, twocountFN, threecountFN, fourcountFN, fivecountFN, sixcountFN, sevencountFN]})
df.to_excel('Book10.xlsx', sheet_name='sheet1', index=False)

It gives me this output which I don't want:

    FN   FP Seq Label     TN    TP
0  123  125   if >= 1  20296  7671
1  123  125   if >= 2  17142  6274
2  123  125   if >= 3   3810  1307
3    7   11   if >= 4    419   213
4    1    4   if >= 5    127    74
5    0    0   if >= 6      0     0
6    0    0   if >= 7      0     0

I want it sort table as the order I have in my df in the code. I want it sort it as:

Seq Label   TP   TN    FP    FN

Second Question How to write on an existing excel sheet without deleting or writing on other data. I have tried to use different libs. such as

import pandas as pd
import openpyxl
import xlrd

I am sorry if I make it too long. I need your help Thanks

Only one question per post please.

Alexander
– Alexander

2018-01-18 04:21:50 +00:00
Commented Jan 18, 2018 at 4:21 — Alexander
– Alexander, Commented Jan 18, 2018 at 4:21

Anil_M · Accepted Answer · 2018-01-18 04:54:47Z

3

How about rearranging columns within data frame df as needed, and then write to excel. That way you don't have to worry about excel end.
NOTE: Values in columns are fictitious.

>>> df = df[['Seq Label', 'TP', 'TN', 'FP', 'FN']]
>>> df
  Seq Label  TP  TN  FP  FN
0   if >= 1   1   1   1   1
1   if >= 2   2   2   2   2
2   if >= 3   3   3   3   3
3   if >= 4   4   4   4   4
4   if >= 5   5   5   5   5
5   if >= 6   6   6   6   6
6   if >= 7   7   7   7   7
>>> df.to_excel('Book10.xlsx', sheet_name='sheet1', index=False)

Result

answered Jan 18, 2018 at 4:54

Anil_M

11.5k6 gold badges56 silver badges76 bronze badges

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

4 Comments

Anil_M Over a year ago

if my answer solved your problem do accept the answer by clicking up arrow next to answer so that community is aware that issue is resolved. See here for howto meta.stackexchange.com/questions/5234/…

Ali Over a year ago

Do you have an idea about the second question? I appreciate your help

Ali Over a year ago

I did click on the up arrow but won't allow me because my reputation is under 15. Thank you though

Anil_M Over a year ago

Yes, you will need to get last row of existing worksheet and specify next row for writing your data. It becomes a bit complicated. Its advisable to ask one question at a time, else it creates confusion. If you ask another question let me know, I can answer in details. OR search for this keyword "pandas python to_excel append"

Alexander · Accepted Answer · 2018-01-18 05:11:31Z

1

First sort the dataframe, then write it to Excel.

df.sort_values(['Seq Label', 'TP', 'TN', 'FP', 'FN']).to_excel(...)

EDIT

Oh, you just want to rearrange your columns into a desired order. Try:

df[['Seq Label', 'TP', 'TN', 'FP', 'FN']].to_excel(...)

The order of the items in a dictionary is not guaranteed. If you use a dictionary to construct your dataframe and have a target ordering in mind, you can do something like:

desired_order = [['Seq Label', 'TP', 'TN', 'FP', 'FN']]
df_order = [k for k in df if k in desired_order] + [k for k in df if k not in desired_order]
df = df[df_order]

This will sort the dataframe in your desired order if the keys are present in the dataframe. Any column not in the desired order would then be appended to the right hand side of columns.

edited Jan 18, 2018 at 5:11

answered Jan 18, 2018 at 4:20

Alexander

110k32 gold badges212 silver badges208 bronze badges

3 Comments

Ali Over a year ago

it didn't work with me. As you can see in my dataframe I used ~{ }~ dict. I couldn't get it the way I ordered it.

Alexander Over a year ago

What do you mean "didn't work". What was the error?

Ali Over a year ago

it gives the same first table I post it which I don't want. I need the same order I put in the dataframe. I need first Col to be '' Seq Label", Sec Col "TP",... etc

griez007 · Accepted Answer · 2018-01-18 05:57:52Z

1

from xlutils.copy import copy  # http://pypi.python.org/pypi/xlutils
from xlrd import open_workbook # http://pypi.python.org/pypi/xlrd
from xlwt import easyxf # http://pypi.python.org/pypi/xlwt

rb = open_workbook('file.xlrd',formatting_info=True)
r_sheet = rb.sheet_by_index(0) # read only copy to introspect the file
wb = copy(rb) # a writable copy (I can't read values out of this, only write to it)
w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy
w_sheet.write(row, col, 'Value') 
wb.save(file_path)

here you can give row and column number or use for loop for writing continuous data cell. I used this. it's work perfect for me.

answered Jan 18, 2018 at 5:57

griez007

4391 gold badge5 silver badges11 bronze badges

Collectives™ on Stack Overflow

writing to excel sheet in python

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related