Chapter 02 Data Handling Using Pandas-1

“If you don’t think carefully, you might believe that programming is just typing statements in a programming language.”

— W. Cunningham

2.1 Introduction to Python Libraries

Python libraries contain a collection of builtin modules that allow us to perform many actions without writing detailed programs for it. Each library in Python contains a large number of modules that one can import and use.

NumPy, Pandas and Matplotlib are three well-established Python libraries for scientific and analytical use. These libraries allow us to manipulate, transform and visualise data easily and efficiently.

NumPy, which stands for ‘Numerical Python’, is a library we discussed in class XI. Recall that, it is a package that can be used for numerical data analysis and scientific computing. NumPy uses a multidimensional array object and has functions and tools for working with these arrays. Elements of an array stay together in memory, hence, they can be quickly accessed.

PANDAS (PANelDAta) is ahigh-leveldatamanipulation tool used for analysing data. It is very easy to import and export data using Pandas library which has a very rich set of functions. It is built on packages like NumPy and Matplotlib and gives us a single, convenient place to do most of our data analysis and visualisation work. Pandas has three important data structures, namely Series, DataFrame and Panel to make the process of analysing data organised, effective and efficient.

The Matplotlib library in Python is used for plotting graphs and visualisation. Using Matplotlib, with just a few lines of code we can generate publication quality plots, histograms, bar charts, scatterplots, etc. It is also built on Numpy, and is designed to work well with Numpy and Pandas.

You may think what the need for Pandas is when NumPy can be used for data analysis. Following are some of the differences between Pandas and Numpy:

  1. A Numpy array requires homogeneous data, while a Pandas DataFrame can have different data types (float, int, string, datetime, etc.).
  2. Pandas have a simpler interface for operations like file loading, plotting, selection, joining, GROUP BY, which come very handy in data-processing applications.
  3. Pandas DataFrames (with column names) make it very easy to keep track of data.
  4. Pandas is used when data is in Tabular Format, whereas Numpy is used for numeric array based data manipulation.

2.1.1. Installing Pandas

Installing Pandas is very similar to installing NumPy. To install Pandas from command line, we need to type in:

$$ \text { pip install pandas } $$

Note that both NumPy and Pandas can be installed only when Python is already installed on that system. The same is true for other libraries of Python.

2.1.2. Data Structure in Pandas

A data structure is a collection of data values and operations that can be applied to that data. It enables efficient storage, retrieval and modification to the data. For example, we have already worked with a data structure ndarray in NumPy in Class XI. Recall the ease with which we can store, access and update data using a NumPy array. Two commonly used data structures in Pandas that we will cover in this book are:

  • Series
  • DataFrame

2.2 Series

A Series is a one-dimensional array containing a sequence of values of any data type (int, float, list, string, etc) which by default have numeric data labels starting from zero. The data label associated with a particular value is called its index. We can also assign values of other data types as index. We can imagine a Pandas Series as a column in a spreadsheet. Example of a series containing names of students is given below:

Index $\quad$ Value
0 $\quad$ Arnab
1 $\quad$ Samridhi
2 $\quad$ Ramit
3 $\quad$ Divyam
4 $\quad$ Kritika

2.2.1 Creation of Series

There are different ways in which a series can be created in Pandas. To create or use series, we first need to import the Pandas library.

(A) Creation of Series from Scalar Values

A Series can be created using scalar values as shown in the example below:

>>> import pandas as pd #import Pandas with alias pd
>>> series1 = pd.Series([10,20,30]) #create a Series
>>> print(series1) #Display the series

Output:

0 $\quad$ 10
1 $\quad$ 20
2 $\quad$ 30

dtype: int 64

Activity 2.1

Create a series having names of any five famous monuments of India and assign their States as index values.

Observe that output is shown in two columns - the index is on the left and the data value is on the right. If we do not explicitly specify an index for the data values while creating a series, then by default indices range from 0 through $N-1$. Here $N$ is the number of data elements.

We can also assign user-defined labels to the index and use them to access elements of a Series. The following example has a numeric index in random order.

>>> series2 = pd.Series([“Kavi”,“Shyam”,“Ravi”], index=[3,5,1]) >>> print(series2) #Display the series

Output:

3 $\quad$ Kavi
5 $\quad$ Shyam
1 $\quad$ Ravi

dtype: object

Here, data values Kavi, Shyam and Ravi have index values 3, 5 and 1, respectively. We can also use letters or strings as indices, for example:

>>> series2 = pd.Series([2,3,4],index=[“Feb”,“Mar”,“Apr”])
>>> print(series2) #Display the series

Output:

Feb $\quad$ 2
Mar $\quad$ 3
Apr $\quad$ 4

dtype: int 64

Here, data values 2,3,4 have index values Feb, Mar and Apr, respectively.

Think and Reflect

While importing Pandas, is it mandatory to always use pd as an alias name? What would happen if we give any other name?

(B) Creation of Series from NumPy Arrays

We can create a series from a one-dimensional (1D) NumPy array, as shown below:

>>> import numpy as np # import NumPy with alias np
>>> import pandas as pd
>>> array1 = np.array([1,2,3,4])
>>> series3 = pd.Series(array1)
>>> print(series3)

Output:

0 $\quad$ 1
1 $\quad$ 2
2 $\quad$ 3
3 $\quad$ 4

dtype: int 32

The following example shows that we can use letters or strings as indices:

>>> series4 = pd.Series(array1, index = [“Jan”, “Feb”, “Mar”, “Apr”])
>>> print(series4)

When index labels are passed with the array, then the length of the index and array must be of the same size, else it will result in a ValueError. In the example shown below, array 1 contains 4 values whereas there are only 3 indices, hence ValueError is displayed.

>>> series5 = pd.Series(array1, index = [“Jan”, “Feb”, “Mar”])
ValueError: Length of passed values is 4, index implies 3

(C) Creation of Series from Dictionary

Recall that Python dictionary has key: value pairs and a value can be quickly retrieved when its key is known. Dictionary keys can be used to construct an index for a Series, as shown in the following example. Here, keys of the dictionary dict 1 become indices in the series.

>>> dict1 = {‘India’: ‘NewDelhi’, ‘UK’: ‘London’, ‘Japan’: ‘Tokyo’}
>>> print(dict1) #Display the dictionary {‘India’: ‘NewDelhi’, ‘UK’: ‘London’, ‘Japan’: ‘Tokyo’}
>>> series8 = pd.Series(dict1)
>>> print(series8) #Display the series

India $\quad$ NewDelhi
UK $\quad$ London
Japan $\quad$ Tokyo

dtype: object

2.2.2 Accessing Elements of a Series

There are two common ways for accessing the elements of a series: Indexing and Slicing.

(A) Indexing

Indexing in Series is similar to that for NumPy arrays, and is used to access elements in a series. Indexes are of two types: positional index and labelled index. Positional index takes an integer value that corresponds to its position in the series starting from 0 , whereas labelled index takes any user-defined label as index.

  • Following example shows usage of the positional index for accessing a value from a Series.

>>> seriesNum = pd.Series([10,20,30])
>>> seriesNum[2]
30

Here, the value 30 is displayed for the positional index 2.

When labels are specified, we can use labels as indices while selecting values from a Series, as shown below. Here, the value 3 is displayed for the labelled index Mar.

>>> seriesMnths = pd.Series([2,3,4],index=[“Feb”,“Mar”,“Apr”])
>>> seriesMnths[“Mar”]
3

In the following example, value NewDelhi is displayed for the labelled index India.

