Excel¶
pandas also supports reading table data stored in Excel 2003 (and higher) files, either with the ExcelFile class or the pandas.read_excel function. Internally, these tools use the add-on packages xlrd and openpyxl to read XLS and XLSX files respectively. These must be installed separately from pandas with pipenv.
To use ExcelFile, create an instance by passing a path to an xls or xlsx file:
[1]:
import pandas as pd
[2]:
xlsx = pd.ExcelFile("library.xlsx")
You can then display the sheets of the file with:
[3]:
xlsx.sheet_names
[3]:
['books']
[4]:
books = pd.read_excel(xlsx, "books")
books
[4]:
| Titel | Sprache | Autor*innen | Lizenz | Veröffentlichungsdatum | |
|---|---|---|---|---|---|
| 0 | Python basics | en | Veit Schiele | BSD-3-Clause | 2021-10-28 |
| 1 | Jupyter Tutorial | en | Veit Schiele | BSD-3-Clause | 2019-06-27 |
| 2 | Jupyter Tutorial | de | Veit Schiele | BSD-3-Clause | 2020-10-26 |
| 3 | PyViz Tutorial | en | Veit Schiele | BSD-3-Clause | 2020-04-13 |
If you are reading in multiple sheets of a file, it is quicker to create the Excel file, but you can also just pass the file name to pandas.read_excel:
[5]:
pd.read_excel("library.xlsx", "books")
[5]:
| Titel | Sprache | Autor*innen | Lizenz | Veröffentlichungsdatum | |
|---|---|---|---|---|---|
| 0 | Python basics | en | Veit Schiele | BSD-3-Clause | 2021-10-28 |
| 1 | Jupyter Tutorial | en | Veit Schiele | BSD-3-Clause | 2019-06-27 |
| 2 | Jupyter Tutorial | de | Veit Schiele | BSD-3-Clause | 2020-10-26 |
| 3 | PyViz Tutorial | en | Veit Schiele | BSD-3-Clause | 2020-04-13 |
To write pandas data in Excel format, you must first create an ExcelWriter and then write data to it using pandas.DataFrame.to_excel:
[6]:
writer = pd.ExcelWriter("library.xlsx")
books.to_excel(writer, "books")
writer.close()
You can also pass a file path to_excel and thus bypass the ExcelWriter:
[7]:
books.to_excel("library.xlsx")