Importing and Exporting Data Between CSV Files, MySQL, and Pandas
C
h
a
p
t
e
r
-
4
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
we have seen Python Pandas library and learn about two data structures of Pandas library i.e
series and
D
ataframes, that are capable of storing any types of data in 2D tabular format.
we can also use spreadsheet files (
Ms excel)
or
database tables (Mysql/Access) to store the
data in 2D table formats. Dataframes can also hold data in similar way, you can transfer data
from Data frame to Such data files (or tables) or from files in to a data frame.
T
w
o
t
y
p
e
s
o
f
t
r
a
n
s
f
e
r
o
f
f
i
l
e
s
w
e
h
a
v
e
t
o
l
e
a
r
n
i
s
:
1.
T
o
t
r
a
n
s
f
e
r
a
d
a
t
a
t
o
/
f
r
o
m
a
.
C
S
V
f
i
l
e
f
r
o
m
/
i
n
t
o
D
a
t
a
f
r
a
m
e
.
2.
T
o
t
r
a
n
s
f
e
r
t
o
/
f
r
o
m
D
a
t
a
b
a
s
e
t
a
b
l
e
f
r
o
m
/
i
n
t
o
D
a
t
a
f
r
a
m
e
.
1
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
A
b
o
u
t
C
S
V
f
i
l
e
:
T
h
e
a
c
r
o
n
y
m
C
S
V
i
s
s
h
o
r
t
f
o
r
m
o
f
c
o
m
m
a
s
e
p
a
r
a
t
e
d
v
a
l
u
e
s
.
T
h
e
c
s
v
f
o
r
m
a
t
r
e
f
e
r
s
t
o
a
t
a
b
u
l
a
r
d
a
t
a
t
h
a
t
h
a
s
b
e
e
n
s
a
v
e
d
a
s
p
l
a
i
n
t
e
x
t
w
h
e
r
e
d
a
t
a
i
s
s
e
p
a
r
a
t
e
d
b
y
c
o
m
m
a
s
.
e.g. consider a table having some data as shown below if you store this tables data in csv
format then the data of the table will be stored in csv format as shown below on the right.
2
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
As you can see that in CSV format
Each row of the table is stored in one row i.e. the number of rows in a csv file are equal to
number of rows in the table( or sheet or database table etc )
The field values of a row are stored together with commas after every field value; but after
the last field’s value in a line /row, no comma is given, just the end of line.
T
h
e
c
s
v
f
o
r
m
a
t
i
s
p
o
p
u
l
a
r
a
s
i
t
o
f
f
e
r
s
f
o
l
l
o
w
i
n
g
a
d
v
a
n
t
a
g
e
s
:
simple compact and indifferent format for data storage.
A common format for data interchange
it can be opened in popular spreadsheet packages like MS Excel , calc etc.
Nearly all spreadsheets and databases support Import / export to csv format
3
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
4
C
r
e
a
t
e
C
S
V
f
i
l
e
:
You can create a csv file yourself by saving data of an MS Excel file in csv format
using Save As command from file menu and selecting Save As type as csv format.
Eg. csv file sample.csv, is shown in notepad as well as in MS Excel.
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
5
R
e
a
d
i
n
g
f
r
o
m
a
C
S
V
f
i
l
e
t
o
D
a
t
a
f
r
a
m
e
:
You can use read_csv()function to read data from a CSV file in your dataframe.
<DF> =pandas.read_csv(filepath)
O
U
T
P
U
T
S
a
v
e
d
p
a
t
h
i
s
E
:
\
s
a
m
p
l
e
.
c
s
v
Note
: If you get file path error by single slash
then change it to double slashes
i.e. “E:\\sample.csv”
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
6
If we don’t want the first row to be considered as column headings and want default
column heading be added .
<DF> =pandas.read_csv(filepath, header=None)
O
U
T
P
U
T
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
7
If we have first row having some default column values, that we don’t want as
columns value than use skiprows argument.
O
U
T
P
U
T
S
a
v
e
d
p
a
t
h
i
s
E
:
\
s
t
u
d
e
n
t
.
c
s
v
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
8
skiprow argument if given as 1 first row is skipped.
O
U
T
P
U
T
S
a
v
e
d
p
a
t
h
i
s
E
:
\
s
t
u
d
e
n
t
.
c
s
v
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
9
<DF> =pandas.read_csv(filepath, names=<sequence containing column
names>,skiprows=<n>)
skiprow argument can also take list of row nos. or a row number to skipped rows
from the beginning.
O
U
T
P
U
T
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
10
G
e
t
D
a
t
a
f
r
a
m
e
i
n
d
e
x
l
a
b
e
l
s
f
r
o
m
C
S
V
:
O
U
T
P
U
T
<DF> =pandas.read_csv(filepath, index_col=<column name>)
i
m
p
o
r
t
i
n
g
/
e
x
p
o
r
t
i
n
g
D
a
t
a
b
e
t
w
e
e
n
C
S
V
f
i
l
e
s
/
M
y
S
Q
L
a
n
d
P
a
n
d
a
s
Made by: Gayatri Ghadiali
11
G
e
t
D
a
t
a
f
r
a
m
e
i
n
d
e
x
l
a
b
e
l
s
f
r
o
m
C
S
V
:
O
U
T
P
U
T
<DF> =pandas.read_csv(filepath, index_col=<column name>)
In this chapter, we explore how to transfer data between CSV files, MySQL databases, and Pandas using Python. We learn about the CSV file format, creating CSV files, and reading data from CSV files into Dataframes. This chapter provides insights into efficient data management techniques using Pandas library.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Chapter- 4 importing/ exporting Data between CSV files /MySQL and Pandas we have seen Python Pandas library and learn about two data structures of Pandas library i.e series and Dataframes, that are capable of storing any types of data in 2D tabular format. we can also use spreadsheet files (Ms excel) or database tables (Mysql/Access) to store the data in 2D table formats. Dataframes can also hold data in similar way, you can transfer data from Data frame to Such data files (or tables) or from files in to a data frame. Two types of transfer of files we have to learn is: 1. To transfer a data to/from a .CSV file from / in to Dataframe. 2. To transfer to/from Database table from/ in to Dataframe. 1 Made by: Gayatri Ghadiali
importing/ exporting Data between CSV files /MySQL and Pandas About CSV file: The acronym CSV is short form of comma separated values. The csv format refers to a tabular data that has been saved as plain text where data is separated by commas. e.g. consider a table having some data as shown below if you store this tables data in csv format then the data of the table will be stored in csv format as shown below on the right. Name Email Phone number Address Bob Smith bob@example.com 123-456-7809 123 Fake street Mike Jones mike@example.com 098-765-4321 321 Fake avenue Made by: Gayatri Ghadiali 2
importing/ exporting Data between CSV files /MySQL and Pandas As you can see that in CSV format Each row of the table is stored in one row i.e. the number of rows in a csv file are equal to number of rows in the table( or sheet or database table etc ) The field values of a row are stored together with commas after every field value; but after the last field s value in a line /row, no comma is given, just the end of line. The csv format is popular as it offers following advantages: simple compact and indifferent format for data storage. A common format for data interchange it can be opened in popular spreadsheet packages like MS Excel , calc etc. Nearly all spreadsheets and databases support Import / export to csv format Made by: Gayatri Ghadiali 3
importing/ exporting Data between CSV files /MySQL and Pandas Create CSV file: You can create a csv file yourself by saving data of an MS Excel file in csv format using Save As command from file menu and selecting Save As type as csv format. Eg. csv file sample.csv, is shown in notepad as well as in MS Excel. Made by: Gayatri Ghadiali 4
importing/ exporting Data between CSV files /MySQL and Pandas Reading from a CSV file to Dataframe: You can use read_csv()function to read data from a CSV file in your dataframe. <DF> =pandas.read_csv(filepath) OUTPUT Saved path is E:\sample.csv Note: If you get file path error by single slash then change it to double slashes i.e. E:\\sample.csv Made by: Gayatri Ghadiali 5
importing/ exporting Data between CSV files /MySQL and Pandas If we don t want the first row to be considered as column headings and want default column heading be added . <DF> =pandas.read_csv(filepath, header=None) OUTPUT Made by: Gayatri Ghadiali 6
importing/ exporting Data between CSV files /MySQL and Pandas If we have first row having some default column values, that we don t want as columns value than use skiprows argument. OUTPUT Saved path is E:\student.csv Made by: Gayatri Ghadiali 7
importing/ exporting Data between CSV files /MySQL and Pandas skiprow argument if given as 1 first row is skipped. OUTPUT Saved path is E:\student.csv Made by: Gayatri Ghadiali 8
importing/ exporting Data between CSV files /MySQL and Pandas <DF> =pandas.read_csv(filepath, names=<sequence containing column names>,skiprows=<n>) skiprow argument can also take list of row nos. or a row number to skipped rows from the beginning. OUTPUT Made by: Gayatri Ghadiali 9
importing/ exporting Data between CSV files /MySQL and Pandas Get Dataframe index labels from CSV: <DF> =pandas.read_csv(filepath, index_col=<column name>) OUTPUT Made by: Gayatri Ghadiali 10
importing/ exporting Data between CSV files /MySQL and Pandas Get Dataframe index labels from CSV: <DF> =pandas.read_csv(filepath, index_col=<column name>) OUTPUT Made by: Gayatri Ghadiali 11
