General Info: https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html
For read_csv
: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html?highlight=read_csv#pandas.read_csv
For read_excel
: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html?highlight=read_excel#pandas.read_excel
For to_json
: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_json.html?highlight=to_json
Pandas provides helper functions to read data from various file formats like CSV, EXCEL, JSON, HTML, SQL table, and many more.
# To install this library in Jupyter notebook
import sys
!{sys.executable} -m pip install pandas --quiet
import pandas as pd
pd.__version__ , pd.__path__
('1.3.4', ['/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pandas'])
CSV: A text file in which the values are separated by a comma or a tab character is called a CSV or a TSV file. Each line of the file is a data record and each record consists of one or more fields, separated by a specific character called separator. A CSV/TSV file is typically used to store tabular data (numbers and text), in which each line will have the same number of fields.
The pd.read_csv()
method is used to read a comma-separated file into a DataFrame.
pd.read_csv(fname, delimiter=None, header='infer', skiprows=None , nrows=None , usecols=None, footer='',...)
! cat datasets/classmarks.csv
rollno,gender,group,age,math,english,urdu MS01,female,group B,28.0,72.0,72,74.0 MS02,female,group C,33.0,69.0,90,88.0 MS03,female,group B,21.0,,95,93.0 MS04,male,group A,44.0,47.0,57,44.0 MS05,male,group C,54.0,76.0,78, MS06,female,group B,,71.0,83,78.0 MS07,female,group B,47.0,88.0,95,92.0 MS08,male,group B,33.0,40.0,43,39.0 MS09,male,group D,27.0,64.0,64,67.0 MS10,female,group B,33.0,38.0,60,50.0 MS11,male,group C,46.0,58.0,54,52.0 MS12,male,group D,53.0,40.0,52,43.0 MS13,female,group B,25.0,65.0,81,73.0 MS14,male,group A,26.0,78.0,72,70.0 MS15,female,group A,25.0,50.0,53,58.0 MS16,female,group C,29.0,69.0,75,78.0 MS17,male,group C,22.0,88.0,89,86.0 MS18,female,group B,31.0,18.0,32,28.0 MS19,male,group C,33.0,46.0,42,46.0 MS20,female,group C,36.0,54.0,58, MS21,male,group D,34.0,66.0,69,63.0 MS22,female,group B,32.0,65.0,75,70.0 MS23,male,group D,31.0,44.0,54,53.0 MS24,female,group C,37.0,,73,73.0 MS25,male,group D,33.0,74.0,71,80.0 MS26,male,group A,39.0,73.0,74,72.0 MS27,male,group B,,69.0,54,55.0 MS28,female,group C,47.0,67.0,69,75.0 MS29,male,group C,44.0,70.0,70,65.0 MS30,female,group D,42.0,62.0,70,75.0 MS31,female,group D,41.0,69.0,74,74.0 MS32,female,group B,,63.0,65,61.0 MS33,female,group E,29.0,56.0,72,65.0 MS34,male,group D,34.0,40.0,42,38.0 MS35,male,group E,45.0,97.0,87,82.0 MS36,male,group E,31.0,81.0,81,79.0 MS37,female,group D,28.0,74.0,81, MS38,female,group D,26.0,50.0,64,59.0 MS39,female,group D,36.0,75.0,90,88.0 MS40,male,group B,33.0,57.0,56,57.0 MS41,male,group C,35.0,55.0,61,54.0 MS42,female,group C,22.0,58.0,73,68.0 MS43,female,group B,38.0,53.0,58,65.0 MS44,male,group B,31.0,59.0,65,66.0 MS45,female,group E,28.0,,56,54.0 MS46,male,group B,26.0,65.0,54,57.0 MS47,female,group A,36.0,55.0,65,62.0 MS48,female,group C,30.0,66.0,71,76.0 MS49,female,group D,40.0,57.0,74,76.0 MS50,male,group C,37.0,66.0,78,81.0
df.shape
(50, 7)
#The `read_csv`, by default assumes that the file contains comma separated values,
# and the first row of the file conatins names of columns, which will be taken as column labels
df = pd.read_csv('datasets/classmarks.csv')
df
rollno | gender | group | age | math | english | urdu | |
---|---|---|---|---|---|---|---|
0 | MS01 | female | group B | 28.0 | 72.0 | 72 | 74.0 |
1 | MS02 | female | group C | 33.0 | 69.0 | 90 | 88.0 |
2 | MS03 | female | group B | 21.0 | NaN | 95 | 93.0 |
3 | MS04 | male | group A | 44.0 | 47.0 | 57 | 44.0 |
4 | MS05 | male | group C | 54.0 | 76.0 | 78 | NaN |
5 | MS06 | female | group B | NaN | 71.0 | 83 | 78.0 |
6 | MS07 | female | group B | 47.0 | 88.0 | 95 | 92.0 |
7 | MS08 | male | group B | 33.0 | 40.0 | 43 | 39.0 |
8 | MS09 | male | group D | 27.0 | 64.0 | 64 | 67.0 |
9 | MS10 | female | group B | 33.0 | 38.0 | 60 | 50.0 |
10 | MS11 | male | group C | 46.0 | 58.0 | 54 | 52.0 |
11 | MS12 | male | group D | 53.0 | 40.0 | 52 | 43.0 |
12 | MS13 | female | group B | 25.0 | 65.0 | 81 | 73.0 |
13 | MS14 | male | group A | 26.0 | 78.0 | 72 | 70.0 |
14 | MS15 | female | group A | 25.0 | 50.0 | 53 | 58.0 |
15 | MS16 | female | group C | 29.0 | 69.0 | 75 | 78.0 |
16 | MS17 | male | group C | 22.0 | 88.0 | 89 | 86.0 |
17 | MS18 | female | group B | 31.0 | 18.0 | 32 | 28.0 |
18 | MS19 | male | group C | 33.0 | 46.0 | 42 | 46.0 |
19 | MS20 | female | group C | 36.0 | 54.0 | 58 | NaN |
20 | MS21 | male | group D | 34.0 | 66.0 | 69 | 63.0 |
21 | MS22 | female | group B | 32.0 | 65.0 | 75 | 70.0 |
22 | MS23 | male | group D | 31.0 | 44.0 | 54 | 53.0 |
23 | MS24 | female | group C | 37.0 | NaN | 73 | 73.0 |
24 | MS25 | male | group D | 33.0 | 74.0 | 71 | 80.0 |
25 | MS26 | male | group A | 39.0 | 73.0 | 74 | 72.0 |
26 | MS27 | male | group B | NaN | 69.0 | 54 | 55.0 |
27 | MS28 | female | group C | 47.0 | 67.0 | 69 | 75.0 |
28 | MS29 | male | group C | 44.0 | 70.0 | 70 | 65.0 |
29 | MS30 | female | group D | 42.0 | 62.0 | 70 | 75.0 |
30 | MS31 | female | group D | 41.0 | 69.0 | 74 | 74.0 |
31 | MS32 | female | group B | NaN | 63.0 | 65 | 61.0 |
32 | MS33 | female | group E | 29.0 | 56.0 | 72 | 65.0 |
33 | MS34 | male | group D | 34.0 | 40.0 | 42 | 38.0 |
34 | MS35 | male | group E | 45.0 | 97.0 | 87 | 82.0 |
35 | MS36 | male | group E | 31.0 | 81.0 | 81 | 79.0 |
36 | MS37 | female | group D | 28.0 | 74.0 | 81 | NaN |
37 | MS38 | female | group D | 26.0 | 50.0 | 64 | 59.0 |
38 | MS39 | female | group D | 36.0 | 75.0 | 90 | 88.0 |
39 | MS40 | male | group B | 33.0 | 57.0 | 56 | 57.0 |
40 | MS41 | male | group C | 35.0 | 55.0 | 61 | 54.0 |
41 | MS42 | female | group C | 22.0 | 58.0 | 73 | 68.0 |
42 | MS43 | female | group B | 38.0 | 53.0 | 58 | 65.0 |
43 | MS44 | male | group B | 31.0 | 59.0 | 65 | 66.0 |
44 | MS45 | female | group E | 28.0 | NaN | 56 | 54.0 |
45 | MS46 | male | group B | 26.0 | 65.0 | 54 | 57.0 |
46 | MS47 | female | group A | 36.0 | 55.0 | 65 | 62.0 |
47 | MS48 | female | group C | 30.0 | 66.0 | 71 | 76.0 |
48 | MS49 | female | group D | 40.0 | 57.0 | 74 | 76.0 |
49 | MS50 | male | group C | 37.0 | 66.0 | 78 | 81.0 |
The df.head(N)
method is used to select/display first N
rows, based on position
, i.e., the integer value corresponding to the position of the row (from 0 to n-1). The default value of N
is 5.
df.head()
df.head(3)
# For negative values of n, this method returns all rows except the last `n` rows, equivalent to df[:-n].
# The df has a total of 50 rows, so the following will return first 2 rows
df.head(-48)
The df.tail(N)
method is used to select/display last N
rows, based on position
, i.e., the integer value corresponding to the position of the row (from 0 to n-1). The default value of N
is 5.
# tail() method is useful for quickly verifying data, after sorting or appending rows.
df.tail()
df.tail(3)
# For negative values of `n`, this function returns all rows except the first `n` rows, equivalent to df[n:]
# The df has a total of 50 rows, so the following will return last 3 rows
df.tail(-46)
read_csv()
expect comma as seperator. But if the CSV file has some other seperator or delimiter like (semi-collon or tab), it will raise an error.delimiter
argument of read_csv()
method.! cat datasets/classmarkswithtab.csv
df = pd.read_csv('datasets/classmarkswithtab.csv')
df.head()
rollno\tgender\tgroup\tage\tmath\tenglish\turdu | |
---|---|
0 | MS01\tfemale\tgroup B\t28.0\t72.0\t72\t74.0 |
1 | MS02\tfemale\tgroup C\t33.0\t69.0\t90\t88.0 |
2 | MS03\tfemale\tgroup B\t21.0\t\t95\t93.0 |
3 | MS04\tmale\tgroup A\t44.0\t47.0\t57\t44.0 |
4 | MS05\tmale\tgroup C\t54.0\t76.0\t78\t |
df = pd.read_csv('datasets/classmarkswithtab.csv', delimiter='\t', header=None)
df.head()
0 | 1 | 2 | 3 | 4 | 5 | 6 | |
---|---|---|---|---|---|---|---|
0 | rollno | gender | group | age | math | english | urdu |
1 | MS01 | female | group B | 28.0 | 72.0 | 72 | 74.0 |
2 | MS02 | female | group C | 33.0 | 69.0 | 90 | 88.0 |
3 | MS03 | female | group B | 21.0 | NaN | 95 | 93.0 |
4 | MS04 | male | group A | 44.0 | 47.0 | 57 | 44.0 |
read_csv()
method assume the first row of the file will contain column labels! cat datasets/classmarkswithoutcollabels.csv
df = pd.read_csv('datasets/classmarkswithoutcollabels.csv')
df.head()
MS01 | female | group B | 28 | 72 | 72.1 | 74 | |
---|---|---|---|---|---|---|---|
0 | MS02 | female | group C | 33.0 | 69.0 | 90 | 88.0 |
1 | MS03 | female | group B | 21.0 | NaN | 95 | 93.0 |
2 | MS04 | male | group A | 44.0 | 47.0 | 57 | 44.0 |
3 | MS05 | male | group C | 54.0 | 76.0 | 78 | NaN |
4 | MS06 | female | group B | NaN | 71.0 | 83 | 78.0 |
To read such files, you have to pass the parameter header=None
to the read_csv()
method as shown below
df = pd.read_csv('datasets/classmarkswithoutcollabels.csv', header=None)
df.head()
0 | 1 | 2 | 3 | 4 | 5 | 6 | |
---|---|---|---|---|---|---|---|
0 | MS01 | female | group B | 28.0 | 72.0 | 72 | 74.0 |
1 | MS02 | female | group C | 33.0 | 69.0 | 90 | 88.0 |
2 | MS03 | female | group B | 21.0 | NaN | 95 | 93.0 |
3 | MS04 | male | group A | 44.0 | 47.0 | 57 | 44.0 |
4 | MS05 | male | group C | 54.0 | 76.0 | 78 | NaN |
Now if you want to assign new column labels to make them more understandable, you can assign the list of column labels to the columns
attribute of the dataframe object
col_names = ['rollno', 'gender', 'group', 'age', 'math', 'english', 'urdu']
df.columns = col_names
df.head()
rollno | gender | group | age | math | english | urdu | |
---|---|---|---|---|---|---|---|
0 | MS01 | female | group B | 28.0 | 72.0 | 72 | 74.0 |
1 | MS02 | female | group C | 33.0 | 69.0 | 90 | 88.0 |
2 | MS03 | female | group B | 21.0 | NaN | 95 | 93.0 |
3 | MS04 | male | group A | 44.0 | 47.0 | 57 | 44.0 |
4 | MS05 | male | group C | 54.0 | 76.0 | 78 | NaN |
! cat datasets/classmarkswithtopcomments.csv
MS50,male,group C,37,66,78,81
# Try reading a csv file having 3 comments lines in the beginning.
df = pd.read_csv('datasets/classmarkswithtopcomments.csv')
df.head()
These are comments | Unnamed: 1 | Unnamed: 2 | Unnamed: 3 | Unnamed: 4 | Unnamed: 5 | Unnamed: 6 | |
---|---|---|---|---|---|---|---|
0 | These are second line of comments | NaN | NaN | NaN | NaN | NaN | NaN |
1 | These are third line of comments | NaN | NaN | NaN | NaN | NaN | NaN |
2 | rollno | gender | group | age | math | english | urdu |
3 | MS01 | female | group B | 28 | 72 | 72 | 74 |
4 | MS02 | female | group C | 33 | 69 | 90 | 88 |
# Try reading a csv file having 3 comments lines in the beginning.
df = pd.read_csv('datasets/classmarkswithtopcomments.csv', skiprows=3)
df.head()
nrows
parameter to read_csv()
methodusecols
parameter to read_csv()
method# Read just 10 rows from the csv file by passing the number of rows to read to `nrows` argument
df = pd.read_csv('datasets/classmarks.csv', nrows=10)
df.shape
#df.head()
(10, 7)
# Read specific columns from the csv file by passing a list of column names to `usecols` argument
df = pd.read_csv('datasets/classmarks.csv', usecols= ['rollno', 'group','english'])
df.shape
#df.head()
# Ofcourse you can use both the parameters at the same time
df = pd.read_csv('datasets/classmarks.csv', nrows= 7, usecols= ['rollno', 'group','english'])
df.shape
(7, 3)
df
rollno | group | english | |
---|---|---|---|
0 | MS01 | group B | 72 |
1 | MS02 | group C | 90 |
2 | MS03 | group B | 95 |
3 | MS04 | group A | 57 |
4 | MS05 | group C | 78 |
5 | MS06 | group B | 83 |
6 | MS07 | group B | 95 |
# To avoid URLError: <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED].....
import ssl
ssl._create_default_https_context = ssl._create_unverified_context
The data1.csv
file actually resides on my GitHub Gist at following URL:
https://gist.githubusercontent.com/arifpucit/bbcb0bba0b5c245585b375f273f17876/raw/28ddebe991c86f7001178896329005ea174f2bde/data1.csv
Bitly is a URL shortening service that I have used to create a short link for easy usage in the cell below:
import pandas as pd
myurl = 'https://bit.ly/31eYGTx'
df = pd.read_csv(myurl)
df
Names | Age | Addr | |
---|---|---|---|
0 | Arif | 50 | Lahore |
1 | Rauf | 52 | Islamabad |
2 | Maaz | 27 | Peshawer |
3 | Hadeed | 22 | Islamabad |
4 | Mujahid | 18 | Karachi |
sheetID = '1H9ZTGVRXN3zuyP3cbJQnbX7JlqhqFNMOE_WwDP-PRTE'
sheetName = 'sheet1'
URL = 'https://docs.google.com/spreadsheets/d/{0}/gviz/tq?tqx=out:csv&sheet={1}'.format(sheetID, sheetName)
df = pd.read_csv(URL)
df.head()
rollno | gender | group | age | math | english | urdu | |
---|---|---|---|---|---|---|---|
0 | MS01 | female | group B | 28.0 | 72.0 | 72 | 74.0 |
1 | MS02 | female | group C | 33.0 | 69.0 | 90 | 88.0 |
2 | MS03 | female | group B | 21.0 | NaN | 95 | 93.0 |
3 | MS04 | male | group A | 44.0 | 47.0 | 57 | 44.0 |
4 | MS05 | male | group C | 54.0 | 76.0 | 78 | NaN |
URL
'https://docs.google.com/spreadsheets/d/1H9ZTGVRXN3zuyP3cbJQnbX7JlqhqFNMOE_WwDP-PRTE/gviz/tq?tqx=out:csv&sheet=sheet1'
pd.to_csv()
method is used to write the contents of a dataframe (with indices) to a CSV file.df_class = pd.read_csv('datasets/classmarks.csv')
df_class.head(7)
- Let us create a new dataframe from above dataframe containing records of only group B
mask = (df_class['group'] == 'group B')
mask.head(7)
df_class_groupB = df_class.loc[mask]
df_class_groupB
df_class_groupB.to_csv('datasets/classmarksgroupB.csv')
df = pd.read_csv('datasets/classmarksgroupB.csv')
df
To avoid writing the row indices column inside the file pass
index=False
argument toto_csv()
method
df_class_groupB.to_csv('datasets/classmarksgroupB.csv', index=False)
df = pd.read_csv('datasets/classmarksgroupB.csv')
df
XLSX: XLSX is a Microsoft Excel Open XML file format. It also comes under the Spreadsheet file format. It is an XML-based file format created by Microsoft Excel. In XLSX data is organized under the cells and columns in a sheet. Each XLSX file may contain one or more sheets. So a workbook can contain multiple sheets
import sys
!{sys.executable} -m pip install xlrd xlwt openpyxl
df = pd.read_excel(io='datasets/classmarks.xlsx')
df.head()
rollno | gender | group | age | math | english | urdu | |
---|---|---|---|---|---|---|---|
0 | MS01 | female | group B | 28.0 | 72.0 | 72 | 74.0 |
1 | MS02 | female | group C | 33.0 | 69.0 | 90 | 88.0 |
2 | MS03 | female | group B | 21.0 | NaN | 95 | 93.0 |
3 | MS04 | male | group A | 44.0 | 47.0 | 57 | 44.0 |
4 | MS05 | male | group C | 54.0 | 76.0 | 78 | NaN |
# The following file has three lines of comments in the beginning of the file.
df = pd.read_excel(io='datasets/classmarkswithcomments.xlsx',skiprows=3)
df.head()
pd.read_excel()
function read only the first sheet.big_mart_sales_with_multiple_sheets.xlsx
is a workbook that contains three sheets for different years data. The sheet names are 1985, 1987, and 1997df = pd.read_excel('datasets/big_mart_sales_with_multiple_sheets.xlsx')
# if you check/view the data you can see, it only contains the data of first excel sheet (for the year 1985)
df.shape
(1463, 12)
df_1985 = pd.read_excel('datasets/big_mart_sales_with_multiple_sheets.xlsx',sheet_name='1985')
df_1987 = pd.read_excel('datasets/big_mart_sales_with_multiple_sheets.xlsx',sheet_name='1987')
df_1997 = pd.read_excel('datasets/big_mart_sales_with_multiple_sheets.xlsx',sheet_name='1997')
df_1985.shape
(1463, 12)
df_1987.shape
(932, 12)
df_1997.shape
(930, 12)
pd.to_excel()
method is used to write the contents of a dataframe (with indices) to an Excel file.
- Let us create a new single dataframe after concatenating all the above three dataframes using
pd.concat()
method
df_concatenated = pd.concat(objs=[df_1985, df_1987, df_1997])
df_concatenated.shape
(3325, 12)
Note the total number of rows in this dataframe equals to 1463+932+930 = 3325
df_concatenated.head()
# you can store the concatenated data inside your dataframe into a single Excel file
# You can mention the argument `index= false` for not storing row indices (0, 1,2,3,... in the Excel file.
df_concatenated.to_excel(excel_writer='temp.xlsx', index=False)
# Let us verify
data = pd.read_excel(io='temp.xlsx')
data.shape
JSON: JavaScript Object Notation is a text-based open standard file format that uses human-readable text consisting of attribute–value pairs and arrays. It is a data interchange format that is used to store and transfer the data via Internet, primarily between a web client and a server.
import sys
!{sys.executable} -m pip install SQLAlchemy psycopg2-binary
! cat datasets/simple.json
[{"name": "Kamal", "age": 12, "grade": "A"}, {"name": "Hashim", "age": 18, "grade": "B"}, {"name": "Salman", "age": 11, "grade": "A"}, {"name": "Mazhar", "age": 12, "grade": "C"}, {"name": "Eisha", "age": 13, "grade": "B"}, {"name": "Farhan", "age": 22, "grade": "C"}, {"name": "Mohsin", "age": 11, "grade": "A"}, {"name": "Bilal", "age": 19, "grade": "A"}, {"name": "Ishaan", "age": 10, "grade": "D"}, {"name": "Zalaid", "age": 9, "grade": "B"}]
# read the json file using read_json method of pandas library
df = pd.read_json('datasets/simple.json')
df
name | age | grade | |
---|---|---|---|
0 | Kamal | 12 | A |
1 | Hashim | 18 | B |
2 | Salman | 11 | A |
3 | Mazhar | 12 | C |
4 | Eisha | 13 | B |
5 | Farhan | 22 | C |
6 | Mohsin | 11 | A |
7 | Bilal | 19 | A |
8 | Ishaan | 10 | D |
9 | Zalaid | 9 | B |
{ 'name' : 'Ahsan', 'roll_no' : '100' } # line 1
{ 'name' : 'Ayesha' , 'roll_no' : '101' } # line 2
! cat datasets/simple_records.json
{"name": "Kamal", "age": 12, "grade": "A"} {"name": "Hashim", "age": 18, "grade": "B"} {"name": "Salman", "age": 11, "grade": "A"} {"name": "Mazhar", "age": 12, "grade": "C"} {"name": "Eisha", "age": 13, "grade": "B"} {"name": "Farhan", "age": 22, "grade": "C"} {"name": "Mohsin", "age": 11, "grade": "A"} {"name": "Bilal", "age": 19, "grade": "A"} {"name": "Ishaan", "age": 10, "grade": "D"} {"name": "Zalaid", "age": 9, "grade": "B"}
# To read such file you need to pass `lines=True` to the `read_json()` method of dataframe
df = pd.read_json('datasets/simple_records.json',lines=True)
df
name | age | grade | |
---|---|---|---|
0 | Kamal | 12 | A |
1 | Hashim | 18 | B |
2 | Salman | 11 | A |
3 | Mazhar | 12 | C |
4 | Eisha | 13 | B |
5 | Farhan | 22 | C |
6 | Mohsin | 11 | A |
7 | Bilal | 19 | A |
8 | Ishaan | 10 | D |
9 | Zalaid | 9 | B |
df.to_json('datasets/temp.json')
df = pd.read_json('datasets/temp.json')
df