this is Torah I'm going to show you how
to import data and analyze it with a
Python and using the pandas package so
let's say we have some data like you
know something like time here in column
and then we might have some sensor data
that we've read in and in this case I'm
going to have eight sensors okay so it's
kind of like a table of sensors I want
to import these and then maybe take the
average of the sensors at least any good
one so I might have some bad data in
here as well and take the average and
then export this okay so after it's
imported you know just this raw data
take the average and then export the
combined table into another data file or
back into something like excel ok so
this is going to be the exercise let me
just show you a couple things from the
course website so if you go to AP
monitor comm and then go to CH e 263 and
then on the on the right hand side we
have the Excel and VBA so we already did
this example in Excel so we imported the
data and then and then took this average
and you can do that quickly in Excel
sometimes when you're dealing with very
large datasets though or you want to do
this programmatically versus in an Excel
we use something like Python ok and so
I'm going to come here to the Python
side and you can select data analysis
here I did a former tutorial on using
doing the same example but with just
numpy ok without the extra pandas
package and so for this one we're going
to do it with pandas so to get the data
file for this go ahead and just select
this source code it'll download a zip
file ok and then go grab that from your
download
the river is saved - I'm just going to
copy that onto my desktop and then
right-click it and extract all
okay so I'll extract this folder and
here I have an example script here's the
solution right here that we're going to
go through but more importantly we just
have these data files okay so here it is
in Excel and if we just open it up click
enable editing and then I see that I
have you know time and then my eight
sensors one of the things that you'll
see is that sensors five six and five
through eight those are not a number
okay as you can see those were bad
sensors and so I want to just take an
average of these four okay you also see
that time does not start at zero okay so
we're going to need to time shift that
as well I'm going to go ahead and close
this I also got the data in just a data
file and that's just a text file without
the headers
I find that okay so no headers there but
you can see the data in each row okay
I'm going to go ahead and close that or
you have a CSV file and if I right click
this one and unless they just edit with
a notepad you can see this is just a
text file as well okay but it has the
time sensor 1 through sensor 8 and then
each row is a separate data point for
each of those sensors okay so I'm going
to actually work with this data with
headers because pandas does a really
good job with headers okay so let's just
go ahead and open up a new ipython
notebook ok when that opens and it'll
come up with okay here and I'm going to
go to my desktop I'm just going to go to
that same folder that I just extracted
because then I can access the the data
file okay and so I'm going to select
over on the right-hand side I'll then
select new and then a new
book here and okay so this is just a
blank notebook I can rename it by
clicking the untitled okay so I'll just
call this import data and analyze okay
so I'm going to import the data and
analyze it
let's go just import some of the
packages that we're going to use for
this so first of all I'll just import
numpy as NP and I'll also do the same
with pandas a lot of people do PD for
pandas and then later I'm going to also
import matplotlib and pipe lot but let's
just go ahead and start with these two
and so it's going to import those two
and then I'm going to go ahead and
insert a cell below okay and then what I
want to do is now load I'll load the
data file okay and so I'll just say data
file these are going to be my results
that's going to be equal to PD and then
I'll do dot read CSV and I'll do my data
with headers dot CSV okay I could also
do read excel here and then do data with
headers Excel SX okay so that is the
excel file but let me go ahead and just
do it with the CSV for now okay so I'm
going to go ahead and import that okay
and then I'll go ahead and insert a cell
below okay so now I've got my data file
and let me just go ahead and you know
we're going to print a little bit of
this data file just so we can see what's
going on with this okay so I have print
data file and let's say I just want to
print the first just the first few rows
of that
okay so I'm going to go from zero to
three that's going to be zero one and
two and then let's just go and take a
look at what it imported okay so I have
my time column my s1 s2 s3 all the way
to s8 you can see the not on numbers
there for some of those okay now I want
to parse this out maybe make it a little
bit easier for me to work with it
I want to exclude the sensors that were
bad for example okay so let me go ahead
and just grab time first of all I'll say
that is a data file you know from my
data file and then I can use the square
brackets and just reference it by the
column name from the header okay and so
that will give me time and let's do
another one now which is good parse out
the sensors that are good okay so do
sensors equals and then data file okay
and I'm going to use the i-x and that is
going to do go from all say all the rows
and then I'll say what columns I want
and I'll go from s1 to s4 okay and then
let me go ahead and just print it just
so I can see what this looks like
I'll go ahead and print sensors and I'll
go just the first six rows of that okay
so it's going to be sensor zero to six
okay so I can see some of my sensors
that I just pronounced I I have new
variable called sensors
that's this table of just the sensor
values and now what I want to do is I
notice time does not start at zero maybe
for the purposes of plotting let me go
ahead and just subtract off this very
first time value right here from all of
the time
a vector from all the time vectors okay
so actually let me come down here and
insert okay so I'll go ahead and delete
that one and oh there it is the delete
okay so now what I'm going to do is go
ahead and just subtract off just that
very first time okay so it's going to be
equal time equals time - and then we're
going to do time and just the very first
value in that okay and then let's go to
print time and we'll go zero to three
okay and okay so there are my times
right there's start zero and then goes
to about one and then three so I don't
start with some large value of
subtracted off the initial starting
value on that okay okay so let's go to
the next line here and let's go and take
our average of these so I want maybe I
have three temperature sensors at
different points in a reactor for
example I just want to take an average
of those at each time okay so I could do
that if I do average and this will be
the average of the row okay
and that's going to be numpy and I'm
going to use the mean and then I'm going
to take the average of my sensors and
then the next the next argument for this
is over what index do I want to do this
so if I do one that's going to be over
the column so it'll take the average of
the rows if I do average with the zero
there instead of the one then it'll take
the average of all of each column along
the way okay so let me go ahead and just
print and I'll do average
I'll just print the first couple rows of
that average row and I'll go one two
three okay and then I'll also print
average on my column and I don't think I
need to connect that I'll just be for
different values okay so there is I had
for my average for s1 is 380 to s2 s3 s4
so that's the column average and this is
the row average that I just printed out
okay so I'm more interested in the
average row instead of the average
column just for this example okay let me
go ahead and insert another one okay so
now what I want to do is now I want to
put all of this information back
together again like I showed here where
you have you know this this average is
on the right now I've cleaned up I've
removed some of the not a number values
I just want to put this back and then
export it again okay so the way to do
that is just go and create something
like my data and then I'm going to
create a new array just with the
different values that I want to put into
my final data file okay so I've got
average row sensors and time okay and
then what I'm going to do is I'm going
to concatenate these I'm going to put
them together so result equals and then
I'll do PD dot kind of cat and that will
put them together and I'll do my data
and I want to do it along the second
axis or axis equals 1 which will be take
these columns column data and just put
them left to right okay if I did if I
did 0 then be like stacking rows on top
of each other but these are stacking
columns next to each other okay and then
when I do that let me go ahead and just
print
result and I'll just print the first
couple of rows on that okay and then you
can see I have my time s1 through s4 and
then my average which I haven't given it
a name for that column it was just a
numpy array okay now the next thing that
I'll do is go ahead and just save it as
something so now that I have my data
file that I want to export I can do
result and then dot two for example CSV
file okay so like where it came from
I'll just do result dot CSV I can also
do something like instead of to CSV I
can export it to an excel file okay so
that's x LS X okay so I can export it to
excel I can also do it to an HTML file
as I just wanted a table that's exported
to a web page okay so there's HTML
actually I need to do HTM L even though
my extension can be HTM or HTML and then
I'm also going to export to the
clipboard as well so when I run this
it's going to I don't have to give this
a file name what I'll do is we'll just
put it out to the clipboard and then I
can do the ctrl V to paste it somewhere
I can paste it in like - like a word
document or something like that you'll
just put this data table out to a
clipboard okay so let me go ahead and
save go and save this and then let's go
and run this last line right here okay
and then it's going to export it let me
go back into that folder so I can see a
couple of these results right here if I
open up the excel file that I just
created I can see all of my data with
the times the sensors and then the
average value I can oh
up a you know this is in HTML okay so
there it is a web page and then here is
my CSV file this is just a text file
that's comma delimited and okay so I
have all of my data here in this CSV
file okay and then alright and then I'm
going to show you clipboard as well so
let's say I wanted to open up word for
example and then I can open up a blank
document it's on the clipboard right now
so if I hit control V then it's just
going to paste all the values in okay so
there it was in clipboard okay so just a
variety of different ways to export this
now let's say we also want to maybe plot
some of these results as well so just in
order to use matplotlib and be able to
show the values here I'm going to do the
percent net plot live in line first of
all before I import okay and then I'll
do import mat plot lib pie plot as PLT
and then let's go ahead and go and
evaluate that and then I insert one more
cell below so now I've got
PLT and I'll just go ahead and create a
new figure and then let me go ahead and
just plot some of these data points I
have time and then I'll plot sensors
let's just plot s1 and then plot that as
a red line okay and then let me add
another one on here as well I'll plot
time
and the average rose let's see what I
knew the average row okay so for every
point on that plot just one sensor -
then the average of all four sensors as
well and I'll do that as a blue dotted
trend and then let me go and add a
legend to that and that will be to say
that is going to be sensor one and then
this one will be the average okay so
there I've got a legend for my plot and
we'll do an X label and a y label as
well so this will be time maybe in
seconds and okay 1y label and then we're
almost done here so that'll be sensor
values okay and then let's also just
save this so I can view it here but I
also maybe just want to save it as a PNG
file let's all good export my like this
plot that I'm creating and I'll do that
with the save fig and I'll just say my
plot dot PNG
okay and I I think I am done now okay so
let me go ahead and just execute this I
can see the sensor one the average
values for that and then let me go back
into my directory where I'm working and
it has my plot dot PNG so here it is you
know in PNG format you can import that
for example into a PowerPoint or other
presentation format okay so I think
we're done with this script now let me
just go back here to the okay so just to
review what we did is we just imported
numpy and pandas we on input two we just
read the CSV file
just displayed some of the data the raw
data just the first three rows assign
this as time then just parse this we
sliced some of the data just took
sensors one through sensors four and
then you reinitialize our time to start
at zero
we took some averages here on the row or
column based averages and then put the
data back together again
with the row averages and then exported
it to a CSV file to excel HTML and
clipboard and then we as a final result
we just plotted some of our data ok so
that concludes this tutorial video if
you're having a hard time with you know
some of these with pandas you know don't
or num num pie you know it just takes a
little bit of practice and then it will
be intuitive it'll actually probably be
faster than something like Excel if you
learn some of these commands there's a
lot of other commands in pandas I've
just kind of given you a very very basic
introduction it's used extensively for
example in the financial analysis sector
or others but engineers and scientists
also use it as well