The Pandas Pundit: Accessing Data in DataFrames
Tags: python, pandas, datascience
Published: 2020-03-31 18:56:10

---
title: The Pandas Pundit: Accessing Data in DataFrames
published: true
description: 
tags: #python #pandas #datascience
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/zxf32rslouw683i4npow.jpg
---


Congratulations -  You have just landed a new job as a Data Scientist :grinning:! In your first month, you need to start analyzing *tons of data*. But before you start unlocking insights and predicting future trends, you need to *access* these data in order to explore it. Yikes! Did you wish you just started predicting future trends right away? Smiles, You are not Doctor Fate!

These tons of data can vary greatly in form and they are commonly seen in a tabular structure, where we have rows (also known as records, observations etc) and columns (also known as features, variables, fields etc) - like in *1.md* below:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 file=1.md %}

Data in .csv and .xlsx files have a tabular-like structure and in order to work efficiently with this kind of data in *Python*, we need to use the *Pandas* package. In Pandas, there is a data structure that can handle tabular-like structure of data - this data structure is called the **DataFrame**. Look at *2.md* below to see the DataFrame version of the *1.md*:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 file=2.md %}

In *2.md*, you can see a similar structure like in *1.md* - we also have rows and columns - each row has a unique row label - *NG*, *CA*, *BR*, *CH*, *FR*. The columns also have labels - *country*, *capital*, *population_millions*. So, how do you put this data in a DataFrame to start exploring? Also for you to explore it well, **what are the different ways to access the data in this DataFrame**? Cheers! You are about to start your journey on becoming a Pandas Pundit!

You are given tons of data in a CSV file as seen below in *3.csv*:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 file=3.csv %}

First of all, to get the data in *3.csv* into a DataFrame, look at *4.py* below:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 file=4.py %}

which returns the DataFrame in *5.txt* below:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 file=5.txt %}


Now that we have our data in a DataFrame, it is time to access it. There are several ways to access or select or index or subset or slice data in DataFrames - Data can be accessed via:

 1. square brackets: [ ]
 2. loc: label-based 
 3. iloc: position-based
 4. at: label-based
 5. iat: position-based

Let’s see how you can access data in  *columns only*, *rows only* and *both rows and columns* from the Dataframe in **5.txt** using the 5 ways above:

# **square brackets [ ]** - 1

Let's look at **column access** and **row access** using []:
**1.1**: **Column Access**:
We have *single column access* and *multiple column access*.
**1.1.1**: **_single column access_**:
To access data in the *Country* column in *5.txt* above, for example, we do:
           
{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 file=6.py %}

which returns:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=7.txt %}
           
In *7.txt* above, the dtype (datatype) of the what is returned is an object. The type of object returned can be known using:
           
{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=8.py %}

In *8.py* above, it is a pandas *Series* object. A *pandas series* is a 1D (1-Dimensional array) that can be labelled just like the DataFrame, a series has row labels/indexes. So, with this, it shows that **a collection of series creates a DataFrame**. 
           
This series object returned can also be accessed using the square brackets. For example, to grab the value *Nigeria* in *7.txt* above, see *9.py* below:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653
file=9.py %}
       
Also, note that I can use the *dot notation* as seen in *lines 19-20* above. Use the dot notation when **the column name does not contain any special characters or spaces and is not a keyword in python**.

However, if you want a DataFrame returned and not a series - use double square brackets as seen in *10.py* below:
       
{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 file=10.py %}

**1.1.2**: **_multiple column access_**:
To access more than one column in *5.txt*, we do:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=11.py %}

which returns:
           
{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=12.txt %}


**1.2**: **Row Access**:
The only way of accessing rows in a DataFrame using the square brackets way is by specifying a slice on the row. Specifying a slicing index takes the form - **start:stop:step/stride**

Indices are either **numeric** which is the default or **labelled**. Let's dive deeper below:
        
**1.2.1**: **_default numeric indices_**:
Given **_[x:y:z]_** as a slicing index, it means **count in increments of *z* starting at *x* inclusive, up to *y* exclusive** - for numeric indexes, *the stop index is always exclusive*.
 
Take a look at this figure below:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ks6435xuikd2xzs5bwff.png)

In the figure above, the direction in which my rows are returned is determined by the **_sign of the step/stride_** i.e z, given *[x:y:z]*. 
 - If the step/stride is positive, start from the specified index position of the DataFrame and go the **_downward/forward_** direction when returning rows*.
 - If it is negative, start from the specified index position of the DataFrame and move **_upwards/backwards_** when returning rows.

**_Given [:y:-z] as a slicing index, start from the last row in the DataFrame and go backwards/upwards but If [:y:z] is given, start from the first row in the DataFrame and go forward/downward_**.

 In the forward/downward or in the backward/upward direction, the start index should always come before the stop index else no rows will be returned.

