PySpark Dataframes Tutorial | Introduction to PySpark Dataframes API | PySpark Training | Edureka Video Lecture | Apache Spark: Master Machine Learning - AI & ML

[Music]
hello everyone this is Chris Lee from
Eureka and today we'll be discussing
about PI spark data frames so let's have
a quick look at the agenda of today's
video I'll start off with why exactly do
we need data frames next we'll learn
what data frame is and have a look at
some of the features of data frames then
I'll show you guys how to create data
frame from various sources and I'll
finish off this video with two amazing
use cases which will help you understand
data frames better so I'm sure you might
be wondering why exactly we need data
frames now the concept of data frames
comes from the world of statistical
software used in empirical research data
frames are designed for processing large
collection of structured or semi
structured data
observation inspark data frame are
organized under named columns which
helps Apache spark to understand the
schema of a data frame this helps spark
optimize execution plan on these queries
now data frames in a party spark has the
ability to handle petabytes of data it
can handle large amounts of data which
are usually the big data now it has
support for a wide range of data formats
and sources we learn about that later in
this video and finally last but not the
least
it has API support for different
languages like Python our Scala Java
which makes it easier to be used by
people having different programming
backgrounds as well now another
important feature of data frame is that
the data frame API usually support
elaborate methods for slicing and dicing
the data it includes operations such as
selecting rows columns and cell by name
or by number filtering out rows and many
other operations statistical data is
very unusual and very messy and contain
lots of missing and wrong values so a
critically important feature of data
frame is the explicit management of
missing data
now let's understand what our data
frames now data frames generally refers
to tabular data a data structure
representing rows each of which consists
of a number of observation or
measurements which are known as columns
alternatively each row may be treated as
a single observation of multiple
variables in any case each row and each
column has the same data type but the
row which is the record data type may be
heterogeneous while the column data type
must be homogeneous data frames usually
contains some metadata in addition to
the data for example the column and the
row names now data frame is like a 2d
data structure similar to a sequel or a
table in a spreadsheet now there are few
important features of data frame that we
must know of first of all they are
distributed in nature which makes it
highly available and fault tolerant now
the second feature is lazy evaluations
now lazy evaluation is an evaluation
strategy which holds the evaluation of
an expression until its value is needed
it avoids repeated evaluation and lazy
evaluation inspark means that the
execution will not start until an action
is triggered and in SPARC the picture of
lazy evaluation comes when the SPARC
transformation occurs transformations
are lazy in nature meaning that when we
call some operations it does not execute
immediately SPARC maintains the record
of which operation has been called
through dat which is the directed
acyclic graph since transformations are
lazy in nature so we can execute
operations any time by calling an action
on the data
hence in lazy evaluation data is not
loaded until it is necessary now there
are many advantages of lazy evaluation
like the increased manageability saving
computation it increases speed it raises
complexities and it also provides
optimization by reducing the number of
queries now finally we can see that data
frames are immutable now by immutable I
mean that it is an object whose state
cannot be modified after it is created
but we can transform its values by
applying certain transformations like in
RIT as well now a data frame in Apache
spark can be created in multiple ways it
can be created using different data
formats for example loading data from
JSON CSV XML file pocket files
it can be also created from an existing
RDD as well as this can be also created
from various databases like the hive
database the Cassandra database and can
be also created from files which are
residing in the file system as well as
the HDFS so let's have a look at the
various important classes of data frames
and sequel we have the PI sparks equal
torts equal context which is the many
entry point for the data frame and
sequel functionality you have the PI
sparks equal door data frame a
distributed collection of data grouped
into named columns and we have the pile
sparks equal dot column it is for the
column expression in a data frame
similarly we have the same for the row
and next we have the PI sparks equal
crypt data which is the aggregation
methods returned by the data frame dot
group by now we'll learn more about
these important classes later in the
video so let's go ahead and create some
data frames so now I use Jupiter
notebook instead of the PI Spock shell
as I personally find it easier to work
with it ultimately it comes down to your
choice so guys let's go ahead and start
the demo
not to start the pie Spock she'll only
need to type in a spy spark when you
have configured the spark in your
virtual machine or your personal
computer this few lines of code which
you need to add to the bash RC file if
you want the Jupiter notebook to be
integrated with the PI's path so I've
done that I'll show you later how it
works so as soon as I enter PI spark
instead of going to the PI spark shell
I'm redirected to the Jupiter notebook
so as you can see here guys it has
opened the Jupiter notebook for me so
I'm going to select a new Python 2 file
so firstly we will create an example
data of departments and employee so I'll
show you how it's done
the first and foremost for the data
frames is to import the PI sparks equal
okay and now that the import is
successful so as you can see I'm
creating an employee database with the
row function so basically the rows will
have the column name as the first name
last name email and the salary now next
I'm going to create some of the employee
data so you can see I've created five
employees with the employee data in the
same format as given in the first name
last name email and salary and these
employee one two three and four are all
in the format of the employee now that
we have created employees let's go ahead
and create the department's now as I was
saying Python is very easy and data
frames are easy to use I will show you
how easy it is suppose I want to look at
the values of the employee three all I
need to do is just execute the command
print and use the employee three as you
can see we have a row in which the first
time is Muriel the last name is not
specified we have the email ID and we
have the salary now suppose you want to
see what was the row value of the
employee which would create an earlier
as you can see here if I do the print
employee and in square brackets if I
write zero is the first name so
basically here I am using the row
function to create the employees and
departments and now we'll create the
department with employees instance from
the departments and the employees now
here as you can see I didn't even use
the curly braces all I need to do was
type in print and Department for now
whatever was in the department for which
is the ID and the name the name being
the name of the department we have nine
zero one two three four which is the ID
of the department and that the name
Department on the name is the
development department next we'll create
data frames from a list of rows so guys
this is where it gets a little trickier
here as you can see we have used the
spark Tod create data frame this will
create a data frame T frame now it will
contain the department's with employees
one and the department's with employees
to instance which in turn have the
department of one and department to
where the employees are employed one two
and five in the department one and we
have the employees three and four
Department - so if we display the D
frame we'll see here the department and
said that we have a string and a name
and then we are the employees it's an
array as we have given you which has the
first name last name email and the
salary so guys this is how we create a
data frame now let's have a look at the
FIFA World Cup use case
I hope Argentina Rico was from the
Croatia match - so here we have the data
of all the players in the FIFA World Cup
let's go ahead and load the data here we
have the data in the CSV format so I'll
show you how it's done so I'm going to
select a new notebook for this one so as
you can see here we have the FIFA
underscore D F and I'm using the fashion
spa dot read dot CSV this will take the
data from the CSV file which is provided
in the file path and I've set the info
schema as true and header as true now if
you don't set these values to true
usually the first row of any database
has the name of the columns so what will
happen it will not infer the schema and
we'll take the first row as the values
of the data frame so as you can see here
the path by default it takes the HDFS
path so in order to give the path of the
local file system you need to put in the
file colon and double forward slashes
now that we have the data frame in FIFA
underscore data frame let's see how this
data frame looks so as you can see here
we have the round ID the match ID we
have the team initials the coach name
and the team they belong to we have the
lineup
yet the player name we have the position
with placed and the event now if you
want to print the schema of the
particular data frame all we need to do
is use the print schema and as you can
see here we have the round idea which is
integer match ID is also integer the
initials are string and here we have the
option of giving the nullable as true so
even if there is no value specified it
will take the value as null now suppose
next I want to see what of the column in
my data frame we saw that earlier when
we used the show command but another way
to do that is using the dot columns so
if you want to know the amount of rows
or the number of records in our data
frame all you need to do is give the
name of the data frame and use the count
function so as it is see here we have
almost 37 thousand rows
maybe want to take a look at the number
of columns we can manually count in but
when it's not possible we can use the
length of the columns function so as you
can see here we have the eight columns
and we had thirty 7784 rows now suppose
we want to describe the summary of a
particular column we use the function
described now what it does is that it
gives a summary that is the count at is
thirty-seven thousand which is the
number of rows we get the mean of the
particular column which we have given in
our case it's null because it's in the
string we get the standard deviation we
get the minimum now here minimum is
according to the alphabetical order so
we get Acosta Nelson and Maxim is Zico
now similarly we can describe the column
position also as you can see here we
have only counter 4143 the mean and
silent deviations are null and the
minimum value is C as the captain and
the maximum is Col kipper captain GKC
now suppose you want to know the player
name and the coach name in a particular
format we just need to use the select
option and specify the column names here
as you can see we have the player name
and the coach name now there's another
function in the data frames which is the
filter function suppose you want to
filter out all the players and all the
data according to the match ID we use
the filter function here and on top of
that we use the show function to get our
desired results so as you can see here
all the data which we have is for the
match ID 1 0 9 6 so it's showing only 20
top rows now similarly you can see that
if you want to filter according to the
position of captain here we our result
and if now I want to check that how many
records I have in this particular data
frame in which the position is given as
captain you can see if we have 50
hundred and 10 rows I'd rather say the
records where the position is defined as
captain it's very similar to sequel
we'll see how we can import sequel
queries also later on similarly if we
filter it using the position of G K
which is the goalkeeper you can see we
have all the records now we can use the
filter on more than one parameters also
so here I've taken the position as
captain and the event as G 40
so here we'll get result of all the
players were paying position as captain
and the event they participated was at G
40 so they were only two results here
now that we have seen filter and select
let's go ahead and see how we can order
a group it to order we use the order by
function here and here we are ordering
it according to the match ID so as you
can see the lowest match ID was 25 it
usually goes in increasing order you can
also change the parameter of ascending
as false to get the desired descending
order result now here I am registering a
temporary table of FIFA underscore table
for the same data frame which we had
earlier which is the FIFA underscore DF
and now I am going to use the sequel
queries here so as you can see it using
the sequel context I have passed the
function as is rather say the command is
this is a select asterisk from FIFA
underscore table this will give us the
same result as FIFA underscore DF dot
show as the same data is now converted
into a table which can be used in a
sequel context so here in this sequel
context we can pass any type of sequel
queries that we wished for we will see
that later in our different use case so
I hope you guys understood what the
basic functions the basic filtering the
order by select show group by all of
that can be used in the particular data
frames and you also saw how we can pass
sequel queries also here I'll show you
you just need to invoke the sequel
context dot sequel and inside that you
can give your sequel queries to be
executed on the table rather as say
because the data frame has been
converted into this table FIFA
underscore table so now that we have
seen how to create a data frame using a
CSV data file and also applies all the
functions like selecting ordering
filtering and also passing sequel
queries to that data frame let's go
ahead with another amazing use case
which is the superhero use keys so here
we have the data of all the superheroes
nonetheless I'll show you that one too
let me load the data set again this data
set is also in a CSV format so we will
use the same method here to get the data
in so as you can see the data is in a
CSV format here the superiors dot CSV
and Willi
the data frame in the same format so
guys keep in mind that if the top row of
the data set or the data frame which we
are using has all the column names of
the data set please make sure you use
the info schema and the header as true
so guys as I mentioned earlier you can
use the show command to see the contents
of the data frame but you can also
specify the number of rows so here in
case if I have specified show 10 that
means that it will show me that top 10
rows of the particular data frame as you
can see here we have the serial number
starting from 0 to 9 so let's have a
look at the columns the columns here we
have the serial number we have the name
gender we have the eye color of the
particular superhero the race to which
it belongs
alien witch or is it human ingredientes
neck entity we have the hair color we
have the height with the publisher and
now there are various publisher of these
superheroes like Marvel DC Dark Horse
NBC we have the skin color of the hero
we have the alignment and we have the
weight now if we use the print schema we
can see which all columns have what all
data types associated with it say for
example rays is a string
you can also set nullable as true and
false as i mentioned earlier now let's
filter out this with using the filter
function and let's see how many male
superheroes we have we have five hundred
five male superheroes and let's see how
many female superheroes we have 200 so
here I'm creating another data frame
which is the race and the squad TF now
what we'll do it we'll take the
superhero data frame and first it will
group by using the race and then it will
provide the count to that particular
race so as you can see here we have the
race and associated count to that
particular race is showing only the top
20 rows but I guess there are more Ray's
of superheroes than we expected
it's not the same as we see in the
comment boxes it
now if we create another data frame here
we are creating the skin at Asko TF
which is take the superhero underscore D
F data frame
I will group by according to the skin
color and it will also provide the count
of how many superheroes have that
particular skin color so as you can see
we have twenty-one green superheroes we
have five grey superheroes we have
certain red and cold in super users but
majority of them are not specified which
is 662 so this is one of the benefits of
using a data frame as and you can
provide null values to to your datasets
doesn't provide an error so till now we
have seen group by and order by let's go
ahead and see the sort function so I've
created weight underscore DF another
data frame and when to sort it according
to the superhero weight then according
to the weight column which is mentioned
here and I'm here I'm using the TSC
function which stands for descending so
as you can see we have Sasquatch which
is male eye color red and if you finally
look at the weight we can see the weight
is 900 I mean whatever be the unit of
that weight is the highest it's 900 now
I've created a DC underscore hero to
filter out all the heroes from the DC
Comics and if I want to see the count of
how many heroes are there in the DC
Comics we have 215 heroes which are in
the DC Comics and similarly if you want
to have a look at the heroes from the
Marvel Comics all we want to do is use
the filter function along with the
publisher as Marvel Comics and same
because do as is Marvel underscore hero
dot count
so you can see we have two hundred
fifteen euros from DC at three hundred
eighty eight heroes from Marvel now if
you want to have a look at all the
publishers and get a count on how many
heroes are there in the publisher you're
going to use the group by function along
with the count function so you can see
here Marvel Comics has 388 this is
another way of seeing how many
superheroes are there and DC Comics has
215 personally I feel that DC has much
better storyline that Marvel but still
as you can see here Image Comics has 14
and the null values has 15 so we can see
that Marvel and DC are the two major
contenders in the superhero comic
now here I'm going to create a table of
superheroes using the data frame
superior underscore DF and similarly I
will show you - how to pass sequel
queries now the select aspects from
superior table this is equivalent to
what we use in superior underscore DF
dot show the output of this one is not
in a very good sophisticated manner as
it was for the data frame now if you
pass the sequel query as select distinct
eye color from superhero table let's see
what the output of this one will be okay
we got the list of all the different eye
colors resisting eye colors yeah I
really love without a violet we have
gray tree in yellow brown in the Oh
silver purple now suppose I want to see
how many actually eye colors are there
two distinct eye colors so we have 23
which we have the distinct I close we
saw earlier that maximum weight went to
900 pounds or 900 kilos for a particular
sasquatch as you saw earlier here you
can see this one so we can get the same
result from sequel as well if we choose
the select maximum given the name of the
column which we won which is the weight
from superhero table although it will
give us only the maximum record but
it'll be the same as if you see the
maximum weight is 900 units so guys
these are some of the functions and the
features which you can use in the data
frames and as I mentioned earlier data
frames are very important as they are
used for structured and unstructured
data and it can load beta bytes of data
which is useful for big data
computations and most importantly it is
used for major slicing and dicing which
is clearing out and cleaning the data
sets so guys I hope you enjoyed this
session on data frames in PI spa so if
you guys have any comment related to
this video or you have any question in
mind feel free to ask in the comment
section below thank you so much I hope
you have enjoyed listening to this video
please be kind enough to like it and you
can comment any of your doubts and
queries and we will reply them at the
earliest do look out for more videos in
our playlist and subscribe to Eddie Rica
channel to learn more happy learning