<jupyter_start><jupyter_text>Check that spark is running<jupyter_code>import org.apache.spark.sql._
val basedir = "spark-warehouse/prescription"<jupyter_output><empty_output><jupyter_text>Let's first compute some basic statistics about the patients<jupyter_code>%run src/scala/register_tables.scala
register_tables(spark, "spark-warehouse/")
val patients = spark.sql("select * from prescription_patients")
patients.createOrReplaceTempView("patients")
val prescriptions = spark.sql("select * from prescription_prescriptions")
prescriptions.createOrReplaceTempView("prescriptions")
val drugs  =spark.sql("select * from prescription_drugs")
drugs.createOrReplaceTempView("drugs")<jupyter_output><empty_output><jupyter_text>We count the number of patients born in every year present in the data<jupyter_code>val birthyearcounts = patients.select("birthyear")
    .groupBy("birthyear").count
    .sort("birthyear")
//submit the birthyearcounts as a table to the spark runtime
//it is now available for all spark apps attaching to the spark runtime
birthyearcounts.createOrReplaceTempView("birthyearcounts")
spark.sql("select * from birthyearcounts").show(5)<jupyter_output>+---------+-----+
|birthyear|count|
+---------+-----+
|     1895|    1|
|     1897|    1|
|     1898|    3|
|     1899|    3|
|     1900|   12|
+---------+-----+
only showing top 5 rows<jupyter_text>Now let's do some analyses with python* We may type python code by using the %%python magic word<jupyter_code>%%python
#magic function for plotting, since the spylon-kernel needs tempfiles to display images
def plotfig_magic():
    fo = tempfile.NamedTemporaryFile(suffix=".png", mode="w")
    fo.close()
    plt.savefig(fo.name)
    return Image(filename=fo.name)
%%python
spark<jupyter_output><empty_output><jupyter_text>Note that the table may also be accessed from the IPython runtime<jupyter_code>%%python
birthyearcounts = spark.sql("select * from birthyearcounts")
birthyearcounts.show(5)<jupyter_output>+---------+-----+
|birthyear|count|
+---------+-----+
|     1895|    1|
|     1897|    1|
|     1898|    3|
|     1899|    3|
|     1900|   12|
+---------+-----+
only showing top 5 rows<jupyter_text>Plot the distribution of patients by birth year<jupyter_code>%%python

patients = spark.sql("select * from patients")
patientDF=patients.toPandas()
total_patients = patientDF.count()

def sample_from_patients(frac):
    sample = patients.sample(True, .1).groupBy("birthyear").count().sort("birthyear").toPandas()
    sample["count"]=sample["count"]/sample["count"].sum()
    return sample

print("ok")<jupyter_output>ok<jupyter_text>Take the DF Birthyearcounts<jupyter_code>%%python
import pandas as pd
import matplotlib.pyplot as plt
from IPython.display import Image
import tempfile
df = birthyearcounts.toPandas()<jupyter_output><empty_output><jupyter_text>Convert the birthyearcounts to density function format (divide by column sum)<jupyter_code>%%python
df["count"]=df["count"]/df["count"].sum()
print(df[:5])
print("\nsum:",df["count"].sum())<jupyter_output>birthyear         count
0       1895  1.893713e-07
1       1897  1.893713e-07
2       1898  5.681138e-07
3       1899  5.681138e-07
4       1900  2.272455e-06

sum: 1.0000000000000002<jupyter_text>Let's plot multiple samples from the patients dataframe and see if there's any variance - This is a somewhat bootstrap-like procedure - resample the data set with replacement<jupyter_code>%%python
plt.clf()
#ax = figure
ax = df.plot(x="birthyear",y="count")
i=0
#plot multiple figures onto the same axis
while i<10:
    sample = sample_from_patients(1/10)
    sample.plot(x="birthyear", y="count", ax=ax)
    i+=1

ax.legend_.remove()
retval = plotfig_magic()<jupyter_output><empty_output><jupyter_text>Plot the distribution of prescriptions by age<jupyter_code>//val id_with_birthyear = patients.select("id", "birthyear")
spark.sql("select pre.id, pat.birthyear from prescriptions pre, patients pat where pre.id = pat.id")
    .groupBy("birthyear")
    .count.sort("birthyear").createOrReplaceTempView("prescription_counts_by_birthyear")

%%python
pres_c_year = spark.sql("select * from prescription_counts_by_birthyear").toPandas()

%%python
plt.clf()
pres_c_year["count"] = pres_c_year["count"]/pres_c_year["count"].sum()
prescription_counts_pdf = pres_c_year

ax = pres_c_year.plot(x="birthyear", y="count", label="distr. of prescriptions by birthyear")
retval = plotfig_magic()<jupyter_output><empty_output><jupyter_text>Now that we've normalized the distributions, we can apply them on top of each other Observe the differences<jupyter_code>%%python
plt.clf()
#plot prescriptions
ax = pres_c_year.plot(x="birthyear", y="count", label="distr. prescriptions by birthyear")
#plot distribution of people
print(df["count"].sum())
print(pres_c_year["count"].sum())

df.plot(x="birthyear",y="count", label="distr. patients by birthyear", ax=ax)
retval = plotfig_magic()
spark.sql("show tables").show<jupyter_output>+--------+--------------------+-----------+
|database|           tableName|isTemporary|
+--------+--------------------+-----------+
|        |     birthyearcounts|       true|
|        |               drugs|       true|
|        |              elders|       true|
|        |        elders_drugs|       true|
|        |     elders_patients|       true|
|        |elders_prescriptions|       true|
|        |          npr_elders|       true|
|        |            patients|       true|
|        |prescription_coun...|       true|
|        |  prescription_drugs|       true|
|        |prescription_pati...|       true|
|        |prescription_pres...|       true|
|        |       prescriptions|       true|
+--------+--------------------+-----------+
{'fraction_non_alphanumeric': 0.156140350877193, 'fraction_numerical': 0.02666666666666667, 'mean_word_length': 3.6960461285008237, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 40, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '543096', 'license': 'MIT', 'n_tokens_mistral': 1972, 'n_tokens_neox': 1819, 'n_words': 406, 'path': 'tsk_20181001_analysis_prescriptions_and_patients_distr_by_birthyear.ipynb', 'repo_name': 'uit-hdl/norpd_prescription_analyses', 'size': '5700'}