How to write data in Excel by Pandas to_excel method
- Pandas is a Python library for data analysis, cleaning of data, and exploring and manipulating data.
- It has methods to read Excel files with a number of useful parameters.
- Similarly, it has to_excel method that can be used to write data in an Excel file.
- Pandas has data frames which is a two-dimensional data structure. Data Frames (DF) can be considered as a table, a 2-D array, or a spreadsheet.
- Once you have the data frame, it can be stored as Excel file (.xlsx).
- In this tutorial, we will show you examples of creating Data frames based on Lists in Python.
- By using data frames, we will use it to create Excel files with various options by using to_excel method parameters.
An example of List to Excel by Pandas to_excel method
For this example, we created a list of employees.
Our Excel file should contain three headers in the first row
- Employee ID
- Employee Name
- Employee Salary
We created a data frame by using a list and there we specified these headers – that act as the first row in the Excel sheet.
Finally, we used to_excel method to write data into an Excel Workbook.
Python program:
import pandas as pd #Creating a list containing emoloyees data employee_list = [ [1, "Mike", 5000], [2, "Michelle", 4500], [3, "Ben", 6000], [4, "Shabee", 3500], [5, "Mina", 3000] ] df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary']) #Save list data to an Excel file df_emp.to_excel("employees.xlsx")
Result:
In the above result, notice these things:
- If employee.xlsx does not exist, to_excel will create a new file with that name.
- It also added the first column with numbers starting at 0 index.
- By default, data is stored in “Sheet1”. You may change this name – we will show it in an example.
How to exclude index column in the Excel file?
If you do not want to have an index column in the created Excel workbook, use the index parameter in the to_excel method of the data frame.
By default:
By assigning it a False value, it will omit the index column. See the program and output below:
import pandas as pd #Creating a list containing emoloyees data employee_list = [ [1, "Mike", 5000], [2, "Michelle", 4500], [3, "Ben", 6000], [4, "Shabee", 3500], [5, "Mina", 3000] ] df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary']) #To Excel file without index column df_emp.to_excel("employees.xlsx", index=False)
Result:
You can see that the above Excel sheet has no index column.
How to specify the sheet name in the Excel file?
Rather than using the default “Sheet1”, you may specify your own sheet name.
For that, use the sheet_name parameter in the to_excel method. The default is:
Let us give it the name “Employee Data”.
The code:
import pandas as pd #Creating a list containing emoloyees data employee_list = [ [1, "Mike", 5000], [2, "Michelle", 4500], [3, "Ben", 6000], [4, "Shabee", 3500], [5, "Mina", 3000] ] df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary']) #To Excel file with custom sheet name and without index column df_emp.to_excel("employees.xlsx", index=False, sheet_name = "Employee Data")
Output:
Create Excel file without headers
If you want to omit the header row as creating a new Excel file then use the header=False parameter as saving the file by to_excel method.
See an example below:
import pandas as pd #Creating a list containing emoloyees data employee_list = [ [1, "Mike", 5000], [2, "Michelle", 4500], [3, "Ben", 6000], [4, "Shabee", 3500], [5, "Mina", 3000] ] df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary']) #To Excel file with custom sheet name and without index column df_emp.to_excel("employees.xlsx", index=False, sheet_name = "Employee Data", header=False)
Output:
Setting the starting row in Excel sheet
You may also specify which row to start writing data – at 0 based index.
For that, use the startrow parameter. Its default value is:
For this example, we will set its value to 7 and see the result:
import pandas as pd #Creating a list containing emoloyees data employee_list = [ [1, "Mike", 5000], [2, "Michelle", 4500], [3, "Ben", 6000], [4, "Shabee", 3500], [5, "Mina", 3000] ] df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary']) #Start writing data from 8th row df_emp.to_excel("employees.xlsx", index=False, sheet_name = "Employee Data", startrow=7)
Result:
Specifying column number example
Similarly, starting column can also be set by using startcol parameter. Its default is:
We will set startcol=4 for our example below:
import pandas as pd #Creating a list containing emoloyees data employee_list = [ [1, "Mike", 5000], [2, "Michelle", 4500], [3, "Ben", 6000], [4, "Shabee", 3500], [5, "Mina", 3000] ] df_emp = pd.DataFrame (employee_list, columns = ['Employee ID', 'Employee Name', 'Salary']) #Start writing data 5th Column adn 4th row in Excel file df_emp.to_excel("employees.xlsx", index=False, sheet_name = "Employee Data", startrow=3, startcol=4)
Output: