Cleaning Messy Sensor Data with Pandas | PyCon AU 2017

[00:00:00]with us now is Xavier ho presenting his first Pyon talk and Xavier is a curiosity driven researcher and software engineer oh uh there is markdown links in this unfortunately I can't render links in my speech so you're just going to bear with me um he works for CSR iro creating interactive data visualizations uh currently pursuing a PhD part-time at the Design Lab at University of Sydney uh keeps him busy and he sometimes wonders about machines and humans and that philosophical lot uh previously Xavier worked in a Sydney startup doing computer vision work uh freelan as a videographer and taught a handful of programming classes to University design students his passion lies somewhere in the spectrum of chocolates video games in a better Ro and you can find him on Twitter please make him feel welcome thank you Sam uh can everyone in the back hear me as well okay I can see knots thank you so today I'm going to give you a a quick tutorial on how you might clean up M data sets so if you've been to um the previous two talks on documentation this is a tutorial I'm going to walk you very slowly on how you might go from getting a data set to something that you can use uh for production so uh this talk will also be online in fact I've already posted it on Friday on slack so you don't have to take any notes and everything is in the dbal notebook so feel free to run through the examples afterwards as well and um I'll hang out for questions at the end in fact I aim to have at least five minutes at the end of this session so feel free to uh ask questions or tag me on Twitter come up and chat with me later okay so uh just to kind of refer to the other two talks that were really excellent if you want to work with data whether as in science Finance any kind of fields uh in the last two days we've seen lots of talks on uh how you might go about working with data sets so in particular I want to kind of shout out to Tennessee and cla's talk on using data sets in hackathons and Python and also visualizing them as well and in particular CLA also did a shout out on the python us talk on the visualization talk which is also excellent check him out um and let's just again so since I work at csro we have to have a little bit of Science and the motivation for this talk is we want to make sure that we have healthy water that we can use so there is this thing called The Aquatic microorganisms they Thrive and die with seasonal temperatures when things gets warmer they multiply when things gets colder they tend to not be able to live as easily and these things are called cob bacterias at least a category of them or called commonly blue green algae they produce oxygen in water and they're the only sort of organism in category that can do so so if your water in like a natural lake or uh in a river is oxygen poor for whatever reason they're the reason why the fish can breathe so having a good number of them in your legs are very very important in our ecosystem so unfortunately when you have too many of them uh they can be toxic for depending on the species they actually will poison you uh some of them will just kind of make you have a stomach EG or throw up if you happen to drink them by accident so you know having to little is bad having too much is bad so we need to sort of like Monitor and moderate the number of uh cell cyano bacterias in our water and what we can do about that is uh using weather data we can hopefully find out what kind of uh numbers that might be in the river and try to keep them in the mount and I just realized with my slides the resolution is a bit different so I may have uh cut off the number of lines I can show so in short uh we can predict and trian or bacteria and keep our water safe and healthy to use so let's take a look at our weather data sets and these are commonly things you can find on the Bureau of medology or other WEA the report sites so in this talk we'll be using pendis for data wrangling uh you can install pendes with your favorite uh package manager like Anaconda miniconda pip and so on and um give me a moment because I'm going to quit this presentation because the resolution is too small so I'm going to actually show you you to uh using the dbit notebooks so this way you can actually read everything I'm writing here okay so uh to begin there are two functions that will be using uh there's pendas read uh under CSV which opens a CSV file for you they're common separated files pretty common you can see it for open government data sets and so on they open it as a data frame which in penda uh talk is a table so in a data frame you'll have a column uh you have a bunch of indexes and you usually have more than one rows of content in a data frame there's a really convenient function called head which will show you the first five roles in the table so uh the first thing is that we can read a C file so I was really hoping to show you this in the presentation but because you see how big it is it's not really going to work right so we'll go here right so here I'm just importing pendas as PD it's a common uh way to refer to pendas it's three List characters I suppose or four List characters and I'm going to read a file that's already in my folder called um camera under observations so let's have a look at this okay so I've opened a file it appeared to have read everything as one giant string it's a little bit strange um I can see there back slts here they tab characters so the file is not actually comma separated but tap separated uh there's time there's Wednesday we know that's eastern daylight savings time and there's I guess some kind of wind direction uh there's some numbers and following and so on so there's a bit of a trouble in just you know understanding the file luckily with pendis it's really versatile for uh keyword arguments so in the read under CSV function um there are a couple of keyword arguments that we'll be using the first one SE or stands for separator so you can say instead of you know commas I might be using taps or I might be using white spaces and so on next there are three things we can use to tell pendas how to pass time better so the first one is pass under date uh you can treat one or more columns as a date or time or daytime columns which can let pendas know that this is actually time instead of just strings and the next thing uh because we're in Australia the time formats starts usually date month year or year month day unlike the month day year confusing formats uh you can tell pendas that actually our uh data is Day first instead of month first saves you the little extra trouble that you have to pass things uh and the third one is really beautiful it's called infer under data time under formats uh pendas will uh try to guess the date format if you passing this and set it to true and the good thing about this is that once you say to try to infer it and if pend can get it right you'll generally see a large uh speed performance in passing larger data sets than just trying to pass everything one by one lastly you can tell pendas what data sets or values are not really valid uh by default you will treat something like n or a number is invalid but in our data sets uh there are other things which can also be invalid it's really hard to spot but there's a little Dash here so we can now pass in these things as uh values that we should ignore or treat as empty so uh let's read the file Independence Way first of all uh we will just add a separated character and we can run this thing so you can see that now we have a table that pendes has correctly passed to be uh multiple columns and there's an index and there's a bunch of data sets now because the dates are still in strings and so is time I'm going to tell pendet that actually my daytime consists of two columns date and time and I'm going to call it date time and I'm also going to say yes please uh you know it's dat month year because I got it from an Australian site I know the format and please try to infer it and lastly I also set the dash to be an empty value as well so if I run this it will take a little bit of time because I have a whole year data set in there but hopefully in a couple of seconds uh you will now see that I've passed the day time to be the correct format and it's shown me in an ISO formats um of this without the time zone um I will have a caveat in this talk I'm not dealing with time zones there was an excellent talk earlier as well to deal with time zones so I think we got all the corners covered there's wind direction wind speed and so on and you can see now it's displayed uh the wind speed instead of the dash to be another a number uh another note is that imp pendis when you see another a number in the table it's actually not the I pr floating point on the number it's just pendas where to say this value is empty okay let moving on so there's a couple things we have to do in terms of data cleaning and actually go back for a little bit you can see that this starting point is the 1st of January at midnight okay the next thing is the 1 of January at 11:30 p.m. or 23:30 which is kind of strange we're going kind know jumping 11 hours so which minute is this it's really confusing and then it's kind of going backwards in time which is even stranger and in fact if you were to play with this a little bit and I'm going to show you using the python sense syntax and I'm going to show the first perhaps 58 some columns which will take some time because I'm repassing it again uh it gets a little bit stranger so you can see I've started at midnight whichever midnight that is uh and then it's 11:30 goes back in time goes back in time and then you get to the beginning of the day of the 1 of January which is around here it's not going to the second the next day at midnight whichever midnight that is and then it goes backwards in time so the data sets don't always come you know in the right order it doesn't always come in the right format and plus there's ambiguity so this is where we go back to the beginning uh when we were passing the thing and then trying to find out what's going on here so if I were to coming this out again and then just keep the separator and then just pass it you can see that uh there is a Wednesday thing here and there's a Tuesday thing here because I've currently discarded the time zones uh pendas just treats time as whichever hours and time that's able to find and therefore it's now shown well it doesn't really know which day it is anymore uh well you can look up on calendar the first day of January in 2013 is in fact a Tuesday so this Wednesday at midnight by Common convention is actually the next day which is a little bit confusing but okay so I'm just going to uh keep that in mind and pass it back and that's going to run so it'll run in a little bit okay so we know the first rle is actually the second of January and then it's going back in time until the beginning of the day and then it's going to go to the second of January um at the end of the day and then it goes back in time so we have to kind of fix this just so we can use it a bit more uh saying so there's a couple of things like each day includes midnight another midnight um and then the order starts you know at the end of day luckily uh pendas offers some functions to help us so the next three functions are built into the data frame which is a table you open after reading the CSV the first thing is drop under duplicates you can tell pendas to get rid of duplicate roles in this case we've got the beginning or the end of day midnight and the uh end of the day which is the beginning which is really confusing but there's two midnights per one day so we'll get rid of one of them uh secondly we can tell pend to sort the values so just to tell sort of in ascending descending order and so on and lastly we will set the index instead of using uh numbers we can use the time as our index so I've got a code here um the previous variable I've set was called observations and I'm just going to say please drop the duplicates and keep the last one because I know the first one was in fact the next day so I don't really want to duplicate so you can see that we've got the uh last well I suppose the second last time stamp on the first day and then it's still going on then the next thing I'm going to do is to say um please sort the values for me and I'm going to copy paste this so I can show you what a sort of thing looks like so pendas by default sorts ascending uh in ascending order which means it's going to go from small to big so I've got the beginning um of January 1st which is correct and then it's going to go uh to the next time point and so on uh lastly because these numbers on the left hand side don't make sense anymore they're just numbers that came into my data reading I'm going to say please use uh the time as my index so I'm going to call a set index function and then just pass in the daytime uh which is the name of our column here and as before I'm going to put that here so I can visualize what it looks like okay so now we have a table which has the correct index hopefully if time is your uh data to identify which row it is we still have wi directions there's still subm values and so on okay so the next thing that we can do is that when you work with data sets especially in a scientific context you want to make sure you can uh read the numbers uh whether they're a string or something so that the machine can understand it better especially when you're modeling weather data uh telling the computer this is east and north is perhaps not as good as a number that it can model in a Direction so uh commonly by I suppose um consistency they've decided that North Wind which is a north to south wind is0 degrees and then it will go clockwise so in this case East Wind which is going from east to west is 90 degrees and so on so we need to transform these values that are like East Northeast and uh South Southwest and so on to be a number so we can use it in modeling later so pendas has a function that you can use to transform your values it doesn't matter if it's string to numbers or numbers to string or any other for uh kind of data type really you can use a function called series. apply now A Series in pendis is just a column in a data frame so I can get a particular column and I'm going to apply a transformation function to it so that I can change the East Northeast and so on to be a number that I can use later okay so um I don't know about you you can write your fancy functions to pass Northeast and intelligently to get the correct numbers out of it in my case I just kind of dumped it in Excel generated numbers out of it and then I put them in a python dictionary format because I know there's only 16 different directions in terms of how the uh directions for wind are recorded and um I can then pass this as a dict and then down here I'm just going to access the data frames column using the python normally you will access a dict uh that this gives you a series which is a column I'm going to use its own function called apply and I'm going to pass a transformation function to it here I've passed the doget function which is the uh building function to our dictionary so um if this looks a little bit confusing I can make it a little bit easier for you to read so this is equivalent to say I'm going to pass in a function which is called Lambda and the Lambda takes perhaps a argument called X and here I'm going to saying uh just give me the value that a dict uh has whatever X index is and by default if you don't find it just please um return none or a no value in Python so what this is doing um it's saying for each item on my column in the series pass it to this function so this x will be taking the first value uh to be e uh East Northeast and followed by another number and so on and so forth so what it's doing then is it will look up the data set here and East Northeast is 67.5 I suppose and if it doesn't exist it's just going to get a none back so uh this will run and oops this is just interesting let's see what happens here live demos so this happens a lot uh name ah forgot to run the previous column isn't that great let's try again so now I've transformed uh the column from whatever string that was to a number and I've preserved the another number uh back here okay so so the next thing that I want to talk about is that the data set may not always be regular and I'll show you what I mean here's a interesting SE I found in a data set it takes a little bit uh to find it you can plot it you can scroll in uh in your file and so on but you'll eventually find things like this um that's 33 minutes okay that's 1:00 that's 111 I don't know why 1:1 okay 130 okay so I've definitely got the half an hour time stamps here but then there's also an extra data set that I'm not really sure why it's there but it's there and it's just treated like any other column so in your data sets it's really important to go through and then trying to find out where what these things exist um generally I actually found this I just opened a file in a text editor scroll through a little bit and just found really weird time stamps so you have to look at it really carefully so um there is a function in uh pendas again in a data frame called as freak or as frequency you can say given this index Please interpret it to be a set fre frequency so that we can say it's always increasing by one or in our case it's time it's every 30 minutes so let's look at how we can set data frequency so my data frame currently is called this variable and I'm going to call the function as freak and I'm going to just passing 30 minutes you can say one day or one hour as well and let's just run that so here I've looked at a different slice in my data sets and I've purposely chosen this magic number because this number matches is the same day which is the 4th of February if you look back here that's the fourth of February at the beginning of the uh of the day and you can see now I've gotten rid of the 11 and 33 minutes and pendas will have gone through this entire table and what as frequently would do is if he found a DOA time Point that's not in a correct frequency we will discard it um if he finds things that's only really close and there's nothing there it will also discard it if it finds things that's in the correct frequency it will keep it so there's a cave there if you want to keep your data sets and you don't want to discard it there perhaps could be other ways to transform it but here I've chosen to discard the extra data sets that's not in the frequency that I'm using Okay so let's look at how to plot the data for a better picture um as before you can select multiple columns uh in your data frame so in this case I'm maybe interested in the wind speed and the wind gust which is the Sudden Rush the maximum recorded uh wind speed in that time frame I'm Al going to look at a temperature and how cold or how hot it feels like which should be correlated in some way and I'm going to get the first 500 uh indexes in this case and I'm just going to call the dotplot function so pendas by default piggybacks with map plot lib so it will just plot the data set in a fashion that uh looks good by itself by default okay so did you notice anything interesting here there is actually a gap uh just around here I can probably zoom in for you you can see there's a gap uh between those two data points and there's a gap there as well and if you were to uh look in the data set a bit more carefully you'll probably see more gaps and what this is that it could be because the sensor dropped off um or the recording data set came in different chunks and when someone put it together there was a time that nothing was recorded right so there's also these kind of gaps we have to be worried about now we can um actually Clan this up in many ways you can say if the data set doesn't exist um I'm going to choose to set a default value or I'm going to choose to ignore the time frame in my analysis or perhaps you might want to keep uh the features of the data set so we'll look at how we can interpolate it so that we can connect the broken joints to the next bit and you have perhaps a linear uh interpolation in this case but you can also choose to do uh quadratic or cubic in which you look at more than two data points and try to make a sensible line between uh the the samples okay so um there is a function luckily pendas is really really awesome called interpolate it works on the column which is the series and it will try to fill empty values uh based on index so what it does by default is a linear interpolation it will look at anything that's not a number or empty it will look at the neighboring values from before and after and because we've already told pendas that the index is in fact a time which has some kind of order it's able to work out whether this point is at you know how close is it is it to the next and before and work out the value exactly at the middle or along that transition line okay so uh here I'm just using a simple for Loop um in pendis you can do the do columns which is for data frame you can get a list of columns and I'm just going to iterate through so for each column I'm going to um again pass it in here to access it so so I'm getting the series for each series I'm going to call the interpolate function I'm going to say my index um is a Time series so please interpretate it as time there's other things like uh numbers and other things you can look at a documentation um and I'm also going to say in place equals true and pendas supplies many functions that support this keyword argument what it does is instead of assigning into a new variable you will do it on its own so I can save memory so let's run that so you can see before uh on the fourth of February there was two non number values and now there's a number that's magically filled in and because the before and after are the same numbers 67.5 it's just kind of filled the same number and also to be sure we can plot it again and you can see that the things in the middle is now a straight line and it's guaranteed every 30 minutes there is a value somewhere in there that you can use and the beauty of this is that when you uh do a simulation typically by uh increasing a Time step you can just say get the next row I know it's 30 minutes and I'm going to get the next value so everything is at a fixed time frequency and the value will be meaningful in some form with the caveat that we built into this data set and you can now use it to a simulation so yay the data set is not really to be used for modeling and you know you can use it confidently in a way that you hopefully clean it up so um let's just uh starting to wrap up so you two can keep the water healthy you can work with data sets that come with all kinds of weird things you can discard them as empty values you can fill them in in a meaningful way um hopefully and today I've just kind of gone through a series of uh the basic steps that hopefully everyone will encounter at some point if you have to work with a tular data set so reading a file how to sort your documents and data sets uh transforming columns from string to numbers or from any other logic uh you can regulate your frequency that can be time can be numbers uh can be anything that has an order really you can interpolate in filling Miss data and what I didn't show is that you can also fill all the way before the beginning of the index or after the end so it's able to kind of project as well and finally just to use the do plot function to go back to map plot lip and just kind of have a quick look of what's going on so I really encourage if you want to use pendas and if you uh haven't used it before to check out the documentation so you might find more gems in there because um I'm going to have to do a full disclosure about a month ago I haven't used pendas I did this project all in Python and then when I was about to give a talk I'm like hm should I give a talk on this messy code that I just wrote or oh there's pendas I should learn it and I found all these functions that did exactly what I was doing so I'm like oh this is awesome so I found all the functions that I was able to hopefully teach uh in ways you can just call in one function or two and that pretty much do the exactly the same thing right so again the slides will be available which is right here I'm going to go back to presentation mode and Skip right to the end which is here uh that's where the slide lives uh there's a bonus section on combining two data frames you're interested to look at it so what I'm doing there is I've got one uh weather data and I've got a cloud observation data they have different frequencies and different attributes I will show you how to sort of join them in there as well so if there's not enough uh questions at the end of this talk I can also go through that uh feel free to be friends on Twitter I'm pretty chatty um currently still having too many tweets otherwise feel free to come up and talk to me and yeah thank you very much that's the [Applause] talk so all right y thanks everybody um saier there's I think fantastic first Pon talk thank you um here is a mug from the conference as a token of their appreciation open the floor for questions right here hi thanks for the talk um be interpolation um so for I'm guessing for modeling purposes you might want to set a flag on those values that they're actually interpolated and not the real values from the sensor is do you have any experience with doing that in pandas yeah so actually when you interpolate the values in pandas uh it changes the internal uh representation from a series to a I think it's a interpolator or frequency or something series so uh internally penda knows that this column has been transformed to be a frequency if you're using interpolation which is what you were probably asking and just filling a value uh pendas doesn't know that this thing has been interpolated in a linear fashion where there were gaps so what you might have to do is then in your data store if you were to use uh commonly for scientific data sets you will have meta datas for your columns you might leave a note and say this has been uh interpolated where there were gaps and um you can also Supply another column and say these are the gaps that were null and these are the gaps that um sorry these are where they values so you can always add more connotations to your data set to make it more meaningful and be able to trace back questions I intended this to be a Q&A at the end as well because I know you have specific questions and I'm happy to insert them um so you showed the linear interpolation do you know of any other interpolation methods um that might work on say noisier data or um for larger gaps yes uh so there's two things I want to point you to in pendas it does interpolation only uh linearly unless you passing to be like cubic or something also scipi scientific Scientific Python has a lot more functions you can do and say give me a window smooth it out which is a way you might uh to combat noise so you can say uh smooth it so that you only take um every two hours asual time stamp but my data set has been every half an hour but in this case because I'm smoothing over perhaps four or more values I might be able to get a better sense of where the data is inside of this noisy Gap and if you were to interpolate in the larger window sometimes you have to decide if this is you know if it's a gap that's over one hour it might be acceptable to do a linear one if the Gap is one year you're going to have to look somewhere else right so at some point you have to decide what is the acceptable quality for my data set can I interpolate it and still make it meaningful and when you do you have to note it and if you can't then you we what we usually do is then we try to synthesize tool more data sets put them in the same frequency clean them up combine them and then you will have a meaningful uh data set that's larger that you can use we got time for one or two more anybody else so um I had to try and use pandas before for uh dealing with water quality data and I had to put up with things like um people typing o instead of zero for floating Point numbers um which pandas couldn't handle and ultimately had to do some ugly hacks to get around is pandas better at handling this now okay so let me show you something I've got this bonus section I'll show you a couple of things so I've got the uh cloud data set which I I'll quickly run through them and just read some stuff now you can see most of the values here are you know none or not a number but then you get clear out of uh one eight so you're like okay so is that a number what is clear mean you have to look up right I found out clear means there's no clouds so zero um but you still have to deal with people entering you know like manual uh annotations so what you can do in pendas now and this has been there before you can say show me the data types so it's able to work out hey everything inside VI current weather are 64-bit floating point at least that's how it's stor it internally but for cloud it's an objects so that's like Python's way to say this is anything could be anything so uh what you can do then is you can say give me a you know list of unique values that's in uh this column and now I can see there's you know 1 over8 and so on there's clear there's also a no value so this is where I can figure out okay someone has typed in something that's not what's intended to be there and I can write functions you know using apply that we've seen before to transform that to combat these values okay thank you I think all we got time for that last thing is actually really slick thank you um so one more round of applause for savier thanks and so that concludes the session I my prior tell me that there should be tea and biscuits out there so please go enjoy