pandas #2: Reading files and basic DataFrame operations
Tags: datascience, python, pandas, tutorial
Published: 2019-10-25 20:05:29

The first step to start doing data analysis in Python is to import your data from a certain source (in my [first post](https://dev.to/gtrindadi/getting-started-with-pandas-3i53) we saw that pandas is great for data analysis). In this post I'm going to show you how to load files into pandas data structure (dataframes) and then we'll check how we can print the whole dataframe or a sample of the data, filter specific values and select specific columns and rows, besides append and delete them. In the end we'll check the logic sequence of pandas operations.

This second post is different from the first one. In the first one we learned the theoretical stuff. On the other hand, this post will be more practical so I would appreciate if you code along with me. If you have any questions please feel free to use the comments section below.

*Note [1]: I made a Jupyter Notebook available on [my GitHub](https://github.com/gabrielatrindade/blog-posts-pandas-series) with the code used in this post.*

## Before we start

This is a pandas tutorial, so the first step is to import it by typing the following in your Python runner:
```python
import pandas as pd
```
You may have noticed that I didn't just import pandas but I also gave an alias to it. Then, everytime I need to use the pandas library I can refer to it as `pd`.

## Reading a file

**What is your file extension?** First of all you need to know what is the file type you'll work on. It's common to have data in .csv (Comma Separated Value) files. But keep in mind that you can work with other file types, like .xls, .xlsx, .txt, .json, .html, and so on.

To analyse the data, the first step is to import it from the file into a dataframe. To do this is easy with pandas since it provides functions for each file type, as you can see [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#). But in this post we're working with .csv files because it is one of the most common or maybe the most common.

The second important thing to know is **where is your file**, i.e. you need to know what is the path directory of your file to allow pandas to find it.

Knowing this, let's **download our working file**.
```text
!wget https://raw.githubusercontent.com/gabrielatrindade/blog-posts-pandas-series/master/restaurant_orders.csv
```

Now, open your file and **make observations** on it, like: Is there a head, i.e. the first line represents the column names? What is the column separator? This two questions are important to import the file into a dataframe. To do that use the `read_csv()` function:
```python
pd.read_csv('restaurant_orders.csv', delimiter=',')
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/pix3uyow8bq7rf2k9xqg.png)

As you can see the file has no head and pandas will considerate the first line a head by default, thus let's add the column names to it.
```python
column_names = ['order_number', 'order_date', 'item_name',
                'quantity', 'product_price']

pd.read_csv('restaurant_orders.csv', delimiter=',', names=column_names)
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/8r8uam2pg2sdrs5d2q26.png)

Now let's store our dataframe into a variable because we'll work a lot with it.
```python
orders = pd.read_csv('restaurant_orders.csv', delimiter=',',
                     names=column_names)
```
Now, you can see the whole dataframe through the variable we just introduced.
```python
orders
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/tnsh69a8u1vgfguh1tfe.png)
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/y1ltv5tkwi3f51ew8a4c.png)

*Note [2]: Did you notice how this file has so many rows (observations)? To be exact there are 74818 rows. Wow! And we are going to work with this dataset.*

Before we move on, pay attention to our dataset and try to understand what it means. It represents orders from Indian takeaway restaurant in London, UK. Each row is a single product within the order, i.e. one order can have more than one row. There are 5 columns: `order_number`, `order_date`, `item_name`, `quantity`, and `product_price`. I believe that the names are self explanatory, but if you have any questions about the dataset, let me know through the comments.

You also can verify the type of `orders` variable. As we know, it's a DataFrame object.
```python
type(orders)
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/1y8zzh0i2hnfwt4ifedw.png)

## Basic DataFrame operations

### Printing samples

#### head and tail

These functions will print the first and last rows, respectively. They are commonly used to see a sample of Series or DataFrame object. By default they will print five rows, but you can pass a number as an argument.
```python
orders.head()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/588o9odk0adp26mxtd6m.png)

```python
orders.tail()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/4thvg6xwqk9605qmmrg4.png)

#### Sample

It's one more function to have a sample of Series or DataFrame. However you need to pass the number as an argument and then it will sort the rows randomly.
```python
orders.sample(5)
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/vjtquz8beazlq2gibnc0.png)


### Selecting specific rows and columns

Other ways to get a subset of your dataframe is by selecting a specific set of rows and columns you want.

#### Rows

To select rows you only need to pass the rows interval between brackets. 

It's important to highlight that pandas is based-indexing zero, i.e. the first element is zero which is included, and the last index is excluded.
```python
orders[0:7]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/8faxuly1ngeujeopbtqp.png)

