Indexing and selecting Data¶

#Import Library
import pandas as pd
import numpy as np

#To generate random number, we import randn from numpy.
from numpy.random import randn
np.random.seed(101)

#Create Dataframe
df = pd.DataFrame(randn(5,4),index='A B C D E'.split(),columns='W X Y Z'.split()) #Split will seperate the column

df

Selection and Indexing¶

Let's learn the various methods to grab data from a DataFrame

#Select W column
df['W']

A    2.706850
B    0.651118
C   -2.018168
D    0.188695
E    0.190794
Name: W, dtype: float64

# Pass a list of column names
df[['W','Z']]

DataFrame Columns are just Series

type(df['W'])

pandas.core.series.Series

Creating a new column:

df['new'] = df['W'] + df['Y']

df

Removing Columns

df.drop('new',axis=1)

# Not inplace unless specified!
df

So you can see above we have droped new column but it still in our dataframe because we have not mentationed the inplace.

df.drop('new',axis=1,inplace=True)

df

Can also drop rows this way:

df.drop('E',axis=0)

Selecting Rows

df.loc['A']

W    2.706850
X    0.628133
Y    0.907969
Z    0.503826
Name: A, dtype: float64

Or select based off of position instead of label

df.iloc[2]

W   -2.018168
X    0.740122
Y    0.528813
Z   -0.589001
Name: C, dtype: float64

Selecting subset of rows and columns

df.loc['B','Y']

-0.8480769834036315

df.loc[['A','B'],['W','Y']]

Conditional Selection¶

An important feature of pandas is conditional selection using bracket notation, very similar to numpy:

df

#if your condituion is not in [] then it will give you true and false.
# Those who full-fill condition they are true and rest false.
df>0

df[df>0]

df[df['W']>0]

df[df['W']>0]['Y']

A    0.907969
B   -0.848077
D   -0.933237
E    2.605967
Name: Y, dtype: float64

df[df['W']>0][['Y','X']]

For two conditions you can use | and & with parenthesis:

#Only display those values that satisfies both condition
df[(df['W']>0) & (df['Y'] > 1)]

#Display those values who satisfies either one condition
df[(df['W']>0) | (df['Y'] > 1)]

More Index Details¶

Let's discuss some more features of indexing, including resetting the index or setting it something else. We'll also talk about index hierarchy!

df

# Reset to default 0,1...n index
df.reset_index()

#Define a new index
newind = 'CA NY WY OR CO'.split()

#Define a new column called state and assign newind to it
df['States'] = newind

df

df.set_index('States')

df

#Set state as index
df.set_index('States',inplace=True)

df

Multi-Index and Index Hierarchy¶

Let us go over how to work with Multi-Index, first we'll create a quick example of what a Multi-Indexed DataFrame would look like:

# Index Levels
outside = ['G1','G1','G1','G2','G2','G2']
inside = [1,2,3,1,2,3]
hier_index = list(zip(outside,inside))#zip function is use to club the list
hier_index = pd.MultiIndex.from_tuples(hier_index)

hier_index

MultiIndex(levels=[['G1', 'G2'], [1, 2, 3]],
           labels=[[0, 0, 0, 1, 1, 1], [0, 1, 2, 0, 1, 2]])

#Convert into dataframe
df = pd.DataFrame(np.random.randn(6,2),index=hier_index,columns=['A','B'])
df

Now let's show how to index this! For index hierarchy we use df.loc[], if this was on the columns axis, you would just use normal bracket notation df[]. Calling one level of the index returns the sub-dataframe:

# We can select G1 data
df.loc['G1']

df.loc['G1'].loc[1]

A    0.302665
B    1.693723
Name: 1, dtype: float64

df.index.names

FrozenList([None, None])

#Define index name
df.index.names = ['Group','Num']

df

df.xs('G1')

df.xs(['G1',1])

A    0.302665
B    1.693723
Name: (G1, 1), dtype: float64

df.xs(1,level='Num')

		A	B
G1	1	0.302665	1.693723
	2	-1.706086	-1.159119
	3	-0.134841	0.390528
G2	1	0.166905	0.184502
	2	0.807706	0.072960
	3	0.638787	0.329646

	A	B
1	0.302665	1.693723
2	-1.706086	-1.159119
3	-0.134841	0.390528

		A	B
Group	Num
G1	1	0.302665	1.693723
	2	-1.706086	-1.159119
	3	-0.134841	0.390528
G2	1	0.166905	0.184502
	2	0.807706	0.072960
	3	0.638787	0.329646

	A	B
Num
1	0.302665	1.693723
2	-1.706086	-1.159119
3	-0.134841	0.390528

	W	X	Y	Z
A	2.706850	0.628133	0.907969	0.503826
B	0.651118	-0.319318	-0.848077	0.605965
C	-2.018168	0.740122	0.528813	-0.589001
D	0.188695	-0.758872	-0.933237	0.955057
E	0.190794	1.978757	2.605967	0.683509

	W	X	Y	Z
A	True	True	True	True
B	True	False	False	True
C	False	True	True	False
D	True	False	False	True
E	True	True	True	True

	W	X	Y	Z
States
CA	2.706850	0.628133	0.907969	0.503826
NY	0.651118	-0.319318	-0.848077	0.605965
WY	-2.018168	0.740122	0.528813	-0.589001
OR	0.188695	-0.758872	-0.933237	0.955057
CO	0.190794	1.978757	2.605967	0.683509