Let us take a look below at how *positive* and *negative* strides works:

**1.2.1.1**: **_positive step(s)/stride(s)_** :
{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=13.py %}
            
In *13.py, lines 3-9* above, we use the default numeric index. It has this structure *start:stop:[step or stride]* - [step or stride] in square brackets means it is optional. Given *countries[1:3]*, *1* is the start while *3* is the stop. When the step or stride is not specified like it is the case here, it has a default value of 1.

In *13.py, lines 11-18* above - given *countries[2:]* - note that the stop index is omitted i.e the explicit end index position is omitted. *countries[2:]* returns rows starting with the row with index position 2 till the last row in the DataFrame inclusive as seen above. **:** is a universal slice. If its left-endpoint (start) is omitted then the rows returned starts from the very first row in the DataFrame but if the right-endpoint is omitted, the row returned is till the very last row in the DataFrame inclusive.

In *13.py, lines 20-27* above - given *countries[:3]* - note that the start index is omitted i.e the explicit start index position is omitted.
*countries[:3]* returns rows starting with the first row till the row with index position 2 inclusive - remember that a *numeric stop index* is exclusive: so, the row at index position 3 is not returned as seen above.

In *13.py, lines 29-38* above, given *countries[:]* - note that both the start and stop indices are omitted i.e the explicit start and stop indices positions are omitted. *countries[:]* returns rows starting from the start of the row till the last row inclusive i.e it returns every row.

In *13.py, line 40-47* above, given *countries[::2]*, using the formula above, this means **counts in *increment/steps/strides* of 2 *starting* from the first row *up to* the last row inclusive**  i.e it returns every 2nd row. So, this is how it works - it return rows
 - starting from the first row which has index 0, 
 - add steps of 2 i.e index 0 + 2 = index 2; then index 2 + 2 = index 4 
 - so, we have rows with index positions 0, 2, 4 returned

In *13.py, lines 49-54* above, given *countries[-1:]* - note the 
following below -
 - the DataFrame, countries, has rows labelled *NG*, *CA*, *BR*, *CH*, *FR*. These rows also have default numeric indices which can be positive *0*, *1*, *2*, *3*, *4* or negative *-5*, *-4*, *-3*, *-2*, *-1*.
 - *countries[-1:]* is the same as *countries[-1::1]* - so we start from the last row and go downwards - downwards since the step/stride is positive
 - this returns only the last row because there is no other row downwards

In *13.py, lines 56-64* above, given *countries[:-1]* - it returns rows 
starting from the first row but excludes the last row.

In *13.py, lines 66-72* above, given *countries[-2:]* - it returns rows starting from the last but one row till the last row inclusive.

In *13.py, lines 74-81* above, given *countries[:-2]* - it returns rows 
starting from the first row till it includes the row before last but one 
row.

In *13.py, lines 83-88* above, given *countries[-1:-1]* - it returns 
an empty DataFrame - starts from the last row but excludes the last 
Row, hence nothing is returned.

**1.2.1.2**: **_negative step(s)/stride(s)_** :
With negative steps, rows get returned backwards. Let us see some examples in 14.py below:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=14.py %}

In *14.py, lines 3-8* above, given *countries[3:-4:-1]* - note the following below:
 - the stride/step is negative, so rows get returned backwards/upwards - when you look at the tabular-like structure in *5.txt* above, we start getting rows from the end depending on the specified index and then go upwards/backwards i.e *from the row with label CH and then move upwards*.
 - the stop has a negative value of -4 which is the row labelled *CA*. So, this row and the ones after it is not be included in the rows returned
 - hence we have just two rows labelled *CH* and *BR* returned.

In *14.py, lines 11-17* above, given *countries[4:-4:-2]* - note the following below:
 - the stride/step is negative and in steps of 2
 - so we return rows starting from index position 4, then go upwards/backwards i.e *from row with label FR and then move upwards/backwards* but *excludes row with index position 4 with all other rows after it too*.

In *14.py, lines 19-24* above, given *countries[0: -1: -1]*, it returns an 
empty DataFrame. Here is why:
 - Since the step/stride is negative we start at 0 and then go upwards or backwards - but it seems the *stop index comes before the start index* - so this cannot work, hence an empty DataFrame is returned

**1.2.1**: **_Labelled indexes_**:
With labelled indexes, it also takes the form *start:stop:step/stride*. But here are some things to take note of below:
 - **_The stop label is inclusive_**, unlike numeric indices where the stop index is exclusive.
 - Pairing labelled and numeric indices in the start or stop indices are not allowed e.g countries[‘NG’: 4] would give an error
 - The step/stride is still numeric and can also be positive or negative
 - and of course, all rules that go with having positive or negative step/stride applies here too.

