# More columns
pd.set_option("display.max_columns", 500)

# More rows
pd.set_option("display.max_rows", 500)

# Floating point precision
pd.set_option("display.precision", 3)

# Increase column width
pd.set_option("max_colwidth", 50)

# Change default plotting backend - Pandas >= 0.25
# https://github.com/PatrikHlobil/Pandas-Bokeh
pd.set_option("plotting.backend", 'pandas_bokeh')

pd.read_csv(
    "data.csv.gz",
    delimiter = "^",
    # line numbers to skip (i.e. headers in an excel report)
    skiprows = 2,
    # used to denote the start and end of a quoted item
    quotechar = "|",
    # return a subset of columns
    usecols = ["return_date", "company", "sales"],
    # data type for data or columns
    dtype = { "sales": np.float64 },
    # additional strings to recognize as NA/NaN
    na_values = [".", "?"],
    # convert to datetime, instead of object
    parse_dates = ["return_date"],
    # for on-the-fly decompression of on-disk data
    # options - gzip, bz2, zip, xz
    compression = "gzip",
    # encoding to use for reading
    encoding = "latin1",
    # read in a subset of data
    nrows = 100
)

pd.read_csv("https://bit.ly/2KyxTFn")

# Requires s3fs library
pd.read_csv("s3://pandas-test/tips.csv")

pd.read_excel("numbers.xlsx", sheet_name="Sheet1")

# Multiple sheets with varying parameters
with pd.ExcelFile("numbers.xlsx") as xlsx:
    df1 = pd.read_excel(xlsx, "Sheet1", na_values=["?"])
    df2 = pd.read_excel(xlsx, "Sheet2", na_values=[".", "Missing"])

import glob

# ignore_index = True to avoid duplicate index values
df = pd.concat([pd.read_csv(f) for f in glob.glob("*.csv")], ignore_index = True)

# More options
df = pd.concat([pd.read_csv(f, encoding = "latin1") for f in glob.glob("*.csv")])

import os
import glob

files = [os.path.join(root, file)
        for root, dir, files in os.walk("./directory")
        for file in glob.glob("*.csv")]

import sqlite3

conn = sqlite3.connect("flights.db")
df = pd.read_sql_query("select * from airlines", conn)
conn.close()

from sqlalchemy import create_engine

# Port 5439 for Redshift
engine = create_engine("postgresql://user@localhost:5432/mydb")
df = pd.read_sql_query("select * from airlines", engine)

# Get results in chunks
for chunk in pd.read_sql_query("select * from airlines", engine, chunksize=5):
    print(chunk)

# Writing back
df.to_sql(
    "table"
    schema="schema"
    # fail, replace or append
    if_exists="append",
    # write back in chunks
    chunksize = 10000
)

from pandas.io.json import json_normalize

json_normalize(data, "counties", ["state", "shortname", ["info", "governor"]])

# How deep to normalize - Pandas >= 0.25
json_normalize(data, max_level=1)

# Lower all values
df.columns = [x.lower() for x in df.columns]

# Strip out punctuation, replace spaces and lower
df.columns = df.columns.str.replace("[^\w\s]", "").str.replace(" ", "_").str.lower()

# Condense multiindex columns
df.columns = ["_".join(col).lower() for col in df.columns]

# Double transpose to remove bottom row for multiindex columns
df.T.reset_index(1, drop=True).T

df[df["dimension"].isin(["A", "B", "C"])]

# not in
df[~df["dimension"].isin(["A", "B", "C"])]

df[df["dimension"].str.contains("word")]

# not in
df[~df["dimension"].str.contains("word")]

df.query("salary > 100000")

df.query("name == 'john'")

df.query("name == 'john' | name == 'jack'")

df.query("name == 'john' and salary > 100000")

df.query("name.str.contains('a')")

# Grab top 1% of earners
df.query("salary > salary.quantile(.99)")

# Make more than the mean
df.query("salary > salary.mean()")

# Subset by top 3 most frequent products purchased
df.query("item in item.value_counts().nlargest(3).index")

# Query for null values
df.query("column.isnull()")

# Query for non-nulls
df.query("column.notnull()")

# @ - allows you to refer to variables in the environment
names = ["john", "fred", "jack"]
df.query("name in @names")

# Reference columns with spaces using backticks - Pandas >= 0.25
df.query("`Total Salary` > 100000")

# Inner join
pd.merge(df1, df2, on = "key")

# Left join on different key names
pd.merge(df1, df2, right_on = ["right_key"], left_on = ["left_key"], how = "left")

def anti_join(x, y, on):
    """Return rows in x which are not present in y"""
    ans = pd.merge(left=x, right=y, how='left', indicator=True, on=on)
    ans = ans.loc[ans._merge == 'left_only', :].drop(columns='_merge')
    return ans

