pandas

pandas is a Python library for data manipulation and analysis.

Panda Eating Bamboo

Libaries

Notes

  • pandas.io.json.json_normalize is a function to normalize structured JSON into a flat dataframe. Useful for working with data that comes from an JSON API.

Snippets

Connect to a SQLite database

import pandas as pd
import sqlite

conn = sqlite3.connect("database.sqlite")
df = pd.read_sql_query("SELECT * FROM table_name;", conn)

df.head()

Using a SQLAlchemy engine to connect to a database

import pandas as pd
import numpy as np
from sqlalchemy import create_engine

engine = create_engine("postgresql:///database")

df = pd.read_sql_query("SELECT * FROM table;", con=engine)

df.head()

Python compatible column names with slugify

Usually I'm dealing with data from external sources that don't have pretty columns names. I like to use slugify to convert them to Python compatible keys.

from slugify import slugify

df.columns = [slugify(c, separator="_", to_lower=True) for c in df.columns]

Read CSV file with all cells as strings

>>> df = pd.read_csv("data/source/example.csv", dtype=str)
>>> df.dtypes
ID                         object
NAME                       object
SALARY                     object

Traspose DataFrame and view all rows

>>> with pd.option_context("display.max_rows", None):
...    print(df.head(1).transpose())
ID                         1
NAME       Myles Braithwaite
SALARY               $10,000

Read a CSV file data in chunk size

Sometimes a CSV is just to large for the memory on your computer. You can tell the argument chunksize how many rows of data you would like to load.

df_chunks = pd.read_csv("data/source/data.csv", chunksize=10000)

df_chunks[0].head()

If you would like to load the scale down the data and load the it into one pd.DataFrame:

def chunk_preprocessing(df):
    """Preprocess a chunk of the data before analysis.
    
    Arguments
    β€”β€”β€”β€”β€”β€”β€”β€”-
    df : `pd.DataFrame`
        The chunk of the data source that needs to be processed.
    """
    pass


df_chunks_processed = [
    chunk_preprocessing(df)
    for df in df_chunks
]

df_concat = pd.concat(df_chunks_processed)

Pandas/SQL Rosetta Stone

IN / pandas.DataFrame.isin

SELECT
	*
FROM table
WHERE
	city IN (
		"Toronto",
		"Richmond Hill"
	);
# City is ether Toronto or Richmond Hill:
df[df['city'].isin(['Toronto', 'Richmond Hill'])]

# City is not Markdale or Oakville:
df[~df['city'].isin(['Markdale', 'Oakville'])]

See the pandas documentation for more information on pandas.DataFrame.isin.

Last Updated: 4/18/2019, 3:43:24 PM