to_excel to write data to excel file

Pandas

We will save data to Excel file.

import pandas as pd 
my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30,40,50],
	'ENGLISH':[20,30,40]
	}
my_data = pd.DataFrame(data=my_dict)
my_data.to_excel('D:\my_file.xlsx')

my_file.xlsx is stored in the root of D drive

By default we will have index as left most column. We can remove index by using option index=False

my_data.to_excel('D:\my_file.xlsx',index=False)

Storing Path

We can keep in D drive ( root )

my_data.to_excel('D:\my_file.xlsx')

Inside data directory

my_data.to_excel('D:\data\my_file.xlsx')

Storing different worksheets

Excel has worksheets to store data in different sheets.
When we create a new excel file the data stored in Sheet1. We can create different Sheets and store data in different worksheets.

By using sheet_name we can store in worksheet with specific name sheet_name='my_Sheet_1'

my_data.to_excel('D:\my_file.xlsx',index=False,sheet_name='my_Sheet_1')

This will store our data in my_sheet_1 worksheet of file name my_file.xlsx at root of D drive.

multiple worksheets

We will use one object of ExcelWriter to create multiple worksheets in a excel file.

import pandas as pd 
my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30,40,50],
	'ENGLISH':[20,30,40]
	}
my_data = pd.DataFrame(data=my_dict)
my_data2 = my_data.copy() # copy of my_data

with pd.ExcelWriter('D:my_file.xlsx') as my_excel_obj: #Object created
    my_data.to_excel(my_excel_obj,sheet_name='my_Sheet_1')
    my_data2.to_excel(my_excel_obj,sheet_name='my_Sheet_2')

Above code will create excel file with two worksheets. Here the new file name is my_file.xlsx with two worksheets.

Appending worksheets

We will add two more worksheets to the existing files by opening the file in append mode.

Note that we are using the same my_file.xlsx file created in above code.

We will be using mode='a' and engine='openpyxl' while creating the ExcelWriter object.

import pandas as pd 
my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30,40,50],
	'ENGLISH':[20,30,40]
	}
my_data = pd.DataFrame(data=my_dict)
my_data2 = my_data.copy() # copy of my_data

with pd.ExcelWriter('D:my_file.xlsx',mode='a',engine='openpyxl') as my_excel_obj:
    my_data.to_excel(my_excel_obj,sheet_name='my_Sheet_3')
    my_data2.to_excel(my_excel_obj,sheet_name='my_Sheet_4')

This code will add two more worksheets my_Sheet_3 and my_Sheet_4 to existing workbook my_file.xlsx .

While executing the above code, you may get error saying Append mode is not supported with xlsxwriter. To solve this issue use engine='openpyxl' while creating the ExcelWriter object.

Data from MySQL table

We can read data from MySQL table and then store them in excel file.

import mysql.connector
import pandas as pd 
my_connect = mysql.connector.connect(
      host="localhost",
      user="userid",
      passwd="password",
      database="my_tutorial"
    )
####### end of connection ####
sql="SELECT * FROM student "
my_data = pd.read_sql(sql,my_connect )

my_data.to_excel('D:\my_file.xlsx')

In above code we have first connected to MySQL database and then collected the records of student table by using read_sql() to a DataFrame. Finally we saved them in an excel file using to_excel().

Separator sep

By default sep="," , This is one char length strig used to spearate data in a row.

my_data.to_excel('D:\my_file.csv',sep='#',index=False)

na_rep Blank data

How to handle if data is blank, we can use na_rep='*'

my_data.to_excel('D:\my_file.csv',na_rep='*')

Storing part of the data

We can filter the DataFrame and then save the rows in CSV file. For this we will use our test.csv file as we have more rows.
Now let us store only two columns, class and name

import pandas as pd 
my_data=pd.read_csv('test.csv')
df=my_data.loc[:,['class','name']]
my_data = pd.DataFrame(data=df)
my_data.to_excel('my_file.csv',index=False)

We can use various other filters to manage the data and store in CSV file. You can rad more on filters sections.

Pandas read_csv to_csv read_excel

Numpy arrys Python & MySQL Python- Tutorials