df.select_dtypes(include = "number")
df.select_dtypes(exclude = "object")

# Specify columns, so DataFrame isn't overwritten
df[["first_name", "last_name", "email"]] = df.select_dtypes(
    include = "object").apply(lambda x: x.str.lower()
)

df.loc[:, ::-1]

df.corr()

# With another DataFrame
df.corrwith(df_2)

df.describe(include=[np.number]).T

dims = df.describe(include=[pd.Categorical]).T

# Add percent frequency for top dimension
dims["frequency"] = dims["freq"].div(dims["count"])

styling_options = {"sales": "${0:,.0f}", "percent_of_sales": "{:.2%f}"}

df.style.format(styling_options)

df.style.highlight_max(color = "lightgreen").highlight_min(color = "red")

df.style.background(subset = ["measure"], cmap = "viridis")

# See NaNs as well
df["meaure"].value_counts(normalize = True, dropna = False)

df["sales"].str.replace("$", "")

df["steps_walked"].where(df["steps_walked"] > 0, 0)

(df.isnull().sum() / df.isnull().count()).sort_values(ascending=False)

df.drop(["column_a", "column_b"], axis = 1)

df.drop_duplicates(subset=["order_date", "product"], keep="first")

# Any column with 90% missing values will be dropped
df.dropna(thresh = len(df) * .9, axis = 1)

# Impute DataFrame with all zeroes
df.fillna(0)

# Impute column with all zeroes
df["measure"].fillna(0)

# Impute measure with mean of column
df["measure"].fillna(df["measure"].mean())

# Impute dimension with mode of column
df["dimension"].fillna(df["dimension"].mode())

# Impute by another dimension's mean
df["age"].fillna(df.groupby("sex")["age"].transform("mean"))

df.replace(".", np.nan)

df.replace(0, np.nan)

df["zipcode"].replace(".*[a-zA-Z].*", np.nan, regex=True)

df[(df != 0).all(1)]

df = df[(df.T != 0).any()]

