<jupyter_start><jupyter_text>Spark Fundamentals I - Introduction to Spark Python - Working with Dataframes**Related free online courses:**Related courses can be found in the following learning paths:- [Spark Fundamentals path](http://cocl.us/Spark_Fundamentals_Path)- [Big Data Fundamentals path](http://cocl.us/Big_Data_Fundamentals_Path) A DataFrame is two-dimensional. Columns can be of different data types. DataFrames accept many data inputs including series and other DataFrames. You can pass indexes (row labels) and columns (column labels). Indexes can be numbers, dates, or strings/tuples.Pandas is a library used for data manipulation and analysis. Pandas offers data structures and operations for creating and manipulating Data Series and DataFrame objects. Data can be imported from various data sources, e.g., Numpy arrays, Python dictionaries and CSV files. Pandas allows you to manipulate, organize and display the data. In this short notebook, we will load and explore the mtcars dataset. Specifically, this tutorial covers:1. Loading data in memory1. Creating SQLContext1. Creating Spark DataFrame1. Group data by columns 1. Operating on columns1. Running SQL Queries from a Spark DataFrame  Loading in a DataFrameTo create a Spark DataFrame we load an external DataFrame, called `mtcars`. This DataFrame includes 32 observations on 11 variables:[, 1]	mpg	Miles/(US) --> gallon  [, 2]	cyl	--> Number of cylinders  [, 3]	disp	--> Displacement (cu.in.)  [, 4]	hp -->	Gross horsepower  [, 5]	drat -->	Rear axle ratio  [, 6]	wt -->	Weight (lb/1000)  [, 7]	qsec -->	1/4 mile time  [, 8]	vs -->	V/S  [, 9]	am -->	Transmission (0 = automatic, 1 = manual)  [,10]	gear -->	Number of forward gears  [,11]	carb -->	Number of carburetors<jupyter_code>import pandas as pd
mtcars = pd.read_csv('https://cocl.us/BD0211EN_mtcars')
mtcars.head()<jupyter_output><empty_output><jupyter_text>Initialize SQLContextTo work with dataframes we need an SQLContext which is created using `SQLContext(sc)`. SQLContext uses SparkContext which has been already created in Data Scientist Workbench, named `sc`.  But first, let's import the tools that we need to use Spark in SN Labs.<jupyter_code>!pip install findspark
!pip install pyspark
import findspark
import pyspark
findspark.init()
sc = pyspark.SparkContext.getOrCreate()
sqlContext = SQLContext(sc)<jupyter_output><empty_output><jupyter_text>Creating Spark DataFramesWith SQLContext and a loaded local DataFrame, we create a Spark DataFrame:<jupyter_code>sdf = sqlContext.createDataFrame(mtcars) 
sdf.printSchema()<jupyter_output><empty_output><jupyter_text>Displays the content of the DataFrame<jupyter_code>sdf.show(5)<jupyter_output><empty_output><jupyter_text>Selecting columns<jupyter_code>sdf.select('mpg').show(5)<jupyter_output><empty_output><jupyter_text>Filtering DataFilter the DataFrame to only retain rows with `mpg` less than 18<jupyter_code>sdf.filter(sdf['mpg'] < 18).show(5)<jupyter_output><empty_output><jupyter_text>Operating on ColumnsSparkR also provides a number of functions that can be directly applied to columns for data processing and aggregation. The example below shows the use of basic arithmetic functions to convert lb to metric ton.<jupyter_code>sdf.withColumn('wtTon', sdf['wt'] * 0.45).show(6)
sdf.show(6)<jupyter_output><empty_output><jupyter_text>Grouping, AggregationSpark DataFrames support a number of commonly used functions to aggregate data after grouping. For example we can compute the average weight of cars by their cylinders as shown below:<jupyter_code>sdf.groupby(['cyl'])\
.agg({"wt": "AVG"})\
.show(5)
# We can also sort the output from the aggregation to get the most common cars
car_counts = sdf.groupby(['cyl'])\
.agg({"wt": "count"})\
.sort("count(wt)", ascending=False)\
.show(5)<jupyter_output><empty_output><jupyter_text>Running SQL Queries from Spark DataFramesA Spark DataFrame can also be registered as a temporary table in Spark SQL and registering a DataFrame as a table allows you to run SQL queries over its data. The `sql` function enables applications to run SQL queries programmatically and returns the result as a DataFrame.<jupyter_code># Register this DataFrame as a table.
sdf.registerTempTable("cars")

# SQL statements can be run by using the sql method
highgearcars = sqlContext.sql("SELECT gear FROM cars WHERE cyl >= 4 AND cyl <= 9")
highgearcars.show(6)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09051724137931035, 'fraction_numerical': 0.011796733212341199, 'mean_word_length': 5.721036585365853, 'pattern_counts': {'":': 2, '<': 39, '<?xml version=': 0, '>': 49, '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}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '309329', 'license': 'MIT', 'n_tokens_mistral': 1405, 'n_tokens_neox': 1284, 'n_words': 502, 'path': 'spark-fundamentals-I/Module 2/BD0211EN-Exercise-Dataframes-py-v2.0.ipynb', 'repo_name': 'Code360In/spark-code-examples', 'size': '4408'}