<commit_before><commit_msg>Add initial .ipynb with DataFrame examples<commit_after>{"cells":[{"cell_type":"code","source":[""],"metadata":{},"outputs":[],"execution_count":1},{"cell_type":"code","source":["from pyspark.sql.types import *  # Necessary for creating schemas\nfrom pyspark.sql.functions import * # Importing PySpark functions"],"metadata":{},"outputs":[],"execution_count":2},{"cell_type":"code","source":["# Make a tuple list\na_list = [('a', 1), ('b', 2), ('c', 3)]\n\n# Create a Spark DataFrame, without supplying a schema value\ndf_from_list_no_schema = \\\nsqlContext.createDataFrame(a_list)\n\n# Print the DF object\nprint df_from_list_no_schema\n\n# Print a collected list of Row objects\nprint df_from_list_no_schema.collect()\n\n# Show the DataFrame\ndf_from_list_no_schema.show()"],"metadata":{},"outputs":[],"execution_count":3},{"cell_type":"code","source":["# Create a Spark DataFrame, this time with schema\ndf_from_list_with_schema = \\\nsqlContext.createDataFrame(a_list, ['letters', 'numbers'])\n\n# Show the DataFrame\ndf_from_list_with_schema.show()"],"metadata":{},"outputs":[],"execution_count":4},{"cell_type":"code","source":["# Make a dictionary\na_dict = [{'letters': 'a', 'numbers': 1},\n          {'letters': 'b', 'numbers': 2},\n          {'letters': 'c', 'numbers': 3}]\n\n# Create a Spark DataFrame from the dictionary\ndf_from_dict = \\\nsqlContext.createDataFrame(a_dict)\n\n# Show the DataFrame\ndf_from_dict.show()"],"metadata":{},"outputs":[],"execution_count":5},{"cell_type":"code","source":["from pyspark.sql.types import *\n\n# Define the schema\nschema = StructType([\n    StructField(\"letters\", StringType(), True),\n    StructField(\"numbers\", IntegerType(), True)])\n\n# Create an RDD from a list\nrdd = sc.parallelize(a_list)\n\n# Create the DataFrame from these raw components\nnice_df = \\\n(sqlContext\n .createDataFrame(rdd, schema))\n\n# Show the DataFrame\nnice_df.show()"],"metadata":{},"outputs":[],"execution_count":6},{"cell_type":"code","source":[""],"metadata":{},"outputs":[],"execution_count":7},{"cell_type":"code","source":["# We now have a `nice_df`, here are some nice functions for\n# inspecting the DF\n\n# `columns`: return all column names as a list\nnice_df.columns"],"metadata":{},"outputs":[],"execution_count":8},{"cell_type":"code","source":["# `dtypes`: get the datatypes for all columns\nnice_df.dtypes"],"metadata":{},"outputs":[],"execution_count":9},{"cell_type":"code","source":["# `printSchema()`: prints the schema of the supplied DF\nnice_df.printSchema()"],"metadata":{},"outputs":[],"execution_count":10},{"cell_type":"code","source":["# `schema`: returns the schema of the provided DF as `StructType` schema\nnice_df.schema"],"metadata":{},"outputs":[],"execution_count":11},{"cell_type":"code","source":["# `first()` returns the first row as a Row while\n# `head()` and `take()` return `n` number of Row objects\nprint nice_df.first() # can't supply a value; never a list\nprint nice_df.head(2) # can optionally supply a value (default: 1);\n                      # with n > 1, a list\nprint nice_df.take(2) # expects a value; always a list"],"metadata":{},"outputs":[],"execution_count":12},{"cell_type":"code","source":["# `count()`: returns a count of all rows in DF\nnice_df.count()"],"metadata":{},"outputs":[],"execution_count":13},{"cell_type":"code","source":["# `describe()`: print out stats for numerical columns\nnice_df.describe().show() # can optionally supply a list of column names"],"metadata":{},"outputs":[],"execution_count":14},{"cell_type":"code","source":["# the `explain()` function explains the under-the-hood evaluation process\nnice_df.explain()"],"metadata":{},"outputs":[],"execution_count":15},{"cell_type":"code","source":[""],"metadata":{},"outputs":[],"execution_count":16},{"cell_type":"code","source":["# `unionAll()`: combine two DataFrames together\n\n# Take the DataFrame and add it to itself\ndouble_df = \\\n(nice_df\n .unionAll(nice_df))\n\n# Add it to itself twice\ntriple_df = \\\n(nice_df\n .unionAll(nice_df)\n .unionAll(nice_df))\n\n# Coercion will occur if schemas don't align\ncoerced_to_strings_df = \\\n(nice_df\n .select(['numbers', 'letters'])\n .unionAll(nice_df))\n\n#coerced_to_strings_df.printSchema()"],"metadata":{},"outputs":[],"execution_count":17},{"cell_type":"code","source":["display(double_df)\n#display(triple_df)"],"metadata":{},"outputs":[],"execution_count":18},{"cell_type":"code","source":["# `orderBy` will perform sorting of DataFrame columns\nsorted_asc_triple_df = \\\n(triple_df\n .orderBy('numbers'))\n\nsorted_desc_triple_df = \\\n(triple_df\n .orderBy('numbers', ascending = False))"],"metadata":{},"outputs":[],"execution_count":19},{"cell_type":"code","source":["display(sorted_asc_triple_df)\n#display(sorted_desc_triple_df)"],"metadata":{},"outputs":[],"execution_count":20},{"cell_type":"code","source":["# `select()` and `drop()` both take a list of column names\n# and these functions do exactly what you might expect\n\n# Select only the first column of the DF\nnice_df_1_col = \\\n(nice_df\n .select('letters'))\n\n# Re-order columns in the DF using `select()`\nnice_df_2_col_reordered = \\\n(nice_df\n .select(['numbers', 'letters']))\n\n# Drop the second column of the DF\nnice_df_1_col_drop = \\\n(nice_df\n .drop('letters'))"],"metadata":{},"outputs":[],"execution_count":21},{"cell_type":"code","source":["# `filter`: performs filtering of DF rows\n\n# Here is some numeric filtering with comparison operators\n# (>, <, >=, <=, ==, != all work)\n#nice_df.filter(nice_df.numbers > 1).show()\n#nice_df.filter(nice_df.numbers > 1).filter(nice_df.numbers < 3).show()\n\n# Not just numbers! Use the `filter()` + `isin()` combo to\n# filter on string columns with a set of values\n#nice_df.filter(nice_df.letters.isin(['a', 'b'])).show()"],"metadata":{},"outputs":[],"execution_count":22},{"cell_type":"code","source":["# Before we get into aggregations, let's\n# load in a CSV with interesting data and\n# create a new DataFrame\n\n# You do this with the `spark-csv` package;\n# documentation: https://github.com/databricks/spark-csv\n\n# This dataset contains data about flights departing\n# New York City airports (JFK, LGA, EWR) in 2013; it\n# has 336,776 rows and 16 columns\n\n# Create a schema object then read the CSV with schema\nnycflights_schema = StructType([\n  StructField(\"year\", IntegerType(), True),\n  StructField(\"month\", IntegerType(), True),\n  StructField(\"day\", IntegerType(), True),\n  StructField(\"dep_time\", StringType(), True),\n  StructField(\"dep_delay\", IntegerType(), True),\n  StructField(\"arr_time\", StringType(), True),\n  StructField(\"arr_delay\", IntegerType(), True),\n  StructField(\"carrier\", StringType(), True),\n  StructField(\"tailnum\", StringType(), True),\n  StructField(\"flight\", StringType(), True),  \n  StructField(\"origin\", StringType(), True),\n  StructField(\"dest\", StringType(), True),\n  StructField(\"air_time\", IntegerType(), True),\n  StructField(\"distance\", IntegerType(), True),\n  StructField(\"hour\", IntegerType(), True),\n  StructField(\"minute\", IntegerType(), True),\n  ])\n\nnycflights = \\\n(sqlContext\n .read\n .format('com.databricks.spark.csv')\n .schema(nycflights_schema)\n .options(header = True)\n .load('mnt/spark-atp/nycflights13/nycflights13.csv'))\n"],"metadata":{},"outputs":[],"execution_count":23},{"cell_type":"code","source":["nycflights.printSchema()"],"metadata":{},"outputs":[],"execution_count":24},{"cell_type":"code","source":["display(nycflights)"],"metadata":{},"outputs":[],"execution_count":25},{"cell_type":"code","source":[""],"metadata":{},"outputs":[],"execution_count":26},{"cell_type":"code","source":["# Let's group and aggregate\n\n# `groupBy()` will group one or more DF columns\n# and prep them for aggregration functions\n(nycflights\n.groupby('month') # creates 'GroupedData'\n.count() # creates a new column with aggregate `count` values\n.show())\n\n# Use the `agg()` function to perform multiple\n# aggregations\n(nycflights\n.groupby('month')\n.agg({'dep_delay': 'avg', 'arr_delay': 'avg'}) # note the new column names\n.show())\n\n# Caveat: you can't perform multiple aggregrations\n# on the same column (only the last is performed)\n(nycflights\n.groupby('month')\n.agg({'dep_delay': 'min', 'dep_delay': 'max'})\n.show())\n"],"metadata":{},"outputs":[],"execution_count":27},{"cell_type":"code","source":["# Use `groupBy()` with a few columns, then aggregate\n\n(nycflights\n.groupby(['month', 'origin', 'dest']) # group by these unique combinations\n.count() # perform a 'count' aggregation on the groups\n.orderBy(['month', 'count'], ascending = [1, 0]) # order by `month` ascending, `count` descending\n.show())"],"metadata":{},"outputs":[],"execution_count":28},{"cell_type":"code","source":["# Use `groupBy()` + `pivot()` + an aggregation function to\n# make a pivot table!\n\n# Get a table of flights by month for each carrier\ndisplay(nycflights.groupBy('month').pivot('carrier').count())"],"metadata":{},"outputs":[],"execution_count":29},{"cell_type":"code","source":["# Another pivot table idea: get the average departure\n# delay for each carrier at the different NYC airports\ndisplay(nycflights.groupBy('carrier').pivot('origin').avg('dep_delay'))"],"metadata":{},"outputs":[],"execution_count":30},{"cell_type":"code","source":[""],"metadata":{},"outputs":[],"execution_count":31},{"cell_type":"code","source":["(nycflights\n.groupby('month')\n.agg({'dep_delay': 'avg', 'arr_delay': 'avg'})\n.withColumnRenamed(\"avg(arr_delay)\", \"mean_arr_delay\")\n.withColumnRenamed(\"avg(dep_delay)\", \"mean_dep_delay\")\n.withColumn(\"mean_arr_delay\", format_number(\"mean_arr_delay\", 1))\n.withColumn(\"mean_dep_delay\", format_number(\"mean_dep_delay\", 1))\n.show())"],"metadata":{},"outputs":[],"execution_count":32},{"cell_type":"code","source":["# Add a new column (`far_or_near`) with a string based on a comparison\n# on a numeric column; uses: `withColumn()`, `when()`, and `otherwise()`\n(nycflights\n .withColumn(\"far_or_near\", when(nycflights.distance > 1000, \"far\")\n             .otherwise(\"near\"))\n .show())"],"metadata":{},"outputs":[],"execution_count":33},{"cell_type":"code","source":["# Perform a few numerical computations across columns\n(nycflights\n .withColumn('dist_per_minute',\n             nycflights.distance / nycflights.air_time)\n .withColumn(\"dist_per_minute\", format_number(\"dist_per_minute\", 2))\n .drop('distance')\n .drop('air_time')\n .show())"],"metadata":{},"outputs":[],"execution_count":34},{"cell_type":"code","source":["# Create a proper date column for once in your life\nnycflights = \\\n(nycflights\n .withColumn('date_string',\n             concat_ws('-', nycflights.year, nycflights.month, nycflights.day))\n .drop('year')\n .drop('month')\n .drop('day'))\n"],"metadata":{},"outputs":[],"execution_count":35},{"cell_type":"code","source":["display(nycflights)"],"metadata":{},"outputs":[],"execution_count":36}],"metadata":{"name":"spark-dataframes","notebookId":96485},"nbformat":4,"nbformat_minor":0}

{'fraction_non_alphanumeric': 0.22868217054263565, 'fraction_numerical': 0.010005408328826392, 'mean_word_length': 7.640965732087228, 'pattern_counts': {'":': 186, '<': 6, '<?xml version=': 0, '>': 9, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4190130', 'n_tokens_mistral': 3834, 'n_tokens_neox': 3556, 'n_words': 706}