Explore puzzles designed to enhance your critical thinking and problem-solving skills using pandas. Gain insights into common pitfalls and sharpen your data manipulation techniques.

test.tar.gz

test

test2

Puzzles are a great way to practice your critical thinking and problem-solving skills. This course assumes a basic understanding of pandas.  The teasers and puzzles in this course are designed to help you practice what you already know and help avoid future mistakes.

Pandas Brain Teasers

# Try it yourself
Try executing the code below to see the result.

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')
conn.executescript('''
CREATE TABLE visits (day DATE, hits INTEGER);
INSERT INTO visits VALUES
  ('2020-07-01', 300),
  ('2020-07-02', 500),
  ('2020-07-03', 900);
''')

df = pd.read_sql('SELECT * FROM visits', conn)
print('time span:', df['day'].max() - df['day'].min())

# Explanation
SQLite3 is a great single-file database that is used many times to
transfer data. It is widely used and heavily tested and can handle vast amounts of data up to 140 terabytes.

However, SQL does require some training.

In the teaser code, we create a `hits` table that has the following two columns:

* `day` column with SQL `DATE` type.
* `hits` column with SQL `INTEGER` type.

The mapping from SQL types to pandas types is defined in the SQL driver that is used to access the database. SQLite3 is a bit different from other
databases in that it only has numbers and strings as types. However, it does support declaring a column with a `DATE`, `TIME`, or `TIMESTAMP` type.
We can see that for ourselves when we look at the `.dtypes` below:
```py
In [1]: df.dtypes
Out[1]:
day object
hits int64
dtype: object
```
The `day` column has an `object` dtype, which in most cases, means it’s an `str`. When we do `df['day'].max() - df['day'].min()`, we’re subtracting two strings, which is not
a legal operation in Python.

We can convert data in a column to a pandas `Timestamp` either by using the `to_datetime` function or by passing the column names to convert in the `parse_dates` parameter of `read_sql`. However, we somehow need to know what columns are time related.

The better option is to use the `detect_types` parameter in `sqlite3.connect`.
When we pass `PARSE_DECLTYPES` to `sqlite3.connect`, it’ll convert `DATE, TIME`, and `TIMESTAMP` columns to Python’s `datetime` types. The parameter `read_sql` will convert these
`pandas.Timestamp` columns.

# Solution

Let's find out how to use SQL with pandas DataFrame.

Before We Begin

Puzzles

Wrapping Up

Read an SQLite File

Try it yourself