>>> seriesCapCntry = pd.Series([‘NewDelhi’, ‘WashingtonDC’, ‘London’, ‘Paris’], index=[‘India’, ‘USA’, ‘UK’, ‘France’])
>>> seriesCapCntry[‘India’]
‘NewDelhi’

Activity 2.2

Write the statement to get NewDelhi as output using positional index.

We can also access an element of the series using the positional index:

>>> seriesCapCntry[1]
‘WashingtonDC’

More than one element of a series can be accessed using a list of positional integers or a list of index labels as shown in the following examples:

>>> seriesCapCntry[[3,2]]

France $\quad$ Paris
UK $\quad$ London

dtype: object

>>> seriesCapCntry[[‘UK’,‘USA’]]

UK $\quad$ London
USA $\quad$ WashingtonDC

dtype: object

The index values associated with the series can be altered by assigning new index values as shown in the following example:

>>> seriesCapCntry.index=[10,20,30,40]
>>> seriesCapCntry

10 $\quad$ NewDelhi
20 $\quad$ WashingtonDC
30 $\quad$ London
40 $\quad$ Paris

dtype: object

(B) Slicing

Sometimes, we may need to extract a part of a series. This can be done through slicing. This is similar to slicing used with NumPy arrays. We can define which part of the series is to be sliced by specifying the start and end parameters [start :end] with the series name. When we use positional indices for slicing, the value at the endindex position is excluded, i.e., only (end start) number of data values of the series are extracted. Consider the following series seriesCapCntry:

>>> seriesCapCntry = pd.Series([‘NewDelhi’, ‘WashingtonDC’, ‘London’, ‘Paris’], index=[‘India’, ‘USA’, ‘UK’, ‘France’])
>>> seriesCapCntry[1:3] #excludes the value at index position 3

USA $\quad$ WashingtonDC
UK $\quad$ London

dtype: object

As we can see that in the above output, only data values at indices 1 and 2 are displayed. If labelled indexes are used for slicing, then value at the end index label is also included in the output, for example:

>>> seriesCapCntry[‘USA’ : ‘France’]

USA $\quad$ WashingtonDC
UK $\quad$ London
France $\quad$ Paris

dtype: object

We can also get the series in reverse order, for example:

>>> seriesCapCntry[ : : -1]

France $\quad$ Paris
UK $\quad$ London
USA $\quad$ WashingtonDC
India $\quad$ NewDelhi

dtype: object

We can also use slicing to modify the values of series elements as shown in the following example:

>>> import numpy as np
>>> seriesAlph = pd.Series(np.arange(10,16,1), index = [‘a’, ‘b’, ‘c’, ’d’, ’e’, ‘f’])
>>> seriesAlph

a $\quad$ 10
b $\quad$ 11
c $\quad$ 12
d $\quad$ 13
e $\quad$ 14
f $\quad$ 15

dtype: int32

>>> seriesAlph[1:3] = 50
>>> seriesAlph

a $\quad$ 10
b $\quad$ 50
c $\quad$ 50
d $\quad$ 13
e $\quad$ 14
f $\quad$ 15

dtype: int32

Observe that updating the values in a series using slicing also excludes the value at the end index position. But, it changes the value at the end index label when slicing is done using labels.

>>> seriesAlph[‘c’:’e’] = 500
>>> seriesAlph

a $\quad$ 10
b $\quad$ 50
c $\quad$ 500
d $\quad$ 500
e $\quad$ 500
f $\quad$ 15

dtype: int32

2.2.3 Attributes of Series

We can access certain properties called attributes of a series by using that property with the series name. Table 2.1 lists some attributes of Pandas series usingseriesCapCntry as an example:

>>> seriesCapCntry

India $\quad$ NewDelhi
USA $\quad$ WashingtonDC
UK $\quad$ London
France $\quad$ Paris

dtype: object

Table 2.1 Attributes of Pandas Series

Attribute Name Purpose Example
name assigns a name to the Series >>> seriesCapCntry.name = ‘Capitals’
>>> print(seriesCapCntry)
India $\quad$ NewDelhi
USA $\quad$ WashingtonDC
UK $\quad$ London
France $\quad$ Paris
Name: Capitals, dtype: object
index.name assigns a name to the index of the series >>>seriesCapCntry. index. name $=$ ’ Countries’
>>>print(seriesCapCntry) Countries
India $\quad$ NewDelhi
USA $\quad$ WashingtonDC
UK $\quad$ London
France $\quad$ Paris
Name: Capitals, dtype: object
values prints a list of the values in the series >>> print (seriesCapCntry. val ues) [‘NewDelhi’ ‘WashingtonDC’ ‘London’ ‘Paris’]
size prints the number of values in the Series object >>>print(seriesCapCntry. size) 4
empty prints True if the series is empty, and False otherwise »> seriesCapCntry. empty False
# Create an empty series
seriesEmpt=pd. Series()
>>> seriesEmpt. empty True

Activity 2.3

Consider the following code:
>>>i mport pandas as pd
>>>i mport numpy as np
>>>s $2=p d$.
Series([12,np.nan, 10])
>>>ppint (s)

Find output of the above code and write a Python statement to count and display only non null values in the above series.

2.2.4 Methods of Series

In this section, we are going to discuss some of the methods that are available for Pandas Series. Let us consider the following series:

