« Pandas
We will save data to CSV ( comma separated values ) file. This is a format used for storing data which can easily exported to MS Excel or any other database.
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_csv('my_file.csv')
Returns None if path of csv file is given and returns the string in CSV format if path is not given.
my_file.csv is stored in the same directory ( script )
By default we will have index as left most column. We can remove index by using option index=False
my_data.to_csv('my_file.csv',index=False)
Storing Path
We can keep in D drive ( root )
my_data.to_csv('D:\my_file.csv')
Inside data directory
my_data.to_csv('D:\data\my_file.csv')
Separator sep
By default sep="," , This is one char length strig used to spearate data in a row.
my_data.to_csv('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_csv('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_csv('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 from MySQL table to CSV file
Read on how to connect to MySQL database and then collected the records of student table by using read_sql() to a DataFrame. Finally we will save them in an CSV file using to_csv().
import mysql.connector
import pandas as pd
my_connect = mysql.connector.connect(
host="localhost",
user="root",
passwd="test",
database="my_tutorial"
)
####### end of connection ####
sql="SELECT * FROM student "
my_data = pd.read_sql(sql,my_connect )
my_data.to_csv('D:\my_file.csv')
« Data input and output from Pandas DataFrame
« Pandas
read_csv()
read_excel()
to_excel()