Data handling with Pandas

Disclaimer: This post has been translated to English using a machine translation model. Please, let me know if you find any mistakes.

1. Summary

Let's take a look at a brief introduction to the data manipulation and analysis library Pandas. With it, we can handle and process tabular data, which will help us operate with the data and extract valuable information.

2. What is Pandas?

Pandas is a Python library designed to make working with relational or labeled data easy and intuitive Pandas is designed for many different types of data:

• Tabular data with heterogeneous column types, such as in an SQL table or an Excel spreadsheet* Time series data, ordered and unordered (not necessarily of fixed frequency).* Arbitrary matrix data (homogeneous or heterogeneous) with row and column labels* Any other form of observational/statistical datasets. Data does not need to be labeled at all in order to place it into a Pandas data structure.

The two main data structures of Pandas are Series (one-dimensional) and DataFrames (two-dimensional). Pandas is built on top of NumPy and is designed to integrate well within a scientific computing environment with many other third-party libraries. For data scientists, working with data is generally divided into several stages: collecting and cleaning data, analyzing/modeling it, and then organizing the analysis results in a suitable format for plotting or displaying them in tabular form. pandas is the ideal tool for all these tasks. Another feature is that pandas is fast, many of the low-level algorithms have been built in C.

2.1. Pandas as pd

Generally when importing pandas, it is usually imported with the alias pd

3. Data Structures in Pandas

In Pandas there are two types of data structures: Series and DataFrames

3.1. Series

The Serie data type is a one-dimensional labeled array capable of holding any data type (integers, strings, floating-point numbers, Python objects, etc.). It is divided into indices.

To create a Series data type, the most common way is

