Python for Spreadsheets and CSV File manipulation - Part 1 Basics

well welcome to another Python tutorial this tutorial is gonna be covering spreadsheet manipulation within Python I believe at least in Excel you can do pretty big commands and I think there's a few other like spreadsheet applications that you can purchase and I don't know anything about like Macs and all that but I know I don't have Excel and the Google Doc spreadsheet doesn't really give you the opportunity to apply the bulk amount like let's say you want to apply a formula to a hundred thousand rows there's no way to do that immediately at least not that I know of if you do know away great also the other downside is you can't really apply a really tricky formulas and if you can it's like you add 15 layers at least to that trickiness right to do it within a spreadsheet it's just like really confusing with the whole columns and rows and the numbers and the symbols and it's just a huge mess so if you can learn to do the manipulation within Python just makes things a lot easier so that's what we're gonna be doing in this video as long as you already have Python great if you don't already have Python then you need to pop over to Python org and download Python one sec I'll bring it over so this is Python org all you do is go to download and download Python 2.7 and get either 64-bit or 32-bit depending on your system so once you have that the next thing that we're going to need is actually numpy I imagine a lot of my viewers already have numpy but if you don't then you're gonna go over to Google here and oh cool it's falling apart again this is why we can't have nice things guys anyway we're gonna search numpy Python 2.7 here search that oh goodness so probably the best thing to do we get to the pi pi package index and download and install for python 2 7 now if you're operating on a 64-bit version of python then this will not no it's not gonna work out for you very well so the next thing you're gonna want to do if you're on 64-bit Python which you should be so if you ever want to edit files larger than two gigabytes you can the other option is to go to this website over here and I'll try to remember to put this link in the description wait here's a bunch of binaries for Python and you can download 64-bit binaries so we can go numpy here and all of these packages require an umpire's so that's why we're finding a bunch of it but eventually we get to numpy and this will be where you want to download if you have a 64-bit you'll download this one right here and that'll make it real easy to get numpy so once you've done that you'll want to make sure that you've done it so go over to your Start bar bring up the Python command line looks something like this and if you don't know what bit version of Python you have you can kind of scroll over and see right here it'll tell you and I have 64-bit and make sure you can do the following import oh my goodness can't tell you important I'm PI like that and as long as you don't have any errors you're free to continue if you do have errors and you can't figure it out leave a comment below and I'll do my best to help you out with that let's go ahead and get right to it so I've got a blank Python window here if you don't how to get there go to your Start bar type in IDE and what will pop up is something pretty similar to what we were just looking on actually it looks like this then you can go to file new window opens up this little untitled window then you can save it make sure when you hit save you save it as whatever the file type is plus py so let me bring it over and I'll hit save as and you'll see here there's a bunch of files here you know first of all pick whatever directory you're gonna be working in but when you save the file name you'll just do file name and don't forget to do dot py otherwise I'll save it as like a regular file and it's really annoying so once you've done that you should have you know window like this I'm calling mine CSB minute so the first thing that we're going to need to do is go ahead and import numpy as NP just so we can shorthand the numpy when we need it next we're going to define our main and within here I always like to put everything and try and accept loops that makes it easier when the program fail so I'm gonna put try and then accept exception with a capital e and then we're gonna call that an E and then we'll just print string e now in our try loop this is where we're gonna actually start using numpy so the file that I've got here is going to be to cheat dot CSV so it's just kind of a sample file here and in fact I'm gonna do you all this solid and give you guys this exact sheet one sec so if you want to follow on exactly what the tutorial go-to syntax comm slash and Tut sheet dot CSV hit enter and that will download the to the same sheet that I'm working with so you can work alongside with exactly the same stuff that we're using you don't have to but you don't have to but it will it could make it easier if you're confused so with that this is the file as you can see we've got date here we've got this little arbitrary name here we're saying gbp/usd and we've got an arbitrary number here and as we can see it really is separated by a comma spreadsheets don't have to be literally CSV which stands for comma separated variable but they can and this one is going to be so what we're going to do now is with numpy numpy has a built-in functionality called load text it's also got something like gen text or something like that that you can use it just it really doesn't matter so the next thing that we're going to do is define the variables here and as you can see we've got like date this is a Forex rate or a ratio so it will just say date here rate and then we'll call this arbitrary number or something so what we do is you define the variables so date rate and then parb for arbitrary number and this these three variables will equal NP for that numpy that we short-handed dot load text so load txt and the file path if the files in the same directory as this script then you can just do what I'm doing here and just type it in so touch sheet dot CSV and the next thing we can specify is the delimiter so come down yes here and say delimiter equals and our delimiter is a comma in this case it can be anything you want this you just specify it here and unpack needs to be true so we can do this right here so we can specify like multiple variables and then finally D tight is gonna need to be string and that is because date is a string variable and so is the label even so we need that to be that way now the next thing that we're gonna do here in the last part of this video anyways is just show you guys how this works if you're not comfortable with this like multiple variable kind of thing but if like say we want the right here this is each column right this is the date column so everything like in date it'll print out all of the dates in that comma or column so if we just call date or Lissa's pre date like this we'll save that and let's run that and we haven't called main yet so we'll just type out main to call it and this gives us an array of all of our dates subsequently the next thing you can do is you can do like date you get like the five it's not the fifth element it's actually the sixth element everything starts at zero just in case you didn't know date and then rate five and armed five will run this again and if you don't know how to run anything I guess I didn't say anything but there is a run button here or you can hit f5 and again will call main and now we can see the sixth row the day gbpusd and the numbers and obviously they're not comma separated you could kind of like superimpose some in there but you don't need to so those are just the basics of like loading it getting numpy if you don't have it in python if you don't have it and how this works in the next video i'm gonna be showing you guys how to actually be in manipulation now if all you really wanted to do is say get rid of a column you could just basically leave it out and then make a for loop which i'll show you guys in a little bit but if you could do this where it just prints the date and the arbitrary number right and so you've already taking it out so you can get remove columns pretty easily now you can remove columns really easily like Google Docs and most spreadsheets as well so I imagine the application of formulas to rows and making like a new column is what most people are interested in so we will be getting to that but in the next video so as always thanks for watching thanks for the support of the subscriptions and until next time

Channel: sentdex

Views: 92,056

Rating: 4.6165805 out of 5

Keywords: Python (Software), adding formulas to CSV, adding formulas to spreadsheets, Comma-Separated Values (File Format), Python Programming Language, spreadsheet, Magic, csv, Programming Language (Literary Genre), How-to (Media Genre), Spreadsheet (File Format Genre), Trick, spreadsheet tricks, Tutorial (Industry), complex formulas, tricks

Id: pbjGo3oj0PM

Length: 9min 11sec (551 seconds)

Published: Fri Oct 25 2013