WEBVTT 00:00.800 --> 00:07.700 Welcome come to the solution for coding exercise 6 and we import pandas so far and we import. 00:07.780 --> 00:09.230 The listings state are set 00:13.120 --> 00:18.760 and actually by calling you the info method we can already see here that we have some missing values 00:18.760 --> 00:25.030 for example and the last price column on the market cap column and we can do this more explicit by checking 00:25.030 --> 00:30.880 for each and every element in our data frame whether the element is missing or not. 00:30.880 --> 00:35.820 And we can do this with the Sunday method and then finally if you want to know how many. 00:35.860 --> 00:42.310 And a values we have in each and every column then we change to the sum method and pass zero to the 00:42.310 --> 00:43.420 access parameter 00:46.880 --> 00:50.830 and the question is how many any are missing values are in the column. 00:50.840 --> 00:59.630 ADR t is so and we have six thousand seven hundred twelve missing values here and we can also get the 00:59.630 --> 01:00.230 opposite. 01:00.230 --> 01:08.270 So the amount of Nono values as we have done here with the info method but we can also do this with 01:08.360 --> 01:10.280 the not and a method. 01:10.280 --> 01:16.010 And again here we change the sum method and figure out how many non missing values we have for each 01:16.010 --> 01:16.900 and every column. 01:18.720 --> 01:21.560 So this looks actually familiar to us. 01:21.850 --> 01:28.170 And now in the next question we said Drop all rows with at least one missing value and we can do this 01:28.320 --> 01:37.740 with the drop and a method and we can also check the shape of the new data frame and we can see that 01:37.830 --> 01:40.670 we are only left with 100 rows. 01:40.740 --> 01:45.630 So dropping all rows are all listings where we have at least the one missing value might not be the 01:45.630 --> 01:47.270 best idea here. 01:47.730 --> 01:54.000 And we can also check the same for all columns so we drop all columns with at least the one missing 01:54.000 --> 01:59.570 value and the differences that we pass one to the access parameter. 01:59.640 --> 02:03.470 So the default value is a zero for dropping all the rows. 02:03.840 --> 02:08.500 And also yeah let's have a look at the new shape of the data frame. 02:08.730 --> 02:11.520 And here we are left with three columns. 02:11.550 --> 02:17.760 So simply dropping all columns of at least one missing values also not the best idea. 02:17.760 --> 02:25.980 And finally we decided to drop all columns with less than three thousand man model values and we can 02:25.980 --> 02:32.790 do this by passing three thousand to the thresh parameter and also here we have to pass through to the 02:32.790 --> 02:37.910 in-place perimeter to save our other change here and look. 02:38.760 --> 02:46.160 So instead of having nine columns now we have eight columns and we obviously dropped the column ADR 02:46.180 --> 02:49.930 tier so where we only have a few hundred known missing value. 02:49.930 --> 02:51.290 So yeah 140 02:54.160 --> 03:01.210 and then the next question we shall filter our data frame file rows of all listings with an A values 03:01.240 --> 03:08.230 in the last price column and we can do this with a local operator and we check for each and every element 03:08.230 --> 03:13.010 in the last price column whether this element is missing or not. 03:13.240 --> 03:17.440 And we save the new data frame and the variable no prize 03:20.560 --> 03:25.650 and we should check what our listings with any values in the last price column have in common. 03:25.850 --> 03:34.500 So let's inspect here the no prize data frame and also the info method here. 03:34.600 --> 03:39.510 So in total we have here one hundred seven listings sorrows where we have missing values. 03:39.520 --> 03:41.570 And the last price column. 03:41.800 --> 03:49.000 And we can also explicitly check the market cap column and count the values in the market cap column. 03:49.000 --> 03:54.620 And here we can see that we have one hundred seven times the market capitalization of zero. 03:55.270 --> 03:59.440 So the market cap is a zero of all these things with any values. 03:59.440 --> 04:06.100 And the last price column and therefore it might make sense to fill DNA values in the last price column 04:06.100 --> 04:08.950 of listings service zero. 04:08.980 --> 04:14.710 So instead of having missing values we have simply zero because the market cap is zero. 04:14.950 --> 04:21.220 And we can actually replace missing values by selecting the respective column last price. 04:21.220 --> 04:24.230 And then we use the fill and a method. 04:24.310 --> 04:28.740 And here we pass our desire to a new value it's zero. 04:28.810 --> 04:35.740 And also here we have to pass the true to the in-place parameter and let's have a look here and now 04:35.740 --> 04:43.490 obviously we have no missing values anymore in the column last price next we have to fill the listings 04:43.490 --> 04:51.560 for all rows are listings that have nominal values in the IPO year column and we can check for each 04:51.560 --> 04:57.860 and every element in the IPO year column whether the element is not missing and then we filter the listings 04:57.860 --> 05:05.850 data frame with the local operator and we save the resulting data frame and the variable known year. 05:05.850 --> 05:13.700 Now the question is how many listings with known IPO year are in the known year data frame and we can 05:13.700 --> 05:15.410 do the service of the same methods. 05:15.440 --> 05:19.500 So we have three thousand one hundred and five listings service. 05:19.540 --> 05:21.210 No no IPO year. 05:21.290 --> 05:27.050 And finally by checking our data source we figured out that the IPO year information is not available 05:27.050 --> 05:31.360 for stocks with an IPO prior to nineteen hundred seventy. 05:31.640 --> 05:39.730 And therefore we decide to fill all in a values in the IPO year column with nineteen hundred sixty nine. 05:39.770 --> 05:45.770 So at least we know that the IPO yes side the nineteen hundred sixty nine or earlier. 05:45.860 --> 05:48.730 And again this can be done with the filename method. 05:48.770 --> 05:55.220 And as new value we determine the value nineteen hundred sixty nine and pass through through the in-place 05:55.310 --> 05:59.860 parameter and we make a final inspection here 06:02.840 --> 06:08.780 and also here with the Inform method we can now see that we have no missing values anymore in the IPO 06:08.780 --> 06:16.260 year column but still we have some missing values and the column market cap and a sector or an industry. 06:16.310 --> 06:23.540 And finally we can see here in the IPO year column that we have uh float data types but that might make 06:23.540 --> 06:32.110 more sense to F into us and we can actually change the data type with the S type method and pass into 06:32.120 --> 06:41.170 for integer to the S type method and actually we overwrite the IPO year column and let's again have 06:41.170 --> 06:42.570 a look here. 06:42.580 --> 06:47.890 So now we have here for the IPO your column the day that hype type Integer. 06:47.920 --> 06:51.240 So let's again inspect our listings data frame. 06:51.310 --> 06:56.590 So this is actually the clean data frame where we have handled the missing values. 06:56.860 --> 06:59.030 And now we can also save and export. 06:59.080 --> 07:03.970 They cleaned the data frame and create a new CSP file listings. 07:04.030 --> 07:12.790 Cleaned out CSB and we can do this with the two CSP method and pass our new desire the file name listings 07:12.790 --> 07:14.040 cleaned out CSB. 07:14.830 --> 07:20.110 And we determined that we want to drop the Range Index so there's no valuable information in the arranged 07:20.150 --> 07:27.040 index and we pass false to the index parameter and then we can re import. 07:27.070 --> 07:34.790 Then you will see as we file of course with the read CSP method and we can re inspect it. 07:35.240 --> 07:42.520 So this is here now our clean data frame where we have only a few missing values for example in the 07:42.520 --> 07:46.420 market cap column on the sector or industry column. 07:46.420 --> 07:54.070 And now we can analyze our clean data frame by using the described method and we get some summary statistics 07:54.190 --> 07:55.530 for numerical columns. 07:55.530 --> 08:02.600 So for last price market cap and IPO year and separately we can also get to the mean or the standard 08:02.600 --> 08:11.390 deviation for the columns and we can do this with the ACT method and pass a list with the desired summary 08:11.390 --> 08:14.210 statistics mean and standard deviation. 08:15.230 --> 08:20.330 So we we have the mean and the standard deviation for the three numerical columns. 08:20.630 --> 08:27.650 And to finally we can also get some summary statistics on our non numerical columns bypassing 0 to include 08:27.650 --> 08:30.400 perimeter of the described method. 08:30.440 --> 08:40.370 So we have here the described method and pass 0 4 object actually A.B. get for the non numerical columns 08:40.400 --> 08:47.710 the symbol exchange names sector and industry the number of Nono values then the number of unique values 08:47.750 --> 08:50.130 and the most frequent value. 08:50.270 --> 08:54.660 And actually the absolute frequency of the most frequent value. 08:54.710 --> 09:01.170 So for example there are 1000 fifty nine companies in the finance sector. 09:01.310 --> 09:07.580 And the question is C. about us that the most frequent value in the industry column and this is apparently 09:07.580 --> 09:12.970 major pharmaceuticals was four hundred and seventy appearances. 09:13.080 --> 09:20.000 Then next we shall get to the maximum values for the columns and we can simply do this with the max 09:20.000 --> 09:21.220 method. 09:21.650 --> 09:24.570 And this not only works for America columns. 09:24.570 --> 09:29.540 So the maximum last prices are about four thousand three hundred dollars. 09:29.960 --> 09:35.500 But we can also see that alphabetically the last symbol is the set y x eye. 09:36.470 --> 09:41.450 And then actually we can also take the sum of all values and the market cap column. 09:41.460 --> 09:45.330 So this is going to be a quite large number here. 09:45.470 --> 09:52.330 And finally we can also create a correlation matrix with the correlations father in America columns. 09:52.370 --> 09:57.770 And the question is here our market cap and last price positively correlated. 09:57.770 --> 10:05.190 And let's have a look here so of course the last price column is perfectly positively correlated with 10:05.370 --> 10:06.260 itself. 10:06.510 --> 10:11.080 And s indeed a positive correlation between last price and market cap. 10:11.130 --> 10:15.390 So the higher the price of the stock the higher the market capitalization. 10:15.510 --> 10:17.520 And that was coding exercise 6. 10:17.520 --> 10:21.450 I hope you had fun and hope to see you also in the next exercise by.