WEBVTT 00:00.830 --> 00:06.890 When it comes to the solution for coding exercise nine group by operations and we have imported panels 00:06.920 --> 00:09.640 and also the listings clean the CSP fire 00:12.340 --> 00:18.060 and first of all we have to calculate the mean or the average stock price for each exchange. 00:18.070 --> 00:26.230 So for example the mean price for all this things on Nicea on Nasdaq and Amex and we can simply do this 00:26.290 --> 00:28.660 by using the group by method. 00:28.660 --> 00:34.990 So we group our listings are framed by the Exchange column and by doing so we are creating a group I 00:34.990 --> 00:42.250 object to consisting of three data frames and for each data frame we are only interested in the last 00:42.250 --> 00:43.580 prize column. 00:43.810 --> 00:47.650 And actually we want to know the mean or the average price. 00:47.650 --> 00:52.140 So let's have a look here and TBA for three exchanges. 00:52.150 --> 00:56.840 The average prices low on AmEx that's twenty four point twenty four. 00:57.040 --> 01:02.700 And actually the highest average prices are on the New York Stock Exchange with forty three point 0 01:02.710 --> 01:03.610 4. 01:03.810 --> 01:04.960 Next which I get. 01:04.990 --> 01:06.600 For each and every sector. 01:06.610 --> 01:13.570 The highest are the maximum market capitalization that can be found on that sector and therefore we 01:13.570 --> 01:19.810 group the listings data framed by the sector column and then we actually get to the maximum value in 01:19.810 --> 01:22.730 the market cap column and. 01:22.780 --> 01:28.360 We divide these values by 1 million to actually grasp the large numbers. 01:28.360 --> 01:34.330 So let's have a look here and here we have on the left hand side to the sectors and on the right hand 01:34.330 --> 01:40.300 side here the market capitalization of the largest the company and that sector. 01:40.390 --> 01:45.280 And the question is here what is the market cap of the largest company in the sector consumer durables 01:46.000 --> 01:53.920 and it is here obviously forty one point eighty six billion U.S. dollar here we have to count the amount 01:53.920 --> 02:02.770 of listings per IPO year and we can do this by group with the listing Stella frame by the IPO year and 02:02.950 --> 02:07.310 then we count the number of financial values and the symbol column. 02:07.390 --> 02:11.080 So we selected the symbol column because the symbol column. 02:11.080 --> 02:12.760 There are no missing values 02:15.870 --> 02:16.860 and let's have a look here. 02:16.860 --> 02:24.000 So this is no surprise that we have three thousand seven hundred forty seven IPOs in the year nineteen 02:24.000 --> 02:26.760 hundred sixty nine or earlier. 02:26.760 --> 02:35.610 So and one of the previous exercises that we figured out that there is no IPO year information for IPO 02:35.610 --> 02:43.260 Oh yes before nineteen hundred seventy and therefore we replace missing values by nineteen hundred sixty 02:43.260 --> 02:43.710 nine. 02:43.740 --> 02:49.980 So this is an approximation and we can also create airline plot for this excluding nineteen hundred 02:49.980 --> 02:56.340 sixty nine and we can do this by starting from the second element the next position 1 02:59.740 --> 03:07.290 so here we have the same graphically and here and exercise say the seven we have to split the data frame 03:07.320 --> 03:08.940 by two keys. 03:08.940 --> 03:16.690 So by the IPO year and by the sector and we should actually count the number of listings per group. 03:16.690 --> 03:22.680 And first of all we pass a list to this IPO year and sector to the group by method. 03:22.980 --> 03:29.520 And then again we count the number of listings by counting the number of normal values in the symbol 03:29.520 --> 03:30.320 column. 03:30.330 --> 03:36.680 And we actually save the resulting data frame and a viable matrix so let's have a look. 03:37.580 --> 03:43.880 And here we are on the left hand side a mighty index with on the outer edge next level the IPO year. 03:44.030 --> 03:48.100 And on the inner index level the sector A.B. F.. 03:48.140 --> 03:52.040 The number of listings in that particular group. 03:52.040 --> 04:00.010 So for example there was a one IPO in the technology sector in the year nineteen hundred seventy two. 04:00.200 --> 04:07.010 And as said here we have a mighty index and uh we can change this and move the in n next level here 04:07.040 --> 04:09.000 and to create uh the columns. 04:09.040 --> 04:12.500 Basic industries like capital goods and so on. 04:12.500 --> 04:19.520 And we can do this with the unstuck method and by doing so we will create the missing values because 04:19.530 --> 04:21.110 so far the combination. 04:21.470 --> 04:23.750 Nineteen hundred seventy two. 04:23.750 --> 04:26.390 And for example the public utilities. 04:26.570 --> 04:33.680 There's no IPO and therefore we would get a missing value but we can also fill the missing values and 04:33.860 --> 04:37.030 the appropriate values actually zero. 04:37.100 --> 04:39.390 So let's all right matrix here. 04:40.410 --> 04:46.730 And here we have our own stacked data frame both to the IPO yes and the index and the SEC to us s uh 04:46.830 --> 04:53.660 column labels and then we can also select all rows are listing service IPO year from nineteen hundred 04:53.660 --> 05:00.560 ninety two today and we can simply do this with the local operator and we create a new data frame matrix 05:00.560 --> 05:00.920 1 05:03.590 --> 05:09.120 so here we have the IPO yes starting from nineteen hundred and ninety. 05:09.130 --> 05:16.120 And if you recall the exercise on seaborne plots then this matrix one year might be a pretty familiar 05:16.120 --> 05:16.960 to you. 05:17.080 --> 05:18.830 And uh this is true. 05:19.240 --> 05:26.110 So in the seaborne exercises that we imported your matrix one and we created a seaborne heat map and 05:26.170 --> 05:28.070 we can redo this here. 05:28.120 --> 05:35.670 So we import seaborne assets and s and then we pass the matrix one data frame to S.A. that heat map. 05:36.010 --> 05:37.630 So let's have a look. 05:37.630 --> 05:41.700 So this is actually nothing new a.. 05:41.750 --> 05:51.350 Actually the hottest combination stuff IPO year and sector being in yellow color and most recently the 05:51.350 --> 05:56.750 hottest sectors were finance healthcare consumer services and technology. 05:56.840 --> 06:04.400 And with this we are finishing with exercise 9 and I hope to see also in the next exercises of power 06:04.430 --> 06:04.880 to.