Reading an Excel file is just as easy as reading any other file you just pass in your file name and directory and sheet name you wish to read

In [1]:
import pandas as pd

I have an Excel file called BSI Values and it's in Sheet1 of my spread sheet I can open it such as below:

In [5]:
df = pd.read_excel("BSI Values.xlsm",sheet_name = "Sheet1")
In [6]:
df.head()
Out[6]:
Current Period Last Sunday of previous Period sales
0 Period 1 2016-03-27 12226
1 Period 2 2016-05-01 10435
2 Period 3 2016-05-29 13280
3 Period 4 2016-06-26 13774
4 Period 5 2016-07-31 15574

Now Let's say I want to sort the Sales value in the spread sheet and save it back to the same file

In [9]:
### I first sort my sales and I am going to use inplace = True so I can save the df as it's sorted inplace
df.sort_values(by="sales",ascending = False,inplace = True)

To save an excel file we need to have an Excel generator or ExcelWriter in Pandas

In [12]:
### Create your excel writer
writer = pd.ExcelWriter("BSI Values.xlsx")

#Now I can save my df using this writer
df.to_excel(writer)

##and now I can close my writer
writer.close()

Bonus: By default Pandas keeps the index values in your table, if you wish to remove this just set any column you want as an index

In [13]:
df.head()
Out[13]:
Current Period Last Sunday of previous Period sales
11 Period 11 2017-01-29 19005
8 Period 9 2016-11-27 18542
9 Christmas 2016-12-04 16231
10 Period 10 2017-01-01 15991
4 Period 5 2016-07-31 15574

I want the current Period to be my index or the first column in my table

In [17]:
df.set_index("Current Period",drop=True, inplace=True)

in the method above I used drop=True , this means you can go ahead and remove the previous index values. I also used inplace=True which is a convenient method, to save inplace, found in a lot of Pandas method.

And now If I save it using the Excel Writer method above my Data should appear just fine.

In [19]:
### Create your excel writer
writer = pd.ExcelWriter("BSI Values.xlsx")

#Now I can save my df using this writer
df.to_excel(writer)

##and now I can close my writer
writer.close()