WEBVTT 00:01.400 --> 00:07.330 In this video we learn how to fill a missing value sometimes series with backfill format fill an old 00:07.330 --> 00:14.930 slope with interpolation so and a missing values are two bigger problems and time series because typically 00:14.930 --> 00:21.420 we do not have 4 timestamps in our index corresponding values and our features and in particular when 00:21.440 --> 00:29.150 we read sample re in next match time series it's very often the case that we create missing values. 00:29.450 --> 00:36.470 So for example we do not have official stock trading prices on bank holidays or on weekends and therefore 00:36.470 --> 00:40.160 it's quite important to handle and fill missing values. 00:40.160 --> 00:43.870 So let's start with our close data frame. 00:43.970 --> 00:45.980 So this is still important. 00:46.190 --> 00:53.240 So we fear the daily closing prices for the six stocks and we have also here the additional information 00:53.240 --> 00:55.480 on the weekday and on the quarter. 00:55.850 --> 01:02.050 And let's let's let's look at the last five rows and we can see here on the left hand side in our day 01:02.050 --> 01:07.370 time index that we only have four rows are timestamps for business day. 01:07.380 --> 01:15.110 So for example here we have the thirty first of December then we have fear kind of a gap slow the next 01:15.110 --> 01:19.950 day is the fourth of January and this is because. 01:19.970 --> 01:23.110 So the first of January is a bank holiday. 01:23.150 --> 01:27.140 Then the second and the third Saturday and Sunday. 01:27.140 --> 01:32.660 So week end and as we imparted to you our stock data from Yahoo Finance. 01:32.720 --> 01:38.010 So by default we only have fewer timestamps for business days. 01:38.090 --> 01:44.840 And let's assume that here in our data frame we want to have all day so also the weekends and the bank 01:44.840 --> 01:55.380 holidays and therefore be creating here a day time index with the PD up date range method and the stats 01:55.380 --> 02:03.270 should be the thirty first of December to nine and the end date should be the very last day of our stock 02:03.270 --> 02:04.760 or close data frame. 02:04.790 --> 02:12.970 So it's here the sixth of February to nineteen and we want to effort daily frequencies and then we started 02:12.970 --> 02:16.570 the day time index and the variable all days. 02:16.790 --> 02:19.190 So let's do this and let's have a look here. 02:19.610 --> 02:28.030 So here we have a daytime index with all the days from the thirty first of December 2009 to the 6th 02:28.140 --> 02:30.810 of February 2019. 02:31.010 --> 02:38.180 And now in our close data frame and set of having a daytime index that does not contain bank holidays 02:38.270 --> 02:39.760 and weekends. 02:39.770 --> 02:47.510 We now want to f for the daytime index with all days and we can do this by re indexing the close data 02:47.510 --> 02:49.470 frame and became positive. 02:49.500 --> 02:56.870 Then the brackets our new daytime time index all days and then actually we override our data frame close. 02:56.870 --> 03:03.130 So let's do this and let's have a look at our new data frame with the new date time index. 03:04.220 --> 03:08.390 So in our new daytime and next year we have now the timestamps. 03:08.420 --> 03:15.450 January the 1st the second and third and consequently we FBA every man missing values. 03:15.470 --> 03:18.310 So again he January the 1st was a bank holiday. 03:18.310 --> 03:20.360 Then he had a Saturday and Sunday. 03:20.810 --> 03:23.850 And here we have a Saturday and Sunday and so on. 03:24.140 --> 03:32.600 And now our task is to actually handle and fill the missing values and for the columns day and quarter 03:32.990 --> 03:41.150 we can do this by actually reassigning here the weekday name attribute and the attribute quarter and 03:41.150 --> 03:42.800 to override the columns. 03:42.860 --> 03:53.990 So let's do this here and let's re inspect so here we have now also the big days for the new timestamps 03:53.990 --> 03:56.170 here and also the quarter. 03:56.300 --> 04:02.900 All right and now it's time to fill the missing values in the stock price data and that actually always 04:02.900 --> 04:04.740 depends on the specific case. 04:04.810 --> 04:07.030 What makes most sense. 04:07.070 --> 04:13.550 So first of all either you see the fill and a method on our data frame and let's have a look here inside 04:13.550 --> 04:14.880 the brackets. 04:14.960 --> 04:22.100 So we've already seen here that we can pass to the values parameter a specific scalar value. 04:22.130 --> 04:26.660 So for example we can say that for our missing values we want to have the value 0. 04:27.020 --> 04:30.080 And in this case this does not really make sense. 04:30.080 --> 04:33.610 And that comes actually at the pyramid parameter method to play. 04:33.620 --> 04:35.510 And by default it's set to None. 04:36.950 --> 04:44.630 So A.B. can define the method to use for filling holes in re index series and by passing f fairly we 04:44.630 --> 04:52.040 can propagate the last valid observation format to next valid or we can use backfill to use the next 04:52.040 --> 04:53.300 valid observation. 04:53.690 --> 04:57.540 So let's first of all pass here beefier fallback for 05:01.260 --> 05:05.730 and if we have a look here at our data frame this are the missing values. 05:05.880 --> 05:13.680 And with the backfill method we take the next value which is not a missing value and backfill the missing 05:13.680 --> 05:14.130 value. 05:14.160 --> 05:22.090 So for example here for our Apple stock we show Taffy and this is three days with the backfill method. 05:22.110 --> 05:28.050 The value of thirty point five seven and here we have three point five seven. 05:28.090 --> 05:34.390 So we backfilled the missing values and we can also use the forward for method 05:37.850 --> 05:41.090 and here we actually used the last valid value. 05:41.390 --> 05:49.230 So for example here the value at the thirty first of December and we forward fitted the missing values. 05:49.280 --> 05:56.340 So now we should have here the value of thirty point one zero and secondly here we have it. 05:56.340 --> 06:01.940 And as I said before it always depends on the case that which method makes most sense. 06:02.130 --> 06:08.640 And in the case of stock prices you should actually always use them the fallout for the method. 06:08.640 --> 06:12.040 So we use here the very last valid traded price. 06:12.120 --> 06:17.090 And this is actually the best indication of the price at the weekend. 06:17.100 --> 06:23.100 So in our stock example we use floorboard filler but there might be other cases there. 06:23.130 --> 06:26.910 Beckford fear is more appropriate. 06:26.910 --> 06:33.840 And once they are happy with our method then we can actually override our data frame and safety that 06:33.890 --> 06:38.460 changes by passing year two to the in-place power meter 06:44.480 --> 06:48.230 and now let's finally re inspect our data frame. 06:48.440 --> 06:52.840 So and here we have successfully replaced all missing values. 06:52.830 --> 06:53.480 All right. 06:54.380 --> 07:01.850 So as a summary in this case we actually used here the area index method on our close data frame and 07:01.850 --> 07:04.760 by doing so we created a missing values. 07:04.760 --> 07:12.680 And then we filled and handed the missing values with the fill and a method but we can also do this 07:12.680 --> 07:21.350 directly when re indexing other data frame so also the re index method provides here for the same tools 07:21.350 --> 07:23.340 to handle missing values. 07:23.480 --> 07:27.420 So also here we can see the pyramid method. 07:27.470 --> 07:30.000 And also here we can define that. 07:30.050 --> 07:36.230 The resulting missing values should be fed with the backfill or the forward for the method. 07:36.860 --> 07:44.270 So we can reenact them to fill the missing various and one step what we can do this in two steps. 07:44.300 --> 07:46.040 All right. 07:46.040 --> 07:48.470 So now let's have a look at another example. 07:48.500 --> 07:51.860 And therefore we import here our temperature data frame 07:54.330 --> 08:00.010 so here we have hourly temperatures for Los Angeles and New York. 08:00.180 --> 08:03.450 And let's assume that we want to up sample our data. 08:03.750 --> 08:12.090 So we want to change the frequency from our lead to 30 minutes and we can do this with the every sample 08:12.090 --> 08:13.170 method. 08:13.170 --> 08:19.490 And as new frequency we can pass 30 minutes and we want to take you that mean. 08:19.500 --> 08:25.910 So let's do this and by doing so we are creating your missing values. 08:26.030 --> 08:30.100 So we're creating more timestamps than we actually have here. 08:30.100 --> 08:38.950 So here we have data for our the uh timestamps but for the 30 minute timestamps we do not have any value. 08:38.980 --> 08:40.970 So here we have missing values. 08:41.140 --> 08:47.500 And also here in this example we could decide that we want to use the backfill or the forward for the 08:47.500 --> 08:48.500 method. 08:48.520 --> 08:55.960 However in this case this continuous temperature or better data it could also make sense to interpolate 08:55.960 --> 08:57.400 here the values. 08:57.520 --> 09:04.810 So for all missing values we would see the interpolated value of the values that we have before and 09:04.810 --> 09:06.490 after some of the missing value. 09:06.850 --> 09:15.190 So here for example for 30 minutes after midnight if you use your interpolated values then I might expect 09:15.190 --> 09:24.130 to get to the mean of eleven point seven and ten point seven which is actually eleven point two. 09:24.820 --> 09:29.070 And we can quite simply do this with the method interpolate. 09:29.110 --> 09:37.930 So let's do this here and we can see that we successfully have a third on missing values are the interpolated 09:37.930 --> 09:38.920 values. 09:39.250 --> 09:44.490 And actually the interpolate method provides quite a lot customization. 09:44.490 --> 09:51.400 So here we can flag somebody find the method and by default it's a linear interpolation but we can also 09:51.400 --> 09:53.030 change this year. 09:53.080 --> 09:59.050 And finally if you want to save our changes we also have to set the in-place perimeter to true. 09:59.890 --> 10:00.550 All right. 10:00.940 --> 10:06.610 So with this we are finished now with this video and I hope to see you also in the next one by.