pyspark
To start working with Spark DataFrames, you first have to create a SparkSession object from your SparkContext. You can think of the SparkContext as your connection to the cluster and the SparkSession as your interface with that connection.
SparkSession has an attribute called catalog which lists all the data inside the cluster. This attribute has a few methods for extracting different pieces of information..sql() method on your SparkSession. This method takes a string containing the query and returns a DataFrame with the results!pandas. Spark DataFrames make that easy with the .toPandas() method. Calling this method on a Spark DataFrame returns the corresponding pandas DataFrame. It's as simple as that!.createDataFrame() method takes a pandas DataFrame and returns a Spark DataFrame.SparkSession catalog. This means that you can use all the Spark DataFrame methods on it, but you can't access the data in other contexts.For example, a SQL query (using the .sql() method) that references your DataFrame will throw an error. To access the data in this way, you have to save it as a temporary table.
You can do this using the .createTempView() Spark DataFrame method, which takes as its only argument the name of the temporary table you'd like to register. This method registers the DataFrame as a table in the catalog, but as this table is temporary, it can only be accessed from the specific SparkSession used to create the Spark DataFrame.
There is also the method .createOrReplaceTempView(). This safely creates a new temporary table if nothing was there before, or updates an existing table if one was already defined. You'll use this method to avoid running into problems with duplicate tables.
Check out the diagram to see all the different ways your Spark data structures interact with each other.
如何使用create or replace temp view
DataFrame.createOrReplaceTempView(name)
DataFrame class to perform common data operations.In Spark you can do this using the .withColumn() method, which takes two arguments. First, a string with the name of your new column, and second the new column itself.
The new column must be an object of class Column. Creating one of these is as easy as extracting a column from your DataFrame using df.colName.
Updating a Spark DataFrame is somewhat different than working in pandas because the Spark DataFrame is immutable. This means that it can't be changed, and so columns can't be updated in place.
Thus, all these methods return a new DataFrame. To overwrite the original DataFrame you must reassign the returned DataFrame using the method like so:
df = df.withColumn("newCol", df.oldCol + 1)
The above code creates a DataFrame with the same columns as df plus a new column, newCol, where every entry is equal to the corresponding entry from oldCol, plus one.
To overwrite an existing column, just pass the name of the column as the first argument!
Remember, a SparkSession called spark is already in your workspace.
+----+-----+---+--------+---------+--------+---------+-------+-------+------+------+----+--------+--------+----+------+ |year|month|day|dep_time|dep_delay|arr_time|arr_delay|carrier|tailnum|flight|origin|dest|air_time|distance|hour|minute|
Filtering Data
Now that you have a bit of SQL know-how under your belt, it's easier to talk about the analogous operations using Spark DataFrames.
Let's take a look at the .filter() method. As you might suspect, this is the Spark counterpart of SQL's WHERE clause. The .filter() method takes either an expression that would follow the WHERE clause of a SQL expression as a string, or a Spark Column of boolean (True/False) values.
For example, the following two expressions will produce the same output:
flights.filter("air_time > 120").show()
flights.filter(flights.air_time > 120).show()
Notice that in the first case, we pass a string to .filter(). In SQL, we would write this filtering task as SELECT * FROM flights WHERE air_time > 120. Spark's .filter() can accept any expression that could go in the WHEREclause of a SQL query (in this case, "air_time > 120"), as long as it is passed as a string. Notice that in this case, we do not reference the name of the table in the string -- as we wouldn't in the SQL request.
In the second case, we actually pass a column of boolean values to .filter(). Remember that flights.air_time > 120 returns a column of boolean values that has True in place of those records in flights.air_time that are over 120, and False otherwise.
two ways to filter
1 pass a string to .filter()
2 pass a column of boolean values
Selecting
The Spark variant of SQL's SELECT is the .select() method. This method takes multiple arguments - one for each column you want to select. These arguments can either be the column name as a string (one for each column) or a column object (using the df.colName syntax). When you pass a column object, you can perform operations like addition or subtraction on the column to change the data contained in it, much like inside .withColumn().
The difference between .select() and .withColumn() methods is that .select() returns only the columns you specify, while .withColumn() returns all the columns of the DataFrame in addition to the one you defined. It's often a good idea to drop columns you don't need at the beginning of an operation so that you're not dragging around extra data as you're wrangling. In this case, you would use .select() and not .withColumn().
- Select the columns
tailnum,origin, anddestfromflightsby passing the column names as strings. Save this asselected1. - Select the columns
origin,dest, andcarrierusing thedf.colNamesyntax and then filter the result using both of the filters already defined for you (filterAandfilterB) to only keep flights from SEA to PDX. Save this asselected2.
Selecting II
Similar to SQL, you can also use the .select() method to perform column-wise operations. When you're selecting a column using the df.colName notation, you can perform any column operation and the .select() method will return the transformed column. For example,
flights.select(flights.air_time/60)
returns a column of flight durations in hours instead of minutes. You can also use the .alias() method to rename a column you're selecting. So if you wanted to .select() the column duration_hrs (which isn't in your DataFrame) you could do
flights.select((flights.air_time/60).alias("duration_hrs"))
The equivalent Spark DataFrame method .selectExpr() takes SQL expressions as a string:
flights.selectExpr("air_time/60 as duration_hrs")
with the SQL as keyword being equivalent to the .alias() method. To select multiple columns, you can pass multiple strings.
Aggregating
All of the common aggregation methods, like .min(), .max(), and .count() are GroupedData methods. These are created by calling the .groupBy() DataFrame method. You'll learn exactly what that means in a few exercises. For now, all you have to do to use these functions is call that method on your DataFrame. For example, to find the minimum value of a column, col, in a DataFrame, df, you could do
df.groupBy().min("col").show()
This creates a GroupedData object (so you can use the .min() method), then finds the minimum value in col, and returns it as a DataFrame.
Now you're ready to do some aggregating of your own!
- Find the length of the shortest (in terms of distance) flight that left PDX by first
.filter()ing and using the.min()method. Perform the filtering by referencing the column directly, not passing a SQL string. - Find the length of the longest (in terms of time) flight that left SEA by
filter()ing and using the.max()method. Perform the filtering by referencing the column directly, not passing a SQL string.
- Use the
.avg()method to get the average air time of Delta Airlines flights (where thecarriercolumn has the value"DL") that left SEA. The place of departure is stored in the columnorigin.show()the result. - Use the
.sum()method to get the total number of hours all planes in this dataset spent in the air by creating a column calledduration_hrsfrom the columnair_time.show()the result.
Grouping and Aggregating I
Part of what makes aggregating so powerful is the addition of groups. PySpark has a whole class devoted to grouped data frames: pyspark.sql.GroupedData, which you saw in the last two exercises.
You've learned how to create a grouped DataFrame by calling the .groupBy() method on a DataFrame with no arguments.
Now you'll see that when you pass the name of one or more columns in your DataFrame to the .groupBy() method, the aggregation methods behave like when you use a GROUP BY statement in a SQL query!
group by with no arguments create a grouped DataFrame by calling the .groupBy() method on a DataFrame with no arguments.
group by with when you pass the name of one or more columns in your DataFrame to the .groupBy() method, the aggregation methods behave like when you use a GROUP BY statement in a SQL query
- Create a DataFrame called
by_planethat is grouped by the columntailnum. - Use the
.count()method with no arguments to count the number of flights each plane made. - Create a DataFrame called
by_originthat is grouped by the columnorigin. - Find the
.avg()of theair_timecolumn to find average duration of flights from PDX and SEA.
Grouping and Aggregating II
In addition to the GroupedData methods you've already seen, there is also the .agg() method. This method lets you pass an aggregate column expression that uses any of the aggregate functions from the pyspark.sql.functions submodule.
This submodule contains many useful functions for computing things like standard deviations. All the aggregation functions in this submodule take the name of a column in a GroupedData table.
- Import the submodule
pyspark.sql.functionsasF. - Create a
GroupedDatatable calledby_month_destthat's grouped by both themonthanddestcolumns. Refer to the two columns by passing both strings as separate arguments. - Use the
.avg()method on theby_month_destDataFrame to get the averagedep_delayin each month for each destination. - Find the standard deviation of
dep_delayby using the.agg()method with the functionF.stddev().
.join(). This method takes three arguments. The first is the second DataFrame that you want to join with the first one. The second argument, on, is the name of the key column(s) as a string. The names of the key column(s) must be the same in each table. The third argument, how, specifies the kind of join to perform. In this course we'll always use the value how="leftouter".At the core of the pyspark.ml module are the Transformer and Estimator classes. Almost every other class in the module behaves similarly to these two basic classes.
Transformer classes have a .transform() method that takes a DataFrame and returns a new DataFrame; usually the original one with a new column appended. For example, you might use the class Bucketizer to create discrete bins from a continuous feature or the class PCA to reduce the dimensionality of your dataset using principal component analysis.
Estimator classes all implement a .fit() method. These methods also take a DataFrame, but instead of returning another DataFrame they return a model object. This can be something like a StringIndexerModel for including categorical data saved as strings in your models, or a RandomForestModel that uses the random forest algorithm for classification or regression.
df.withColumnRenamed('age', 'age2').collect()
[Row(age2=2, name='Alice'), Row(age2=5, name='Bob')]
- First, rename the
yearcolumn ofplanestoplane_yearto avoid duplicate column names. - Create a new DataFrame called
model_databy joining theflightstable withplanesusing thetailnumcolumn as the key.
Data types
Good work! Before you get started modeling, it's important to know that Spark only handles numeric data. That means all of the columns in your DataFrame must be either integers or decimals (called 'doubles' in Spark).
When we imported our data, we let Spark guess what kind of information each column held. Unfortunately, Spark doesn't always guess right and you can see that some of the columns in our DataFrame are strings containing numbers as opposed to actual numeric values.
To remedy this, you can use the .cast() method in combination with the .withColumn() method. It's important to note that .cast() works on columns, while .withColumn() works on DataFrames.
The only argument you need to pass to .cast() is the kind of value you want to create, in string form. For example, to create integers, you'll pass the argument "integer" and for decimal numbers you'll use "double".
You can put this call to .cast() inside a call to .withColumn() to overwrite the already existing column, just like you did in the previous chapter!
String to integer
Now you'll use the .cast() method you learned in the previous exercise to convert all the appropriate columns from your DataFrame model_data to integers!
To convert the type of a column using the .cast() method, you can write code like this:
dataframe = dataframe.withColumn("col", dataframe.col.cast("new_type"))
dataframe = dataframe.withColumn("col", dataframe.col.cast("new_type"))
Use the method.withColumn()to.cast()the following columns to type"integer". Access the columns using thedf.colnotation:
model_data.arr_delaymodel_data.air_timemodel_data.monthmodel_data.plane_year
- Create the column
plane_ageusing the.withColumn()method and subtracting the year of manufacture (columnplane_year) from the year (columnyear) of the flight.
- Use the
.withColumn()method to create the columnis_late. This column is equal tomodel_data.arr_delay > 0. - Convert this column to an integer column so that you can use it in your model and name it
label(this is the default name for the response variable in Spark's machine learning routines). - Filter out missing values (this has been done for you).
Strings and factors
As you know, Spark requires numeric data for modeling. So far this hasn't been an issue; even boolean columns can easily be converted to integers without any trouble. But you'll also be using the airline and the plane's destination as features in your model. These are coded as strings and there isn't any obvious way to convert them to a numeric data type.
Fortunately, PySpark has functions for handling this built into the pyspark.ml.features submodule. You can create what are called 'one-hot vectors' to represent the carrier and the destination of each flight. A one-hot vector is a way of representing a categorical feature where every observation has a vector in which all elements are zero except for at most one element, which has a value of one (1).
Each element in the vector corresponds to a level of the feature, so it's possible to tell what the right level is by seeing which element of the vector is equal to one (1).
The first step to encoding your categorical feature is to create a StringIndexer. Members of this class are Estimators that take a DataFrame with a column of strings and map each unique string to a number. Then, the Estimator returns a Transformer that takes a DataFrame, attaches the mapping to it as metadata, and returns a new DataFrame with a numeric column corresponding to the string column.
The second step is to encode this numeric column as a one-hot vector using a OneHotEncoder. This works exactly the same way as the StringIndexer by creating an Estimator and then a Transformer. The end result is a column that encodes your categorical feature as a vector that's suitable for machine learning routines!
This may seem complicated, but don't worry! All you have to remember is that you need to create a StringIndexer and a OneHotEncoder, and the Pipeline will take care of the rest.
Carrier
In this exercise you'll create a StringIndexer and a OneHotEncoder to code the carrier column. To do this, you'll call the class constructors with the arguments inputCol and outputCol.
The inputCol is the name of the column you want to index or encode, and the outputCol is the name of the new column that the Transformer should create.
- Create a
StringIndexercalledcarr_indexerby callingStringIndexer()withinputCol="carrier"andoutputCol="carrier_index". - Create a
OneHotEncodercalledcarr_encoderby callingOneHotEncoder()withinputCol="carrier_index"andoutputCol="carrier_fact".
- Create a
StringIndexercalleddest_indexerby callingStringIndexer()withinputCol="dest"andoutputCol="dest_index". - Create a
OneHotEncodercalleddest_encoderby callingOneHotEncoder()withinputCol="dest_index"andoutputCol="dest_fact".
Assemble a vector
The last step in the Pipeline is to combine all of the columns containing our features into a single column. This has to be done before modeling can take place because every Spark modeling routine expects the data to be in this form. You can do this by storing each of the values from a column as an entry in a vector. Then, from the model's point of view, every observation is a vector that contains all of the information about it and a label that tells the modeler what value that observation corresponds to.
Because of this, the pyspark.ml.feature submodule contains a class called VectorAssembler. This Transformer takes all of the columns you specify and combines them into a new vector column.
- Import
Pipelinefrompyspark.ml. - Call the
Pipeline()constructor with the keyword argumentstagesto create aPipelinecalledflights_pipe.stagesshould be a list holding all the stages you want your data to go through in the pipeline. Here this is just:[dest_indexer, dest_encoder, carr_indexer, carr_encoder, vec_assembler]
elasticNetParam and regParam, and using the cross validation error to compare all the different models so you can choose the best one!Make a grid
Next, you need to create a grid of values to search over when looking for the optimal hyperparameters. The submodule pyspark.ml.tuning includes a class called ParamGridBuilder that does just that (maybe you're starting to notice a pattern here; PySpark has a submodule for just about everything!).
You'll need to use the .addGrid() and .build() methods to create a grid that you can use for cross validation. The .addGrid() method takes a model parameter (an attribute of the model Estimator, lr, that you created a few exercises ago) and a list of values that you want to try. The .build() method takes no arguments, it just returns the grid that you'll use later.
- Import the submodule
pyspark.ml.tuningunder the aliastune. - Call the class constructor
ParamGridBuilder()with no arguments. Save this asgrid. - Call the
.addGrid()method ongridwithlr.regParamas the first argument andnp.arange(0, .1, .01)as the second argument. This second call is a function from thenumpymodule (importedas np) that creates a list of numbers from 0 to .1, incrementing by .01. Overwritegridwith the result. - Update
gridagain by calling the.addGrid()method a second time create a grid forlr.elasticNetParamthat includes only the values[0, 1]. - Call the
.build()method ongridand overwrite it with the output.
Fit the model(s)
You're finally ready to fit the models and select the best one!
Unfortunately, cross validation is a very computationally intensive procedure. Fitting all the models would take too long on DataCamp.
To do this locally you would use the code:
# Fit cross validation models
models = cv.fit(training)
# Extract the best model
best_lr = models.bestModel
Remember, the training data is called training and you're using lr to fit a logistic regression model. Cross validation selected the parameter values regParam=0 and elasticNetParam=0 as being the best. These are the default values, so you don't need to do anything else with lr before fitting the model.
浙公网安备 33010602011771号