>>> seriesTenTwenty=pd. Series(np. arange( 10, 20, 1 1)
>>> print(seriesTenTwenty)

$0 \quad 10$
$1 \quad 11$
$2 \quad 12$
$3 \quad 13$
$4 \quad 14$
$5 \quad 15$
$6 \quad 16$
$7 \quad 17$
$8 \quad 18$
$9 \quad 19$

dtype: int 32

Method Explanation Example
head(n) Returns the first n members of the series. If the value for n is not passed, then by default n takes 5 and the first five members are displayed. >>>seriesTenTwenty.head(2)
0 $\quad$ 10
1 $\quad$ 11
dtype: int32
>>>seriesTenTwenty.head()
0 $\quad$ 10
1 $\quad$ 11
2 $\quad$ 12
3 $\quad$ 13
4 $\quad$ 14
dtype: int32
count() Returns the number of non-NaN values in the Series >>>seriesTenTwenty.count()10
tail(n) Returns the last n members of the series. If the value for n is not passed, then by default n takes 5 and the last five members are displayed. >>> seriesTenTwenty.tail(2)
8 $\quad$ 18
9 $\quad$ 19
dtype: int32
>>> seriesTenTwenty.tail()
5 $\quad$ 15
6 $\quad$ 16
7 $\quad$ 17
8 $\quad$ 18
9 $\quad$ 19
dtype: int32

2.2.5 Mathematical Operations on Series

We have learnt in Class XI that if we perform basic mathematical operations like addition, subtraction, multiplication, division, etc., on two NumPy arrays, the operation is done on each corresponding pair of elements. Similarly, we can perform mathematical operations on two series in Pandas.

While performing mathematical operations on series, index matching is implemented and all missing values are filled in with NaN by default.

Consider the following series: seriesA and seriesB for understanding mathematical operations on series in Pandas.

>>> seriesA = pd.Series([1,2,3,4,5], index = [‘a’, ‘b’, ‘c’, ’d’, ’e’])
>>> seriesA
a $\quad$ 1
b $\quad$ 2
c $\quad$ 3
d $\quad$ 4
e $\quad$ 5
dtype: int64

>>> seriesB = pd.Series([10,20,-10,-50,100], index = [‘z’, ‘y’, ‘a’, ‘c’, ’e’])
>>> seriesB
z $\quad$ 10
y $\quad$ 20
a $\quad$ -10
c $\quad$ -50
e $\quad$ 100
dtype: int64

(A) Addition of two Series

It can be done in two ways. In the first method, two series are simply added together, as shown in the following code. Table 2.2 shows the detailed values that were matched while performing the addition. Note here that the output of addition is $\mathrm{NaN}$ if one of the elements or both elements have no value.

_>>> seriesA + seriesB
_ a $\quad$ -9.0
b $\quad$ NaN
c $\quad$ -47.0
d $\quad$ NaN
e $\quad$ 105.0
y $\quad$ NaN
z $\quad$ NaN
dtype: float64

Table 2.2 Details of addition of two series

index value from seriesA value from seriesB seriesA + seriesB
a 1 -10 -9.0
b 2 NaN
c 3 -50 -47.0
d 4 $\mathrm{NaN}$
e 5 100 105.00
y 20 $\mathrm{NaN}$
z 10 $\mathrm{NaN}$

The second method is applied when we do not want to have NaN values in the output. We can use the series method add() and a parameter fill_value to replace missing value with a specified value. That is, calling seriesA.add(seriesB) is equivalent to calling seriesA+seriesB, but add() allows explicit specification of the fill value for any element in seriesA or seriesB that might be missing, as shown in Table 2.3.

Activity 2.4

Draw two tables for subtraction similar to tables 2.2 and 2.3 showing the changes in the series elements and corresponding output without replacing the missing values, and after replacing the missing values with 1000.

>>> seriesA.add(seriesB, fill_value=0)
a $\quad$ -9.0
b $\quad$ 2.0
c $\quad$ -47.0
d $\quad$ 4.0
e $\quad$ 105.0
y $\quad$ 20.0
z $\quad$ 10.0
dtype: float64

Table 2.3 Details of addition of two series using add() method

index value from seriesA value from seriesB seriesA + seriesB
a 1 -10 -9.0
b 2 0 2.0
c 3 -50 -47.0
d 4 0 4.0
e 5 100 105.00
y 0 20 20.0
z 0 10 10.0

Note that Table 2.2 shows the changes in the series elements and corresponding output without replacing the missing values, while Table 2.3 shows the changes in the series elements and corresponding output after replacing missing values by 0 . Just like addition, subtraction, multiplication and division can also be done using corresponding mathematical operators or explicitly calling of the appropriate method.

Activity 2.5

Draw two tables for multiplication similar to Tables 2.2 and 2.3 showing the changes in the series elements and corresponding output without replacing the missing values, and after replacing the missing values with 0 .

(B) Subtraction of two Series

Again, it can be done in two different ways, as shown in the following examples:[^0]

_>>> seriesA – seriesB #using subtraction operator
_ a $\quad$ 11.0
b $\quad$ NaN
c $\quad$ 53.0
d $\quad$ NaN
e $\quad$ -95.0
y $\quad$ NaN
z $\quad$ NaN
dtype: float64

Let us now replace the missing values with 1000 before subtracting seriesB from seriesA using explicit subtraction method sub().

>>> seriesA.sub(seriesB, fill_value=1000)
# using fill value 1000 while making explicit
$\quad$ # call of the method”
a $\quad$ 11.0
b $\quad$ -998.0
c $\quad$ 53.0
d $\quad$ -996.0
e $\quad$ -95.0
y $\quad$ 980.0
z $\quad$ 990.0
dtype: float64

(C) Multiplication of two Series

Again, it can be done in two different ways, as shown in the following examples:

>>>seriesA * seriesB #using multiplication operator
a $\quad$ -10.0
b $\quad$ NaN
c $\quad$ -150.0
d $\quad$ NaN
e $\quad$ 500.0
y $\quad$ NaN
z $\quad$ NaN
dtype: float64

Activity 2.6

Draw two tables for division similar to tables 2.2 and 2.3 showing the changes in the series elements and corresponding output without replacing the missing values, and after replacing the missing values with 0 .

Let us now replace the missing values with 0 before multiplication of seriesB with seriesA using explicit multiplication method mul().

>>> seriesA.mul(seriesB, fill_value=0)
# using fill value 0 while making
# explicit call of the method
a $\quad$ -10.0
b $\quad$ 0.0
c $\quad$ -150.0
d $\quad$ 0.0
e $\quad$ 500.0
y $\quad$ 0.0
z $\quad$ 0.0
dtype: float64

Explicit call to a mathematical operation is preferred when series may have missing values and we want to replace it by a specific value to have a concrete output in place of $\mathrm{NaN}$.

(D) Division of two Series

Again, it can be done in two different ways, as shown in the following examples:

>>> seriesA/seriesB # using division operator
a $\quad$ -0.10
b $\quad$ NaN
c $\quad$ -0.06
d $\quad$ NaN
e $\quad$ 0.05
y $\quad$ NaN
z $\quad$ NaN
dtype: float64

Let us now replace the missing values with 0 before dividing seriesA by seriesB using explicit division method $\operatorname{div}($ ).

# using fill value 0 while making explicit
# call of the method

a $\quad$ -0.10
b $\quad$ inf
c $\quad$ -0.06
d $\quad$ inf
e $\quad$ 0.05
y $\quad$ 0.00
z $\quad$ 0.00
dtype: float64

2.3 DataFrame

Sometimes we need to work on multiple columns at a time, i.e., we need to process the tabular data. For example, the result of a class, items in a restaurant’s menu, reservation chart of a train, etc. Pandas store such tabular data using a DataFrame. A DataFrame is a two-dimensional labelled data structure like a table of MySQL. It contains rows and columns, and therefore has both a row and column index. Each column can have a different type of value such as numeric, string, boolean, etc., as in tables of a database.

State Geographical Area (sq Km) Area under Very Dense Forests (sq Km)
1 Assam 78438 2797
2 Delhi 1483 6.72
3 Kerala 38852 1663

2.3.1 Creation of DataFrame

There are a number of ways to create a DataFrame. Some of them are listed in this section.

(A) Creation of an empty DataFrame

An empty DataFrame can be created as follows:

>>> import pandas as pd
>>> dFrameEmt = pd. DataFrame()
>>> dFrameEmt

Empty Dataframe
Columns: [ ]
Index: [ ]

Think and Reflect

What would happen if we pass 3 columns or 5 columns instead of 4 in the above code? What is the reason?

(B) Creation of DataFrame from NumPy ndarrays

Consider the following three NumPy ndarrays. Let us create a simple DataFrame without any column labels, using a single ndarray:

>>> import numpy as np
>>> array1 = np.array([10,20,30])
>>> array2 = np.array([100,200,300])
>>> array3 = np.array([-10,-20,-30, -40])
>>> dFrame4 = pd.DataFrame(array1)
>>> dFrame4


0
1
2
0
10
20
30

We can create a DataFrame using more than one ndarrays, as shown in the following example:

>>> dFrame5 = pd.DataFrame([array1, array3, array2], columns=[ ‘A’, ‘B’, ‘C’, ‘D’])
>>> dFrame5


0
1
2
A
10
-10
100
B
20
-20
200
C
30
-30
300
D
NaN
-40.0
NaN

(C) Creation of DataFrame from List of Dictionaries

We can create DataFrame from a list of Dictionaries, for example:# Create list of dictionaries

# Create list of dictionaries
>>> listDict = [{‘a’:10, ‘b’:20}, {‘a’:5, ‘b’:10, ‘c’:20}]
>>> dFrameListDict = pd.DataFrame(listDict)
>>> dFrameListDict


0
1
a
10
5
b
20
10
c
NaN
20.0

Here, the dictionary keys are taken as column labels, and the values corresponding to each key are taken as rows. There will be as many rows as the number of dictionaries present in the list. In the above example there are two dictionaries in the list. So, the DataFrame consists of two rows. Number of columns in a DataFrame is equal to the maximum number of keys in any dictionary of the list. Hence, there are three columns as the second dictionary has three elements. Also, note that NaN (Not a Number) is inserted if a corresponding value for a column is missing.

(D) Creation of DataFrame from Dictionary of Lists

DataFrames can also be created from a dictionary of lists. Consider the following dictionary consisting of the keys ‘State’, ‘GArea’ (geographical area) and ‘VDF’ (very dense forest) and the corresponding values as list.

>>> dictForest = {‘State’: [‘Assam’, ‘Delhi’, ‘Kerala’],
$\quad$ ‘GArea’: [78438, 1483, 38852] ,
$\quad$ ‘VDF’ : [2797, 6.72,1663]}
>>> dFrameForest= pd.DataFrame(dictForest)
>>> dFrameForest


0
1
2
State
Assam
Delhi
Kerala
GArea
78438
1483
38852
VDF
2797.00
6.72
1663.00

Note that dictionary keys become column labels by default in a DataFrame, and the lists become the rows. Thus, a DataFrame can be thought of as a dictionary of lists or a dictionary of series.

We can change the sequence of columns in a DataFrame. This can be done by assigning a particular sequence of the dictionary keys as columns parameter, for example:

>>> dFrameForest1 = pd.DataFrame(dictForest, columns = [‘State’,‘VDF’, ‘GArea’])
>>> dFrameForest1


0
1
2
State
Assam
Delhi
Kerala
VDF
2797.00
6.72
1663.00
GArea
78438
1483
38852

In the output, VDF is now displayed as the middle column instead of last.

(E) Creation of DataFrame from Series

Consider the following three Series:

seriesA = pd.Series([1,2,3,4,5],
$\quad$ index = [‘a’, ‘b’, ‘c’, ’d’, ’e’])
seriesB = pd.Series ([1000,2000,-1000,-5000,1000],
$\quad$ index = [‘a’, ‘b’, ‘c’, ’d’, ’e’])
seriesC = pd.Series([10,20,-10,-50,100],
$\quad$ index = [‘z’, ‘y’, ‘a’, ‘c’, ’e’])

We can create a DataFrame using a single series as shown below:

>>> dFrame6 = pd.DataFrame(seriesA)
>>> dFrame6


a
b
c
d
e

0
1
2
3
4
5

Here, the DataFrame dFrame 6 has as many numbers of rows as the numbers of elements in the series, but has only one column. To create a DataFrame using more than one series, we need to pass multiple series in the list as shown below:

>>> dFrame7 = pd.DataFrame([seriesA, seriesB])
>>> dFrame7

a
0
1
b
1
1000
c
2
2000
d
3
-1000
e
4
-5000

5
1000

Observe that the labels in the series object become the column names in the DataFrame object and each series becomes a row in the DataFrame. Now look at the following example:

>>> dFrame8 = pd.DataFrame([seriesA, seriesC])
>>> dFrame8

a
0
1
b
1.0
-10.0
c
2.0
NaN
d
3.0
-50.0
e
4.0
NaN
z
5.0
100.0
y
NaN
10.0

NaN
20.0

Here, different series do not have the same set of labels. But, the number of columns in a DataFrame equals to distinct labels in all the series. So, if a particular series does not have a corresponding value for a label, $\mathrm{NaN}$ is inserted in the DataFrame column.

(F) Creation of DataFrame from Dictionary of Series

A dictionary of series can also be used to create a DataFrame. For example, ResultSheet is a dictionary of series containing marks of 5 students in three subjects. The names of the students are the keys to the dictionary, and the index values of the series are the subject names as shown below:

Activity 2.7

Use the type function to check the datatypes of ResultSheet and ResultDF. Are they the same?

>>> ResultSheet={
‘Arnab’: pd.Series([90, 91, 97],
$\quad$ index=[‘Maths’,‘Science’,‘Hindi’]),
‘Ramit’: pd.Series([92, 81, 96],
$\quad$ index=[‘Maths’,‘Science’,‘Hindi’]),
‘Samridhi’: pd.Series([89, 91, 88],
$\quad$ index=[‘Maths’,‘Science’,‘Hindi’]),
‘Riya’: pd.Series([81, 71, 67],
$\quad$ index=[‘Maths’,‘Science’,‘Hindi’]),
‘Mallika’: pd.Series([94, 95, 99],
$\quad$ index=[‘Maths’,‘Science’,‘Hindi’])}
>>> ResultDF = pd.DataFrame(ResultSheet)
>>> ResultDF

maths
Science
Hindi
Arnab
90
91
97
Ramit
92
81
96
Samridhi
89
91
88
Riya
81
71
67
Mallika
94
95
99

The following output shows that every column in the DataFrame is a Series:

>>> type(ResultDF.Arnab)
<class ‘pandas.core.series.Series’>

When a DataFrame is created from a Dictionary of Series, the resulting index or row labels are a union of all series indexes used to create the DataFrame. For example:

dictForUnion = { ‘Series1’ :
pd.Series([1,2,3,4,5],
$\quad$ index = [‘a’, ‘b’, ‘c’, ’d’, ’e’]) ,
$\quad$ ‘Series2’ :
pd.Series([10,20,-10,-50,100],
$\quad$ index = [‘z’, ‘y’, ‘a’, ‘c’, ’e’]),
$\quad$ ‘Series3’ :
pd.Series([10,20,-10,-50,100],
$\quad$ index = [‘z’, ‘y’, ‘a’, ‘c’, ’e’]) }
>>> dFrameUnion = pd.DataFrame(dictForUnion)
>>> dFrameUnion

a
b
c
d
e
y
z
Series1
1.0
2.0
3.0
4.0
5.0
NaN
NaN
Series2
-10.0
NaN
-50.0
NaN
100.0
20.0
10.0
Series3
-10.0
NaN
-50.0
NaN
100.0
20.0
10.0

2.3.2 Operations on rows and columns in DataFrames

We can perform some basic operations on rows and columns of a DataFrame like selection, deletion, addition, and renaming, as discussed in this section.

(A) Adding a New Column to a DataFrame

We can easily add a new column to a DataFrame. Let us consider the DataFrame ResultDF defined earlier. In order to add a new column for another student ‘Preeti’, we can write the following statement:

Maths
Science
Hindi
Arnab
90
91
97
Ramit
92
81
96
Samridhi
89
91
88
Riya
81
71
67
Mallika
94
95
99
Preeti
89
78
76

Assigning values to a new column label that does not exist will create a new column at the end. If the column already exists in the DataFrame then the assignment statement will update the values of the already existing column, for example:

>>> ResultDF[‘Ramit’]=[99, 98, 78]
>>> ResultDF


Maths
Science
Hindi
Arnab
90
91
97
Ramit
99
98
78
Samridhi
89
91
88
Riya
81
71
67
Mallika
94
95
99
Preeti
89
78
76

We can also change data of an entire column to a particular value in a DataFrame. For example, the following statement sets marks $=90$ for all subjects for the column name ‘Arnab’:

>>> ResultDF[‘Arnab’]=90
>>> ResultDF


Maths
Science
Hindi
Arnab
90
90
90
Ramit
99
98
78
Samridhi
89
91
88
Riya
81
71
67
Mallika
94
95
99
Preeti
89
78
76

(B) Adding a New Row to a DataFrame

We can add a new row to a DataFrame using the DataFrame.loc[ ] method. Consider the DataFrame ResultDF that has three rows for the three subjects Maths, Science and Hindi. Suppose, we need to add the marks for English subject in ResultDF, we can use the following statement:

>>> ResultDF

Arnab Ramit Samridhi Riya Mallika Preeti
Maths 90 92 89 81 94 89
Science 91 81 91 71 95 78
Hindi 97 96 88 67 99 76

>>> ResultDF.loc[‘English’] = [85, 86, 83, 80, 90, 89]
>>> ResultDF

Arnab Ramit Samridhi Riya Mallika Preeti
Maths 90 92 89 81 94 89
Science 91 81 91 71 95 78
Hindi 97 96 88 67 99 76
English 85 86 83 80 90 89

We cannot use this method to add a row of data with already existing (duplicate) index value (label). In such case, a row with this index label will be updated, for example:

>>> ResultDF.loc[‘English’] = [95, 86, 95, 80, 95,99]
>>> ResultDF

Arnab Ramit Samridhi Riya Mallika Preeti
Maths 90 92 89 81 94 89
Science 91 81 91 71 95 78
Hindi 97 96 88 67 99 76
English 95 86 95 80 95 99

DataFRame.loc[] method can also be used to change the data values of a row to a particular value. For example, the following statement sets marks in ‘Maths’ for all columns to 0 :

>>> ResultDF.loc[‘Maths’]=0
>>> ResultDF

Arnab Ramit Samridhi Riya Mallika Preeti
Maths 0 0 0 0 0 0
Science 91 81 91 71 95 78
Hindi 97 96 88 67 99 76
English 95 86 95 80 95 99

Think and Reflect

Can you write a program to count the number of rows and columns in a DataFrame?

If we try to add a row with lesser values than the number of columns in the DataFrame, it results in a ValueError, with the error message: ValueError: Cannot set a row with mismatched columns.

Similarly, if we try to add a column with lesser values than the number of rows in the DataFrame, it results in a ValueError, with the error message: ValueError: Length of values does not match length of index.

Further, we can set all values of a DataFrame to a particular value, for example:

>>> ResultDF[: ] = 0 # Set all values in ResultDF to 0
>>> ResultDF

Arnab Ramit Samridhi Riya Mallika Preeti
Maths 0 0 0 0 0 0
Science 0 0 0 0 0 0
Hindi 0 0 0 0 0 0
English 0 0 0 0 0 0

(C) Deleting Rows or Columns from a DataFrame

We can use the DataFrame.drop() method to delete rows and columns from a DataFrame. We need to specify the names of the labels to be dropped and the axis from which they need to be dropped. To delete a row, the parameter axis is assigned the value 0 and for deleting a column, the parameter axis is assigned the value 1 . Consider the following DataFrame:

>>> ResultDF

Arnab Ramit Samridhi Riya Mallika
Maths 90 92 89 81 94
Science 91 81 91 71 95
Hindi 97 96 88 67 99
English 95 86 95 80 95

The following example shows how to delete the row with label ‘Science’:

>>> ResultDF = ResultDF.drop(‘Science’, axis=0)
>>> ResultDF

Arnab Ramit Samridhi Riya Mallika
Maths 90 92 89 81 94
Hindi 97 96 88 67 99
English 95 86 95 80 95

The following example shows how to delete the columns having labels ‘Samridhi’, ‘Ramit’ and ‘Riya’:

>>> ResultDF = ResultDF.drop([‘Samridhi’,‘Ramit’,‘Riya’], axis=1)
>>> ResultDF

Arnab Mallika
Maths 90 94
Hindi 97 99
English 95 95

If the DataFrame has more than one row with the same label, the DataFrame.drop() method will delete all the matching rows from it. For example, consider the following DataFrame:


Maths
Science
Hindi
Hindi
Arnab
90
91
97
97
Ramit
92
81
96
89
Samridhi
89
91
88
78
Riya
81
71
67
60
Mallika
94
95
99
45

To remove the duplicate rows labelled ‘Hindi’, we need to write the following statement:

Think and Reflect

What if in the rename function we pass a value for a row label that does not exist?

>>> ResultDF= ResultDF.drop(‘Hindi’, axis=0)
>>> ResultDF

Arnab Ramit Samridhi Riya Mallika
Maths 90 92 89 81 94
Science 91 81 91 71 95

(D) Renaming Row Labels of a DataFrame

We can change the labels of rows and columns in a DataFrame using the DataFrame.rename() method. Consider the following DataFrame. To rename the row indices Maths to sub1, Science to sub2, Hindi to sub3 and English to sub4 we can write the following statement:

>>> ResultDF

Arnab Ramit Samridhi Riya Mallika
Maths 90 92 89 81 94
Science 91 81 91 71 95
English 97 96 88 67 99
Hindi 97 89 78 60 45

>>> ResultDF=ResultDF.rename({‘Maths’:‘Sub1’, ‘Science’:‘Sub2’,‘English’:‘Sub3’, ‘Hindi’:‘Sub4’}, axis=‘index’)
>>> print(ResultDF)

Arnab Ramit Samridhi Riya Mallika
Sub1 90 92 89 81 94
Sub2 91 81 91 71 95
Sub3 97 96 88 67 99
Sub4 97 89 78 60 45

The parameter axis=‘index’ is used to specify that the row label is to be changed. If no new label is passed corresponding to an existing label, the existing row label is left as it is, for example:

>>> ResultDF=ResultDF.rename({‘Maths’:‘Sub1’,‘Science’:‘Sub2’,‘Hindi’:‘Sub4’}, axis=‘index’) >>> print(ResultDF)


Sub1
Sub2
English
Sub
Arnab
90
91
97
97
Ramit
92
81
96
89
Samridhi
89
91
88
78
Riya
81
71
67
60
Mallika
94
95
99
45

(E) Renaming Column Labels of a DataFrame

To alter the column names of ResultDF we can again use the rename() method, as shown below. The parameter axis=‘columns’ implies we want to change the column labels:

>>>ResultDF=ResultDF.rename({‘Arnab’:‘Student1’,‘Ramit’:‘Student2’,‘Samridhi’:‘Student3’,
‘Mallika’:‘Student4’},axis=‘columns’)
>>> print(RsultDF)

Student1 Student2 Student3 Riya Student4
Maths 90 92 89 81 94
Science 91 81 91 71 95
English 97 96 88 67 99
Hindi 97 89 78 60 45

Note that the column Riya remains unchanged since we did not pass any new label.

2.3.3 Accessing DataFrames Element through Indexing

Data elements in a DataFrame can be accessed using indexing. There are two ways of indexing Dataframes : Label based indexing and Boolean Indexing.

Think and Reflect

What would happen if the label or row index passed is not present in the DataFrame?

(A) Label Based Indexing

There are several methods in Pandas to implement label based indexing. DataFrame.loc[ ] is an important method that is used for label based indexing with DataFrames. Let us continue to use the ResultDF created earlier. As shown in the following example, a single row label returns the row as a Series.

>>> ResultDF

Maths Arnab Ramit Samridhi Riya Mallika
Science 90 92 89 81 94
Hindi 91 81 91 71 95

>>> ResultDF.loc[‘Science’]

Arnab 91
Ramit 81
Samridhi 91
Riya 71
Mallika 95

Name: Science, dtype: int 64

Also, note that when the row label is passed as an integer value, it is interpreted as a label of the index and not as an integer position along the index, for example:

>>> dFrame10Multiples = pd.DataFrame([10,20,30,40,50])
>>> dFrame10Multiples.loc[2]
0 $\quad $ 30
Name: 2, dtype: int64

When a single column label is passed, it returns the column as a Series.

>>> ResultDF.loc[:,‘Arnab’]

Maths $\quad$ 90
Science $\quad$ 91
Hindi $\quad$ 97
Name: Arnab, dtype: int64

Also, we can obtain the same result that is the marks of ‘Arnab’ in all the subjects by using the command:

>>> print(df[‘Arnab’])

Maths $\quad$ 56
Science $\quad$ 91
English$\quad$ 97
Hindi $\quad$ 97
Name: Arnab, dtype: int64

To read more than one row from a DataFrame, a list of row labels is used as shown below. Note that using [[]] returns a DataFrame.

>>> ResultDF.loc[[‘Science’, ‘Hindi’]]

Arnab Ramit Samridhi Riya Mallika
Science 91 81 91 71 95
Hindi 97 96 88 67 99

(B) Boolean Indexing

Boolean means a binary variable that can represent either of the two states - True (indicated by 1) or False (indicated by 0). In Boolean indexing, we can select the subsets of data based on the actual values in the DataFrame rather than their row/column labels. Thus, we can use conditions on column names to filter data values. Consider the DataFrame ResultDF, the following statement displays True or False depending on whether the data value satisfies the given condition or not.

>>> ResultDF.loc[‘Maths’] > 90
Arnab $\quad$ False
Ramit $\quad$ True
Samridhi $\quad$ False
Riya $\quad$ False
Mallika $\quad$ True
Name: Maths, dtype: bool

To check in which subjects ‘Arnab’ has scored more than 90, we can write:

>>> ResultDF.loc[:,‘Arnab’]>90
Maths $\quad$ False
Science $\quad$ True
Hindi $\quad$ True
Name: Arnab, dtype: bool

2.3.4 Accessing DataFrames Element through Slicing

We can use slicing to select a subset of rows and/or columns from a DataFrame. To retrieve a set of rows,slicing can be used with row labels. For example:

>>> ResultDF.loc[‘Maths’: ‘Science’]

Arnab Ramit Samridhi Riya Mallika
Maths 90 92 89 81 94
Science 91 81 91 71 95

Activity 2.8

a) Using the DataFrame ResultDF, write the statement to access Marks of Arnab in Maths.

