06 Two-way (column or row) relative frequency tables

%%html
<iframe width="700" height="400" src="https://www.youtube.com/embed/_ETPMszULXc/" frameborder="0" allowfullscreen></iframe>

import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import plotly.graph_objects as go
import seaborn as sns

import findspark

findspark.init()
from pyspark.context import SparkContext
from pyspark.sql import functions as F
from pyspark.sql.session import SparkSession

spark = SparkSession.builder.appName("statistics").master("local").getOrCreate()

WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.apache.spark.unsafe.Platform (file:/home/runner/work/statistics/spark-3.1.3-bin-hadoop3.2/jars/spark-unsafe_2.12-3.1.3.jar) to constructor java.nio.DirectByteBuffer(long,int)
WARNING: Please consider reporting this to the maintainers of org.apache.spark.unsafe.Platform
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release

22/07/21 02:32:02 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable

Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).

khanacademy

dataset = {
    "Car": 28 * ["SUV"] + 35 * ["Sport car"] + 97 * ["SUV"] + 104 * ["Sport car"],
    "Accident": 28 * ["yes"] + 35 * ["yes"] + 97 * ["no"] + 104 * ["no"],
}

df = pd.DataFrame(dataset)
df

	Car	Accident
0	SUV	yes
1	SUV	yes
2	SUV	yes
3	SUV	yes
4	SUV	yes
...	...	...
259	Sport car	no
260	Sport car	no
261	Sport car	no
262	Sport car	no
263	Sport car	no

264 rows × 2 columns

sdf = spark.createDataFrame(zip(*dataset.values()), schema=list(dataset.keys()))
sdf.registerTempTable("sdf_table")
sdf.show()

[Stage 0:>                                                          (0 + 1) / 1]

+---+--------+
|Car|Accident|
+---+--------+
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
|SUV|     yes|
+---+--------+
only showing top 20 rows

df.groupby(["Accident", "Car"]).size()

Accident  Car      
no        SUV           97
          Sport car    104
yes       SUV           28
          Sport car     35
dtype: int64

sdf.groupby("Accident", "Car").count().show()

[Stage 8:======================>                                 (41 + 1) / 100]

[Stage 8:=================================>                      (59 + 1) / 100]

[Stage 8:=============================================>          (81 + 1) / 100]

                                                                                

+--------+---------+-----+
|Accident|      Car|count|
+--------+---------+-----+
|      no|      SUV|   97|
|     yes|Sport car|   35|
|      no|Sport car|  104|
|     yes|      SUV|   28|
+--------+---------+-----+

spark.sql(
    "select Accident, Car, count(*) as count from sdf_table group by Accident, Car"
).show()

[Stage 18:====================================================>  (96 + 1) / 100]

                                                                                

+--------+---------+-----+
|Accident|      Car|count|
+--------+---------+-----+
|      no|      SUV|   97|
|     yes|Sport car|   35|
|      no|Sport car|  104|
|     yes|      SUV|   28|
+--------+---------+-----+

two_way_table = pd.crosstab(df["Accident"], df["Car"])
two_way_table

Car	SUV	Sport car
Accident
no	97	104
yes	28	35

s_two_way_table = sdf.crosstab("Accident", "Car")
s_two_way_table.show()

+------------+---+---------+
|Accident_Car|SUV|Sport car|
+------------+---+---------+
|         yes| 28|       35|
|          no| 97|      104|
+------------+---+---------+

freq_table = two_way_table.copy()
freq_table["SUV"] = two_way_table["SUV"] / two_way_table["SUV"].sum()
freq_table["Sport car"] = two_way_table["Sport car"] / two_way_table["Sport car"].sum()
freq_table

Car	SUV	Sport car
Accident
no	0.776	0.748201
yes	0.224	0.251799

s_freq_table = s_two_way_table
s_freq_table = s_freq_table.withColumn(
    "SUV", F.col("SUV") / s_freq_table.select(F.sum("SUV")).collect()[0][0]
)
s_freq_table = s_freq_table.withColumn(
    "Sport car",
    F.col("Sport car") / s_freq_table.select(F.sum("Sport car")).collect()[0][0],
)
s_freq_table.show()

+------------+-----+------------------+
|Accident_Car|  SUV|         Sport car|
+------------+-----+------------------+
|         yes|0.224|0.2517985611510791|
|          no|0.776|0.7482014388489209|
+------------+-----+------------------+

freq_table = freq_table.append(freq_table.sum().rename("Total"))
freq_table

/tmp/ipykernel_4071/3552347670.py:1: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.
  freq_table = freq_table.append(freq_table.sum().rename("Total"))

Car	SUV	Sport car
Accident
no	0.776	0.748201
yes	0.224	0.251799
Total	1.000	1.000000