Exploring data with Jupyter Notebook, Pandas and CrateDB
Tags: database, datascience, opensource, beginners
Published: 2021-04-15 15:07:20

In a [previous post](https://dev.to/carlotasoto/integrating-cratedb-with-jupyter-56de-temp-slug-8209101?preview=187e5732cd6f41ff94951c8bdec994bde2f90b94df24df87b962973f2725ac65b8f7bae073bcfc7a34197983c7d0fad13ef3f19dfc03387f80c617b5), I showed you how to pair [CrateDB](https://crate.io) (an open-source database specialized in machine data) and [Jupyter Notebook](https://jupyter.org/), an application that allows you to create and share documents containing live code. I also told you how to load two different sample datasets into CrateDB, one recording the position of the ISS and another containing information about taxi trips in NYC.

In this post, I'll play a little bit with the NYC taxi dataset using [Pandas](https://pandas.pydata.org) and [Matplotlib](https://matplotlib.org). This will be a simple example, but you can take it from here and [explore further what you can do with these tools](https://www.learndatasci.com/tutorials/python-pandas-tutorial-complete-introduction-for-beginners/).

Before continuing, make sure you take it from [where we left it](https://dev.to/carlotasoto/integrating-cratedb-with-jupyter-56de-temp-slug-8209101?preview=187e5732cd6f41ff94951c8bdec994bde2f90b94df24df87b962973f2725ac65b8f7bae073bcfc7a34197983c7d0fad13ef3f19dfc03387f80c617b5), i.e. with Jupyter Notebook running and the NYC taxi dataset loaded into CrateDB. 

To start, let's import our libraries. We will be importing pandas and matplotlib. Open a new Jupyter Notebook and run the following code:

```python
import pandas as pd

import matplotlib.pyplot as plt
from matplotlib import rcParams
plt.style.use ('ggplot')

```

If there's no error, you will see no return. 

Now, let's query CrateDB from Jupyter. This is how you can do it: 

```python
limit = 1000000
sql_query = """
  SELECT total_amount, trip_distance FROM nyc_taxi_sample
LIMIT 1000000;
""".format(limit)
try:
    df_data = pd.read_sql(
    sql_query, 'crate://localhost:4200')
    display(df_data.head(5))
except Exception as error:
    print(error)
```

This query asks CrateDB for a table containing the price of each taxi trip and its distance, and with the help of pandas, we're defining a function called df_data with this result. Besides, we're asking to display a table with the first 5 values in our notebook.

If everything goes well, you will see such table showing up:

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

Next, let's see how our data looks like by building our first plot:

```python
df_data.plot.scatter(x='trip_distance',y='total_amount')
plt.show()
```
After running the command above, a price vs distance plot will display in your notebook:

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

We notice some interesting things; for example, there are some anomalies, most notably what appears to be negative trip distances. Datasets are rarely perfect... Especially those with millions of data points. 

We can change our original query, so it only includes positive values in the return:

```python
limit = 1000000
sql_query = """
  SELECT total_amount, trip_distance FROM nyc_taxi_sample
  WHERE total_amount> '0' AND trip_distance> '0'
LIMIT 1000000;
""".format(limit)
try:
    df_data = pd.read_sql(
    sql_query, 'crate://localhost:4200')
    display(df_data.head(5))
except Exception as error:
    print(error)
```

Again, if it's successful, you will see a table with the first five data points. 

Let's plot the data again:

```python
df_data.plot.scatter(x='trip_distance',y='total_amount')
plt.show()
```

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

Now, let's zoom in a little, limiting our x axis to 75 and our y axis to 1000. You can do that like this:

```python
df_data.plot.scatter(x='trip_distance',y='total_amount')
plt.xlim([0,75])
plt.ylim([0,1000])
plt.show()
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8ab5pytxfi2v6iws8rp0.png)

Let's zoom in even more:

```python
df_data.plot.scatter(x='trip_distance',y='total_amount')
plt.xlim([0,50])
plt.ylim([0,300])
plt.show()
```

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

Nice! 

To finish with this little exploration, let's estimate the linear function describing the relationship both variables. 

In order to do this, let's use [SciPy](https://www.scipy.org): 

```python
import scipy.stats
slope, intercept, r, p, stderr = scipy.stats.linregress(df_data.trip_distance, df_data.total_amount)
```
Next, run the command below. It will return you a string with the equation of the regression line and the value of the Pearson correlation coefficient:

```python
line = f'Regression line: y={intercept:.2f}+{slope:.2f}x, r={r:.2f}'
line
```

Something like this:

`'Regression line: y=10.27+3.19x, r=0.75'`

To end, if you want to show the regression line in a plot it together with our data, this is how you can do it:

```python
fig, ax = plt.subplots()
ax.plot(df_data.trip_distance, df_data.total_amount, linewidth=0, marker='s', label='Data points')
ax.plot(df_data.trip_distance, intercept + slope * df_data.trip_distance, label=line)
ax.set_xlabel('trip_distance')
ax.set_ylabel('total_amount')
ax.legend(facecolor='white')
plt.show()
```

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

Hope this was fun!