b) Create a DataFrame having 5 rows and write the statement to get the first 4 rows of it.

Here, the rows with labels Maths and Science are displayed. Note that in DataFrames slicing is inclusive of the end values. We may use a slice of labels with a column name to access values of those rows in that column only. For example, the following statement displays the rows with label Maths and Science, and column with label Arnab:

>>> ResultDF.loc[‘Maths’: ‘Science’, ‘Arnab’]
Maths $\quad$ 90
Science $\quad$ 91
Name: Arnab, dtype: int64

We may use a slice of labels with a slice of column names to access values of those rows and columns:

>>> ResultDF.loc[‘Maths’: ‘Science’, ‘Arnab’:’Samridhi’]

Arnab Ramit Samridhi
Maths 90 92 89
Science 91 81 91

Alternatively, we may use a slice of labels with a list of column names to access values of those rows and columns:

>>> ResultDF.loc[‘Maths’: ‘Science’,[‘Arnab’,’Samridhi’]]

Arnab Samridhi
Maths 90 89
Science 91 91

Filtering Rows in DataFrames

In DataFrames, Boolean values like True (1) and False (0) can be associated with indices. They can also be used to filter the records using the DataFrmae.loc[] method.

In order to select or omit particular row(s), we can use a Boolean list specifying ‘True’ for the rows to be shown and ‘False’ for the ones to be omitted in the output. For example, in the following statement, row having index as Science is omitted:

