To create an Excel file with pandas in Python we will do some imports. Pandas is a versatile open source library with many data analysis tools that among other things allows you to create Excel files.
If you are working from Linux Ubuntu you must execute the following command in the terminal to download the libraries and install Pandas.
sudo apt install python3-pandas
Now the code:
import pandas as pd
from pandas import ExcelWriter
# geekole.com
df = pd.DataFrame({'Id': [1, 3, 2, 4],
'Name': ['Sophia', 'James', 'Oliva', 'Antony'],
'Surname': ['Whatson', 'Hopkins', 'Pattinson', 'Smith']})
df = df[['Id', 'Name', 'Surname']]
writer = ExcelWriter('/path/example.xlsx')
df.to_excel(writer, 'Data sheet', index=False)
writer.save()
This example is very simple. What we’ll do first is import pd and ExcelWriter.
In this shortcode snippet, ‘Id’, ‘Name’ and ‘Surname’ are the names of the columns in our spreadsheet, each followed by its list of data. With this structure we create our DataFrame.
df = pd.DataFrame({'Id': [1, 3, 2, 4],
'Name': ['Sophia', 'James', 'Oliva', 'Antony'],
'Surname': ['Whatson', 'Hopkins', 'Pattinson', 'Smith']})
The next line of code allows us to get that same data structure, but with the columns in a specific order. If we don’t do this the document will be created sorting the columns alphabetically. You can try to create the excel file without doing it like this to see the result.
df = df[['Id', 'Name', 'Surname']]
Finally we create the file:
writer = ExcelWriter('/path/example.xlsx')
df.to_excel(writer, 'Data sheet', index=False)
writer.save()
The index=False argument prevents an extra column from being added with the enumeration of each row.
The resulting file should be something like this:
And that’s it, to create an Excel file with pandas in Python is easy and requires very little code, we hope this little example will help you get started.
See more documentation: https://pandas.pydata.org/