Hey there, this is Ruben Lerner. Thanks for coming to my talk about dates and times in pandas for conf fourty two. I am not using to be using slides for this talk. Rather, I'm going to be typing directly into the Jupyter notebook. And I hope that you'll then see not only the sorts of cool things we can do with times and dates in pandas with Python, of course, but you'll also see the process that I can use to play with Jupyter and use it in order to do what I want to do here. In case you don't know who I am. As I said, my name is Ruben Lerner. You can learn more about me at learner Co. Il. I'm a full time Python trainer. I have a whole variety of online courses that I offer to people around the world, everything from intro Python to advanced Python courses. I also have a set of courses called weekly Python exercise, and I have a number of free courses as well, including one about Python interviews and Python for non programmers. Take a look at my site. It'll lead you to all the good stuff there, as well as to my blog and to my mailing list, the better developers list, which about 20,000 people subscribe to this point. You can get that@betterdevelopersweekly.com okay, well, what are we going to talk about here? We're not here to talk about all my stuff. We're here to talk about Python pandas. So we're going to be talking about dates and times in pandas. So first of all, let's define dates and times, how they work in programming. Then we're going to talk about how we can load date time information into a data frame. And then what we're going to do is look at dates, let's say extracting date time information. And then we're using to talk about time intervals, aka deltas, and how we can get that in a data frame. And then we will talk about how we can use date time objects in a data frame index. And then finally we will talk about time series aggregation and resampling. Cool. That's a lot of stuff, but we're going to go through it and hopefully be really cool and interesting. I find this stuff incredibly interesting, maybe because I'm a nerd and maybe because probably because it's actually interesting. So let's first start off with dates and times in programming. So dates and times. The thing is, there are two different ways to measure time, and I'm not talking about whether we measure with month day, year, hour, minute, second, or whether measure with seconds, which is sort of the traditional Unix way of doing it, number of seconds. Since the 1 January 1970, we basically have two different things. We can talk about a timestamp, aka a date time, aka a point in time, and we can talk about a time interval, aka a time delta. So what are these different things? Well, basically a date time or a timestamp, depending on what language you use, what system you're using, this points to, or this is a specific point in time. So we can say, e g. When you were married or when you were born or when you got your first paycheck or something like that, is a specific point in time. There were many, many points in time before it, many, many points in time after it, but is a specific specific point in time and it will never repeat. It is unique. And so we can use all sorts of data structures in Python to do this. The time module provides us with all sorts of stuff for working with that. Also the date time module, which is sort of a higher level way of working with dates and times, can also do that for us. So that's great, right? If I have a date time, in fact, if I can go here, I can say here, import date time, I can say date time, date time, knows date time now. And you see, I get back. This is a pure Python date time object. It's in the daytime module. The datetime class knows it was a method we call on that and we get back now. 2021. 516, 1222. 552. And this is the number of, I believe it's milliseconds. Microseconds. I guess it would be. Well, okay, what if I say like, start equals date time, date time now, and then we wait a few seconds. Okay, I've waited enough. End equals date time. Date time. Now we see that start is here and end is there. Okay, this is not a huge surprise. I waited, what does it look like? About 7 seconds. And sure enough, the second one is 7 seconds later. What if, though, I want to find but how much time pass between these two date time objects? Right? So that's also a measurement of time. But it's not a measurement of time in the same way. And I must say it took a long time, no pun intended, it took a long time for me to understand that when we talk about time as people, we are actually using these two different ideas. One is a specific point in time. One is a length of time. A span of time, or has I called it before? A time interval, a time delta, for example, let's say you have a meeting that starts at 01:00 p.m. And it will go from 01:00 p.m. To 02:00 p.m. So the start time will be at 01:00 p.m. The end time will be at 02:00 p.m. But the length of time it takes will be 1 hour. That's a time interval. That's a time span. And what if you get a call just before the meeting starts and they say, oh, we can't meet now, let's meet half an hour later, we'll just push it back. Well, everything gets pushed back to the start time, and because later the end time becomes later. But that time interval will actually be the same. It's still going to be 1 hour in length, as if we're lucky enough to have 1 hour meetings. Right? So how can I deal with that? Has a python. Well, I can actually just say end minus start, and look what it does. If I take one daytime object, I subtract it from another daytime object, I get a time delta object. Daytime delta. And it's kind of weird in that it's going to measure here seconds and microseconds. If it's really large, it'll measure in days, seconds, and microseconds. But those are the only components of a time delta that we really need to think about and worry about, even though it's covering any sort of time delta, these are the three sort of pieces that it uses to measure that. So anything up to a second will be in microseconds. Anything between a second and a day will be in seconds. Anything a day or greater will be in days. And you can of course mix these together as we see here on my screen. Okay, so we have daytime objects and we have time delta objects. What in the world does this have to do with pandas? Well, it turns out that when we're working with pandas, we actually deal with a lot of dates and time dates. So I'm going to load up now one of my favorite data sets to play with. So I'm just going to first say Pylab inline, I'll say import pandas as PD and from pandas, import series and data friendly. Okay, so I've loaded up pylab inline is for using Jupyter. It makes sure that numpy and Matplotlib are both loaded and they're in the current namespace. I load up pandas because it's not loaded by default. And then from the Pandas library from the pandas module. I'm going to load years and data frame into the current namespace just because it's super useful to be able to do that. And now I can actually create a new data frame and I can do it based on a file. And a whole lot of files in the world data science are in CSV format. It's not a surprise. We have PD read CSV and I can just read in a file here. Well, what is this file that I'm going to read in? Let's just take a look at it first. This csv file. So this is taxi csV. This is from a number of years ago. It's a little out of date, but not. We'd still do analysis on. It's kind of fun. These are taxi rides from New York City from 2015. And basically when I was growing up, and even though I live in Israel now, I grew up in the New York area, if I wanted to take taxi. So I'd get into the taxi, tell the driver where I wanted to go, and when we arrived there, the driver would tell me, well, it was written on the meter how much I had to pay, and then basically I would pay the driver however much it was, add a tip, and we would be done, and I'd get a receipt as well. Well, nowadays, of course, in New York, it's way more high tech than it was back when I was a teenager. Nowadays, when you get into a taxi, there's actually a computer in the backseat, and the computer, you think, is there just to annoy you with television ads and movies and all sorts of spammy stuff. But at the end of the trip, it allows you to use a credit card to pay, and then it asks you how much you want to tip. And it's, I think it's like 2025 and 30%, something like that. People definitely pay more for tips now than they did when I was a teenager, when we were told it was 15%. And of course, you can choose something else if you want. So basically, this is a record of every single taxi ride in New York, where you can go to the New York City open data system and download taxi rides from just about anytime you want. I think it lasts ten or 15 years. I don't remember exactly when they started recording this. So the computer is not just there to annoy you with ads and not just there to help you with taking your credit card, but it also records a lot of information about the trip so that you can use it in all sorts of analysis or so that the taxi limousine Commission in New York can use it for all sorts of analysis as well. So this is from a number of years ago. And you'll see they have a whole bunch of different columns here, the vendor id, that is to say, which company made this computer the pickup daytime and drop off daytime. We're going to be getting back to this in just a little bit. Number of passengers, how far did you go in miles? The pickup longitude and pickup latitude. And these actually have been removed from more recent data because I think people realize it's kind of creepy to have this open data, say, where you're going from where you're going to. What rate were you using? Storm Ford flag I've never quite understood, truth be told, where you're dropped off, longitude and latitude, how you paid, how much the fare was, how much extra tax, tips, tools, improvement, surcharge and then finally the total. So we can read this data in and if we read it in then we can do some analysis of it. And I love to do analysis of this in all of my data science courses that I do for corporations and for individuals. Well, let's read it in. If I say here DF equals CSV, it actually gets read in very, very nicely. Right? We can see that we have the headers here and we have the rows and there's only the has. That's only going to show me the first five rows in this file. But if we ask DF count we're going to find out that there are actually 10,000 knows in this file and there are no nan values. So every single column is going to give us nine, 9910 thousand minus one. And so what else? We have passenger count and this and this and this also really great stuff. The thing is, if I now say DF info, show me pandas, what kind of data do we have for the pickup and the drop off dates? We have objects and DF info tells us not about the data in the data frame, but about the data frame itself. How is it defined, what does it have? And we see that we have two objects here. Now you might say, oh, that's fine, the objects are good, but it's not actually that good because what it means is pandas wasn't able to figure out what kind of data it was. It's usually able to figure out integers and usually able to figure out floats. And we see that here. But anything else it just says, oh, that must be an object and calls it an object that's really a string. So we really have here is string data. Now I'm going to reduce the number of columns we have because we don't really care that much but a whole bunch of them for our purposes today. So I'm going to say t, I think we'll read CSV. And I'm going to say here. Oh, yeah, I'm going to say here, use calls equals. And I'm going to say TPF, picked off daytime. And I'm going to say here, TPF drop off daytime. I'm going to say here passenger count. And then I'm going to say here, I'll do trip distance. And I'm going to say here total amount. These are like the columns that I enjoy playing with. And so now if I do a DF info, it's at least fewer columns. But we still have the problem we did before, that these are all objects, these are all strings. Like what are we going to do now? I need to somehow tell pandas, listen, pandas, because if we look at it now, it looks like a date and time, right? I mean it has dates, it has times, but these are being read as strings. If we want to do any sort of date time manipulation, we're really going to need to have it read in as date time objects. So how can we do that? Well, it's not going to guess on its own, so we're going to need to give it a little bit of a kick. We're going to need to give it a little bit of a hint. And what we can do is we can say here our dates equals and I'll say here which columns I want TP date time and TP date time. And we do that. And now I look at DF and it looks exactly the same. The visual part of this or the printed representation is exactly the same. But if I say DF info, oh, look at that. Now we see that we have 64 bit dates time objects, and it's even telling us it's down to the nanosecond, which is kind of nice because I hate to be late to a meeting by nanosecond. So it's good that pandas can keep track of this stuff for me. So this is now how we're going to read it in. Now you might be saying, wait, it's very nice that when I read the. I'm just going to scroll up here for a moment. So you can see it's very nice that pandas is able to read this sort of date format and understand that this is a date. So I tell parse dates and it says, oh, I see year, month, day, hour, minute, second. This is indeed a common date format. It's also one that's very easy to parse. But what if, just what if I have it in different format? What if I have it in month day year format, which is pretty common in the United States. Yeah. Then it might have a harder time understanding it. It can understand it, but might have a harder time. Moreover, is it month a year or day month year? Is it american style or rest of the world style? I should say like european style, shall we say? Because typically in asian countries they actually use this style of from biggest to smallest. So year of month, day, hour, minute, second. So if we say basically, how does it know whether the month come first or the date comes first? If I have 1220 21, is it the 1 February or the 2 January? So it turns out that when I read in a date and time, or when I read anything, if I do Alpha PD read CSV, there are a ton of different options that we can provide. One of them, of course, was parse dates. And I gave it, told it which ones to do. But there are a whole bunch of other things that have to do with dates, and one of them here is. Here we go. Parse dates. Okay, that's good. Equals false infer daytime format. Like should it assume that it knows the format? If it doesn't infer it, then what are we going to do? Well, we can play with this a little bit. Keep date, call date parser. And this is where you can, if you really want, to, provide a custom function that will read in the date and then spit out a daytime object based on its parsing. And this is what I was talking about here. Day first equals false meaning. Is the first number there the date, or is the month? Month. So day first equals false means? No, it's the month. So if you're reading in european dates, you're going to have to say day first equals true. Okay, fine. Then we got all sorts of other stuff there, but we're going to ignore those for now. So I've successfully read this data in and knows I can say DF info and we see that it's dates and I say df head ten and we see that it states and times. Fantastic. Well, now how does that help me? What have I gotten from this? That these are actually date and time objects? Well, I can now perform all sorts of calculations on them. For example, maybe I want to find out what was the hour at which each of these trips took place. How can I find that out? Well, I can say DF or TPAP date time, and I run that and we get here, that column, that's not a big surprise. Wouldn't it be nice if I could sort of go through each of these and say, I just want to pull out the hour, I just want to pull out the minute, I just want to pull out the month. All reasonable, right? Things that we would want to do with dates and times. So it turns out that there's a DT accessor object. This is similar to the stir accessor object that you might be familiar with. And this allows me to pull out date and time information. So if I say DT now, we're going to the daytime accessor now, it has its own attributes that I can use to retrieve stuff. If I say DT hour, and look at that, we're pulling out the hour, I can say DT day of week, and it'll tell you what day of the week it is. I can say DT month, and I'll look at that, I can say year. That'll allow me to do it. Yeah. Is leap year, is order end. So I can basically pull in all sorts of really useful, interesting information based on this DT accessor. All right? And so the DT accessor has a whole bunch of attributes that allow us to do these. Now you might be saying, okay, I can understand hour, I can understand minute, but what's with this is quarter end. Why in the world would I care if it's the quarter end or not? And the answer is that, well, you have to remember that pandas was developed in an investment firm, and so they were trying to do all sorts of, or actually Wes McKinney, who invented pandas, was trying to figure out, how can I use Python to perform these sorts of calculations that are typically, traditionally done, say, in Excel? And if you are working in investments, then you want to know, is the month end? Is the month beginning? Is it a holiday? Is other quarter end? And so there are all sorts of attributes you can use to pull information out like that from the DT accessor. And then you'll either get a number out, as we did for a K minute, or you'll get a true false value. You have to decide what you're going to do with that. So how can I use this? Well, maybe I want to know what is the distribution like? I have all this data here. I have 10,000 taxi rides. Is this a reasonable distribution of hours across the day? Is this a reasonable distribution of dates across the week? So let's find out. I'm going to say here DT hour. Let's find out here DT hour. Let's do a value count, because remember, what I'm getting back here is a series. So because I'm getting a series back, I can apply the value counts method and thus get back what's basically a count of how often each of these values appears. And you can see very clearly here that this is not a representative sample, that we have a whole lot of trips from 11:00 a.m. Some from 03:00 p.m. Some from midnight and a handful from 04:00 p.m. So if you're trying to make any sorts of assertions based on this data, they're probably going to be fundamentally flawed because they don't actually have anything to do with, it's not a real distribution of data. So you might be making all sorts of mistakes. What about days of the week? Can we do that? Well, I'm sure I could check here day of week and we get value counts and we see that once again the distribution is totally wacky. You can't really make any sorts of serious decisions based on the data here. Or if you do make decisions, you'd better go check them based on actual more representative data so we can find out this sort of information, then plot it accordingly, or just make assertions accordingly. Or we can pull things out based on that. So I can say, for example, DF types pickup daytime DT day of week equals, equals one. That's going to give me, of course, true or false. I can use that as a boolean index into DF. And then I get back only those rows in which the day of week was one, meaning Monday. And then I can even use this, right, this is like a good start, but I can even say, let's apply that actually just to total amount. And then I can get that. So I can find, but what's the total amount for this day of week? How much do people pay on average on Monday versus, say Sunday? I can pull this out here and say day of week zero and get that out as well. We see that basically on Sundays they paid more. Can we really say that? I don't really think so, given this data, but given real data, you absolutely could. So that's like an initial view of what we can do with date and time data. And for many people that's great. That's enough, because basically we can use that and then we can do all sorts of analysis. I can find out on what day of the week, on what day of the month, at what hour of the day, did people buy the most from our online store or do they buy the lease? Or then we can sort of juice it with oh, let's give them some coupons during the dead time. Sort of like a happy hour at a bar except that it's online so less physical drinking. At least we're not spreading across the network there. But maybe I want to find out not at what hour did things take place or what day did things take place. Maybe I want to find out how long different trips were. Well, we saw already before that. If I have a date time and another daytime I can subtract them one from the other and I will get a time delta. Guess what? We can do that here. So I can say df of TP drop off date time minus DF types pickup date time. And if I subtract those, oops, I didn't mean to do that. I did an e. I meant to do minus. Let's go reload our file again. Read CSV. Now I'm going to go down to the bottom. I'm going to say minus not equals. There we go. And what do we see? We actually see a time delta object for each of our elements. And you see here the dtype is indeed time Delta. Well what I'm going to do now is I'm going to create a new column. Say df of trip time equals equal sign. Seems to be like really for your happy today. And now if I say df head look what we're going to get and what we have then is the pickup drop off pass, recount trip distance, total amount and then we have the trip time which is pretty great. So now I can find out what was the trip time. Well how am I going to search for that? I could say like ds of trip time equals equals. And we could say like what zero day 28 23. Could I do that? I can. Right. And it'll find all the ones that were like that which is going to be one. So that's like one way to do it. But here's the thing. I can also do comparisons. I can say DS of trip time is less or let's say less than and I can say meaning how many trips were less than 1 hour in length. And this gives us a boolean index or boolean series which we can apply as an index. So this will give us all the rides that took less than 1 hour which we'd hope is quite a lot. How many rides took less than 1 minute? We can search for that too and we'll find you'd be surprised. 84 of the rides out of our 10,000 actually took less than 1 minute. Now if you're thinking this is kind of weird, how am I comparing this dates? Time object or this time delt object, I should say with a string. Well, that's because they're automatically doing some conversions for us. That's because pandas are smart enough to say, wait, if I have a time delta here and I have a string here, maybe, just maybe, I should do the comparison. It turns the string into a time delta and then it can do the comparison, which is pretty snazzy. I can even use some words. I can say like, hey, not, I can say 1 minute or I can say 1 second. Of course I can make, by the way, look at that, there are a whole bunch of them that took less than 1 second. And you thought traffic in New York was always terrible, right? I can also say like how many of them were more than, oh, I don't know, 10 hours. Were there any taxi rides that were more than 10 hours? Yes, there were. There was one ride that actually took twelve and a half hours. So much for no traffic, right? But it's okay, they only had to pay $11 for the privilege and they only went 1 mile. So I guess actually the traffic was pretty bad. Or this is representing some data that's a little, shall we say, not reliable. So we can use timestamp objects, we can read them in, we can use them, extract from them, but we can also use time delta objects and see how they compare them on our own and look for them in different ways. So these are two major ways that we can use dates and times in pandas. But it gets better than that. As you might know, pandas indexes are really powerful. And right now what we have is just a bunch of numbers, right? There's nothing wrong with that per se. But if I say types head, let's say 20, we're going to see that the indexes are zero through 20. And that's because we didn't set them to be anything special. And the default index is just integers from zero to whatever. But one of the nice things about pandas indexes is that we can set them to be anything we want, more or less. They can be just about any data type and then we can retrieve based on those. And what we can do then is we can say, hey, I actually want one of these columns to be the index. And we call that a time series. So what I can do is I can say df set index, bDF tpad let's say pickup date time. And if I do that, look what I get back, I get back a data frame that's identical to what we had before, except that now the index is that column. And you can even see sort of this historical remnant here. We know that the column was originally called that, even though indexes aren't really called by name. There's just one problem with what I did here, which is that I did not actually change the data frame. This returned a new data frame. And so if I want to change it, it's really tempting to use in place equals true, but don't do that. The pandas core developers have told us now for a few years that's not the right way to do it. Instead, what you should do is say DF equals that. And what I'm doing is I'm then taking the new data frame that I got back and assigning it to DF. And now if I say DF head, sure enough, we have changed. Okay, that's nice. So what? Well, actually, remember that we can retrieve things based on an index. That's sort of the whole point. So I can say DF lock of 20 15 62 11 19 29. And remember also that indexes and pandas are not guaranteed to be unique. Now, this might sound weird, but it means I can pull out all of the trips that took place on the 2 June 2015 at 1119 and 29 seconds. And we see that there were three trips that started at exactly that time. But it gets better than that, because if I now use not the entire timestamp, but part of it, if I chop off the seconds from there, now I get everything that matches any seconds for those minutes. It's like having a star there. Obviously, we don't use a star. And so now we see it's 146 trips that took place on that day, at that time, at that minute, but any seconds. So sometimes it's 56, sometimes it's 59, sometimes it's 30th. Could be any of those. And we can keep doing this. I want to find out everything that happened at 11:00 a.m. Any minute. I want to find out anything that happened on that date, anytime. And so this allows me to really sort of find stuff based on those dates and times with a lot of flexibility, which is fantastic. The other thing I can do is I can use a slice so I can say DF lock of 2015, let's say 6211, 1929, all in. And then we'll say 2015 6211, let's say 29, 29. Actually, let's even say like 2029. So just 1 minute and this will now give us all the rights that took place in that 1 minute interval from 1119 29 until 11 20 29. Notice it'll also be up to and including because when you do a slice in, pandas don't have integers or you don't have numbers, I should say actually I guess it's just integers then. It's always up to and including as opposed to what integers is up to and not including. So this is actually a really powerful technique as well. So if you're looking to find out, hey, show me during this interval, what was the average trip distance? Well, I can do that. I can just say here dflock of that trip distance and I can say mean and we'll find out what was the mean trip distance during this little slice of time. But you know what might be fun to do, or even useful, not just fun, is to say I want to find out from the first timestamp we have to the last one, we know that it's not going to be continuous because we know it's going to be chopped up and we have some data. We don't have the data. So from the earliest one until the latest one in let's say 1 hour intervals for each of those hours, what was the average distance? Or what was the average total fare? Total amount. Can I do that? Yes, I can, because now that I've set my index to be a timestamp, now that we have its time series knows I can say df resample, and resample allows me to say I want to go based on 1 hour. So again, it's going to take the earliest time, the latest time and find all the times in between in 1 hour blocks. There might be no data for a bunch of those, and that's okay, in which case we'll just get none. So if I resample, look what we get back. We get the date time index resampler. Very exciting. And where do I know that it's one h from? Because I looked at the documentation and quite frankly, except for a few of them, I have to look it up for all of them. So okay, one h. Now what? Now I can say show me. Let's say trip distance mean. And look at that. I now have the mean trip distance for every hour from the earliest time to the latest time. Now it's not showing me everything because pandas traditionally doesn't show me everything and there are a lot of Nan values. So once I get back to the series I can actually drop na, you're going to see, it's going to go back to be those 4 hours that I had before. Okay, not so exciting. That's fine. But what if I were to say, like for example, one, I think lowercase M is minute. No, it's month. But you can use here any number you want. You can use here any of the extractors that you want. I know about hour, I know about day, again, the others, I have to just sort of look up because there's just so many. And so you can play, I can say here, well, what about like eight hour intervals or twelve hour intervals? Or I can say in each even twelve hour interval, how much did people go? How far did people go? Or how much did they pay? By the way, if I take off that trip distance, let's say I do it, apply to the entire data frame. And there we go. I'm going to for each of the columns and then for each of these timestamps. And so you can do this with resampling on any interval you want. You can pull out columns if you want, but you don't have to. And then you can use any aggregation function that you want as well. And the aggregation functions are min, max, mean, standard deviation count. And I'm sure there's some others, but those are the main ones that we would use. And so again, they have to aggregate, they have to give us a full answer over a whole bunch of things, but allows it to split up by time. And the fact that it doesn't care where we have data, it's just going to sort of go from that earliest to latest makes it especially useful. Okay, I hope that you enjoyed this talk. I hope it was useful, mind opening and so forth. Once again, I would be delighted to hear from you. If you have comments or questions about this talk, I can also send you a copy of the Jupiter notebook that I used for this. So you can just drop me a line. You can always catch me on Twitter as learner, or you can catch me via email at Reuven M. Lerner Coil. And once again, my newsletter, better developers, goes out every week to about 20,000 developers and I would love to have you join and years with me with a new article about Python free every Monday. Thanks so much for watching my talk. I hope you enjoyed and I hope to hear from you. Enjoy the rest of the conference.