But if you don't want to specify the begin or the end of your subset, you can simply omit this information. It will consider the first index or the last index for omitted ones, respectively.
```python
orders[:6]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/iyc48npplp7yz6ph9oed.png)

#### Columns

To select columns, if you want to get a DataFrame object, use double brackets.
```python
orders[['item_name']]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/paxpvorws6thv7jmms1x.png)

But if your need is to have a Series object, there are two ways to access it.
```python
orders['item_name']
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/msxcxhwsn7pnwaq3yq0i.png)

```python
orders.item_name
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/84aozz32ike8wi77ertr.png)

As you'll see at the end of this post, you can chain operations and it will follow the linear logic. So you can do something like this.
```python
orders[['item_name']][:6]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/7ervtkmyqweop1op6sx4.png)

But I will explain it in more details in the end.

#### .iloc, .loc

The selection of rows and columns also can be made by some attributes, such as `.iloc` and `.loc`. The general syntax for these attributes is `dataframe.attribute[<row selection>, <column selection>]`. In these cases you can omit the `<column selection>`, but you need to inform the row ones. I am going to show you some examples.

`.iloc` is based on position.
```python
orders.iloc[0:7]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/kykka9x1hm70dn0tr19j.png)


```python
orders.iloc[:, 2:4]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/6q0xa0pfwrxv5nh8w67z.png)

```python
orders.iloc[-1]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/3ha9c2r5pyo6ej0t6irb.png)

*Note [3]: Negative index will consider from the bottom to the top. Then, in this case it returns the last row. Ah, and it works in the same logic to columns as well.*

*Note [4]: `.iloc` returns a pandas Series when one row is selected, and a pandas DataFrame when multiple rows are selected or if any column in full is selected. If you want a DataFrame use double brackets.*

`.loc` is based on label. However our label for rows was not changed so by default it is the index.
```python
orders.loc[0:7, ['order_number', 'item_name']]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/ugkdttibth9shidzjumv.png)

```python
orders.loc[[1, 3, 5], ['order_number', 'item_name']]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/9p2oj1obt2l7763wudem.png)


### Filtering by specific values

What if you need to select rows that contain specific values? Let's say we want to select only the order 16118 to see the items of it.
```python
orders[orders.order_number == 16118]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/qzjf1dvc82x28r3bnyf7.png)

In this way we can see all the items listed for this order. But how it works? First pandas check the condition between brackets `orders.order_number == 16118`. If you just compile this part, it returns a pandas Series object with Boolean value where each one is the result of the condition for all rows in the dataframe. Then, in the second part `orders[...]` pandas prints just the correspondent rows for values that were True.

It works like a 'where' clause in SQL.

One more example is to filter by day.
```python
orders[(orders.order_date == '2019-08-03') |
       (orders.order_date == '2019-08-02')]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/t0czd9vbwpysdq6yhdle.png)

![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/cdzgstbbn3m9svfqtgkp.png)

*Note [5]: In these examples I'm using the two ways to access a column in a dataframe. As I explained at the beginning of this post, I can access column using brackets or dot notation.*

As you probably noticed we can use some logical operators (`&`, `|` to represent 'and', 'or') as well as comparison operators (`==`, `!=`, `<>`, `<`, `>`, `<=`, `>=`) in our conditions.

### Adding rows and columns

In order to not compromise our dataframe, for this subsection and the next one, we'll create a copy of our dataframe and then work from it.
```python
orders_copy = orders.copy()

orders_copy.head()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/r2mcd33868o6rifdnvm7.png)

*Note [6]: To create a copy you need to use `copy()` function. If you just assign `orders` to `orders_copy` you are creating a reference to it which means that changing `orders_copy` will change `orders` too.*

#### Rows

To add new rows to a dataframe is easy. We can use the `append()` function and as an argument we pass a Series list, or Dictionary to represent the row.

Pay attention about the type of the values, they don't need to match with the data types of each dataframe column, but you don't want to mess up your dataframe and consequently your analysis. So be sure that you are adding the right values in the correct columns.
```python
orders_copy = orders_copy.append(
    pd.Series([123456, '2019-08-04', 'Product Test', 4, 1.00],
              index=orders_copy.columns),
    ignore_index=True)

orders_copy.tail()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/liv4jim8uj99q9yda19t.png)

It's also possible to do it by using the `.loc` attribute directly. Although it's not considered a good practice since you should rely more on the object API instead of changing directly their internal state.
```python
row = [12134567, '2019-08-04', 'Product Test', 4, 1.70]
orders_copy.loc[len(orders_copy)] = row

orders_copy.tail()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/munie02vaiau6iwi08oe.png)

#### Columns

There are some ways to add columns in a dataframe.

You can do that using list, but in this case you need to assign for each row a value. Then, in our case we'd need to create a list with 74820 values. In general we do this by assigning a function or an expression to the column. But, if you want to assign the same values for all rows, you can do this just like the example below.

Let's say that all items had a discount.
```python
orders_copy['discount_pct'] = 10

