WEBVTT 00:00.720 --> 00:06.430 Welcome to the solution for coding exercise five and we've already imported pen us and the listings 00:06.460 --> 00:13.990 data set and in question 39 we ask them to sort of the listings that are frame by the column market 00:13.990 --> 00:20.640 capitalization from high to low and we should actually save the new order and we can simply do this 00:20.640 --> 00:23.110 service of the sort value use method. 00:23.140 --> 00:30.240 Then we pass our desired column labor market cap to the BI perimeter and the we determined that we want 00:30.240 --> 00:36.790 the FAA descending order so from high to low end again we pass through to the in-place perimeter to 00:36.790 --> 00:37.480 a safe. 00:37.480 --> 00:42.520 The change is here and that's the running of the cell and let's have a look. 00:44.480 --> 00:49.070 And the question is here which company has the highest market capitalization. 00:49.070 --> 00:52.960 And it's obviously here Apple. 00:53.090 --> 00:56.000 Next we have to sort out what data frame by two columns. 00:56.150 --> 01:04.820 So by the IPO year in ascending order and by the market capitalization in a descending order and. 01:04.880 --> 01:12.520 We can simply do this by passing here a list with column labels to the bipolar meta and the we pass 01:12.530 --> 01:17.300 also year a list with boolean values to the ascending parameter. 01:17.360 --> 01:22.240 So for the IPO Yeah we want to f in ascending order so true here. 01:22.400 --> 01:25.620 And for the market cap a descending order. 01:25.640 --> 01:31.130 And finally we also pass on the true to the in-place parameter. 01:31.160 --> 01:32.120 Let's have a look here. 01:35.320 --> 01:37.470 So the companies are the listings. 01:37.480 --> 01:44.560 Start with the very early listings here in the nineteen hundred seventy two and four companies are all 01:44.560 --> 01:47.310 listings with the same IPO year. 01:47.410 --> 01:53.130 Then these companies are sorted by the market capitalization from high to low. 01:53.230 --> 01:58.360 And the question is here from all companies that are listed in nineteen hundred eighty three which one 01:58.360 --> 02:00.940 has the highest market capitalization. 02:00.940 --> 02:07.600 So we go down year to the year nineteen hundred eighty three and the very first company here has the 02:07.600 --> 02:15.120 highest market cap of the listings in nineteen hundred eighty three and it's that the Amgen company. 02:15.190 --> 02:19.910 Next we have to set up the column symbol s the new index of our data frame. 02:20.170 --> 02:22.990 And again we have to save the changes. 02:22.990 --> 02:29.890 So we used this set index method and pass the column labeled symbol and also true to the in-place parameter 02:33.050 --> 02:37.940 and then we have to sort of the listings data are by the new index from low to high. 02:38.390 --> 02:44.410 And of course we can do this with these sort index method and we pass through to the ascending parameter 02:44.570 --> 02:46.910 and true to the in-place parameter 02:50.540 --> 02:57.860 so here we are starting with the ticker symbol a then overlay and so on and let's go back to a range 02:57.890 --> 03:01.280 index by using here the reset index method 03:06.060 --> 03:11.230 here and exercise forty three we have to check the number of unique values in all the columns of the 03:11.230 --> 03:20.520 data frame and uh we can do this by using the any unique method on the complete data frame and here 03:20.530 --> 03:21.970 we have for all columns. 03:22.000 --> 03:28.330 The number of unique values and in the Exchange column we have a three unique values. 03:28.360 --> 03:37.150 So we have stack New York Stock Exchange and Amex then next we are asked to select the 10 listings with 03:37.210 --> 03:43.770 the highest market capitalizations and this can be done with the end largest method. 03:43.810 --> 03:50.880 And we pass 10 to the parameter and and also market cap to the columns per meter. 03:50.920 --> 03:51.940 So let's have a look here. 03:52.690 --> 03:59.590 And the question is about us so the company name of the third largest listing and it's here Amazon actually 04:00.750 --> 04:02.460 so the largest company is Apple. 04:02.460 --> 04:05.280 Then we have Microsoft and then Amazon 04:08.040 --> 04:12.820 he said select the 15 listings with the lowest prices. 04:13.020 --> 04:20.790 And uh we can do this year with the smallest method and pass 15 to the perimeter end and the column 04:20.790 --> 04:22.060 label last price. 04:22.080 --> 04:23.850 Two columns. 04:23.850 --> 04:27.990 And the question is C average company has the fourth lowest stock price. 04:27.990 --> 04:32.430 And it's here the Vila Real Estate Investment Trust. 04:32.690 --> 04:40.310 All right. 04:40.450 --> 04:46.780 So now let's go to filtering and first of all we have to check for all elements in the column exchange 04:46.810 --> 04:53.460 whether they are equal to Investec and we can do this by creating a boolean series and we save for the 04:53.470 --> 04:56.070 boolean serious and the variable mask one. 04:56.260 --> 05:02.470 So we select here the Exchange column and check for each and every element whether the element the sequel 05:02.470 --> 05:04.120 to a mistake or not. 05:04.240 --> 05:05.430 And be safe. 05:05.500 --> 05:14.570 The boolean serious and mask one so here we have truth and false and all we have to check for all elements 05:14.570 --> 05:18.650 in the column last price whether they are less than one. 05:18.860 --> 05:23.640 And we can do this here with the smaller than sine. 05:23.780 --> 05:28.640 So we select the last price column and check whether the elements are smaller than one. 05:28.640 --> 05:32.630 And we save the resulting boolean series and the rabbit mask to 05:35.650 --> 05:39.260 and here we have to filter the data frame for Nasdaq listings. 05:39.460 --> 05:47.140 So we pass our boolean Serious Mask one to the local operator and actually we create a copy and we save 05:47.140 --> 05:51.060 for the residing data frame and the rabble stack. 05:51.400 --> 05:59.470 And let's have a look here so here we have all listings that are actually listed on the Nasdaq stock 05:59.470 --> 06:03.220 exchange. 06:03.270 --> 06:08.110 And the question is here how many nest egg listings does the nest SEC data frame contain. 06:08.500 --> 06:15.820 And with the Inform method we can determine that we have a three thousand four hundred thirty five listings 06:16.320 --> 06:20.890 van in question 49 we have to filter the data frame for penny stocks. 06:20.890 --> 06:28.930 So we should use a mask too and we pass the mask to to the lock operator and actually also here we create 06:28.960 --> 06:32.680 an independent data frame and save the data frame and the variable Penny. 06:33.730 --> 06:44.010 And let's have a look so typically stocks with a price lower than one not quite penny stocks and let's 06:44.010 --> 06:45.990 also for give with the info method. 06:46.020 --> 06:53.190 So we have in total three hundred twenty seven penny stocks and these penny stocks are spread over all 06:53.340 --> 06:59.360 three exchanges and now comes the final question 50. 06:59.880 --> 07:06.630 So we have to filter the data frame for penny stocks that are listed on the Nasdaq and this is a good 07:06.630 --> 07:11.800 example for combining conditions so we have the conditions of mask 1 and mask 2. 07:11.940 --> 07:16.690 And we want to have all Rose our listings that fulfill both conditions. 07:16.770 --> 07:24.150 So Penny Stocks and Nasdaq listed and therefore we have to combine both conditions with an end sign 07:24.690 --> 07:27.600 and we pass them to the local operator. 07:27.960 --> 07:32.570 And we actually save for the resulting data frame and available Nasdaq Penny. 07:33.390 --> 07:37.110 And let's have a look here. 07:37.150 --> 07:45.430 For example we have uh American Electric Technologies which is listed on Nasdaq and has a price of four 07:45.430 --> 07:47.580 point a seven here. 07:47.800 --> 07:53.590 And the final question is how many listings does the Nasdaq Penny that frame contain. 07:53.590 --> 08:01.420 And again we you see the Inform method and we can see that there are two hundred fifty seven penny stocks 08:01.420 --> 08:02.650 on the Nasdaq 08:05.740 --> 08:11.620 so that whilst coding exercise 5 I hope you enjoyed it and looking forward to seeing you also in coding 08:11.620 --> 08:13.030 exercise 6 by.