0

So I have this code to read an excel file:

import pandas as pd

DataFrame = pd.read_excel("File.xlsx", sheetname=0)
DataFrame.groupby(["X", "Y"]).size()

res = DataFrame.groupby(["X", "Y"]).size()
print res

This code: 

res = DataFrame.groupby(["X", "Y"]).size()

Return how many time the items from X appears in the file for example, if I have the following example:

X     Y 
abc   test
abc   test
a     test

I get 

X    Y
abc test  2
a   test  1

How can I add a title to the 3rd column, so i can sort it, for example I want:

X    Y    Z
abc test  2
a   test  1

and how can I write it to an excel file where each column from the output would be a column in excel?

AMayer
  • 415
  • 5
  • 19

1 Answers1

2

Try either:

res.rename('Z').sort_values().to_excel(...)

Or:

res.rename('Z').to_frame().sort_values(by='Z').to_excel(...)
IanS
  • 15,771
  • 9
  • 60
  • 84
  • OK, it works but it skips a step. The problem is that I want to sort the Z and then write it to xls. – AMayer Jul 14 '17 at 12:55
  • Sorry I missed that part, please see my edit. You don't need the series to be named to sort the values, so you could swap `rename` and `sort_values`... – IanS Jul 14 '17 at 12:59
  • Is it also possible to append it to an existing Excel file? – AMayer Jul 14 '17 at 19:10
  • Try [this](https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas)? – IanS Jul 17 '17 at 07:32