WEBVTT 00:00.840 --> 00:06.720 Welcome to the solution for coding exercise aid and import pen us and our listings that are set 00:09.510 --> 00:15.330 and first before we should drop the columns a symbol and sector from the data frame. 00:15.330 --> 00:22.860 And we can do this with the drop method and then we pass a list with the column labeled symbol and sector 00:23.190 --> 00:25.650 to the columns parameter. 00:25.860 --> 00:30.110 And of course we also have to pass through to the in-place parameter. 00:30.510 --> 00:33.240 And actually there is a second option. 00:33.240 --> 00:35.200 So let's have a look again here. 00:35.490 --> 00:42.030 And instead of passing a symbol and sector to the columns parameter we can also pass the symbol and 00:42.030 --> 00:49.380 sector here the list to the label it's the parameter and then passing either one or columns to the access 00:49.380 --> 00:50.010 parameter. 00:50.010 --> 00:51.690 So this is identical. 00:52.110 --> 00:54.440 But this is here actually shorter. 00:54.600 --> 01:02.040 So let's drop the two columns and two we can see that we have successfully dropped the columns. 01:02.170 --> 01:05.470 Next we should drop all rows or listings on the Amex. 01:05.770 --> 01:13.150 And the the best way to do this is actually to filter the data frame and check for all elements in the 01:13.150 --> 01:17.100 Exchange column whether they are not equal to Amex. 01:17.140 --> 01:23.710 And by doing so we're creating a boolean series and we pass the boolean series to the local indexer 01:24.190 --> 01:26.760 and we actually overwrite here listings. 01:26.770 --> 01:30.690 So let's do this and let's check here 01:34.300 --> 01:41.970 so here we only have left six thousand five hundred forty one listings without the Amex listings. 01:42.160 --> 01:48.370 Then in the next exercise we shall add to the new column market capitalization and million U.S. dollar 01:48.400 --> 01:49.750 to our data frame. 01:49.990 --> 01:55.870 And we can simply do this by first of all selecting the market cap column and divide each and every 01:55.870 --> 01:58.250 element by one million. 01:58.360 --> 02:05.740 And then we create here the new column market cap and million U.S. dollar so let's run here the sell 02:06.610 --> 02:12.070 and let's inspect our data frame and on the right hand side we have for the new column market cap in 02:12.070 --> 02:13.350 the million U.S. dollar. 02:13.990 --> 02:19.230 And the question is here what is the market cap in U.S. dollar for the AIC holdings. 02:19.390 --> 02:25.550 And it is obviously fifty three point one four million U.S. dollar. 02:25.590 --> 02:31.260 Next we have to add to the new column shares with the total amount of shares outstanding for each listing. 02:31.500 --> 02:39.450 And actually we can calculate the market capitalization of a listing by multiplying shares with the 02:39.480 --> 02:47.180 price and to solve this equation for these shares we simply have to divide market cap by the last price. 02:47.400 --> 02:55.770 And we can also create a new column called shares and we divide the market cap column by the last price 02:55.770 --> 03:01.080 column by using here that this method so let's have a look here. 03:01.720 --> 03:04.710 And on the right hand side we have the new column shares. 03:04.720 --> 03:08.440 So these are the amount of shares outstanding for each and every listing 03:12.220 --> 03:17.260 then he questioned 80 we have to sort the list things that are framed by the chef's column from high 03:17.260 --> 03:18.220 to low. 03:18.320 --> 03:23.040 And the question is which listing has the largest number of shares outstanding. 03:23.080 --> 03:25.280 So this is nothing new. 03:25.310 --> 03:32.200 You see the Assad value is method and thought by the chef's column in a descending order 03:35.360 --> 03:42.110 and here we are starting with the listing with the highest number of shares and this delights the banking 03:42.110 --> 03:48.750 group with 17 billion shares outstanding here. 03:48.750 --> 03:55.160 The next question we have to assume that we have the listings for nicely Nasdaq and Amex in three separate 03:55.160 --> 04:03.230 data sets and first of all we have to import these data sets and we inspect the three separate data 04:03.230 --> 04:03.760 sets. 04:03.770 --> 04:06.820 So we have fun I see and also hear the info. 04:06.830 --> 04:14.180 So we have in total three thousand one hundred and six the listings on the New York Stock Exchange and 04:14.180 --> 04:15.680 let's have a look at Nasdaq 04:18.210 --> 04:23.640 and in total we fear three thousand four hundred thirty five listings. 04:23.640 --> 04:32.090 And last but not least also for Amex and we have three hundred and eleven listings. 04:32.260 --> 04:37.480 And now the next step which I concatenate the three data frames into one data frame and see if the new 04:37.480 --> 04:42.030 data frame and the variable all listings and actually each row are listing. 04:42.040 --> 04:46.430 So the third contain the information about the respective exchange. 04:46.570 --> 04:54.010 And actually we can vertically concatenate data frames with the direct pen us method con cap and we 04:54.010 --> 04:55.920 pass here in a list. 04:55.930 --> 05:02.620 The three separate data frames nice CNS dynamics and as we want to have the additional information about 05:02.620 --> 05:11.890 the respective exchange we pass here a list with a nice nest egg and Amex to the Keys parameter and 05:11.890 --> 05:18.630 by doing so we are creating an additional index level and the name of the additional index level as 05:18.660 --> 05:23.920 exchange and we save for the new data frame and all listings. 05:23.920 --> 05:28.770 So let's inspect here and PBF as the index level. 05:28.780 --> 05:32.410 The exchange where we have here and I see. 05:32.470 --> 05:34.300 Nasdaq and Amex. 05:34.390 --> 05:42.530 And finally to tidy up our data frame we reset the index and let's again have a look here. 05:42.540 --> 05:48.000 So here we have now again a range index and we have for the column exchange where we have the information 05:48.360 --> 05:57.060 whether the listing is on an icy Nasdaq or AmEx so let's finally have a look here at the info method. 05:57.810 --> 06:02.450 And again here we have all six thousand eight hundred fifty five listings. 06:02.460 --> 06:09.090 Now concatenated in one data frame and with this we are finishing with the exercise rate and I hope 06:09.090 --> 06:11.010 to see also on the next exercise by.