>>> ResultDF.loc[[True, False, True]]

Arnab Ramit Samridhi Riya Mallika
Maths 90 92 89 81 94
Hindi 97 96 88 67 99

2.3.5 Joining, Merging and Concatenation of DataFrames

(A) Joining

We can use the pandas.DataFrame.append() method to merge two DataFrames. It appends rowsof the second DataFrame at the end of the first DataFrame. Columns not present in the first DataFrame are added as new columns. For example, consider the two DataFramesdFrame1 and dFrame2described below. Let us use theappend() method to append dFrame2 to dFrame1:

>>> dFrame1=pd.DataFrame([[1, 2, 3], [4, 5], [6]], columns=[‘C1’, ‘C2’, ‘C3’], index=[‘R1’, ‘R2’, ‘R3’])
>>> dFrame1

C1 C2 C3
R1 1 2.0 3.0
R2 4 5.0 NaN
R3 6 NaN NaN

>>> dFrame2=pd.DataFrame([[10, 20], [30], [40, 50]], columns=[‘C2’, ‘C5’], index=[‘R4’, ‘R2’, ‘R5’])
>>> dFrame2

C2 C3
R4 10 20.0
R2 30 NaN
R5 40 50.0

>>> dFrame1=dFrame1.append(dFrame2)
>>> dFrame1

