Hello everyone and welcome to the data input and output lecture panels as a library has the ability
to read data and write data to a wide variety of sources for this lecture.
We're going to be concerned with just four main data sources see files Excel files H.T. mail from the
web and sequel in order to work with ASTM l files and sequel databases with panderers only to install
for libraries.
The instructions are in the notebook or if you just want to do it quickly.
These are the four libraries needed to install sequel alchemy lxi Mel HVM L5 lid and then the beautiful
soup for library you can do this either with Pipp or Khanda.
All right.
Once you've installed these libraries and reference a notebook you're ready to get started.
Let's go ahead and jumped in.
Jupiter note book and show you how to read and write files with pandas.
All right here we are at the Jupiter notebook.
Keep in mind that as we reference the files such as the example see X-Fi file and the example Excel
file you'll need to make sure they're in the same location as your Jupiter notebook is in order to check
the location of your a notebook.
You can just go in a cell and type P WD do shift enter and report back the location.
This is the same file location.
The example see every file in the example Excel file should be in.
If you've downloaded the entire repository of notebooks and are working in the data input and output
section or notebook you should be fine by just referencing the files as if they're already there.
OK let's go ahead and show how to open and read C as V files in order to do this.
You can just say PD read underscore CXXVI and then begin typing out the name of your C as the file.
In this case I know it's called the example.
So as I started typing this out a nice trick it's a click tab and it will auto complete the file name
.
In your case you may just have.
Example Not example that C S V.
Let's go ahead and read that in.
I did shift enter and now I can see the C as we file as a data frame with the columns A B C and D and
0 1 to 3 as the rows.
Note that if I want to read any files I just use the read underscore command line click tab here and
I get this dropdown menu of the variety of formats that Pancho's can read from.
So Pan this can read from even your clipboard.
See as we excel files HDR files Jaison pickles seek sequel files tables etc..
Just keep that in mind that they have a lot of options here.
Right now we're just going over the most common ones.
OK in order to write to a CXXVI file you're going to need a data frame so I'm going to go ahead and
just say DF is equal to PD thought.
Read underscore C as V.
And again just to do example tab autocomplete here and now I have T.F. as that same example DFS.
I've actually saved it to a variable off of a data frame.
You can say to underscore if you clicked tab here.
You'll notice that you have a variety of formats you can write to.
So basically almost all the same formats that you could read from.
So I'm going to go in and say DFA underscore to s.
I'm going to label this my underscore output and the other thing I do is say Index equals false and
the reason for that is because I don't actually want to save this index as a column.
Let me go ahead and show you what happens if I don't put indexical to false and they just put the F
to see Esrey.
I'm going to go ahead and say PD that read underscore cxxviii and I'm going to read the file I just
saved my output.
No I get this column here called unnamed 0 and that was the old index.
Now if I say T.F. to see is the index false in run that cell.
And then if I read my output again that I will actually say that index and I will automatically assign
a range index to the data frame.
Keep that in mind as you're saving your CACP files whether or not it's appropriate for you to say Index
equals false.
All right.
Now let's go ahead and move on to reading and writing from Excel files.
Pandas can read and write excel files.
Keep in mind that pandas can only import the data.
It can't import formulas or images or things like macros using that with the read other score.
Excel method may cause pandas to crash.
Another thing to note is that you may get an error asking you to install the x l r d library that should
have been automatically installed.
If you use the Anaconda distribution.
But if you did not you'll go ahead and go to your terminal and in your terminal or command line you'll
type conda install X LRAD or you'll do the same thing with a pip install.
Again you're not going to do this in a cell.
You will actually do this at the command line or terminal.
Hopefully if you did the Anaconda distribution that should have automatically installed along with pantless
.
Let's go ahead and see how this works.
I'm going to go ahead and show the Excel file.
I'll be working with your you have a very simple Excel sample.
In fact it looks almost the same as the so the file We're working with except here it's noted that it's
in sheet 1 and basically what Pandurs think thinks a workbook is.
Is that a workbook.
It's just a bunch of sheets and each sheet is a data frame meaning I can say PD that read Excel.
I pass in the Excel file name the X LS X File.
A good trick for this is to begin typing the file name press tab and select it again this only works
if the excel files in the same location as this notebook.
If it's not you'll need to pass in the whole file path and then you can say sheet name and then pasand
sheets one go ahead and read this and we get the output we expected.
And that's basically it for reading from an excel file.
You pass in the file and then you pass in the sheet name you want in Pandurs tries its best to read
it in as a data frame.
A data frame to an Excel output.
It's also a very simple process.
You just take the data frame you want to write to excel say to underscore Excel pass in the name of
the Excel file you want to save it to.
That's going to make a new one called Excel.
Sample 2 dot x l s x and then we can specify a sheet name if you want to.
Something to know is that where you're saying to underscore Excel for whatever reason pandas you need
to write in sheets underscore name and the name of the sheet.
We'll go in and put in new sheet.
All right it's good and run this and that's it.
We've saved that to an Excel file.
Now let's go ahead and move along to working T.M..
Again you'll need to install quite a few libraries in order for this to work and you probably need to
restart that Jupiter notebook after that.
Go ahead and check out the notes for this lecture.
The note book for it to get a list of all the libraries need to install or go ahead and go to the beginning
of the lecture where we show the list of libraries.
Let's talk about HTL input here.
I have a link to the FDIC doc of failed bank list page and here's just the list of all the failed banks
by the FDIC.
As far as them keeping track is the Federal Deposit Insurance Corporation.
And here we have a bunch of failed banks.
The closing date updated day etc. but this is basically just watched him blink.
This link is in the notebooks he can go out and copy and paste it from there.
I'm going to go ahead and copy this link and try to read it.
And of panderers nerds that do this all I have to do is say PD.
Well let's go ahead and make say data equals PD dots.
Read h T.M..
And then as a string I'm going to pass in that actual age TMLC.
They'll take a little bit of time to read it that it should work out if you have all the libraries.
OK.
Looks like it worked out.
Something to note here is that usually this isn't going to directly relate to a data frame.
If I check out what data is if I say type you'll notice that it's actually a list.
And essentially what pantless tried to do was to find every table element that was in this h team l
file.
And if you're familiar if h T.M. you know that if you can right click here and view page source you'll
have this list of actual HTL code.
And if you're familiar of h l you'll know that you can actually look for just like there's list classes
here.
There's actually table references so there's table markings in here in the HTML code and that's basically
the panel is going to do.
And that's going to make a list of them and convert each item in that list into a data frame.
And what you're going to do is just cycle through that list until you find what you want it to look
for.
In this case it should be the very first item in the list.
So if we say data at point zero.
So the very first item in that list that's the data frame we were looking for big name city is updated
.
Cetera.
Let's go and just check out the head of that data frame and that's what this looks like.
Again some things don't copy over.
Exactly.
You get a bunch of no values.
But keep in mind panderers is basically just reading this H.T. mail file as best as it can.
All right.
That's H-2A mail which is essentially just web scrapin of Pandurs.
Let's finally move along to our last topic which is working with sequel.
Now there's quite a few notes in your notebook regarding sequel and pandas.
Pandas is it actually probably the best way to read a sequel database because there are many flavors
of sequel engines such as postscripts sequel my sequel sequel light cetera where we're going to do here
is just actually build a very simple sequel engine that's going to be just temporarily held in memory
in order to show you how you can use Pandurs to read tables completely as data frames.
However I would recommend that you actually do a search for a specific driver dependent on your specific
sequel engine.
For example if you go to the notebook for this lecture you'll see that if you're using Postgres sequel
you should actually using a library called Psycho PGE 2 which is specifically designed to work with
post-career sequel or if you're using my sequel you should use pi my sequel.
In other library specifically designed for that specific flavor of sequel.
Right now we're just going to do a very simple example of how pandas could read an entire table and
sequel if it had to.
We'd end up doing is saying from sequel alchemy imports creates engine and this is going to just allow
us to create a very simple sequel engine in memory.
I'm going to go ahead and say engine is equal to create underscore engine and here and just going to
type in sequel lights
three forward slashes and then memory colon.
So I have sequel like colon three forward slashes colon memory colon.
We're going to go ahead and run that and this little thing.
This little line of code has created a very temporary very small sequel light engine database that's
running in memory.
I'm going to go ahead and take my data frame that DSF and I'm going to say to underscore sequel and
I'm going to write it to that very temporary engine that we have running in memory.
So what you can do is we're going to say to sequel you're going to pass in what you want this table
to be called.
So go ahead and call this my table.
And then you pass in the sequel engine you're working with.
In this case it's just an engine.
Now this engine is going to be a connection usually I would recommend you read through the documentation
thoroughly but again like I mentioned earlier pandas just by itself probably isn't the best source for
you to actually read sequel into pandas directly.
You should probably look for specialized Python libraries for whatever version of sequel you're working
with.
But as far as a very simple setting you can just say DF to sequel passen the new table name you want
and then the connection to your sequel engine.
And now I'm going to go ahead and say sequel if and actually read back that table.
So going to say PD Reed underscore sequel I pass in the name of my table in this case it's my table
.
And then I specify the actual connection and so cion.
And that's just the engine that very small sequel lights database that's happening in memory.
If I go ahead and read that and check out what that looks like I get sequel DMF and notice that index
is a new column here.
All right go ahead and feel free to post the Q&A forums.
If you're having trouble finding a particular library that works if your sequel engine or your flavor
of sequel as far as this lecture the main thing to get out of it is how to read and write to C S V files
because they're only going to be work of C S V files for the rest of the Course as far as your own personal
usage.
Hopefully you can just check out the read underscore methods.
There are a lot of them and you can just look at the documentation or reference a notebook for how to
actually read to a panderers data frame.
All right thanks everyone.
Go ahead and ask any questions you have on this lecture at the Q&A forum because there's a lot of options
here and we just don't have time to cover them all.
But if you have any questions I'm happy to answer them in the forums.
Thanks everyone and I'll see you at the next lecture