A hidden fact you must know when working with pandas

![hidden fact.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2917078/4905066f-0184-f800-ffca-8cca584e5f15.jpeg)

Hello, my name is Sourav Saha, a member of NEC Digital Technology Research and Development Laboratories. This is an article for [NEC デジタルテクノロジー開発研究所 Advent Calendar 2023](https://qiita.com/advent-calendar/2023/nec-dt-labs) **Day 16**.

Recently I came across a few examples from some performance-critical data analysis applications that revealed some hidden costs while working with pandas. I am going to explain one of them in today's blog.

Let's first look at the below example:
```python
ret = pd.DataFrame()
ret["x"] = s1 * 2 # Line-1
ret["y"] = s2 * 2 # Line-2
```

This is simply multiplying two series data and assigning the result into a data frame. Both `s1` and `s2` have 100 million elements of type float64. 

<font color=green> Still, they differ in their execution time to a great extent as shown below: </font>

![etime_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2917078/95727c05-311c-7de4-4433-0191e8ac9b00.png)

<font color=blue> :point_right: **Can you guess why?** </font>

Well, the fact amazed me when I first encountered this situation. 
In pandas, the series and data frames are labeled with an “index” column (useful for multiple purposes).

Whenever an assignment (=) operation takes place, pandas first checks whether the index columns for both side of the expression are equally aligned or not. If not aligned equally, it performs an expensive `reindex` operation to align the data on the right-hand side of the assignment operator according to the data on the left-hand side.

In the above example, `s1` and `s2` have different index layouts (due to some sorting).
Hence **Line-1** only involves the computation cost of a binary multiplication, whereas **Line-2** involves the cost of a binary multiplication, along with the cost of index-alignment as depicted in the following figure.

![ex_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2917078/a4b17824-7042-022f-c3fe-c3773a9d1305.png)

Because of this expensive `reindex` operation, the execution of <ins> Line-2 consumes almost 50 times the execution time for Line-1 </ins>.

Needless to say, it is very crucial to take care of such assignments when performing within a loop.
For example, below is a sample program that attempts to perform the multiplication of each numeric column in the input data frame, `df` with 2. For each column that contains missing values (nulls), it sorts the data by the `dates` column, performs a forward fill operation to fill the missing values for those columns, and finally performs the multiplication on the modified column:

```python
ret = pd.DataFrame()
for c in df.select_dtypes("number").columns:
    if any(df[c].isnull())
        tmp = df.sort_values("dates")[c].ffill()
        ret[c] = tmp * 2
    else:
        ret[c] = df[c] * 2
```

There are two performance issues involved in the `if` part of the above program. The clearly visible one is the `sort` operation which is performed on the same data on every execution of the `if` part within the loop. Hence, let's first work on to optimize that:

```python
# sorting and forward-filling are performed only once
sorted_df = df.sort_values("dates").ffill() 

ret = pd.DataFrame()
for c in df.select_dtypes("number").columns:
    if any(df[c].isnull()):
        ret[c] = sorted_df[c] * 2
    else:
        ret[c] = df[c] * 2
```

<font color=salmon> The invisible performance factor is involved in the line: </font> <font color=blue> **`ret[c] = sorted_df[c] * 2`**. </font>
Here it is trying to multiply target columns from `sorted_df` with 2 and assigning into the resultant dataframe.

After the sorting operation, `index` columns of `df` and `sorted_df` are no longer the same. Hence, whenever the `if` part is executed, along with the binary multiplication, an expensive `reindex` operation will take place making the above program very slow.

**Hence, such assignments inside a loop should be avoided wherever possible.**
One can perform the multiplication on the `sorted_df` and then perform `sort_index` on the result to get the intended result as shown below:

```python
# sorting and forward-filling is performed only once
sorted_df = df.sort_values("dates").ffill()
target_input = sorted_df.select_dtypes("number")
ret = target_input * 2
ret = ret.sort_index() # to make the result as expected in the original program
```

Hope these tips will help you to accelerate your analysis when working with pandas. It is recommended to use an alternative python library developed at our laboratory, named `FireDucks`.

It is a multithreaded library accelerated with compiler technology enabling you to perform faster analysis even with the above situation (it can auto-optimize the first case, i.e., performing sort operation only once and reuse the same whenever needed) and is publicly available for use.

https://fireducks-dev.github.io/en/

You may like to check out one of my [articles](https://qiita.com/qsourav/items/e87f25c4b307391d784a) explaining key features of `FireDucks`, along with its different layers of optimization to realize its potential to be used as a drop-in replacement for pandas.

# Wrapping-up
Thank you for reading so far. :bow: :gift: :rose: 

To summarize, I have discussed a few tips one should avoid while working with pandas. In the actual situation, just by performing the above optimization, we could successfully reduce the execution time of a performance-critical application from **2 hours 5 mins** to **1 hour 30 mins**. Using FireDucks further makes it possible to reduce the overall analysis time to **56 mins**. With some further optimization, we could reduce the overall analysis time within 10 mins. Let's discuss a few of them in some other blog. 

We recommend getting it installed using `pip` and try using it in your pandas application to enjoy its benefit. We are continuously striving towards improving its performance and releasing the same at frequent intervals. Please stay tuned with [us](https://fireducks-dev.github.io/en/docs/about-us/about-us/) and keep sharing your valuable feedback on any execution issues you may face or optimization requests you may have. We are eagerly looking forward to hearing from you!