df[]
iloc
Methodloc
Method
import numpy as np
import pandas as pd
df = pd.read_csv('datasets/groupdata.csv')
df
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
6 | MS07 | Zobia | 40 | Sialkot | AFT | group B | Female | 90.2 | NaN | 4000.0 |
7 | MS08 | Idrees | 51 | Multan | MORNING | group D | Male | 84.1 | 76.0 | 8000.0 |
8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
9 | MS10 | Shahid | 38 | Lahore | AFTERNOON | group D | Male | 90.5 | 81.3 | 3800.0 |
10 | MS11 | Khurram | 35 | Islamabad | MOR | group B | Male | 90.5 | 81.3 | 6000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
14 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
# `shape` attribute of a dataframe object return a two value tuple containing rows and columns
# Note the rows count does not include the column labels and column count does not include the row index
df.shape
(16, 10)
# `index` attribute of a dataframe object return the list of row indices and its datatype
df.index
RangeIndex(start=0, stop=16, step=1)
# `columns` attribute of a dataframe object return the list of column labels and its datatype
df.columns
Index(['roll no', 'name', 'age', 'address', 'session', 'group', 'gender', 'subj1', 'subj2', 'scholarship'], dtype='object')
# `dtypes` attribute of a dataframe object return the data type of each column in the dataframe
df.dtypes
roll no object name object age int64 address object session object group object gender object subj1 float64 subj2 float64 scholarship float64 dtype: object
#This method prints information about a DataFrame including the row indices, column labels,
# non-null values count in each column, datatype and memory usage
df.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 16 entries, 0 to 15 Data columns (total 10 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 roll no 16 non-null object 1 name 16 non-null object 2 age 16 non-null int64 3 address 16 non-null object 4 session 16 non-null object 5 group 16 non-null object 6 gender 16 non-null object 7 subj1 15 non-null float64 8 subj2 15 non-null float64 9 scholarship 14 non-null float64 dtypes: float64(3), int64(1), object(6) memory usage: 1.4+ KB
# If no argument is passed, this method displays descriptive statistics about the numeric columns of the dataframe
df.describe(include='all')
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
count | 16 | 16 | 16.000000 | 16 | 16 | 16 | 16 | 15.000000 | 15.000000 | 14.000000 |
unique | 16 | 16 | NaN | 6 | 4 | 4 | 2 | NaN | NaN | NaN |
top | MS01 | Rauf | NaN | Lahore | AFT | group C | Male | NaN | NaN | NaN |
freq | 1 | 1 | NaN | 5 | 5 | 5 | 10 | NaN | NaN | NaN |
mean | NaN | NaN | 36.062500 | NaN | NaN | NaN | NaN | 82.153333 | 78.133333 | 5328.571429 |
std | NaN | NaN | 12.228212 | NaN | NaN | NaN | NaN | 9.885261 | 5.964378 | 1888.600854 |
min | NaN | NaN | 16.000000 | NaN | NaN | NaN | NaN | 64.900000 | 60.500000 | 3500.000000 |
25% | NaN | NaN | 27.250000 | NaN | NaN | NaN | NaN | 74.400000 | 76.000000 | 3800.000000 |
50% | NaN | NaN | 36.500000 | NaN | NaN | NaN | NaN | 84.100000 | 81.300000 | 4500.000000 |
75% | NaN | NaN | 44.250000 | NaN | NaN | NaN | NaN | 90.500000 | 81.300000 | 6750.000000 |
max | NaN | NaN | 53.000000 | NaN | NaN | NaN | NaN | 90.500000 | 84.400000 | 8500.000000 |
import pandas as pd
df = pd.read_csv('datasets/groupdata.csv')
df.head()
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
df = pd.read_csv('datasets/groupdatawithoutcollables.csv')
df.head()
MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000 | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
1 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
2 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
3 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
4 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
# To read such files, you have to pass the parameter `header=None` to the `read_csv()` method
df = pd.read_csv('datasets/groupdatawithoutcollables.csv', header=None)
df.head()
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
Let us suppose we have above dataframe, in which the column indices are just integer values associated with the position of every column. We want to assign some meaningful names to the columns for better understanding. There are many options or ways to do that.
Changing Column IndicesLabels: Assign a list of column labels to the columns
attribute of dataframe
col_names = ['roll no', 'name', 'age', 'address', 'session', 'group', 'gender', 'subj1', 'subj2', 'scholarship']
df.columns = col_names
df.head()
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
Note that in the above dataframe, first column name has a space, which is a bit difficult to use sometimes, so if you want to change value of a specific column label, you can use the
df.rename()
method
# You pass a dictionary object to the columns argument to rename() method
# The key is the old column name, while the value is the new column name
df1 = df.rename(columns={'roll no': 'rollno'}, inplace=False)
df1.head(3)
rollno | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
Last but not the least, another way is to assign appropriate column labels to your dataframes by passing a list of column names to the
names
argument of thedf.read_csv()
method. Do it at your own :)
df.set_index()
and df.reset_index()
# Let us load the dataset again into dataframe
import pandas as pd
df = pd.read_csv('datasets/groupdata.csv')
df.head(3)
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
df.index
RangeIndex(start=0, stop=16, step=1)
Let us suppose we have above dataframe, in which the row indices are just integer values associated with the position of every row. We want to assign some meaningful indices to the rows for better understanding. Suppose, we want to set values of the column rollno as index of this dataframe. So we donot want positional indices rather want some meaningful string indices, which are roll numbers of students in this case.
Changing Row Indices: The df.set_index()
method can be used to change row index of a dataframe using an existing column(s)
df.set_index(keys, drop=True, append=False, inplace=False, verify_integrity=False)
keys
is the column labeldrop=True
, to drop the column from data part of dataframe that is being used as column index nowappend=False
, Set it to True if you want to append columns to existing indexinplace=False
, Set it to True to make changes in the original dataframe, i.e., do not create a new objectverify_integrity=False
, Set it to True to check the new index for duplicates. Default value of False will improve the performance of this method.Returns Dataframe if inplace=False
or None if inplace=True
df1 = df.set_index(keys='roll no', drop=False)
df1.head(3)
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
roll no | ||||||||||
MS01 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
MS02 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
MS03 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
Note: The rollno column still exist as part of the dataframe. To drop it set drop=True
df1 = df.set_index(keys='roll no', drop=True)
df1.head(3)
name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|
roll no | |||||||||
MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
Another point to note is that no change has been made to the original dataframe since inplace
arguement is by default False. Let us verify this
df.head()
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
Instead of returning a dataframe, the df.set_index()
method can change the column index inplace. Let us do that now
df.set_index(keys='roll no', drop=True, inplace=True)
df.head()
name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|
roll no | |||||||||
MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
Note that the roll no column has become the index now, it is no more data of the dataframe
df.index
Index(['MS01', 'MS02', 'MS03', 'MS04', 'MS05', 'MS06', 'MS07', 'MS08', 'MS09', 'MS10', 'MS11', 'MS12', 'MS13', 'MS14', 'MS15', 'MS16'], dtype='object', name='roll no')
Changing Row Indices Back to Positional: The df.reset_index()
method is used to reset the row index of the dataframe back to positional integer indices
df.reset_index(drop=False, inplace=False)
Where
drop=False
, Do not try to insert index into dataframe columns. This resets the index to the default integer index.inplace=False
, Modify the DataFrame in place (do not create a new object).Returns Dataframe if inplace=False
or None if inplace=True
df.head()
name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|
roll no | |||||||||
MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
df.shape
(16, 9)
# reset the index
df2 = df.reset_index()
df2.index
RangeIndex(start=0, stop=16, step=1)
df2.head(3)
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
df3 = df2.reset_index()
df3
index | roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | 1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | 2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
3 | 3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
4 | 4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
5 | 5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
6 | 6 | MS07 | Zobia | 40 | Sialkot | AFT | group B | Female | 90.2 | NaN | 4000.0 |
7 | 7 | MS08 | Idrees | 51 | Multan | MORNING | group D | Male | 84.1 | 76.0 | 8000.0 |
8 | 8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
9 | 9 | MS10 | Shahid | 38 | Lahore | AFTERNOON | group D | Male | 90.5 | 81.3 | 3800.0 |
10 | 10 | MS11 | Khurram | 35 | Islamabad | MOR | group B | Male | 90.5 | 81.3 | 6000.0 |
11 | 11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
12 | 12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
13 | 13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
14 | 14 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
15 | 15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
df[]
¶[]
, which in this case are strings, however, can be integer values as well. It will return a new Series object.[]
. It will return a new Dataframe object.[start:stop:step]
, (In this case the positional indices do not match with the actual row indices). It will return a new Dataframe object.start
: specifies from where the slicing should start, inclusive (default is 0)stop
: specifies where it has to stop, exclusive (default is end of the array)step
: is by-default 1Note:
df[2][5]
to access a specific element of a dataframe as in case of numPy arrays.df[]
, it can either return a subset of columns only or rows only.df.loc[]
and df.iloc[]
methods that provides simpler, elegant and powerful way to subset a dataframe as compared to df[]
syntax.import pandas as pd
df_sorted
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
14 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
10 | MS11 | Khurram | 35 | Islamabad | MOR | group B | Male | 90.5 | 81.3 | 6000.0 |
9 | MS10 | Shahid | 38 | Lahore | AFTERNOON | group D | Male | 90.5 | 81.3 | 3800.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
6 | MS07 | Zobia | 40 | Sialkot | AFT | group B | Female | 90.2 | NaN | 4000.0 |
15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
7 | MS08 | Idrees | 51 | Multan | MORNING | group D | Male | 84.1 | 76.0 | 8000.0 |
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
# Let us read a fresh dataframe and sort it by age column to have a clear understanding about indexing
df = pd.read_csv('datasets/groupdata.csv')
df_sorted = df.sort_values('age')
df_sorted.head()
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
df_sorted[1:2]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
Example 1: Select the data under the column name
. Since the column labels are strings, so we mention it in single quotes.
s1 = df_sorted['name']
print(s1)
type(s1)
5 Mohid 12 Mujahid 3 Hadeed 11 Maaz 13 Sara 14 Fatima 2 Shaista 10 Khurram 9 Shahid 4 Zara 6 Zobia 15 Kakamanna 1 Arif 7 Idrees 0 Rauf 8 Jamil Name: name, dtype: object
pandas.core.series.Series
The result is a new Series object. Since this is a series, so you can use many of the methods that you can use on Pandas Series and Dataframes, in a chained format, as shown below
s1.head()
# or
df['name'].head()
0 Rauf 1 Arif 2 Shaista 3 Hadeed 4 Zara Name: name, dtype: object
Example 2: To select multiple columns of a dataframe, pass a list of column names. The result is a new DataFrame object with the selected columns.
# Just get the first five rows for the columns `roll no`, `gender` and `age`
d1 = df_sorted[['roll no', 'gender', 'age']].head()
d1
roll no | gender | age | |
---|---|---|---|
5 | MS06 | Female | 16 |
12 | MS13 | Male | 18 |
3 | MS04 | Male | 20 |
11 | MS12 | Male | 25 |
13 | MS14 | Female | 28 |
Example 3: Select the data of a single row at position 1.
df_sorted[1:2:1]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
Point to note that in
df_sorted
dataframe at position 1, we have record of Muhahid having dataframe index 12
Example 4: Select the rows from positional index 2 to 3.
df_sorted[2:4]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
Example 5: Select the rows from positional index 0, 5, 10, and 15
df_sorted[::5]
5 Mohid 14 Fatima 6 Zobia 8 Jamil Name: name, dtype: object
Note that the output dataframe contains a subset of original dataframe. However, the index or row labels stays with the rows. It is not renumbered. So this means that every row is identified by a row label, which remain associated wih the row or record until you decide to reset the index
df.reset_index()
method discussed above as well.df2 = df_sorted[5:12:2]
df2
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
14 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
10 | MS11 | Khurram | 35 | Islamabad | MOR | group B | Male | 90.5 | 81.3 | 6000.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
df3 = df2.reset_index()
df3
index | roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 14 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
1 | 10 | MS11 | Khurram | 35 | Islamabad | MOR | group B | Male | 90.5 | 81.3 | 6000.0 |
2 | 4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
3 | 15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
Note that the index has been reset, however, the old index is now added as a column in the dataframe. Mostly this is not required, so pass the
drop=True
argument toreset_index()
method to avoid this.
df4 = df2.reset_index(drop=True)
df4
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
1 | MS11 | Khurram | 35 | Islamabad | MOR | group B | Male | 90.5 | 81.3 | 6000.0 |
2 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
3 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
iloc
Method¶df.iloc[]
is more powerful than the df[]
, as it allows to filter rows as well as columns of user choice at the same time.age
rather you need to give its positional index and that is 2.df.iloc[rowstoselect, colstoselect]
[ , ]
are:5
(note that 5
is interpreted as an integer position along the index).[9, 2, 7]
.2:9
..iloc
will raise IndexError
if a requested indexer is out-of-bounds, except slice indexers which allow out-of-bounds indexing (this conforms with python/numpy slice semantics).
# Let us read a fresh dataframe and sort it by age column to have a clear understanding about indexing
import pandas as pd
df = pd.read_csv('datasets/groupdata.csv')
df_sorted = df.sort_values('age')
df_sorted.head()
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
Example 1: Select a single row with positional index 2 and all the columns
df_sorted.iloc[2,:]
roll no MS04 name Hadeed age 20 address Lahore session MOR group group A gender Male subj1 82.0 subj2 84.3 scholarship 4000.0 Name: 3, dtype: object
Note that the integer values are interpreted as row# (positional index) of the dataframe. Moreover a Series object is returned
Example 2: Select rows with positional indices 2, 4 and 1 and all the columns
df_sorted.iloc[[2,4,1], :]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
Example 3: Select rows with positional indices from 3 to 5 (stop value is not inclusive) and all the columns
df_sorted.iloc[3:5, :]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
In all above examples, if you omit comma and collon for the columns part, Pandas assumes it. I strongly recommend using the above style for clarity of code.
df_sorted.columns
Index(['roll no', 'name', 'age', 'address', 'session', 'group', 'gender', 'subj1', 'subj2', 'scholarship'], dtype='object')
Example 1: Select all the row values under the column at positional index 3
df_sorted.iloc[:, 3]
5 Lahore 12 Lahore 3 Lahore 11 Karachi 13 Multan 14 Sialkot 2 Karachi 10 Islamabad 9 Lahore 4 Peshawer 6 Sialkot 15 Multan 1 Islamabad 7 Multan 0 Lahore 8 Karachi Name: address, dtype: object
Example 2: Select all the row values under the column at positional index 1, 4, and 5
df_sorted.iloc[:, [1,4,5]]
name | session | group | |
---|---|---|---|
5 | Mohid | MORNING | group C |
12 | Mujahid | MORNING | group D |
3 | Hadeed | MOR | group A |
11 | Maaz | AFTERNOON | group C |
13 | Sara | AFTERNOON | group A |
14 | Fatima | AFT | group C |
2 | Shaista | AFTERNOON | group B |
10 | Khurram | MOR | group B |
9 | Shahid | AFTERNOON | group D |
4 | Zara | AFT | group D |
6 | Zobia | AFT | group B |
15 | Kakamanna | AFTERNOON | group A |
1 | Arif | AFT | group A |
7 | Idrees | MORNING | group D |
0 | Rauf | MORNING | group C |
8 | Jamil | AFT | group C |
Example 3: Select all the row values under the columns from position 2 to 4 (Note that the stop index is not inclusive)
df_sorted.iloc[:, 2:5]
age | address | session | |
---|---|---|---|
5 | 16 | Lahore | MORNING |
12 | 18 | Lahore | MORNING |
3 | 20 | Lahore | MOR |
11 | 25 | Karachi | AFTERNOON |
13 | 28 | Multan | AFTERNOON |
14 | 33 | Sialkot | AFT |
2 | 35 | Karachi | AFTERNOON |
10 | 35 | Islamabad | MOR |
9 | 38 | Lahore | AFTERNOON |
4 | 40 | Peshawer | AFT |
6 | 40 | Sialkot | AFT |
15 | 42 | Multan | AFTERNOON |
1 | 51 | Islamabad | AFT |
7 | 51 | Multan | MORNING |
0 | 52 | Lahore | MORNING |
8 | 53 | Karachi | AFT |
df.iloc[whatrowsIwant, whatcolumnsIwant]
Example 1: Select only the rows at positional index 3 and 0, and from those two rows select only columns at positional index 1 and 5
df_sorted.iloc[[3, 0], [1, 5]]
name | group | |
---|---|---|
11 | Maaz | group C |
5 | Mohid | group C |
Example 2: Select only the rows at positional index 0 to 4 (stop index is not inclusive), and from those two rows select only columns at positional index 2 and 3
df_sorted.iloc[0:5, 2:4]
age | address | |
---|---|---|
5 | 16 | Lahore |
12 | 18 | Lahore |
3 | 20 | Lahore |
11 | 25 | Karachi |
13 | 28 | Multan |
df.loc[]
Method¶df.loc[]
is also used for filtering rows and selecting columns but by row index value/label or by column index value/label (NOT by position).df.loc[rowstoselect, colstoselect]
[ , ]
are:5
or 'a'
, (note that 5
is interpreted as actual index/label NOT as an integer position along the index).[9, 2, 7]
or ['ms07', 'ms02', 'ms08']
.[3:6:2]
or ['ms05':'ms09']
.# Let us read a fresh dataframe and sort it by age column to have a clear understanding about indexing
import pandas as pd
df = pd.read_csv('datasets/groupdata.csv')
df_sorted = df.sort_values('age')
df_sorted.head()
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
Example 1: Select a single row with row index 2 and all the columns
df_sorted.loc[2,:]
roll no MS03 name Shaista age 35 address Karachi session AFTERNOON group group B gender Female subj1 64.9 subj2 75.1 scholarship 8500.0 Name: 2, dtype: object
Example 2: Select rows with row indices 2, 4 and 1 and all the columns
df_sorted.loc[[2,4,1], :]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
Example 3: Select rows with row indices from 5 to 3 (stop value is inclusive) and all the columns
df_sorted.loc[5:2, :]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
14 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
df_sorted.loc[5:3, :]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
Question: If you give the slice as 3:5
, it will select no row. Can anyone guess why it is so?
df_sorted.loc[3:5:-1, :]
3 group A 12 group D 5 group C Name: group, dtype: object
In all above examples, if you omit comma and collon for the columns part, Pandas assumes it. I strongly recommend using the above style for clarity of code.
df_sorted.columns
Index(['roll no', 'name', 'age', 'address', 'session', 'group', 'gender', 'subj1', 'subj2', 'scholarship'], dtype='object')
Example 1: Select all the row values under the column with label name
df_sorted.loc[:, 'name']
5 Mohid 12 Mujahid 3 Hadeed 11 Maaz 13 Sara 14 Fatima 2 Shaista 10 Khurram 9 Shahid 4 Zara 6 Zobia 15 Kakamanna 1 Arif 7 Idrees 0 Rauf 8 Jamil Name: name, dtype: object
Example 2: Select all the row values under the columns with labels name
, address
, and scholarship
df_sorted.loc[:, ['name', 'address', 'scholarship']]
name | address | scholarship | |
---|---|---|---|
5 | Mohid | Lahore | NaN |
12 | Mujahid | Lahore | 7000.0 |
3 | Hadeed | Lahore | 4000.0 |
11 | Maaz | Karachi | NaN |
13 | Sara | Multan | 8000.0 |
14 | Fatima | Sialkot | 3500.0 |
2 | Shaista | Karachi | 8500.0 |
10 | Khurram | Islamabad | 6000.0 |
9 | Shahid | Lahore | 3800.0 |
4 | Zara | Peshawer | 3500.0 |
6 | Zobia | Sialkot | 4000.0 |
15 | Kakamanna | Multan | 3800.0 |
1 | Arif | Islamabad | 6000.0 |
7 | Idrees | Multan | 8000.0 |
0 | Rauf | Lahore | 5000.0 |
8 | Jamil | Karachi | 3500.0 |
Example 3: Select all the row values under the columns from a column range given as column labels (Note that the stop index is inclusive)
df_sorted.loc[:, 'address':'name']
5 |
---|
12 |
3 |
11 |
13 |
14 |
2 |
10 |
9 |
4 |
6 |
15 |
1 |
7 |
0 |
8 |
df_sorted.loc[:, 'name':'address']
name | age | address | |
---|---|---|---|
5 | Mohid | 16 | Lahore |
12 | Mujahid | 18 | Lahore |
3 | Hadeed | 20 | Lahore |
11 | Maaz | 25 | Karachi |
13 | Sara | 28 | Multan |
14 | Fatima | 33 | Sialkot |
2 | Shaista | 35 | Karachi |
10 | Khurram | 35 | Islamabad |
9 | Shahid | 38 | Lahore |
4 | Zara | 40 | Peshawer |
6 | Zobia | 40 | Sialkot |
15 | Kakamanna | 42 | Multan |
1 | Arif | 51 | Islamabad |
7 | Idrees | 51 | Multan |
0 | Rauf | 52 | Lahore |
8 | Jamil | 53 | Karachi |
df_sorted.loc[:, 'name':'address':2]
name | address | |
---|---|---|
5 | Mohid | Lahore |
12 | Mujahid | Lahore |
3 | Hadeed | Lahore |
11 | Maaz | Karachi |
13 | Sara | Multan |
14 | Fatima | Sialkot |
2 | Shaista | Karachi |
10 | Khurram | Islamabad |
9 | Shahid | Lahore |
4 | Zara | Peshawer |
6 | Zobia | Sialkot |
15 | Kakamanna | Multan |
1 | Arif | Islamabad |
7 | Idrees | Multan |
0 | Rauf | Lahore |
8 | Jamil | Karachi |
df.iloc[whatrowsIwant, whatcolumnsIwant]
Example 1: Select rows with row indices 3 and 0, and from those two rows select only columns at column labels name
and address
df_sorted.loc[[3, 0], ['name', 'address']]
name | address | |
---|---|---|
3 | Hadeed | Lahore |
0 | Rauf | Lahore |
Example 2: Select rows with row indices 0 to 5 (Stop index is inclusive), and from those six rows select columns name
, age
and session
df_sorted.loc[3:13, ['name', 'age', 'session']]
name | age | session | |
---|---|---|---|
3 | Hadeed | 20 | MOR |
11 | Maaz | 25 | AFTERNOON |
13 | Sara | 28 | AFTERNOON |
Question: You might be expecting 3:13
will return 11 rows, but it has returned only three. Can anyone guess why it is so?
df_sorted.loc[5:8, ['name', 'age', 'session']]
name | age | session | |
---|---|---|---|
5 | Mohid | 16 | MORNING |
12 | Mujahid | 18 | MORNING |
3 | Hadeed | 20 | MOR |
11 | Maaz | 25 | AFTERNOON |
13 | Sara | 28 | AFTERNOON |
14 | Fatima | 33 | AFT |
2 | Shaista | 35 | AFTERNOON |
10 | Khurram | 35 | MOR |
9 | Shahid | 38 | AFTERNOON |
4 | Zara | 40 | AFT |
6 | Zobia | 40 | AFT |
15 | Kakamanna | 42 | AFTERNOON |
1 | Arif | 51 | AFT |
7 | Idrees | 51 | MORNING |
0 | Rauf | 52 | MORNING |
8 | Jamil | 53 | AFT |
The range 5:8
has returned all the 16 rows in the dataframe :)
Always keep the row indices of your dataframe as 0, 1, 2, 3, 4, ... and the column indices as meaningful labels. If after a slicing or sorting operation, the row indices are a bit disturbed, use
df.reset_index()
method to adjust your row indices match the positional indices.
# Let us read a fresh dataframe and sort it by age column to have a clear understanding about indexing
import pandas as pd
df = pd.read_csv('datasets/groupdata.csv')
df
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
6 | MS07 | Zobia | 40 | Sialkot | AFT | group B | Female | 90.2 | NaN | 4000.0 |
7 | MS08 | Idrees | 51 | Multan | MORNING | group D | Male | 84.1 | 76.0 | 8000.0 |
8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
9 | MS10 | Shahid | 38 | Lahore | AFTERNOON | group D | Male | 90.5 | 81.3 | 3800.0 |
10 | MS11 | Khurram | 35 | Islamabad | MOR | group B | Male | 90.5 | 81.3 | 6000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
14 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
df.age
0 52 1 51 2 35 3 20 4 40 5 16 6 40 7 51 8 53 9 38 10 35 11 25 12 18 13 28 14 33 15 42 Name: age, dtype: int64
list1 = []
for length in df.age:
if length > 40:
list1.append(True)
else:
list1.append(False)
print(list1)
[True, True, False, False, False, False, False, True, True, False, False, False, False, False, False, True]
df[list1]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
7 | MS08 | Idrees | 51 | Multan | MORNING | group D | Male | 84.1 | 76.0 | 8000.0 |
8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
df[cond]
operator, that will automatically generate the Boolean list.df[df['age']>40]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
7 | MS08 | Idrees | 51 | Multan | MORNING | group D | Male | 84.1 | 76.0 | 8000.0 |
8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
df[df.age >40]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
7 | MS08 | Idrees | 51 | Multan | MORNING | group D | Male | 84.1 | 76.0 | 8000.0 |
8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
df.loc[cond]
method.df.loc[df.age > 40]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
7 | MS08 | Idrees | 51 | Multan | MORNING | group D | Male | 84.1 | 76.0 | 8000.0 |
8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
# Using 'loc' gives you the facility to slice the required columns as well
df.loc[df.age > 40, ['name', 'age']]
name | age | |
---|---|---|
0 | Rauf | 52 |
1 | Arif | 51 |
7 | Idrees | 51 |
8 | Jamil | 53 |
15 | Kakamanna | 42 |
&
, |
) in betweendf[(condition1) op (condition2) op (condition3)]
df
df[(df.age < 40) & (df.address != 'Multan')]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
9 | MS10 | Shahid | 38 | Lahore | AFTERNOON | group D | Male | 90.5 | 81.3 | 3800.0 |
10 | MS11 | Khurram | 35 | Islamabad | MOR | group B | Male | 90.5 | 81.3 | 6000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
14 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
df[(df.age > 40) & (df.address == 'Multan')]
# Select records of group A male students only
df1 = df[(df.group == 'group A') & (df.gender == 'Male')]
df1
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
# Select the records of students who belong to Sialkot or Karachi
df1 = df[(df.address == 'Sialkot') | (df.address == 'Karachi')]
df1
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
6 | MS07 | Zobia | 40 | Sialkot | AFT | group B | Female | 90.2 | NaN | 4000.0 |
8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
14 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
# Select records who lives outside Karachi and earn a scholarship of greater than 7000, or lives in Peshawer
out = df[(df.address != 'Karachi') & (df.scholarship > 7000) | (df.address == 'Peshawer')]
out
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
7 | MS08 | Idrees | 51 | Multan | MORNING | group D | Male | 84.1 | 76.0 | 8000.0 |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
If there are many conditions connected with or operator, you can simplify it using the series.isin()
method as shown below:
df[df.address.isin(['Karachi', 'Peshawer', 'Islamabad'])]
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
10 | MS11 | Khurram | 35 | Islamabad | MOR | group B | Male | 90.5 | 81.3 | 6000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
df.select_dtypes()
method is used to get a subset of the dataframe to select columns of a specific datatype(s)df.select_dtypes(include, exclude)
include
and exclude
arguments can be scalar or list-like# Let us first check the data types of each column
df.dtypes
roll no object name object age int64 address object session object group object gender object subj1 float64 subj2 float64 scholarship float64 dtype: object
# Select the columns with object data type (categorical variables) only`
df.select_dtypes(include='float64')
subj1 | subj2 | scholarship | |
---|---|---|---|
0 | 78.3 | 84.4 | 5000.0 |
1 | 70.5 | 60.5 | 6000.0 |
2 | 64.9 | 75.1 | 8500.0 |
3 | 82.0 | 84.3 | 4000.0 |
4 | 65.9 | 72.8 | 3500.0 |
5 | 69.3 | 78.6 | NaN |
6 | 90.2 | NaN | 4000.0 |
7 | 84.1 | 76.0 | 8000.0 |
8 | 90.5 | 81.3 | 3500.0 |
9 | 90.5 | 81.3 | 3800.0 |
10 | 90.5 | 81.3 | 6000.0 |
11 | 90.5 | 81.3 | NaN |
12 | NaN | 76.5 | 7000.0 |
13 | 84.1 | 76.0 | 8000.0 |
14 | 90.5 | 81.3 | 3500.0 |
15 | 90.5 | 81.3 | 3800.0 |
# Select the columns with int64 datatype
df.select_dtypes(include=['int64', 'float64']).head()
age | subj1 | subj2 | scholarship | |
---|---|---|---|---|
0 | 52 | 78.3 | 84.4 | 5000.0 |
1 | 51 | 70.5 | 60.5 | 6000.0 |
2 | 35 | 64.9 | 75.1 | 8500.0 |
3 | 20 | 82.0 | 84.3 | 4000.0 |
4 | 40 | 65.9 | 72.8 | 3500.0 |
# Let us read a fresh dataframe and sort it by age column to have a clear understanding about indexing
import pandas as pd
df = pd.read_csv('datasets/groupdata.csv')
df = df.sort_values('age')
df
roll no | name | age | address | session | group | gender | subj1 | subj2 | scholarship | |
---|---|---|---|---|---|---|---|---|---|---|
5 | MS06 | Mohid | 16 | Lahore | MORNING | group C | Female | 69.3 | 78.6 | NaN |
12 | MS13 | Mujahid | 18 | Lahore | MORNING | group D | Male | NaN | 76.5 | 7000.0 |
3 | MS04 | Hadeed | 20 | Lahore | MOR | group A | Male | 82.0 | 84.3 | 4000.0 |
11 | MS12 | Maaz | 25 | Karachi | AFTERNOON | group C | Male | 90.5 | 81.3 | NaN |
13 | MS14 | Sara | 28 | Multan | AFTERNOON | group A | Female | 84.1 | 76.0 | 8000.0 |
14 | MS15 | Fatima | 33 | Sialkot | AFT | group C | Female | 90.5 | 81.3 | 3500.0 |
2 | MS03 | Shaista | 35 | Karachi | AFTERNOON | group B | Female | 64.9 | 75.1 | 8500.0 |
10 | MS11 | Khurram | 35 | Islamabad | MOR | group B | Male | 90.5 | 81.3 | 6000.0 |
9 | MS10 | Shahid | 38 | Lahore | AFTERNOON | group D | Male | 90.5 | 81.3 | 3800.0 |
4 | MS05 | Zara | 40 | Peshawer | AFT | group D | Female | 65.9 | 72.8 | 3500.0 |
6 | MS07 | Zobia | 40 | Sialkot | AFT | group B | Female | 90.2 | NaN | 4000.0 |
15 | MS16 | Kakamanna | 42 | Multan | AFTERNOON | group A | Male | 90.5 | 81.3 | 3800.0 |
1 | MS02 | Arif | 51 | Islamabad | AFT | group A | Male | 70.5 | 60.5 | 6000.0 |
7 | MS08 | Idrees | 51 | Multan | MORNING | group D | Male | 84.1 | 76.0 | 8000.0 |
0 | MS01 | Rauf | 52 | Lahore | MORNING | group C | Male | 78.3 | 84.4 | 5000.0 |
8 | MS09 | Jamil | 53 | Karachi | AFT | group C | Male | 90.5 | 81.3 | 3500.0 |
dfgb = df.groupby('address')
df.groupby('address').get_group('Multan').scholarship.min()
3800.0
# It will delete the Scholarship column
df_sorted.drop(columns=df.columns[df.apply(lambda col: col.isnull().sum() >= 1)], axis=1, inplace=True)
df_sorted.shape
(16, 7)