C1 C2 C3 C5
R1 1.1 2.0 3.0 NaN
R2 4.0 5.0 NaN NaN
R3 6.0 NaN NaN NaN
R4 NaN 10.0 NaN 20.0
R2 NaN 30.0 NaN NaN
R5 NaN 40.0 NaN 50.0

Alternatively, if we append dFrame 1 to dFrame2, the rows of $d$ Frame 2 precede the rows of $d$ Frame 1 . To get the column labels appear in sorted order we can set the parameter sort=True. The column labels shall appear in unsorted order when the parameter sort $=$ False.

# append dFrame1 to dFrame2
>>> dFrame2 =dFrame2.append(dFrame1, sort=’True’)
>>> dFrame2

C1 C2 C3 C5
R4 NaN 10.0 NaN 20.0
R2 NaN 30.0 NaN NaN
R5 NaN 40.0 NaN 50.0
R1 1.0 2.0 3.0 NaN
R2 4.0 5.0 NaN NaN
R3 6.0 NaN NaN NaN

# append dFrame1 to dFrame2 with sort=False
>>> dFrame2 = dFrame2.append(dFrame1, sort=’False’)
>>> dFrame2

C1 C5 C1 C3
R4 10.0 20.0 NaN 20.0
R2 30.0 NaN NaN NaN
R5 40.0 50.0 NaN NaN
R1 2.0 NaN 1.0 3.0
R2 5.0 NaN 4.0 NaN
R3 NaN NaN 6.0 NaN

The parameter verify_integrity of append()method may be set to True when we want to raise an error if the row labels are duplicate. By default, verify_integrity $=$ False. That is why we could append the duplicate row with label R2 when appending the two DataFrames, as shown above.

Think and Reflect

How can you check whether a given DataFrame has any missing value or not?

The parameter ignore_index of append()method may be set to True, when we do not want to use row index labels. By default, ignore_index = False.

>>> dFrame1 = dFrame1.append(dFrame2, ignore_index=True)
>>> dFrame1

C1 C2 C3 C5
0 1.0 2.0 3.0 NaN
1 4.0 5.0 NaN NaN
2 6.0 NaN NaN NaN
3 NaN 10.0 NaN 20.0
4 NaN 30.0 NaN NaN
5 NaN 40.0 NaN 50.0

The append()method can also be used to append a series or a dictionary to a DataFrame.

2.3.6 Attributes of DataFrames

Like Series, we can access certain properties called attributes of a DataFrame by using that property with the DataFrame name. Table 2.4 lists some attributes of Pandas DataFrame. We are going to use a part of the data from a report called “STATE OF FOREST REPORT 2017”, Published by Forest Survey of India, accessible at http://fsi.nic.in/forest-report-2017, as our example data in this section.

As per this report, the geographical area, the area under very dense forests, the area under moderately Think and Reflect

