« 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.
« Data input and output from Pandas DataFrame
« Pandas
read_csv
to_csv
read_excel