orders_copy.head()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/lwxdce3aoz4dmehn38yz.png)

It doesn't look so useful, does it? But in our case we'll use it to give the discount for each product price.

So let's see the interesting part. You can use an expression to fill the column. And here we're going to create the column that represents discount through price.
```python
orders_copy['discount_price'] = (orders_copy['product_price'] *
                                 (orders_copy['discount_pct'])) / 100

orders_copy.head()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/prsm2w24f3k0z3387tkr.png)

So all the `discount_price` column is representing the discounted value for each product (row).

You can also add columns using `assign()` function.

Let's create the total discount taking into account the quantity of products.
```python
orders_copy = orders_copy.assign(
    discount_subtotal=lambda row: (row['quantity'] * row['discount_price']))

orders_copy.head()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/w4kv3sdk6bal82hxbqo9.png)

One more way to do add columns is through the `apply()` function. In this case you'll create a column using a function that will be applied for each row. Like the examples above.
```python
orders_copy['subtotal'] = orders_copy.apply(
    lambda row: (row['quantity'] *
                 (row['product_price'] - row['discount_price'])), axis=1)

orders_copy.head()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/xp2q2bnkvchzgtdxsvge.png)

The `axis=1` means that we are working with column.

### Deleting rows and columns

It's important to evaluate the decision of deleting something before doing it. You can do some filters to check if it's really what you want to delete. Here we are working with the dataframe copy, so we don't need to worry about that.

To both, rows and columns, we use `drop()` function.

#### Row

Let's delete the row that we added. But before, let's check the row index.
```python
orders_copy[orders_copy.item_name == 'Product Test']
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/919rgceh76qzzofzq8tx.png)

Now we know that the index is 74818. Let's drop it.
```python
orders_copy = orders_copy.drop(orders_copy.index[74818])
```
Check if it was deleted.
```python
orders_copy[orders_copy.index == 74818]
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/zqfj7d99srtxnr8if6xd.png)

It doesn't exist anymore.

#### Column

In the same way, let's delete the columns that were created.
```python
orders_copy = orders_copy.drop(['discount_pct', 'discount_price',
                                'discount_subtotal', 'subtotal'], axis=1)
```

The `axis=1` refers that it's a column, not a row.

Check it is was deleted.
```python
orders_copy.head()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/2m4nl9dgo9wlv19m67gj.png)

### Linear logic

The pandas logic is very linear (compared to SQL, for example), you can chain operations one after the other. The input of the latter function is the output of the previous one. Let's see some examples.

Get the first 3 different products bought at '2019-08-03' . Consider that the dataset is sorted by the orders. Print only the product name.
```python
orders[orders.order_date == '2019-08-03']['item_name'].head(3)
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/9rver5j4vhoh5sjsii7l.png)

First we filtered by the orders at '2019-08-03', then we selected just the product name column and finally we printed the first 3 products.

Get the 3 last different products which was buying. Print the product name and the quantity.
```python
orders[['item_name','quantity']].tail(3)
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/38x5xipt6i26r4le2qms.png)

As you can see we selected the right columns and then we applied the tail function to get the last 3. Similarly we would first apply the tail function and then select the columns. The output would be the same.

And finally, let's print the five last orders that happened on '2019-08-03' and have a 'Plain Papadum' on it.
```python
orders[(orders.order_date == '2019-08-03') &
       (orders.item_name == 'Plain Papadum')].tail()
```
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/sbs7xhd2pyos1da2ayrr.png)

As you can see, in this case we used the `&` operator to filter our dataset.

## Wrapping up

In this post we learned how to create a dataframe by reading a file. We saw how it is easy with pandas. We saw the main information we need to know to read a file, such as file extension, how it is organized, and the file path directory.

We also learned about dataframe basic operations. Such as print sample of our dataframe; select, append and delete rows and columns; and filter specific rows through conditions.

Finally, I showed to you about how to do chained operations in only one line of code, explaining about the pandas linear logic.

In the next post we'll see about aggregation and grouping. How we can apply some interesting methods, like for example `sum()`, `mean()`, `avg()`, and grouping our dataset by a collection of elements in common. I'm looking forward to show you interesting things we can do with pandas.


*Dataset original reference: [Takeaway Food Orders](https://www.kaggle.com/henslersoftware/19560-indian-takeaway-orders/)*