Working with Dates and Time Series Data in Python Pandas

[00:00:00]hey there how's it going everybody in this video we're gonna be learning how to work with date and time series data within pandas now there's a ton of interesting stuff that we can do with date time data and we'll be learning about that here so we'll learn how to properly read in our data so that we can use date time functionality we'll also see how to filter by date times how to group dates by resampling the timeframes and we'll also take a look at doing some simple plotting with our time series data as well now I'd like to mention that we do have a sponsor for the series of videos and that is brilliant so I really want to thank brilliant for sponsoring this series and it would be great if y'all could check them out using the link in the description section below and support the sponsors and I'll talk more about their services and just a bit so with that said let's go ahead and get started okay so first of all I've been using the stack overflow survey data for this entire series so far but that data set doesn't actually have any date or time series data so I had to choose a different data set for this video I downloaded some historical cryptocurrency data that we can analyze for this video and as usual I'm gonna have links to download the data and the notebooks that I'm using in the description section below so I've got my notebook opened up here where I'm reading in this CSV file of data and let's go ahead and take a look at what this looks like so we can see here that I'm loading in this CSV file and I called this eth underscore 1h and that's because this is historical data for aetherium which is a cryptocurrency and i'm and this data is broken down on one hour segments so if we look down here at the head of this data we can see that we have some columns here the first one is a date column and these are broken down by the hour we also have some other information here like the symbols the open and closing values for these hours the highs and lows and also the volume so we can so all of this here is for let's see March 13th and this is for 8:00 p.m. 7:00 p.m. 6:00 p.m. and so on now remember if you want to see more information about your data frame so for example how many rows and columns there are I can run DFS shape and we can see that there are 23,000 rose here almost 24,000 so a good bit of data for us to work with okay so now let's actually get into working with date/time data so we have this date column here and it looks like this is just giving us every hour of the day but right now this isn't actually a date/time object I can kind of tell this just because it's not in a format that date times usually display as but if you want to be sure you can always try running a panda's data and estate time method on this to see if it works so let me just grab the first row of this data frame and I'll grab that date value so and then I'll go ahead and try to run a date/time method so to grab that first nap value I'm just gonna say D F dot loke and we can see here that the index is just zero over here so I'm just gonna pass in a zero and I want to grab that date column there so if I run what we have now then we can see that I've plucked out that first date so now let's just try to run a date/time method on this so there's one method called day name that will give us the weekday that this date fell on but if I run this now and I say okay dot day name for this value here if I run this then we can see that we get an error and it says that a string object has no attribute day name and that's because we are reading this in as a string currently so how do we convert this to a date/time so there's a few different ways that we can do this and we'll go over some of those here now if you want to convert a column like we have here to a date/time then we can use the pandas to underscore date/time method so to do this I could simply say we'll access that date column and we'll set this date column equal to and then we'll just say PD if for what we imported pandas as to underscore date/time and now I want to pass in that same column to convert that to a date/time now I'm not gonna run this right now because if I run this as is then pandas would do its best to figure out the formatting of the date/time and converted accordingly but the day time that I have he is in a pretty different format so I doubt that this is gonna work but let's go ahead and try it out anyway okay so I expected to get an error if we scroll down and look at the error here we can see that it says unknown at string format so it did not know how to parse this date but like I said before depending on how your dates are formatted then that might actually work for you this just so happens to be format in a way that pandas can't convert this automatically without us telling it how our date is formatted so what we need to do here is pass in a format string specifying how dates are formatted so that it can parse this correctly now I went ahead and I created the correct string format ahead of time for this specific date but just to be clear I never really remember these formatting codes off the top of my head I always need to go and find these codes within the Python documentation so I have that page open here and I will leave a link to this in the description section as well but however your date is formatted here so ours started with the year so we can see that that is a percent Y and then we have the month day so we can find that and here another one is that we have like 8 p.m. and things like that so we can see here that these eyes here this eye is for a 12-hour clock which is what ours is doing and then this % P is for the local equivalent of a.m. or p.m. so those are going to be in our format string but I'll leave a link to this just in case your date formatting is different and you need to create your own so the format string that I need to pass in here and again this is basically just telling pandas how to parse our date we're gonna say that first we're gonna see the year and then a dash and then the month and then the day with a dash in between that and then a space and then % I was that 12 hour clock and then there is a dash and then it is % P so let me go ahead and run this and if I put this in correctly then this should work ok so we didn't get any errors there but let's go ahead and make so I'm going to go ahead and look at the date column here and we can see that now these look more like date/time objects that we might be used to seeing in programming so it converted 11 p.m.