How can you check whether a given DataFrame has any missing value or not? dense forests, and the area under open forests (all in sq $\mathrm{km}$ ), in three States of India - Assam, Delhi and Kerala are as shown in the following DataFrame ForestAreaDF:

>>> ForestArea = {
$\quad$‘Assam’ :pd.Series([78438, 2797,
10192, 15116], index = [‘GeoArea’, ‘VeryDense’,
ModeratelyDense’, ‘OpenForest’]),
$\quad$‘Kerala’ :pd.Series([ 38852, 1663,
9407, 9251], index = [‘GeoArea’ ,‘VeryDense’,
‘ModeratelyDense’, ‘OpenForest’]),
$\quad$ ‘Delhi’ :pd.Series([1483, 6.72, 56.24,
129.45], index = [‘GeoArea’, ‘VeryDense’,
‘$\quad$ModeratelyDense’, ‘OpenForest’])}

>>> ForestAreaDF = pd.DataFrame(ForestArea)
>>> ForestAreaDF

Assam Kerala Delhi
GeoArea 78438 38852 1483.00
VeryDense 2797 1663 6.72
ModeratelyDense 10192 9407 56.24
OpenForest 15116 9251 129.45

Table 2.4 Some Attributes of Pandas DataFrame

Attribute Name Purpose Example
DataFrame.index to display row labels »> ForestAreaDF.index
Index([‘GeoArea’, ‘VeryDense’,
‘ModeratelyDense’, ‘OpenForest’], dtype =’object’)
DataFrame.columns to display column labels »> ForestAreaDF.columns
Index([‘Assam’, ‘Kerala’, ‘Delhi’], dtype=’object’)
DataFrame.dtypes to display data type of each column in the DataFrame >>> ForestAreaDF.dtypes
Assam $\quad$ int64
Kerala $\quad$ int64
Delhi $\quad$ float64
dtype: object
DataFrame.values to display a NumPy ndarray having all the values in the DataFrame, without the axes labels >>> ForestAreaDF.values
array([[7.8438e+04, 3.8852e+04, 1.4830e+03],
$\qquad$ [2.7970e+03, 1.6630e+03, 6.7200e+00],
$\qquad$ [1.0192e+04, 9.4070e+03, 5.6240e+01],
$\qquad$ [1.5116e+04, 9.2510e+03, 1.2945e+02]])
DataFrame.shape to display a tuple representing the dimensionality of the DataFrame >>>ForestAreaDF.shape(4, 3)
It means ForestAreaDF has 4 rows and 3 columns.
DataFrame.size to display a tuple representing the dimensionality of the DataFrame >>> ForestAreaDF.size12
This means the ForestAreaDF has 12 values in it.
DataFrame.T to transpose the DataFrame. Means, row indices and column labels of the DataFrame replace each other’s position >>> ForestAreaDF.T
DataFrame.head(n) to display the first n rows in the DataFrame >>> ForestAreaDF.head(2)

displays the first 2 rows of the DataFrame ForestAreaDF.If the parameter n is not specified by default it gives the first 5 rows of the DataFrame.
DataFrame.tail(n) to display the last n rows in the DataFrame >>> ForestAreaDF.tail(2)

displays the last 2 rows of the DataFrame ForestAreaDF.If the parameter n is not specified by default it gives the last 5 rows of the DataFrame.
to returns the value True if DataFrame is empty and Falseotherwise >>> ForestAreaDF.empty
False
>>> df=pd.DataFrame() #Create an empty dataFrame
>>> df.empty
True

2.4 Importing and Exporting Data between CSV Files and DataFrames

We can create a DataFrame by importing data from CSV files where values are separated by commas. Similarly, we can also store or export data in a DataFrame as a .csv file.

2.4.1 Importing a CSV file to a DataFrame

Let us assume that we have the following data in a csv file named ResultData.csv stored in the folder C:/NCERT. In order to practice the code while we progress, you are suggested to create this csv file using a spreadsheet and save in your computer.

Roll No Name Eco Maths
1 Arnab 18 57
2 Kritika 23 45
3 Divyam 51 37
4 Vivaan 40 60
5 Aaroosh 18 27

We can load the data from the ResultData.csv file into a DataFrame, say marks using Pandas read_csv() function as shown below:

>>> marks = pd.read_csv(“C:/NCERT/ResultData.csv”,sep =",", header=0)
>>> marks

Roll No Name Eco Maths
0 1 Arnab 18 57
1 2 Kritika 23 45
2 3 Divyam 51 37
3 4 Vivaan 40 60
4 5 Aaroosh 18 27
  • The first parameter to the read_csv() is the name of the comma separated data file along with its path.
  • The parameter sep specifies whether the values are separated by comma, semicolon, tab, or any other character. The default value for sepis a space.
  • The parameter header specifies the number of the row whose values are to be used as the column names. It also marks the start of the data to be fetched. header $=0$ implies that column names are inferred from the first line of the file. By default, header $=0$.

We can exclusively specify column names using the parameter names while creating the DataFrame using the read_csv() function. For example, in the following statement, names parameter is used to specify the labels for columns of the DataFrame marks1:

>>> marks1 = pd.read_csv(“C:/NCERT/ResultData1.
csv”,sep=",",
names=[‘RNo’,‘StudentName’, ‘Sub1’, ‘Sub2’])
>>> marks1

Roll No StudentName Sub1 Sub2
0 1 Arnab 18 57
1 2 Kritika 23 45
2 3 Divyam 51 37
3 4 Vivaan 40 60
4 5 Aaroosh 18 27

2.4.2 Exporting a DataFrame to a CSV file

We can use the to_csv() function to save a DataFrame to a text or csv file. For example, to save the DataFrame ResultDF created in the previous section; we can use the following statement:[^1]

Arnab Ramit Samridhi Riya Mallika
Maths 90 92 89 81 94
Science 91 81 91 71 95
Hindi 97 96 88 67 99

>>> ResultDF.to_csv(path_or_buf=‘c:/NCERT/ resultout. csv’, sep $=$ ‘, ‘)

A CommaSeparated Value (CSV) file is a text file where values are separated by comma. Each line represents a record (row). Each row consists of one or more fields (columns). They can be easily handled through a spreadsheet application

This creates a file by the name resultout.csv in the folder C:/NCERT on the hard disk. When we open this file in any text editor or a spreadsheet, we will find the above data along with the row labels and the column headers, separated by comma.

In case we do not want the column names to be saved to the file we may use the parameter header=False. Another parameter index=False is used when we do not want the row labels to be written to the file on disk. For example:

Think and Reflect

What are the other parameters that can be used with read_csv() function? You may explore from https://pandas.pydata.org.

