SCIO-PySpark-DF-Day2.ipynb

{"cells":[{"cell_type":"code","source":["df = spark.createDataFrame([(1, 4), (2, 5), (3, 6)], [\"A\", \"B\"])\ndf2 = spark.createDataFrame([(1, 4), (2, 5), (3, 6)], [\"A\", \"B\"])"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":1},{"cell_type":"code","source":["display(df)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>A</th><th>B</th></tr></thead><tbody><tr><td>1</td><td>4</td></tr><tr><td>2</td><td>5</td></tr><tr><td>3</td><td>6</td></tr></tbody></table></div>"]}}],"execution_count":2},{"cell_type":"code","source":["df.count()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"><span class=\"ansired\">Out[</span><span class=\"ansired\">3</span><span class=\"ansired\">]: </span>3\n</div>"]}}],"execution_count":3},{"cell_type":"code","source":["df = df.withColumn('C',df.A + df.B)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":4},{"cell_type":"code","source":["display(df)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr><td>1</td><td>4</td><td>5</td></tr><tr><td>2</td><td>5</td><td>7</td></tr><tr><td>3</td><td>6</td><td>9</td></tr></tbody></table></div>"]}}],"execution_count":5},{"cell_type":"code","source":["display(df[['A']])"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>A</th></tr></thead><tbody><tr><td>1</td></tr><tr><td>2</td></tr><tr><td>3</td></tr></tbody></table></div>"]}}],"execution_count":6},{"cell_type":"code","source":["from pyspark.sql.functions import lit\ndf.withColumn('C',lit(5)).show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+---+---+\n  A|  B|  C|\n+---+---+---+\n  1|  4|  5|\n  2|  5|  5|\n  3|  6|  5|\n+---+---+---+\n\n</div>"]}}],"execution_count":7},{"cell_type":"code","source":["df.select('*',(df.A > 2).alias(\"State\"),(lit(2)).alias(\"StateB\"),(df.A+df.B).alias(\"StateC\")).show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+---+---+-----+------+------+\n  A|  B|  C|State|StateB|StateC|\n+---+---+---+-----+------+------+\n  1|  4|  5|false|     2|     5|\n  2|  5|  7|false|     2|     7|\n  3|  6|  9| true|     2|     9|\n+---+---+---+-----+------+------+\n\n</div>"]}}],"execution_count":8},{"cell_type":"code","source":["from pyspark.sql import functions as F"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":9},{"cell_type":"code","source":["df.withColumn('N',F.when((df.B > 5) & (df.C >9),20).when(df.A > 2,10).otherwise(1)).show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+---+---+---+\n  A|  B|  C|  N|\n+---+---+---+---+\n  1|  4|  5|  1|\n  2|  5|  7|  1|\n  3|  6|  9| 10|\n+---+---+---+---+\n\n</div>"]}}],"execution_count":10},{"cell_type":"code","source":["df = spark.createDataFrame([('a',33), ('b',11), ('a',22)],['names','age'])"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":11},{"cell_type":"code","source":["gb = df.groupBy(['names'])"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":12},{"cell_type":"code","source":["display(gb.avg('age'))"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>names</th><th>avg(age)</th></tr></thead><tbody><tr><td>b</td><td>11.0</td></tr><tr><td>a</td><td>27.5</td></tr></tbody></table></div>"]}}],"execution_count":13},{"cell_type":"code","source":["display(gb.count())"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>names</th><th>count</th></tr></thead><tbody><tr><td>b</td><td>1</td></tr><tr><td>a</td><td>2</td></tr></tbody></table></div>"]}}],"execution_count":14},{"cell_type":"code","source":["from pyspark.sql.functions import pandas_udf, PandasUDFType"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":15},{"cell_type":"code","source":["from pyspark.sql.functions import udf\nfrom pyspark.sql.types import IntegerType"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":16},{"cell_type":"code","source":["def func(x):\n  return int(x)+5\n\nstrToInt = udf( func, IntegerType())"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":17},{"cell_type":"code","source":["df = spark.createDataFrame([('1',1),('2',2)],['a','b'])"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":18},{"cell_type":"code","source":["df.show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+---+\n  a|  b|\n+---+---+\n  1|  1|\n  2|  2|\n+---+---+\n\n</div>"]}}],"execution_count":19},{"cell_type":"code","source":["df.withColumn('X',strToInt(df.a)).show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+---+---+\n  a|  b|  X|\n+---+---+---+\n  1|  1|  6|\n  2|  2|  7|\n+---+---+---+\n\n</div>"]}}],"execution_count":20},{"cell_type":"code","source":["titanic_data = spark.read.csv('/FileStore/tables/titanic_train-ac800.csv', header=True, inferSchema=True)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":21},{"cell_type":"code","source":["def getStrLen(s):\n  return len(s)\n\nstrLenUDF = udf( getStrLen, IntegerType())"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":22},{"cell_type":"code","source":["titanic_data.withColumn('NameLen',strLenUDF(titanic_data.Name)).count()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"><span class=\"ansired\">Out[</span><span class=\"ansired\">56</span><span class=\"ansired\">]: </span>891\n</div>"]}}],"execution_count":23},{"cell_type":"code","source":["@pandas_udf('int', PandasUDFType.SCALAR)\ndef strLenPUDF(c):\n  return c.str.len()\n\ntitanic_data.withColumn('a',strLenPUDF(titanic_data.Name)).count()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"><span class=\"ansired\">Out[</span><span class=\"ansired\">59</span><span class=\"ansired\">]: </span>891\n</div>"]}}],"execution_count":24},{"cell_type":"code","source":["df = spark.range(100)\nsqr = udf(lambda x:x*x, IntegerType())\ndf = df.withColumn('sqr',df.id)\ndf = df.withColumn('key',df.sqr%3)\ndf.show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+---+---+\n id|sqr|key|\n+---+---+---+\n  0|  0|  0|\n  1|  1|  1|\n  2|  2|  2|\n  3|  3|  0|\n  4|  4|  1|\n  5|  5|  2|\n  6|  6|  0|\n  7|  7|  1|\n  8|  8|  2|\n  9|  9|  0|\n 10| 10|  1|\n 11| 11|  2|\n 12| 12|  0|\n 13| 13|  1|\n 14| 14|  2|\n 15| 15|  0|\n 16| 16|  1|\n 17| 17|  2|\n 18| 18|  0|\n 19| 19|  1|\n+---+---+---+\nonly showing top 20 rows\n\n</div>"]}}],"execution_count":25},{"cell_type":"code","source":["import numpy as np\nimport pandas as pd\n\n@pandas_udf(\"key int, sqr int\", PandasUDFType.GROUPED_MAP)\ndef func(pdf):\n  print (pdf)\n  sqr = pdf.sqr\n  r= pdf.assign(sqr=np.mean(pdf.sqr))\n  print (r)\n  return r\n\ndf.select(['key','sqr']).groupBy(\"key\").apply(func).distinct().show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+---+\nkey|sqr|\n+---+---+\n  1| 49|\n  2| 50|\n  0| 49|\n+---+---+\n\n</div>"]}}],"execution_count":26},{"cell_type":"code","source":["import numpy as np\n\n@pandas_udf(\"key int, sqr int\", PandasUDFType.GROUPED_MAP)\ndef func(pdf):\n  print (pdf)\n  sqr = pdf.sqr\n  return pdf.assign(sqr=(np.sum(pdf.sqr)))\n\ndf.select(['key','sqr']).groupBy(\"key\").apply(func).distinct().show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+---------+\nkey|      sqr|\n+---+---------+\n  0|166167000|\n  1|166666500|\n+---+---------+\n\n</div>"]}}],"execution_count":27},{"cell_type":"code","source":["strLenPUDF(titanic_data.Name)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"><span class=\"ansired\">Out[</span><span class=\"ansired\">69</span><span class=\"ansired\">]: </span>Column&lt;strLenPUDF(Name)&gt;\n</div>"]}}],"execution_count":28},{"cell_type":"code","source":["@pandas_udf('string', PandasUDFType.SCALAR)\ndef strLenPUDF(c):\n  return c.map(lambda n: \"May b Male\" if \"Mr.\" in n else \"May be Female\")\n\ntitanic_data.withColumn('FindGender',strLenPUDF(titanic_data.Name)).show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+-----------+--------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+-------------+\nPassengerId|Survived|Pclass|                Name|   Sex| Age|SibSp|Parch|          Ticket|   Fare|Cabin|Embarked|   FindGender|\n+-----------+--------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+-------------+\n          1|       0|     3|Braund, Mr. Owen ...|  male|22.0|    1|    0|       A/5 21171|   7.25| null|       S|   May b Male|\n          2|       1|     1|Cumings, Mrs. Joh...|female|38.0|    1|    0|        PC 17599|71.2833|  C85|       C|May be Female|\n          3|       1|     3|Heikkinen, Miss. ...|female|26.0|    0|    0|STON/O2. 3101282|  7.925| null|       S|May be Female|\n          4|       1|     1|Futrelle, Mrs. Ja...|female|35.0|    1|    0|          113803|   53.1| C123|       S|May be Female|\n          5|       0|     3|Allen, Mr. Willia...|  male|35.0|    0|    0|          373450|   8.05| null|       S|   May b Male|\n          6|       0|     3|    Moran, Mr. James|  male|null|    0|    0|          330877| 8.4583| null|       Q|   May b Male|\n          7|       0|     1|McCarthy, Mr. Tim...|  male|54.0|    0|    0|           17463|51.8625|  E46|       S|   May b Male|\n          8|       0|     3|Palsson, Master. ...|  male| 2.0|    3|    1|          349909| 21.075| null|       S|May be Female|\n          9|       1|     3|Johnson, Mrs. Osc...|female|27.0|    0|    2|          347742|11.1333| null|       S|May be Female|\n         10|       1|     2|Nasser, Mrs. Nich...|female|14.0|    1|    0|          237736|30.0708| null|       C|May be Female|\n         11|       1|     3|Sandstrom, Miss. ...|female| 4.0|    1|    1|         PP 9549|   16.7|   G6|       S|May be Female|\n         12|       1|     1|Bonnell, Miss. El...|female|58.0|    0|    0|          113783|  26.55| C103|       S|May be Female|\n         13|       0|     3|Saundercock, Mr. ...|  male|20.0|    0|    0|       A/5. 2151|   8.05| null|       S|   May b Male|\n         14|       0|     3|Andersson, Mr. An...|  male|39.0|    1|    5|          347082| 31.275| null|       S|   May b Male|\n         15|       0|     3|Vestrom, Miss. Hu...|female|14.0|    0|    0|          350406| 7.8542| null|       S|May be Female|\n         16|       1|     2|Hewlett, Mrs. (Ma...|female|55.0|    0|    0|          248706|   16.0| null|       S|May be Female|\n         17|       0|     3|Rice, Master. Eugene|  male| 2.0|    4|    1|          382652| 29.125| null|       Q|May be Female|\n         18|       1|     2|Williams, Mr. Cha...|  male|null|    0|    0|          244373|   13.0| null|       S|   May b Male|\n         19|       0|     3|Vander Planke, Mr...|female|31.0|    1|    0|          345763|   18.0| null|       S|May be Female|\n         20|       1|     3|Masselmani, Mrs. ...|female|null|    0|    0|            2649|  7.225| null|       C|May be Female|\n+-----------+--------+------+--------------------+------+----+-----+-----+----------------+-------+-----+--------+-------------+\nonly showing top 20 rows\n\n</div>"]}}],"execution_count":29},{"cell_type":"code","source":["stringJSONRDD = sc.parallelize((\"\"\" \n  { \"id\": \"123\",\n    \"name\": \"Katie\",\n    \"age\": 19,\n    \"eyeColor\": \"brown\"\n  }\"\"\",\n   \"\"\"{\n    \"id\": \"234\",\n    \"name\": \"Michael\",\n    \"age\": 22,\n    \"eyeColor\": \"green\"\n  }\"\"\", \n  \"\"\"{\n    \"id\": \"345\",\n    \"name\": \"Simone\",\n    \"age\": 23,\n    \"eyeColor\": \"blue\"\n  }\"\"\")\n)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":30},{"cell_type":"code","source":["swimmer = spark.read.json(stringJSONRDD)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":31},{"cell_type":"code","source":["swimmer.createOrReplaceTempView(\"s\")"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":32},{"cell_type":"code","source":["spark.sql(\"select * from s limit 2\").show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+--------+---+-------+\nage|eyeColor| id|   name|\n+---+--------+---+-------+\n 19|   brown|123|  Katie|\n 22|   green|234|Michael|\n+---+--------+---+-------+\n\n</div>"]}}],"execution_count":33},{"cell_type":"code","source":["swimmer = swimmer.withColumn('age',lit(10))"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":34},{"cell_type":"code","source":["swimmer.show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+--------+---+-------+\nage|eyeColor| id|   name|\n+---+--------+---+-------+\n 10|   brown|123|  Katie|\n 10|   green|234|Michael|\n 10|    blue|345| Simone|\n+---+--------+---+-------+\n\n</div>"]}}],"execution_count":35},{"cell_type":"code","source":["spark.sql(\"select * from s where age > 20\").show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+--------+---+-------+\nage|eyeColor| id|   name|\n+---+--------+---+-------+\n 22|   green|234|Michael|\n 23|    blue|345| Simone|\n+---+--------+---+-------+\n\n</div>"]}}],"execution_count":36},{"cell_type":"code","source":["spark.catalog.listTables()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"><span class=\"ansired\">Out[</span><span class=\"ansired\">116</span><span class=\"ansired\">]: </span>\n[Table(name=u&apos;cs_test_31b97_csv&apos;, database=u&apos;default&apos;, description=None, tableType=u&apos;EXTERNAL&apos;, isTemporary=False),\n Table(name=u&apos;hr_comma_sep_csv_55265_txt&apos;, database=u&apos;default&apos;, description=None, tableType=u&apos;EXTERNAL&apos;, isTemporary=False),\n Table(name=u&apos;hr_data&apos;, database=u&apos;default&apos;, description=None, tableType=u&apos;EXTERNAL&apos;, isTemporary=False),\n Table(name=u&apos;s&apos;, database=None, description=None, tableType=u&apos;TEMPORARY&apos;, isTemporary=True),\n Table(name=u&apos;swiimerview&apos;, database=None, description=None, tableType=u&apos;TEMPORARY&apos;, isTemporary=True),\n Table(name=u&apos;swimmerview&apos;, database=None, description=None, tableType=u&apos;TEMPORARY&apos;, isTemporary=True)]\n</div>"]}}],"execution_count":37},{"cell_type":"code","source":["from pyspark.sql.types import *\n\nstringCSVRDD = sc.parallelize([(123, 'Katie', 19, 'brown'), (234, 'Michael', 22, 'green'), (345, 'Simone', 22, 11)])"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":38},{"cell_type":"code","source":["from pyspark.sql.types import StringType, StructField, LongType, StructType"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":39},{"cell_type":"code","source":["schemaString = \"id name age eyeColor\""],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":40},{"cell_type":"code","source":["schema = StructType([\n    StructField(\"id\", LongType(), True),    \n    StructField(\"name\", StringType(), True),\n    StructField(\"age\", LongType(), True),\n    StructField(\"eyeColor\", StringType(), True)\n])"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":41},{"cell_type":"code","source":["swimmers = spark.createDataFrame(stringCSVRDD, schema)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":42},{"cell_type":"code","source":["swimmers.write.csv(\"a.csv\")"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":43},{"cell_type":"code","source":["from pyspark.sql.types import Row"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":44},{"cell_type":"code","source":["# Create the Departments\ndepartment1 = Row(id='123456', name='Computer Science')\ndepartment2 = Row(id='789012', name='Mechanical Engineering')\ndepartment3 = Row(id='345678', name='Theater and Drama')\ndepartment4 = Row(id='901234', name='Indoor Recreation')"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":45},{"cell_type":"code","source":["Employee = Row(\"firstName\", \"lastName\", \"email\", \"salary\")"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":46},{"cell_type":"code","source":["employee1 = Employee('michael', 'armbrust', 'no-reply@berkeley.edu', 100000)\nemployee11 = Employee('michael', 'armbrust', 'no-reply@berkeley.edu', 200000)\nemployee2 = Employee('xiangrui', 'meng', 'no-reply@stanford.edu', 120000)\nemployee3 = Employee('matei', None, 'no-reply@waterloo.edu', 140000)\nemployee31 = Employee('matei', None, 'no-reply@waterloo.edu', 180000)\nemployee4 = Employee(None, 'wendell', 'no-reply@berkeley.edu', 160000)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":47},{"cell_type":"code","source":["departmentWithEmployees1 = Row(department=department1, employees=[employee1, employee2])\ndepartmentWithEmployees2 = Row(department=department2, employees=[employee3, employee4, employee11])\ndepartmentWithEmployees3 = Row(department=department3, employees=[employee1, employee4, employee31])\ndepartmentWithEmployees4 = Row(department=department4, employees=[employee2, employee3])"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":48},{"cell_type":"code","source":["df = spark.createDataFrame([departmentWithEmployees1,departmentWithEmployees2,departmentWithEmployees3,departmentWithEmployees4])"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":49},{"cell_type":"code","source":["display(df)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>department</th><th>employees</th></tr></thead><tbody><tr><td>List(123456, Computer Science)</td><td>List(List(michael, armbrust, no-reply@berkeley.edu, 100000), List(xiangrui, meng, no-reply@stanford.edu, 120000))</td></tr><tr><td>List(789012, Mechanical Engineering)</td><td>List(List(matei, null, no-reply@waterloo.edu, 140000), List(null, wendell, no-reply@berkeley.edu, 160000), List(michael, armbrust, no-reply@berkeley.edu, 200000))</td></tr><tr><td>List(345678, Theater and Drama)</td><td>List(List(michael, armbrust, no-reply@berkeley.edu, 100000), List(null, wendell, no-reply@berkeley.edu, 160000), List(matei, null, no-reply@waterloo.edu, 180000))</td></tr><tr><td>List(901234, Indoor Recreation)</td><td>List(List(xiangrui, meng, no-reply@stanford.edu, 120000), List(matei, null, no-reply@waterloo.edu, 140000))</td></tr></tbody></table></div>"]}}],"execution_count":50},{"cell_type":"code","source":["from pyspark.sql.functions import explode\ndf = df.select(\"*\", explode(\"employees\").alias(\"e\"))"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":51},{"cell_type":"code","source":["display(df)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>department</th><th>employees</th><th>e</th></tr></thead><tbody><tr><td>List(123456, Computer Science)</td><td>List(List(michael, armbrust, no-reply@berkeley.edu, 100000), List(xiangrui, meng, no-reply@stanford.edu, 120000))</td><td>List(michael, armbrust, no-reply@berkeley.edu, 100000)</td></tr><tr><td>List(123456, Computer Science)</td><td>List(List(michael, armbrust, no-reply@berkeley.edu, 100000), List(xiangrui, meng, no-reply@stanford.edu, 120000))</td><td>List(xiangrui, meng, no-reply@stanford.edu, 120000)</td></tr><tr><td>List(789012, Mechanical Engineering)</td><td>List(List(matei, null, no-reply@waterloo.edu, 140000), List(null, wendell, no-reply@berkeley.edu, 160000), List(michael, armbrust, no-reply@berkeley.edu, 200000))</td><td>List(matei, null, no-reply@waterloo.edu, 140000)</td></tr><tr><td>List(789012, Mechanical Engineering)</td><td>List(List(matei, null, no-reply@waterloo.edu, 140000), List(null, wendell, no-reply@berkeley.edu, 160000), List(michael, armbrust, no-reply@berkeley.edu, 200000))</td><td>List(null, wendell, no-reply@berkeley.edu, 160000)</td></tr><tr><td>List(789012, Mechanical Engineering)</td><td>List(List(matei, null, no-reply@waterloo.edu, 140000), List(null, wendell, no-reply@berkeley.edu, 160000), List(michael, armbrust, no-reply@berkeley.edu, 200000))</td><td>List(michael, armbrust, no-reply@berkeley.edu, 200000)</td></tr><tr><td>List(345678, Theater and Drama)</td><td>List(List(michael, armbrust, no-reply@berkeley.edu, 100000), List(null, wendell, no-reply@berkeley.edu, 160000), List(matei, null, no-reply@waterloo.edu, 180000))</td><td>List(michael, armbrust, no-reply@berkeley.edu, 100000)</td></tr><tr><td>List(345678, Theater and Drama)</td><td>List(List(michael, armbrust, no-reply@berkeley.edu, 100000), List(null, wendell, no-reply@berkeley.edu, 160000), List(matei, null, no-reply@waterloo.edu, 180000))</td><td>List(null, wendell, no-reply@berkeley.edu, 160000)</td></tr><tr><td>List(345678, Theater and Drama)</td><td>List(List(michael, armbrust, no-reply@berkeley.edu, 100000), List(null, wendell, no-reply@berkeley.edu, 160000), List(matei, null, no-reply@waterloo.edu, 180000))</td><td>List(matei, null, no-reply@waterloo.edu, 180000)</td></tr><tr><td>List(901234, Indoor Recreation)</td><td>List(List(xiangrui, meng, no-reply@stanford.edu, 120000), List(matei, null, no-reply@waterloo.edu, 140000))</td><td>List(xiangrui, meng, no-reply@stanford.edu, 120000)</td></tr><tr><td>List(901234, Indoor Recreation)</td><td>List(List(xiangrui, meng, no-reply@stanford.edu, 120000), List(matei, null, no-reply@waterloo.edu, 140000))</td><td>List(matei, null, no-reply@waterloo.edu, 140000)</td></tr></tbody></table></div>"]}}],"execution_count":52},{"cell_type":"code","source":["df.selectExpr(\"department.id\",\"department.name\",\"e.firstname\",\"e.email\",\"e.salary\").show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+------+--------------------+---------+--------------------+------+\n    id|                name|firstname|               email|salary|\n+------+--------------------+---------+--------------------+------+\n123456|    Computer Science|  michael|no-reply@berkeley...|100000|\n123456|    Computer Science| xiangrui|no-reply@stanford...|120000|\n789012|Mechanical Engine...|    matei|no-reply@waterloo...|140000|\n789012|Mechanical Engine...|     null|no-reply@berkeley...|160000|\n789012|Mechanical Engine...|  michael|no-reply@berkeley...|200000|\n345678|   Theater and Drama|  michael|no-reply@berkeley...|100000|\n345678|   Theater and Drama|     null|no-reply@berkeley...|160000|\n345678|   Theater and Drama|    matei|no-reply@waterloo...|180000|\n901234|   Indoor Recreation| xiangrui|no-reply@stanford...|120000|\n901234|   Indoor Recreation|    matei|no-reply@waterloo...|140000|\n+------+--------------------+---------+--------------------+------+\n\n</div>"]}}],"execution_count":53},{"cell_type":"code","source":["sjr = sc.parallelize((\"\"\" \n  { \"id\": \"{'h':\"234\"}\",\n    \"eyeColor\": [\"brown\",\"yellow\"]\n  }\"\"\",\n   \"\"\"{\n    \"id\": {'h':\"234\"}\"\"\n    \"eyeColor\": [\"green\",\"purple\"]\n  }\"\"\", \n  \"\"\"{\n    \"id\": {'g':\"234\"},\n    \"eyeColor\": [\"blue\"]\n  }\"\"\")\n)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":54},{"cell_type":"code","source":["df = spark.read.json(sjr)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":55},{"cell_type":"code","source":["df.select(\"*\",explode(df.eyeColor).alias(\"c\")).show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---------------+-------+------+\n       eyeColor|     id|     c|\n+---------------+-------+------+\n[brown, yellow]|[, 234]| brown|\n[brown, yellow]|[, 234]|yellow|\n[green, purple]|[, 234]| green|\n[green, purple]|[, 234]|purple|\n         [blue]| [234,]|  blue|\n+---------------+-------+------+\n\n</div>"]}}],"execution_count":56},{"cell_type":"code","source":["data = spark.read.json('/FileStore/tables/mixed.json')"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":57},{"cell_type":"code","source":["data.selectExpr(\"address.zip\").show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+-----+\n  zip|\n+-----+\n null|\n98016|\n null|\n+-----+\n\n</div>"]}}],"execution_count":58},{"cell_type":"code","source":["display(data)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>a</th><th>address</th><th>id</th><th>orders</th><th>since</th></tr></thead><tbody><tr><td>1.0</td><td>null</td><td>101</td><td>List(List(null, 100001))</td><td>2001</td></tr><tr><td>2.0</td><td>List(98016)</td><td>102</td><td>null</td><td>1999</td></tr><tr><td>3.1</td><td>null</td><td>103</td><td>List(List(B001, 300001), List(B005, 300002), List(A007, 300003))</td><td>foo</td></tr></tbody></table></div>"]}}],"execution_count":59},{"cell_type":"code","source":["data_ex = data.select(\"*\",explode(\"orders\").alias(\"o\")).show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+-------+---+--------------------+-----+--------------+\n  a|address| id|              orders|since|             o|\n+---+-------+---+--------------------+-----+--------------+\n1.0|   null|101|        [[, 100001]]| 2001|    [, 100001]|\n3.1|   null|103|[[B001, 300001], ...|  foo|[B001, 300001]|\n3.1|   null|103|[[B001, 300001], ...|  foo|[B005, 300002]|\n3.1|   null|103|[[B001, 300001], ...|  foo|[A007, 300003]|\n+---+-------+---+--------------------+-----+--------------+\n\n</div>"]}}],"execution_count":60},{"cell_type":"code","source":["display(data)"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .table-result-container {\n    max-height: 300px;\n    overflow: auto;\n  }\n  table, th, td {\n    border: 1px solid black;\n    border-collapse: collapse;\n  }\n  th, td {\n    padding: 5px;\n  }\n  th {\n    text-align: left;\n  }\n</style><div class='table-result-container'><table class='table-result'><thead style='background-color: white'><tr><th>a</th><th>address</th><th>id</th><th>orders</th><th>since</th></tr></thead><tbody><tr><td>1.0</td><td>null</td><td>101</td><td>List(List(null, 100001))</td><td>2001</td></tr><tr><td>2.0</td><td>List(98016)</td><td>102</td><td>null</td><td>1999</td></tr><tr><td>3.1</td><td>null</td><td>103</td><td>List(List(B001, 300001), List(B005, 300002), List(A007, 300003))</td><td>foo</td></tr></tbody></table></div>"]}}],"execution_count":61},{"cell_type":"code","source":["data.select(\"id\",explode(\"orders\")).show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+---+--------------+\n id|           col|\n+---+--------------+\n101|    [, 100001]|\n103|[B001, 300001]|\n103|[B005, 300002]|\n103|[A007, 300003]|\n+---+--------------+\n\n</div>"]}}],"execution_count":62},{"cell_type":"code","source":["rdd = sc.textFile('/FileStore/tables/movie_json-7ce1e.txt')"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":63},{"cell_type":"code","source":["rdd.filter(lambda x: len(x) != 0).map(lambda x: x.strip()).collect()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"><span class=\"ansired\">Out[</span><span class=\"ansired\">193</span><span class=\"ansired\">]: </span>\n[u&apos;{&apos;,\n u&apos;&quot;David Smith&quot;:&apos;,\n u&apos;{&apos;,\n u&apos;&quot;Vertigo&quot;: 4,&apos;,\n u&apos;&quot;Scarface&quot;: 4.5,&apos;,\n u&apos;&quot;Raging Bull&quot;: 3.0,&apos;,\n u&apos;&quot;Goodfellas&quot;: 4.5,&apos;,\n u&apos;&quot;The Apartment&quot;: 1.0&apos;,\n u&apos;},&apos;,\n u&apos;&quot;Brenda Peterson&quot;:&apos;,\n u&apos;{&apos;,\n u&apos;&quot;Vertigo&quot;: 3.0,&apos;,\n u&apos;&quot;Scarface&quot;: 1.5,&apos;,\n u&apos;&quot;Raging Bull&quot;: 1.0,&apos;,\n u&apos;&quot;Goodfellas&quot;: 2.0,&apos;,\n u&apos;&quot;The Apartment&quot;: 5.0,&apos;,\n u&apos;&quot;Roman Holiday&quot;: 4.5&apos;,\n u&apos;},&apos;,\n u&apos;&quot;Bill Duffy&quot;:&apos;,\n u&apos;{&apos;,\n u&apos;&quot;Vertigo&quot;: 4.5,&apos;,\n u&apos;&quot;Scarface&quot;: 5.0,&apos;,\n u&apos;&quot;Goodfellas&quot;: 4.5,&apos;,\n u&apos;&quot;The Apartment&quot;: 1.0&apos;,\n u&apos;},&apos;,\n u&apos;&quot;Samuel Miller&quot;:&apos;,\n u&apos;{&apos;,\n u&apos;&quot;Scarface&quot;: 3.5,&apos;,\n u&apos;&quot;Raging Bull&quot;: 5.0,&apos;,\n u&apos;&quot;The Apartment&quot;: 1.0,&apos;,\n u&apos;&quot;Goodfellas&quot;: 5.0,&apos;,\n u&apos;&quot;Roman Holiday&quot;: 1.0&apos;,\n u&apos;},&apos;,\n u&apos;&quot;Julie Hammel&quot;:&apos;,\n u&apos;{&apos;,\n u&apos;&quot;Scarface&quot;: 2.5,&apos;,\n u&apos;&quot;Roman Holiday&quot;: 4.5,&apos;,\n u&apos;&quot;Goodfellas&quot;: 3.0&apos;,\n u&apos;},&apos;,\n u&apos;&quot;Clarissa Jackson&quot;:&apos;,\n u&apos;{&apos;,\n u&apos;&quot;Vertigo&quot;: 5.0,&apos;,\n u&apos;&quot;Scarface&quot;: 4.5,&apos;,\n u&apos;&quot;Raging Bull&quot;: 4.0,&apos;,\n u&apos;&quot;Goodfellas&quot;: 2.5,&apos;,\n u&apos;&quot;The Apartment&quot;: 1.0,&apos;,\n u&apos;&quot;Roman Holiday&quot;: 1.5&apos;,\n u&apos;},&apos;,\n u&apos;&quot;Adam Cohen&quot;:&apos;,\n u&apos;{&apos;,\n u&apos;&quot;Vertigo&quot;: 3.5,&apos;,\n u&apos;&quot;Scarface&quot;: 3.0,&apos;,\n u&apos;&quot;The Apartment&quot;: 1.0,&apos;,\n u&apos;&quot;Goodfellas&quot;: 4.5,&apos;,\n u&apos;&quot;Roman Holiday&quot;: 3.0&apos;,\n u&apos;},&apos;,\n u&apos;&quot;Chris Duncan&quot;:&apos;,\n u&apos;{&apos;,\n u&apos;&quot;The Apartment&quot;: 1.5,&apos;,\n u&apos;&quot;Raging Bull&quot;: 4.5&apos;,\n u&apos;}&apos;,\n u&apos;}&apos;]\n</div>"]}}],"execution_count":64},{"cell_type":"code","source":["sc.textFile('/FileStore/tables/movie_json-7ce1e.txt').collect()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"><span class=\"ansired\">Out[</span><span class=\"ansired\">195</span><span class=\"ansired\">]: </span>\n[u&apos;{&apos;,\n u&apos;    { &quot;Vertigo&quot;: 4,  &quot;Scarface&quot;: 4.5,  &quot;Raging Bull&quot;: 3.0,  &quot;Goodfellas&quot;: 4.5, &quot;The Apartment&quot;: 1.0 },&apos;,\n u&apos;    { &quot;Vertigo&quot;: 3.0,  &quot;Scarface&quot;: 1.5,  &quot;Raging Bull&quot;: 1.0,  &quot;Goodfellas&quot;: 2.0, &quot;The Apartment&quot;: 5.0, &quot;Roman Holiday&quot;: 4.5 },&apos;,\n u&apos;}&apos;]\n</div>"]}}],"execution_count":65},{"cell_type":"code","source":["df = spark.read.json('/FileStore/tables/movie_json-7ce1e.txt')"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\"></div>"]}}],"execution_count":66},{"cell_type":"code","source":[""],"metadata":{},"outputs":[],"execution_count":67},{"cell_type":"code","source":["df.show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+----------+-----------+-------------+--------+-------------+-------+---------------+\nGoodfellas|Raging Bull|Roman Holiday|Scarface|The Apartment|Vertigo|_corrupt_record|\n+----------+-----------+-------------+--------+-------------+-------+---------------+\n      null|       null|         null|    null|         null|   null|              {|\n       4.5|        3.0|         null|     4.5|          1.0|    4.0|           null|\n       2.0|        1.0|          4.5|     1.5|          5.0|    3.0|           null|\n      null|       null|         null|    null|         null|   null|              }|\n+----------+-----------+-------------+--------+-------------+-------+---------------+\n\n</div>"]}}],"execution_count":68},{"cell_type":"code","source":["spark.read.json('/FileStore/tables/movie_json-7ce1e.txt').show()"],"metadata":{},"outputs":[{"metadata":{},"output_type":"display_data","data":{"text/html":["<style scoped>\n  .ansiout {\n    display: block;\n    unicode-bidi: embed;\n    white-space: pre-wrap;\n    word-wrap: break-word;\n    word-break: break-all;\n    font-family: \"Source Code Pro\", \"Menlo\", monospace;;\n    font-size: 13px;\n    color: #555;\n    margin-left: 4px;\n    line-height: 19px;\n  }\n</style>\n<div class=\"ansiout\">+----------+-----------+-------------+--------+-------------+-------+\nGoodfellas|Raging Bull|Roman Holiday|Scarface|The Apartment|Vertigo|\n+----------+-----------+-------------+--------+-------------+-------+\n       4.5|        3.0|         null|     4.5|          1.0| [4, 4]|\n       2.0|        1.0|          4.5|     1.5|          5.0| [1, 2]|\n+----------+-----------+-------------+--------+-------------+-------+\n\n</div>"]}}],"execution_count":69},{"cell_type":"code","source":[""],"metadata":{},"outputs":[],"execution_count":70}],"metadata":{"name":"SCIO-PySpark-DF-Day2","notebookId":1607195304495224},"nbformat":4,"nbformat_minor":0}