[00:06:54]to 23 well I'm sorry I thought 11 p.m.

[00:06:58]was the first one no it's 8 p.m. okay so it converted 8 p.m. to 20 and 7 p.m. to 19 and so on and now that this is converted to a date/time we should be able to run these date/time methods that gave us an error before so up here where we got this error where we tried to grab the day name for these I'm just going to copy that and paste that in down here and now let's try to rerun this and we can see that now it's saying that that first date in our series here this March 13th was a Friday ok so that's nice so it looked like it works now the way that we did this here is that we converted this to a date after we loaded in our data with this line right here but if we wanted to convert this to a date as we're loading in our data then we can also do that as well so if I go up here to the top where we loaded this in at this read CSV line here then I can actually pass in some arguments to read CSV so that it loads in certain columns as date times and then we can pass in our formatting string as well so that it parses those as the data is read in so to do this we need to pass in this parse dates argument here and now I'm just going to pass in a list of the columns that are going to be dates we only have one here so it's just going to be a list of one item whoops and I meant to put date not dates and now just like with before if your dates are already formatted in a way that pandas can parse them then you don't need to add anything else here but we already saw before that we need to pass in a specific format so to do this here we can't just pass in a format string we instead need to pass in a function that converts each string to a date/time object so first let's create that function and we've seen lambda functions in this series before but just in case you're unfamiliar with those you can simply create a normal function instead if you are more comfortable with those but this is just a shorter way so to create this lambda function I'm just gonna call this D underscore parser I'm gonna set this equal to a lambda function and I'll just use X as the variable here and now what do we want to return so when we used PD to date time down here we actually passed in an entire series to PD to date/time but now this is actually just going to be each individual string and it's going to send each individual string through this function so in order to convert this we can use a function called PD dot date time dot STR P time that's how we convert a string to time and then we can just pass in our string that we want converted to a date time and then the format and I already had the format down here so I'll just go ahead and copy that and paste that in here and that's all we need for that date parser function so now the argument for the date parser is date underscore parser and I'm going to set that equal to that D parser variable there that is set to our lambda function okay so now if I run this cell here then we can see that we didn't get any errors so that's good and now if I run at this D F dot head here then we can see that now our data frame was already loaded in as a date time so we didn't have to do any conversions later on it just did it as it was reading in that CSV file okay so now let's look at some more useful things that we can do with date times so first I'm going to delete the cells that we have below here so that we are not converting these columns again since they're already loaded in as dates so I'll delete that one I will delete that one since that was what was converting it earlier I'll delete that as well and I'll keep this one here just for reference since I will have these up on my github afterwards okay so before actually right here we saw how to run a date/time method on a single value when we use this day name method but what if we want to run that method on our entire series so let's say that we wanted to view the day name of this entire date column here so to do this we can access the DT class on the series object and access the date/time methods that way so to do this we can just say we can first grab that series so that date column is going to return a series if I run that we can see that we get all those values and now if we wanted to access the DT class on the series object then we can just say dot DT and now the date/time method that we want to use so if I want to get the day name of all these values then I can just dude a name there and if I run that then we can see that we get the day of the week for each of the dates in this series so using the DT class on the series object is very similar to how we access the string class or the STR class for the string methods on an entire series and we saw that in previous videos so this can definitely be pretty useful so let's say that we wanted to you know create another column so that we can quickly reference what day all of these trades took place so to do that we could just grab what we have here and I could simply create a new column by simply like I'm accessing a column so I can call this column day of week and set this equal to and paste in that date time method there if I run this and then we look at our data frame then we can see that now we can quickly see over here on the right that okay the 13th was a Friday and then we have these dates down here towards the end this was a Saturday so it's nice to see about her be able to see what days these trades actually took place so now let's look at how we can explore our data a bit so we can see by looking at the indexes here on the far left that there are over 20,000 rows in this data set so let's see how we can view the earliest and latest dates in this data so to do this we can use the min and Max methods so to see the earliest date I could simply access this date series here and I could just run the min method on this if I run that then we can see that the earliest date that it gives us is 2017 zero seven zero one now if I wanted to see so what is that that's a July 1st of 2017 so to view the most recent date that I have and it should be the date that I downloaded this data then I can just look at the max value here and if I run this then we can see that this is March 13th 2020 which actually was the day that I downloaded this data and one really cool thing with date times is that we can actually subtract dates in order to view the time between those two dates and this is called a time Delta so to get the amount of time that spans between these two dates here then I could simply say take the max value and then subtract the min value and if I run this then we can see that we get this time Delta that says that there are almost a thousand days between the earliest date in our data set and the most recent so we have 986 days in this entire data set of cryptocurrency data almost a thousand so that would definitely be a lot of days to look through if we want to find some specific ranges so what if we wanted to do some filters by date so for example let's say that we just wanted to view the data for 2020 now that we have these converted to date times we can create filters just like we have in previous videos and we should be able to use strings that are formatted like date times or we can use actual date/time objects we'll take a look at both so let's see an example of this and some code so that it makes some more sense so first I'm going to create a filter and a separate variable like I've done in previous videos but you can also do this inline if you prefer to do it that way I just think that creating our filters separate is a little bit easier to read so let's say that I want our date series I want the objects or the rows that are greater than and then I'm just going to pass in a string here for now and I can just pass in a twenty twenty there and pandas will know that I'm talking about the year 2020 let's actually do a greater than or equal to here okay so now that I have that filter let's just do a D F dot Lok again we've seen this in previous videos and then I'll pass in that filter so if I run this then my bottom row here should be January 1st of 2020 and it is and we can see that we have 17,000 hours here of 20/20 data or I'm sorry that's 1700 hours of 20/20 data okay so the reason that this doesn't go above 20/20 is simply because you know our latest data runs out so we're not getting 2021 since 2021 hasn't happened yet but what if we wanted data for 2019 well in order to do that we'd also have to put in an upper bound as well so to do that I'm gonna say okay we want our data to be greater than or equal to 2019 and and we just want to do an ampere sign there I'll go ahead and copy this here and then just replace this with a less-than and we'll say less than 2020 if I run this then we can see that our bottom row here we have January 1st of 2019 at midnight and then our top row here is December 31st at 11:00 p.m. of 2019 so that gives us all of the rows of data that we have for 2019 and right now we're just using strings up here for these comparisons but we can use actual date times as well so to do that we could actually say I could just say PD dot date time and then let me go ahead and pass in the month and day here as well so I'll say that I want this to be greater than 2019 January first and then I'll just grab this here and replace this 2020 and then I'll say but I want this to be less than 20 20s January 1st so now if I run this whoops and I got an error here the it says you know integer is required got a string that might not make sense what I did here is I don't want PD date/time that was my mistake I want to do the same thing that we did before and do to date time so that it converts the string here to a day time so let's do PD to date time for both of those and run this and now we can see that we get those same results as before for all of the rows in 2019 now one nice feature about dates is that if we set our index so that it uses the date which would actually be a good idea for this data set since all of these date or time stamps are unique then we can actually do this same thing by using slicing instead so let's see what this looks like so that it makes more sense so first let's set our index so that it's using this date column here so here at the bottom I'm going to say DF dot set underscore index and then I'm going to pass in that we want to set the index to date and if I run this then that looks good we have set at our index to use date here and now that that looks good it actually didn't make that change I want to say in place is equal to true to make that change permanent so I'll run that and if we look at our data frame again then now we have that date as our index and now with that date index we can actually filter our dates just by passing them into our brackets so if we wanted the data for 2019 then I could literally just say that I want the data here for 2019 pass that into my brackets if I run that then we can see that we get the same thing here we get this value for January first and then the top value here is for December 31st so it's a bit easier to you know just access these within brackets when these are our indexes rather than creating a filter now if you want to grab dates for a specific range then you can use a slice so let's say that we wanted all of the data for January and February of 2020 so to do that using this slicing here then I could say okay I want from 20 2001 which would be January and then I could just do a slice here using that colon and then say okay well I want to go up to February of 2020 so if I run this the second value here is inclusive so we can see that we have January 1st of 2020 down here at the bottom that slices all the way up to February 29th since this was a leap year now this can be really useful for analyzing our data because let's say that we wanted to get the average closing price for aetherium for all of our rows of these dates to do that we could simply grab this close column here and then grab that average or grab that mean so to do that we can just say let me copy this part here first let me just access that close series there that column if I run that then we can see that we get all of those closing values on each of those hours for all of those days and now to get the mean of that I can just say dot mean and that gives us the average closing price for all of those rows within that timeframe and remember each of those days is reporting by the hour but what if we wanted to see this data in a different way what if we instead wanted to look at this data on a daily basis instead of on an hourly basis well first we need to think about what would make sense to view on a daily basis so for example let's say that we wanted to you know view the highs for each day so right now we have all of these highs broken down by our let me actually look at the first let me grab this date range here and let's look at the first 20 four of these so that we can get 24 hours here so we can see that for February 29th we have all these different hours here and each hour has a different high value but what if we were like okay well we see all these different high values here but what was the highest value of the day so actually let me just grab a single day here and then we will look at the high values for that so instead of doing all of these dates here I'm just gonna grab January 1st of 2020 and then we will look at the high values for that day so again we don't really care what the highs are for each hour of each day we just want to know the high for the entire day so to do this all we need to do is grab the max value for this series and we saw how to do this it's just like running mean right here all we have to do is say dot Max and if I run that then we can see that the high value for that day was 132 0.68 okay so let's remember this value here right now this one thirty two point six eight because we're going to see how we can resample our data so that we can get the highest trades for each day of our data and then we'll use this one here to compare for January 1st of 2020 so again right now our data is broken down on an hourly basis so if we want to redo this so that it's instead broken down by day or week or month then we'll do this by doing something called resampling so let's see what this looks like so if I want to resample this and see the high value by day then I can simply access this high column here and then on that series I can say okay I want to resample this and now we have to tell resample how we want to resample this data right now it's hourly if I put in ad then it resamples it two days and I can do 1d or 2d you can do whatever there you can do a w4 week there's all kinds of different code here now just like with these date/time formats I hardly ever remember these so I always need to look them up in the documentation so I've got this pulled up in the pandas documentation here for these date offsets and I will leave a link to this page in the description section below as well if you all would like to try out some of these but we can see we have our minute second milliseconds microseconds all kinds of things if you're doing finances you can do quarterly and things like that so I want to do this on a daily basis so I'm gonna put ad there and now we have to tell it okay well what do we want to do what these resampling Ziff I'm looking at entire days here so if I take this entire day of the first what do I want to do with this high value and we're just saying well we want the max value for each of those days so if I run this then we can see that that gives us a series with all of the high values for each day so now let's save this series here as a new variable and look up the specific date that we used before so I'm going to save this as a variable and call that highs and then let's access that specific date of 20 2001 oh one for the highs now what we should get here since we're using the same date that we did here we should get this one value of 132 point six eight so if I run that then we can see that the high for that day was in fact equal to what we did here so that works but now instead of just getting one day at a time like we did here now that we've resampled this now we have those high values for every single day in our data okay so why would something like this be useful I mean you know that might be useful just because it's interesting but there are other things that we can do as well so let's say that maybe we wanted to plot this out but instead of you know viewing a plot that had these prices broken down hour by hour now we can just do a plot for the total price broken by day so within Jupiter notebooks it's extremely easy to plot out information I'm actually going to do an entire series on plotting with pandas so I'm not going to go into a ton of details in this video but we will see how we can do a you know very simple line plot here so to do this well we first need to use this special line within jupiter notebooks that allows our plots to display within the browser so all we have to do is say this is a % here then we can say mat plot lib in line now one thing that I do want to mention here is that I did have to go and install matplotlib in the virtual environment that I'm using so if you've only installed you know pandas or and that's it then you might want to go back and install matplotlib or else you will get an import error here but I went and installed that in my virtual environment so we can see that that worked there and with that one line of code there now we can display plots directly within our Jupiter notebook so I can simply run the plot method on this data frame variable that was resampled and get a plot of that so I'm just gonna say okay I want highs plotted out so heís dot plot I'll run that and we can see that we get a nice matplotlib plot here ok so that's you know pretty nice for you know just a few lines of code there now one thing that you might be wondering is if it's possible to resample multiple columns at once and we can do that by running the resample method on our entire data frame instead of one a single series so for example what do i mean by this ok so whenever I say you know resample multiple columns at once I mean that what if we wanted to resample this by day but so far we've only seen ok how we got the high value but what if we said okay well I want to resample this by day but I also want you know the average closing cost of that entire day I want the sum of all of these volumes for that entire day and then I want the you know the max high value and I want the men low value so the way that we've done that down here where we just access that single column we wouldn't be able to do it using this method that we did here so in order to resample and use multiple columns like that here's how we can do this so we can do this by running the resample method on our entire data frame so if you want to use the same aggregation method on all of your columns so for example let's say D F dot resample so now we're resampling our entire data frame object here and now we're gonna pass in what we want to resample on instead of day let's change it up and do week now we'll resample by the each week so if you want to use the same aggregation method on everything then you can just put in that aggregation method there so if I run this then this is going to give me the mean values for each of our columns on a weekly basis now this is cool that we can do this and sometimes you might want to do something like this but in this instance it doesn't really make sense to use mean to get the average of all of our columns so for example there's no real reason to get you know the average volume per hour or something like that you probably want to get the sum for the you know entire time period or for our high and low values here these are giving us the average highs and the average lows but the point of a high and low value is to know the high for that time period and the low for that time period so we probably don't want mean here either so how can we recess to where we can you know resample and use multiple columns but also use multiple aggregation methods now we've actually seen this in previous videos and use this method but what we want to use here is the AGG the AGG method and the AG method also accepts a map of columns and the aggregation functions that we want to run on that column so for example let's do this with the values for let's see we'll do the closing column we'll do the high and low columns and then we'll also do the volume here so I'm going to grab this from up here and then we'll do D F dot resample and we'll pass in a W for a weekly basis and now instead of passing in dot mean like we did up here I'm going to pass in dot AGG and now I can pass in a dictionary of the columns and/or the column names and then the values will be the aggregation function that we want to use on that column so for example let's say that for the closing value I do want to grab the mean of that and then I'll say for the hi column I want to use the max aggregation function for that since we want the max value for the low column I want to get the min and for volume I'll go ahead and just sum up all of the volume for that entire time period ok so again the keys here for this dictionary that we passed into AG the AG method this is the column name here then this is the aggregation function so we're taking the mean of clothes we're taking the max for this entire weekly period here for the highs the min for the low and then some for volume so if we run this then it gives us this nice weekly overview of the you know the weekly highs and the weekly lows here and also the average closing costs here and we also have the summation of the volume of trades so you know this really touches on what we can do with date times and time series data in pandas like I said a little bit ago I do plan on doing a full series on pandas plotting where we'll cover more advanced topics you know such as plotting plotting things out and having rolling averages for data and things like that now before we do end here I do want to thank the sponsor of this video and that is brilliant and I really enjoy the tutorials that brilliant provides and what recommend checking them out so in this series we've been learning about pandas and how to analyze data and Python and brilliant would be an excellent way to supplement what you learn here with their hands-on courses they have some excellent courses and lessons that do a deep dive on how to think about and analyze data correctly for data analysis fundamentals I would really recommend checking out their statistics course which shows you how to analyze graphs and determine significance in the data and I would also recommend their machine learning course which takes data analysis to a new level while you're learning about the techniques being used that allow machines to make decisions where there's just too many variables for a human to consider so to support my channel and learn more about brilliant you can go to brilliant org Forge slash CMS to sign up for free and also the first 200 people they go to that link will get 20% off the annual premium subscription and you can find that link in the description section below again that's brilliant dot org forge slash CMS okay so I think that's going to do it for this pay in this video I hope you feel like you got a good idea for how to work with date and time series data within pandas and like I said there's a lot more that we can cover with date time data but I feel like what we did here should definitely provide you with the basics of being able to convert analyze and resample your data so that you can do the exact analysis that you need now in the next video we're going to be learning how to read data in pandas from different sources so far in this series we've only covered CSV files but we're gonna learn how to read in data from excel from websites SQL databases and a few more so be sure to stick around for that but if anyone has any questions about what we covered in this video then feel free to ask in the comment section below and I'll do my best to answer those and if you enjoy these tutorials and would like to support them then there are some ways you can do that the easiest ways to simply like the video and give it a thumbs up and also it's a huge help to share these videos with anyone who you think would find them useful and if you have the means you can contribute the patreon and there's a link to that page and the description section below be sure to subscribe for future videos and thank you all for watching you