>>> ResultDF.to_CSv( ‘C:/NCERT/resultonly.txt’, sep = ’ @’, header = False, index= Falsel

If we open the file resultonly.txt, we will find the following contents:

$90 @ 92 @ 89 @ 81 @ 94$

$91 @ 81 @ 91 @ 71 @ 95$

$97 @ 96 @88 @67@99$

Think and Reflect

Besides comma, what are the other allowed characters that can be used as a separator while creating a CSV file frmo a DataFrame?

2.5 Pandas Series Vs NumPy ndarray

Pandas supports non-unique index values. If an operation that does not support duplicate index values is attempted, an exception will be raised at that time.

A basic difference between Series and ndarray is that operations between Series automatically align the data based on the label. Thus, we can write computations without considering whether all Series involved have the same label or not.

The result of an operation between unaligned Series (i.e. where the corresponding labels of the series are not the same or are not in the same order) will have the union of the indexes involved. If a label is not found in one Series or the other, the result will be marked as missing NaN. Being able to write code without doing any explicit data alignment grants immense freedom and flexibility in interactive data analysis and research.

Table 2.5 Difference between Pandas Series and NumPy Arrays

Pandas Series NumPy Arrays
In series we can define our own labeled index to access elements of an array. These can be numbers or letters NumPy arrays are accessed by their integer position using numbers only.
The elements can be indexed in descending order also. The indexing starts with zero for the first element and the index is fixed.
If two series are not aligned, NaN or missing values are generated. There is no concept of NaN values and if there are no matching values in arrays, alignment fails.
Series require more memory. NumPy occupies lesser memory.

SUMMARY

  • NumPy, Pandas and Matplotlib are Python libraries for scientific and analytical use.
  • pip install pandas is the command to install Pandas library.
  • A data structure is a collection of data values and the operations that can be applied to that data. It enables efficient storage, retrieval and modification to the data.
  • Two main data structures in Pandas library are Series and DataFrame. To use these data structures, we first need to import the Pandas library.
  • A Series is a one-dimensional array containing a sequence of values. Each value has a data label associated with it also called its index.
  • The two common ways of accessing the elements of a series are Indexing and Slicing.
  • There are two types of indexes: positional index and labelled index. Positional index takes an integer value that corresponds to its position in the series starting from 0 , whereas labelled index takes any user-defined label as index.
  • When positional indices are used for slicing, the value at end index position is excluded, i.e., only (end - start) number of data values of the series are extracted. However with labelled indexes the value at the end index label is also included in NOTES the output.
  • All basic mathematical operations can be performed on Series either by using the operator or by using appropriate methods of the Series object.
  • While performing mathematical operations index matching is implemented and if no matching indexes are found during alignment, Pandas returns NaN so that the operation does not fail.
  • A DataFrame is a two-dimensional labeled data structure like a spreadsheet. It contains rows and columns and therefore has both a row and column index.
  • When using a dictionary to create a DataFrame, keys of the Dictionary become the column labels of the DataFrame. A DataFrame can be thought of as a dictionary of lists/ Series (all Series/columns sharing the same index label for a row).
  • Data can be loaded in a DataFrame from a file on the disk by using Pandas read_csv function.
  • Data in a DataFrame can be written to a text file on disk by using the pandas.DataFrame.to_ $\operatorname{csv}($ ) function.
  • DataFrame.T gives the transpose of a DataFrame.
  • Pandas haves a number of methods that support label based indexing but every label asked for must be in the index, or a KeyError will be raised.
  • DataFrame.loc[ ] is used for label based indexing of rows in DataFrames.
  • Pandas.DataFrame.append() method is used to merge two DataFrames.
  • Pandas supports non-unique index values. Only if a particular operation that does not support duplicate index values is attempted, an exception is raised at that time.
  • The basic difference between Pandas Series and NumPy ndarray is that operations between Series automatically align the data based on labels. Thus, we can write computations without considering whether all Series involved have the same label or not whereas in case of ndarrays it raises an error.

Exercise

1. What is a Series and how is it different from a 1-D array, a list and a dictionary?
2. What is a DataFrame and how is it different from a 2-D array?
3. How are DataFrames related to Series?
4. What do you understand by the size of (i) a Series, (ii) a DataFrame?
5. Create the following Series and do the specified operations:

a) EngAlph, having 26 elements with the alphabets as values and default index values.

b) Vowels, having 5 elements with index labels ’ $a$ ‘, ’ $e$ ‘, ’ $\mathrm{i}$ ‘, ’ $\mathrm{o}$ ’ and ’ $\mathrm{u}$ ’ and all the five values set to zero. Check if it is an empty series.

c) Friends, from a dictionary having roll numbers of five of your friends as data and their first name as keys.

d) MTseries, an empty Series. Check if it is an empty series.

e) MonthDays, from a numpy array having the number of days in the 12 months of a year. The labels should be the month numbers from 1 to 12 .

6. Using the Series created in Question 5, write commands for the following:

a) Set all the values of Vowels to 10 and display the Series.

b) Divide all values of Vowels by 2 and display the Series.

c) Create another series Vowels 1 having 5 elements with index labels ’ $a$ ‘, ’ $e$ ‘, ’ $i$ ‘, ’ $o$ ’ and ’ $u$ ’ having values $[2,5,6,3,8]$ respectively.

d) Add Vowels and Vowels 1 and assign the result to Vowels3.

e) Subtract, Multiply and Divide Vowels by Vowels 1.

f) Alter the labels of Vowels 1 to [‘A’, ’ $E$ ‘, ‘I’, ’ $O$ ‘, ’ $U$ ‘].

7. Using the Series created in Question 5, write commands for the following:

a) Find the dimensions, size and values of the Series EngAlph, Vowels, Friends, MTseries, MonthDays.

b) Rename the Series MTseries as SeriesEmpty.

c) Name the index of the Series MonthDays as monthno and that of Series Friends as Fname. d) Display the 3rd and 2nd value of the Series Friends, in that order.

e) Display the alphabets ’ $e$ ’ to ’ $p$ ’ from the Series EngAlph.

f) Display the first 10 values in the Series EngAlph.

g) Display the last 10 values in the Series EngAlph.

h) Display the MTseries.

8. Using the Series created in Question 5, write commands for the following:

a) Display the names of the months 3 through 7 from the Series MonthDays.

b) Display the Series MonthDays in reverse order.

9. Create the following DataFrame Sales containing year wise sales figures for five sales persons in INR. Use the years as column labels, and sales person names as row labels.

$\mathbf{2 0 1 4}$ $\mathbf{2 0 1 5}$ $\mathbf{2 0 1 6}$ $\mathbf{2 0 1 7}$
Madhu 100.5 12000 20000 50000
Kusum 150.8 18000 50000 60000
Kinshuk 200.9 22000 70000 70000
Ankit 30000 30000 100000 80000
Shruti 40000 45000 125000 90000

10. Use the DataFrame created in Question 9 above to do the following:

a) Display the row labels of Sales.

b) Display the column labels of Sales.

c) Display the data types of each column of Sales.

d) Display the dimensions, shape, size and values of Sales.

e) Display the last two rows of Sales.

f) Display the first two columns of Sales.

g) Create a dictionary using the following data. Use this dictionary to create a DataFrame Sales2.

2018
Madhu 160000
Kusum 110000
Kinshuk 500000
Ankit 340000
Shruti 900000

h) Check if Sales2 is empty or it contains data.

11. Use the DataFrame created in Question 9 above to do the following:

a) Append the DataFrame Sales2 to the DataFrame Sales.

b) Change the DataFrame Sales such that it becomes its transpose.

c) Display the sales made by all sales persons in the year 2017.

d) Display the sales made by Madhu and Ankit in the year 2017 and 2018 .

e) Display the sales made by Shruti 2016 .

f) Add data to Sales for salesman Sumeet where the sales made are [196.2, 37800, 52000, 78438, 38852] in the years $[2014,2015,2016,2017$, 2018] respectively.

g) Delete the data for the year 2014 from the DataFrame Sales.

h) Delete the data for sales man Kinshuk from the DataFrame Sales.

i) Change the name of the salesperson Ankit to Vivaan and Madhu to Shailesh.

j) Update the sale made by Shailesh in 2018 to 100000 .

k) Write the values of DataFrame Sales to a comma separated file SalesFigures.csv on the disk. Do not write the row labels and column labels.

  1. Read the data in the file SalesFigures.csv into a DataFrame SalesRetrieved and Display it. Now update the row labels and column labels of SalesRetrieved to be the same as that of Sales.


विषयसूची