(pd.read_csv("employee_salaries.csv")
    .query("salary > 0")
    .assign(sex = lambda df: df["sex"].replace({"female": 1, "male: 0}),
            age = lambda df: pd.cut(df["age"].fillna(df["age"].median()),
                                    bins = [df["age"].min(), 18, 40, df["age"].max()],
                                    labels = ["underage", "young", "experienced"]))
    .rename({"name_1": "first_name", "name_2": "last_name"})
)

def fix_headers(df):
    df.columns = df.columns.str.replace("[^\w\s]", "").str.replace(" ", "_").str.lower()
    return df
    
def drop_columns_missing(df, percent):
    df = df.dropna(thresh = len(df) * percent, axis = 1)
    return df

def fill_missing(df, value):
    df = df.fillna(value)
    return df

def replace_and_convert(df, col, orig, new, dtype):
    df[col] = df[col].str.replace(orig, new).astype(dtype)
    return df


(df.pipe(fix_headers)
    .pipe(drop_columns_missing, percent=0.3)
    .pipe(fill_missing, value=0)
    .pipe(replace_and_convert, col="sales", orig="$", new="", dtype=float)
)

# this
df.groupby("dimension", as_index = False)["measure"].sum()

# versus this
df.groupby("dimension")["measure"].sum().reset_index()

# H for hours
# D for days
# W for weeks
# WOM for week of month
# Q for quarter end
# A for year end
df.groupby(pd.Grouper(key = "date", freq = "M"))["measure"].agg(["sum", "mean"])

# count - number of non-null observations
# sum - sum of values
# mean - mean of values
# mad - mean absolute deviation
# median - arithmetic median of values
# min - minimum
# max - maxmimum
# mode - mode
# std - unbiased standard deviation
# first - first value
# last - last value
# nunique - unique values
df.groupby("dimension")["measure"].sum()

# Specific aggregations for columns
df.groupby("dimension").agg({"sales": ["mean", "sum"], "sale_date": "first", "customer": "nunique"})

pd.pivot_table(
    df,
    values=["sales", "orders"],
    index=["customer_id"],
    aggfunc={
        "sales": ["sum", "mean"],
        "orders": "nunique"
    }
)

# DataFrame - Version 1
df.groupby("country").agg(
    min_height = pd.NamedAgg(column = "height", aggfunc = "min"),
    max_height = pd.NamedAgg(column = "height", aggfunc = "max"),
    average_weight = pd.NamedAgg(column = "weight", aggfunc = np.mean)
)

# DataFrame - Version 2
df.groupby("country").agg(
    min_height=("height", "min"),
    max_heights=("height", "max"),
    average_weight=("weight", np.mean)
)

# Series
df.groupby("gender").height.agg(
    min_height="min",
    max_height="max"
)

df["sales"] = df.eval("price * quantity")

# Assign to different DataFrame
pd.eval("sales = df.price * df.quantity", target=df_2)

# Multiline assignment
df.eval("""
aov = price / quantity
aov_gt_50 = (price / quantity) > 50
top_3_customers = customer_id in customer_id.value_counts().nlargest(3).index
bottom_3_customers = customer_id in customer_id.value_counts().nsmallest(3).index
""")

np.where(df["gender"] == "Male", 1, 0)

np.where(df["measure"] < 5, "Low", np.where(df["measure"] < 10, "Medium", "High"))

conditions = [
    df["country"].str.contains("spain"),
    df["country"].str.contains("italy"),
    df["country"].str.contains("chile"),
    df["country"].str.contains("brazil")
]

choices = ["europe", "europe", "south america", "south america"]

data["continent"] = np.select(conditions, choices, default = "other")

values = {"Low": 1, "Medium": 2, "High": 3}

df["dimension"].map(values)

dimension_mappings = {v: k for k, v in enumerate(df["dimension"].unique())}

df["dimension"].map(dimension_mappings)

df["email"].str.split("@", expand = True)[0]

df["domain"] = [x.split("@")[1] for x in df["email"]]

import re

pattern = "([A-Z0-9._%+-]+)@([A-Z0-9.-]+)"

# Inserting colum headers, applied after extract
pattern = "(?P<email>[A-Z0-9._%+-]+)@(?P<domain>[A-Z0-9.-]+)"

# Generates two columns
email_components = df["email"].str.extract(pattern, flags=re.IGNORECASE)

df.pivot(index = "date", columns = "companies", values = "sales")

# this
df["mean_company_salary"] = df.groupby("company")["salary"].transform("mean")

# versus this
mean_salary = df.groupby("company")["salary"].agg("mean").rename("mean_salary").reset_index()
df_new = df.merge(mean_salary)

df["date"].dt.year
df["date"].dt.quarter
df["date"].dt.month
df["date"].dt.week
df["date"].dt.day
df["date"].dt.weekday
df["date"].dt.weekday_name
df["date"].dt.hour

# Days between
df["first_date"].sub(df["second_date"]).div(np.timedelta64(1, "D"))

# Months between
df["first_date"].sub(df["second_date"]).div(np.timedelta64(1, "M"))

# Equivalent to above
(df["first_date] - df["second_date"]) / np.timedelta64(1, "M")

df["is_weekend"] = np.where(df["date"].dt.dayofweek.isin([5, 6]), 1, 0)

df.sort_values(by=["customer_id, "order_date"])\
    .groupby("customer_id")["order_date"].shift(periods=1)

df.sort_values(by = ["customer_id", "order_date"])\
    .groupby("customer_id")["order_date"]\
    .diff()\
    .div(np.timedelta64(1, "D"))

df.sort_values(by = ["customer_id", "order_date"])\
    .groupby("customer_id")["order_date"]\
    .pct_change()

df["salary"].rank(pct=True)

import re

df["review"].str.count("great", flags=re.IGNORECASE)

df["unique_products"] = df.groupby("customer_id").agg({"products": "unique"})

# Transform each element -> row - Pandas >= 0.25
df["unique_products"].explode()

df.groupby("customer_id")["products"].value_counts().unstack().fillna(0)

pd.qcut(data["measure"], q = 4, labels = False)

# Numeric
pd.cut(df["measure"], bins = 4, labels = False)

# Dimension
pd.cut(df["age"], bins = [0, 18, 25, 99], labels = ["child", "young adult", "adult"])

# Use drop_first = True to avoid collinearity
pd.get_dummies(df, drop_first = True)

df.sort_values(by = "variable").groupby("dimension").first()

df["salary_minmax"] = (
    df["salary"] - df["salary"].min()) / (df["salary"].max() - df["salary"].min()
)

df["salary_zscore"] = (df["salary"] - df["salary"].mean()) / df["salary"].std()

# For positive data with no zeroes
np.log(df["sales"])

# For positive data with zeroes
np.log1p(df["sales"])

# Convert back - get predictions if target is log transformed
np.expm1(df["sales"])

from scipy import stats

# Must be positive
stats.boxcox(df["sales"])[0]

df["age_reciprocal"] = 1.0 / df["age"]

df["age_sqrt"] = np.sqrt(df["age"])

upper_limit = np.percentile(df["salary"].values, 99)
lower_limit = np.percentile(df["salary"].values, 1)

df["salary"].clip(lower = lower_limit, upper = upper_limit)

df.groupby("dimension")["target"].transform("mean")

from scipy import stats
import numpy as np

z = np.abs(stats.zscores(df))
df = df[(z < 3).all(axis = 1)]

q1 = df["salary"].quantile(0.25)
q3 = df["salary"].quantile(0.75)
iqr = q3 - q1

df.query("(@q1 - 1.5 * @iqr) <= salary <= (@q3 + 1.5 * @iqr)")

import geocoder

df["lat_long"] = df["ip"].apply(lambda x: geocoder.ip(x).latlng)

rfm = (
    df.groupby("customer_id")
    .agg(
        {
            "order_date": lambda x: (x.max() - x.min()).days,
            "order_id": "nunique",
            "price": "mean",
        }
    )
    .rename(
        columns={"order_date": "recency", "order_id": "frequency", "price": "monetary"}
    )
)

rfm_quantiles = rfm.quantile(q=[0.2, 0.4, 0.6, 0.8])

recency_conditions = [
    rfm.recency >= rfm_quantiles.recency.iloc[3],
    rfm.recency >= rfm_quantiles.recency.iloc[2],
    rfm.recency >= rfm_quantiles.recency.iloc[1],
    rfm.recency >= rfm_quantiles.recency.iloc[0],
    rfm.recency <= rfm_quantiles.recency.iloc[0],
]

frequency_conditions = [
    rfm.frequency <= rfm_quantiles.frequency.iloc[0],
    rfm.frequency <= rfm_quantiles.frequency.iloc[1],
    rfm.frequency <= rfm_quantiles.frequency.iloc[2],
    rfm.frequency <= rfm_quantiles.frequency.iloc[3],
    rfm.frequency >= rfm_quantiles.frequency.iloc[3],
]

monetary_conditions = [
    rfm.monetary <= rfm_quantiles.monetary.iloc[0],
    rfm.monetary <= rfm_quantiles.monetary.iloc[1],
    rfm.monetary <= rfm_quantiles.monetary.iloc[2],
    rfm.monetary <= rfm_quantiles.monetary.iloc[3],
    rfm.monetary >= rfm_quantiles.monetary.iloc[3],
]

ranks = [1, 2, 3, 4, 5]

rfm["r"] = np.select(recency_conditions, ranks, "other")
rfm["f"] = np.select(frequency_conditions, ranks, "other")
rfm["m"] = np.select(monetary_conditions, ranks, "other")

rfm["segment"] = rfm["r"].astype(str).add(rfm["f"].astype(str))

segment_map = {
    r"[1-2][1-2]": "hibernating",
    r"[1-2][3-4]": "at risk",
    r"[1-2]5": "cannot lose",
    r"3[1-2]": "about to sleep",
    r"33": "need attention",
    r"[3-4][4-5]": "loyal customers",
    r"41": "promising",
    r"51": "new customers",
    r"[4-5][2-3]": "potential loyalists",
    r"5[4-5]": "champions",
}

rfm["segment"] = rfm.segment.replace(segment_map, regex=True)

import numpy as np
from numpy import pi, deg2rad, cos, sin, arcsin, sqrt

def haversine(s_lat, s_lng, e_lat, e_lng):
    """
    determines the great-circle distance between two point
    on a sphere given their longitudes and latitudes
    """

    # approximate radius of earth in miles
    R = 3959.87433

    s_lat = s_lat * np.pi / 180.0
    s_lng = np.deg2rad(s_lng)
    e_lat = np.deg2rad(e_lat)
    e_lng = np.deg2rad(e_lng)

    d = (
        np.sin((e_lat - s_lat) / 2) ** 2
        + np.cos(s_lat) * np.cos(e_lat) * np.sin((e_lng - s_lng) / 2) ** 2
    )

    return 2 * R * np.arcsin(np.sqrt(d))


df['distance'] = haversine(
    df["start_lat"].values,
    df["start_long"].values,
    df["end_lat"].values,
    df["end_long"].values
)

def manhattan(s_lat, s_lng, e_lat, e_lng):
    """
    sum of horizontal and vertical distance between
    two points
    """
    a = haversine(s_lat, s_lng, s_lat, e_lng)
    b = haversine(s_lat, s_lng, e_lat, s_lng)
    return a + b

from pandas.api.types import union_categoricals

food = pd.Categorical(["burger king", "wendys"])
food_2 = pd.Categorical(["burger king", "chipotle"])

union_categoricals([food, food_2])

from pandas.util.testing import assert_frame_equal

# Methods for Series and Index as well
assert_frame_equal(df_1, df_2)

from pandas.api.types import is_numeric_dtype

is_numeric_dtype("hello world")
# False

from pandas.api.types import infer_dtype

infer_dtype(["john", np.nan, "jack"], skipna=True)
# string

infer_dtype(["john", np.nan, "jack"], skipna=False)
# mixed