Let’s see some examples in *15.py* below:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=15.py %}

In *15.py, lines 3-10* above, given *countries[‘CA’:’CH’]*, we can see that the rows returned includes the row with the label *CH* which is the stop index label specified.

In *15.py, lines 12-20* above, given *countries[:’CH’]*, the rows returned starts from the first row in the DataFrame till it includes the row labelled *CH*.

In *15.py, lines 22-30* above, given *countries[‘CA’:]*, the rows returned starts from the row labelled ‘CA’ in the DataFrame till it includes the last row.

In *15.py, lines 32-39* above, given *countries[‘NG’::2]*, the rows returned -  
 - starts from the row labelled ‘NG’ in the DataFrame
 - add steps of 2 - index NG has a default index position of 0, so 0 + 2 = *index 2* which is the row labelled BR; then index 2 + 2 = *index 4*, which is the row labelled FR
 - so, we have rows labelled *NG*, *BR*, *FR* which have default numeric index positions 0, 2, 4 returned.
			
In *15.py, lines 41-49* above, given *countries[:‘CA’:-1]*, the rows 
returned -
 - starts from the last row labelled *FR* in the dataframe
 - add steps of -1 : 
    - index *FR* has a default negative index position of *-1*, so -1 + -1 
      = *index -2* which is the row labelled *CH* 
    - then index -2 + (-1) = *index -3*, which is the row labelled *BR* 
    - then index -3 + (-1) = *index -4*, which is the row labelled *CA* 
 - so, we have rows labelled *FR*, *CH*, *BR*, *CA* which have default numeric index positions -1, -2, -3, -4 returned.

Using square brackets, [ ] has its limitations like the ability to select several rows and columns at the same time. So, let's jump into loc and iloc to see its awesomeness!



# **loc** - 2

**2.1**: **Row Access**:
By default, loc accesses rows. Loc is label-based, so I just need to specify the row label. Let us see some examples in 16.py below:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=16.py %}

In *16.py, lines 3-16* above, given *countries.loc[‘FR’]* or *countries.loc[[‘FR’]]*, the row with label *FR* returns a series or dataframe respectively.

In *16.py, lines 18-25* above, given *countries.loc[[‘CA’, ‘BR’, ‘CH’]]*, the rows with labels listed in the square brackets are returned

**2.2**: **Row and Column Access**:
We can simultaneously access rows and columns using loc. It takes the form *countries.loc[row, column]*. Let us see some examples below in *17.py*:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=17.py %}

In *17.py, lines 3-10* above, given *countries.loc[['CA', 'BR', 'CH'], ['Country', 
'Capital']]* - we specify a list of row labels and also a list of column labels we want to be returned.

In *17.py, lines 12-50* above, we can see slices can also be specified. Everything we have seen so far in relation to positive or negative steps/strides also applies here as seen in the examples given.

**2.3**: **Column Access**:
We can also select the specific columns we need while we select all rows as seen below in *18.py*:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=18.py %}



# **iloc** - 3

Just like loc, iloc is row-based by default. The difference is that iloc is position-based. let's see some examples in *19.py* below:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=19.py %}

*19.py* above gives the iloc version of the examples given for loc for *row access*, *row and column access* and *column access*. As I earlier said, the only difference is that iloc is position-based while loc is label-based.

Kindly note that *ix*, which is also a way to accessing data in a DataFrame, is deprecated in favour of *loc* and *iloc*, so it is not advisable to use it the *ix* indexer.  



# **at** - 4

*at* is label-based like loc but it is used only to access a single value in a DataFrame. Unlike *loc*, which can not just get a single value but also several values as we have seen above.

See some examples below in *20.py*:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=20.py %}



# **iat** - 5

*iat* is position-based like iloc but also accesses a single value like *at*. See some examples below in *21.py*:

{% gist https://gist.github.com/joyadauche/5cd5c8d6ef5b6ae1370d9ed7f3570653 
file=21.py %}



Now, let's summarize the key points together:
 - Accessing data using indices takes the form, *[start:stop:step]*
 - Numeric stop indices are exclusive
 - Label stop indices are inclusive
 - A step can be positive or negative - When it is positive, start at the specified index then go downwards/forward but when it is negative, start at the specified index then go upwards/backwards.
 - Given *[:y:-z]* as a slicing index, start from the last row in the DataFrame and go backwards/upwards.  
 - Given *[:y:z]* is given, start from the first row in the DataFrame and go forward/downward*.
 - *loc* - label-based
 - *iloc* - position-based
 - *at* - label-based but returns a single value
 - *iat* - position-based but returns a single value



Wowww! That was so much to take in. But you are on your way in becoming a Pandas Pundit! Stay tuned on this series for my next article on **Filtering Data in Dataframes**! Have an amazing and fulfilled week ahead!