series = pd.Series(data, index=index)```
      
      Where `data` can be:* A dictionary* A list or tuple* A `ndarray` from Numpy* A scalar value
      

As one of the data types can be a ndarray from NumPy, we import NumPy to be able to use it

3.1.1. Series from a dictionary

If an index is passed, the values corresponding to the index labels will be extracted. If they do not exist, they are created as NaN (not a number)

3.1.2. Series from a list or tuple

If the data comes from a list or tuple and no index is passed, one will be created with values [0, ..., len(data)-1]

If an index is passed, it must have the same length as the data

3.1.3. Series from an ndarray

If the data comes from an ndarray and no index is passed, one will be created with values [0, ..., len(data)-1]

If an index is passed, it must have the same length as the data

3.1.4. Series from a scalar

If the series is created from a scalar, it will be created with a single item

If you want to create more items in the series, you need to pass the index with the number of items you want, so all the items will have the value of the scalar

3.1.5. Operations with Series

Just like with Numpy, we can perform operations on all elements of a series without having to iterate through each one of them.

A difference between Series and ndarrays is that operations between Series automatically align the data according to their labels. Therefore, calculations can be written without considering whether the involved Series have the same labels. If a label is not found in one Series or the other, the result will be marked as missing (NaN).

3.1.6. Attribute name of the Series

One of the attributes of Series is name, which corresponds to the name they will have when added to a DataFrame. Conversely, when a series is obtained from a DataFrame, this series will have the same name it had in the DataFrame.

The name of a series can be changed using the rename() method.

3.2. DataFrames

A DataFrame is a labeled and two-dimensional data structure, with potentially different types of columns, meaning that one column can contain integer data, another column string data, etc. You can think of it as a spreadsheet or an SQL table, or a dictionary of Series objects. It is the most used pandas object. Just like Series, DataFrames accept many different types of input:

Along with the data, you can optionally pass index arguments (row labels) and columns (column labels). If you pass an index and/or columns, you are guaranteeing the index and/or columns of the resulting DataFrame. Therefore, a dictionary of Series plus a specific index will discard all data that does not match the passed index.

If axis labels are not provided, they will be constructed from the input data based on common sense rules.

3.2.1. DataFrames from a Dictionary of Series

If a dictionary of Series is passed, the DataFrame will be created with as many columns as Series the dictionary has.

If each of the Series has defined indices, the resulting DataFrame will be the union of these indices.

If the columns are passed, they will appear in the passed order

3.2.2. DataFrames from a dictionary of ndarrays or lists

All ndarrays or lists must have the same length. If an index is passed, it must also have the same length as the ndarrays or lists.

3.2.3. DataFrames from a matrix

If an index is passed, it must have the same length as the number of rows in the array, and if columns are passed, they must have the same length as the columns in the array.

3.2.4. DataFrames from a list of dictionaries

3.2.5. DataFrames from a dictionary of tuples

3.2.6. DataFrames from a Series

The result will be a DataFrame with the same index as the input Series, and with a column whose name is the original name of the Series (only if another column name is not provided).

4. Exploration of a DataFrame

When a DataFrame is very large, it cannot be fully represented.

So it's very useful to have methods for exploring it and getting information quickly.

4.1. DataFrame Header

To view the first rows and get an idea of what the DataFrame looks like, there is the head() method, which by default displays the first 5 rows of the DataFrame. If you want to see a different number of rows, specify it using the n attribute.

4.2. Tail of the DataFrame

If you want to see the last rows, you can use the tail() method. By using the n parameter, you can choose how many rows to display.

4.3. DataFrame Information

Another very useful method is info() which gives us information about the DataFrame

4.4. Rows and Columns of DataFrame

The indices and columns of a DataFrame can be obtained using the index and columns methods.

4.5. Description of the DataFrame

The describe() method displays a quick statistical summary of the data in the DataFrame

4.6. Sorting the DataFrame

Rows of a DataFrame can be alphabetically sorted using the sort_index() method.

Since the rows were already sorted in this case, we set ascending=False to reverse the order.

If you want to sort the columns, you need to set axis=1 since the default is 0.

If we want to sort the DataFrame by a specific column, we need to use the sort_values() method and specify the label of the column to sort by.

4.7. DataFrame Statistics

Statistics can be obtained from the DataFrame, such as the mean, mode, and standard deviation.

If you want to get statistics about the rows instead of the columns, you need to specify it using axis=1

Another useful thing that can be obtained from DataFrames is, for example, the number of times each item in a column repeats.

For example, we can see that there are a total of 16 houses with 1582 rooms.

4.8. Used Memory

We can see the memory used by the DataFrame

5. Addition of Data

5.1. Addition of Columns

Columns can be easily added as operations of other columns

Columns can also be added indicating what value all their items will have

If a Series is added that does not have the same number of indices as the DataFrame, it will be adjusted to match the number of indices of the DataFrame.

With the previous methods, the column was added at the end, but if you want to add the column in a specific position, you can use the insert() method. For example, if you want to add a column at position 3 (keeping in mind that indexing starts from position 0), with the name of the column being inserted column and its value being double that of the column three, it would be done as follows:

If you want to add more than one column per command, you can use the assign() method.

5.2. Addition of Rows

We can add a row at the end using the concat method (which we will cover in more detail later)

We see that the column has been added at the end, but it has index zero, so we reorder the indices using the method reset_index(drop=True)

6. Data Deletion

6.1. Column Deletion

A specific column can be deleted using the pop() method.

Or using del

6.1. Row Deletion

If we want to delete a row, we can use the drop method, specifying its position. For example, if we want to delete the row at position 1

If we want to delete the last row

If what we want is to delete a range of rows

If what we want is to remove a specific set of rows

Just like when we added rows, we see that some indices have been removed, so we reorder the indices using the method reset_index(drop=True)

7. Operations on DataFrames

Operations can be performed on DataFrames just as they can with Numpy.

If more complex operations are desired, the apply() method can be used.

A lambda function has been applied because it is a simple function, but if we want to apply more complex functions, we can define and apply them.

Using the apply method on a DataFrame is much faster than looping through each row and performing the operation.

Let's calculate the percentage of bedrooms out of the total number of rooms.

The lambda function took about 300 ms, while the for loop took more than 1 second.

8. Transpose

The transpose of a DataFrame can be obtained using the T method.

9. Conversion to Numpy

If you want to convert a Series or DataFrame to NumPy, you can use the to_numpy() method or the np.asarray() function.

This example is not the most appropriate, as it mixes numbers with booleans, and as we explained in the previous post Matrix Calculus with NumPy, all elements of an ndarray must be of the same type. In this case we are mixing numbers with booleans, so to solve it NumPy converts them all to objects To solve this, we keep only the numbers and convert them to an ndarray

Now it can be seen that an ndarray has been created where all the data are of type float

10. Reading data from external sources

One of the greatest strengths of Pandas is being able to read data from files, so it's not necessary to create a DataFrame with the data you want to process; instead, it can be read from a file. Just like you can create DataFrames from external files, you can also save DataFrames to files, so you can create your own dataset, configure it the way you want, and save it to a file to use it later. The following table shows the functions for reading and writing files in different formats.

|Format|File type|Reading function|Writing function||---|---|---|---||text|CSV|read_csv|to_csv||text|Fixed-Width Text File|read_fwf|||text|JSON|read_json|to_json||text|HTML|read_html|to_html||text|Local clipboard|read_clipboard|to_clipboard||binary|MS Excel|read_excel|to_excel||binary|OpenDocument|read_excel||binary|HDF5 Format|read_hdf|to_hdf||binary|Feather Format|read_feather|to_feather||binary|Parquet Format|read_parquet|to_parquet||binary|ORC Format|read_orc||binary|Msgpack|read_msgpack|to_msgpack||binary|Stata|read_stata|to_stata||binary|SAS|read_sas||binary|SPSS|read_spss||binary|Python Pickle Format|read_pickle|to_pickle||SQL|SQL|read_sql|to_sql||SQL|Google BigQuery|read_gbq|to_gbq|

11. Indexing in DataFrames

There are many ways to index into DataFrames.

11.1. Column Indexing

To select columns within a DataFrame, you can do so by selecting the column between brackets [], or by indicating the column as if it were an attribute of the DataFrame.

If specific rows are desired, they are passed through a list

11.2. Row Indexing by Positions

A range of rows from a DataFrame can be selected as follows

If you want to select a single row, you need to specify a range of rows that includes only that one. For example, if you want to select row number 1

Another method to select a row by its position is the iloc[] method

If specific rows are desired, a list with their positions is passed.

11.3. Row Label Indexing

To select a row by its labels, we can use the loc[] method.

If you want to select a range of rows, you can index them using the colon :

If specific rows are desired, they are passed through a list

11.4. Selection of a Portion of the DataFrame by Positions

If specific rows and columns are desired, lists with the desired positions are passed.

11.5. Selecting a Portion of the DataFrame Using Labels

If you want certain rows and columns, you pass lists with the desired labels

11.6. Lambda Function Indexing

Data can be selected from a DataFrame that meets a condition given by a lambda function.

As can be seen, this form of indexing is very powerful

11.7. Conditional Indexing

If we don't need complex functions for indexing, but only conditionals, we can do

We can make multiple conditions

11.8. Random Indexing

By using the sample() method, we will obtain a random row from the DataFrame.

If we want more than one sample, we indicate it with the attribute n

If random columns are desired, this must be indicated using axis=1

If a single item from the DataFrame is desired, the sample() method has to be called twice.

12. Merging DataFrames

12.1. Concatenation of DataFrames

To concatenate multiple DataFrames we use the concat() method, where a list of the DataFrames to be joined is passed.

As can be seen, the indices 0, 1, 2, and 3 are repeated because each dataframe has those indices. To prevent this, you should use the parameter ignore_index=True.

If the concatenation was intended to be performed along the columns, the variable axis=1 should have been used.

12.1.1. Intersection of Concatenation

There are two ways to perform the concatenation, either taking all the indices from the DataFrames or only those that match. This is determined by the join variable, which accepts the values 'outer' (default) (takes all indices) or 'inner' (only those that match).

Let's see an example of 'outer'

Let's see an example of 'inner'

12.2. Merge of DataFrames

We previously created a new dataframe by merging several dataframes. Now we can complete one dataframe with another using merge, passing the parameter on to specify which column should be used for the merge.

In this case, both dataframes had a key with the same name (Key), but if we have dataframes where their keys are named differently, we can use the left_on and right_on parameters.