WEBVTT 00:01.320 --> 00:06.630 Welcome to the first video about creating new data and new features for our data frame. 00:06.790 --> 00:13.360 And in this and the next video we will perform arithmetic operations like and subsection multiplication 00:13.360 --> 00:17.110 and division to combine existing data and to create new data. 00:18.250 --> 00:20.540 So let's start right now on first of all. 00:20.530 --> 00:25.430 We import pandas and also an as MP. 00:25.660 --> 00:29.860 Then of course we will also create new features for our Titanic data frame 00:33.740 --> 00:38.820 and then we can see here that we have some missing values and our edge column. 00:38.820 --> 00:43.970 And for this section here we will replace the missing values by the mean age. 00:44.780 --> 00:51.020 So we're using here the feel and a method and we replace all missing values in the age column by The 00:51.020 --> 00:51.740 mean age 00:58.020 --> 01:05.340 so we can see for example the peasants at index label 5 before we head to a missing value and now the 01:05.340 --> 01:08.100 age just twenty nine point seven. 01:08.130 --> 01:13.860 All right so first of all we want to create new data on new future US by combining existing columns 01:13.860 --> 01:20.700 of our data frame and we can do this with the arithmetic operator's additions abstraction multiplication 01:20.730 --> 01:28.480 and division and the first example is the addition of two columns and and our Titanic data frame we 01:28.480 --> 01:30.290 have for the columns as P.. 01:30.460 --> 01:36.090 This is the amount of siblings and spouses on board of the respective peasants are here. 01:36.430 --> 01:38.220 And the column part. 01:38.250 --> 01:44.320 So so the amount of parents and children of the respective passengers on board. 01:44.320 --> 01:47.950 So for example here had the peasants are at index say about 7. 01:48.190 --> 01:55.060 It's obviously here a baby and this peasant just obviously with his three siblings on board and also 01:55.330 --> 02:02.300 either the mother or the fathers on board and actually now we could say that we don't care if they are 02:02.300 --> 02:08.870 siblings spouses parents ought to run on board of a peasants are the most important and relevant information 02:08.870 --> 02:15.860 is how many relatives of a peasant are also on board and we can actually calculate the amount of relatives 02:15.890 --> 02:22.820 by adding these two columns here and we can simply do this by selecting the columns here for example 02:22.820 --> 02:30.110 with the attributes notation so we like to add these siblings column and the parts column and we add 02:30.140 --> 02:37.430 both columns and here we actually create an element y's operation so we add for both columns so the 02:37.430 --> 02:44.270 first element here one plus the row then the second element one plus zero and so on and obviously the 02:44.270 --> 02:48.180 peasants with index label 7 has the far latest on board. 02:48.260 --> 02:57.680 So here three plus one and that's actually a second alternative how to add columns and we can do this 02:57.680 --> 03:03.390 with the B method add so we are selecting here the first column it's Titanic. 03:03.590 --> 03:10.730 Sub S P and then we are calling the method at and within that brackets we are passing the second column 03:10.730 --> 03:12.490 Titanic top part. 03:13.610 --> 03:22.460 So we should get here actually the same results as above so we can see that the first passengers one 03:22.510 --> 03:24.780 relative the second peasants are 1 related. 03:24.810 --> 03:29.040 And so on. 03:29.080 --> 03:35.290 So the question is now which a turn of six should be use in many cases actually the add method is a 03:35.290 --> 03:39.990 bit faster here than adding two columns see it with a plus sign. 03:40.000 --> 03:47.710 So in general the bit and method has a bit better performance and there's a second advantage of the 03:47.710 --> 03:48.550 add method. 03:49.060 --> 03:55.180 And we will see this in a minute but for the time being we can create now a brand new column for our 03:55.180 --> 04:02.100 data frame so a number of relatives and to create this column we are here actually the add method. 04:02.530 --> 04:09.040 So let's do this and then let's check in again here our data frame and we can see here on the right 04:09.040 --> 04:17.660 hand side our new column number for ladies and this column is simply the sum of the columns the best 04:17.830 --> 04:18.530 part. 04:18.920 --> 04:19.390 All right. 04:19.390 --> 04:28.150 So now let's have another example and let's import sales data from the CSP fire sale that CSB and let's 04:28.150 --> 04:29.820 have a look here at our sales data. 04:30.790 --> 04:37.090 So we have four salesmen Stephen Mike and Ian Porter and this set up frame actually shows here the weekly 04:37.090 --> 04:39.780 sales so from Monday to Friday. 04:40.050 --> 04:47.200 And for Stephen here we have on Thursday a missing value now let's assume that we want to add to the 04:47.200 --> 04:55.110 sales of Monday and they say it's the first day and we can simply do this with uh the plus sign. 04:55.920 --> 04:56.930 So no problem. 04:56.970 --> 05:01.860 This and we can do this with the add method. 05:01.860 --> 05:10.460 So these are the two alternatives and as I said before one advantage of the S method is that at least 05:10.460 --> 05:14.700 for larger data frames the method is a bit faster than here. 05:14.720 --> 05:16.990 The operation with a plus sign. 05:17.240 --> 05:20.630 And also there's a second advantage of the method. 05:20.990 --> 05:26.810 So you can see here when we are adding the Monday column to the first column we got a problem here for 05:26.830 --> 05:30.800 Steven because he our first day we have a missing value. 05:30.980 --> 05:33.340 And if we use here the plus operator. 05:33.950 --> 05:40.610 And if you're adding 34 to a missing value we also get for the sum a missing value here. 05:40.670 --> 05:43.250 And also the method works in the same way. 05:43.670 --> 05:49.330 So in the default setting we also get a any end value for the sum for Steven. 05:49.340 --> 05:57.130 But let's have a look here inside the brackets then press shift tab and here we can see that the method 05:57.160 --> 06:05.620 provides a parameter called filter value and with the filter value parameter we can define that we want 06:05.620 --> 06:09.490 to replace at the end the end value on the first day by 0. 06:09.490 --> 06:10.510 So let's do this. 06:12.190 --> 06:20.600 So here we have the fill that you A.B. can save and we add Monday our first date it says whenever we 06:20.600 --> 06:28.010 have here some missing values in the columns we treat the missing values as being 0 for example. 06:28.520 --> 06:34.400 So let's run again here and we can see that we get fast even a 34. 06:34.700 --> 06:45.550 So we replaced here the missing value for Thursday by 0 and then pandas at C a 34 plus 0 guess 34. 06:45.570 --> 06:52.590 So this is the advantage of the add method that been calling here the add method we can already define 06:52.740 --> 06:56.310 which values we want to have four missing values. 06:56.310 --> 07:01.370 So also here we could after what's the replace here the any end value by 34. 07:01.710 --> 07:04.400 But this is of course the less comfortable. 07:04.980 --> 07:11.550 So if we have many and a values then we can simply say that we want to treat all and a values as being 07:11.550 --> 07:11.940 zero. 07:11.940 --> 07:14.020 For example. 07:14.180 --> 07:14.630 All right. 07:14.630 --> 07:17.890 So now let's create another column called percentage bonus. 07:18.290 --> 07:19.580 So let's do this right now. 07:19.580 --> 07:22.280 So we are having here a list with four elements. 07:22.280 --> 07:26.080 So it's the percentage bonus for all four salesmen. 07:26.090 --> 07:29.330 So let's run here and let's have a look. 07:29.330 --> 07:33.240 So here we are on the right hand side of the column percentage bonus. 07:33.620 --> 07:40.550 And let's assume that Steven gets for his say it's a twelve percent bonus then might get a 15 percent 07:40.550 --> 07:44.570 bonus and the 10 percent and poor 20 percent. 07:44.620 --> 07:50.840 And now let's assume that we want to calculate the dollar bonus so we can simply multiply the first 07:50.930 --> 07:54.020 column with the percentage bonus column. 07:54.020 --> 08:01.670 So for example for Mike we are calculating 15 percent on 87 and for ending 10 percent on aid and so 08:01.670 --> 08:05.690 on and also for multiplication there are two alternatives. 08:05.720 --> 08:09.890 So the first alternative would be to use here the multiplication sign. 08:09.920 --> 08:10.820 So let's do this. 08:12.620 --> 08:15.500 And here we get 15 percent on 87. 08:15.500 --> 08:17.960 So it's thirteen point five. 08:18.020 --> 08:19.790 Then we get 10 percent on 8. 08:19.850 --> 08:26.490 So point 8 and so on and again here for Steven we are getting a any and all missing value because they 08:26.490 --> 08:33.590 are multiplying a missing value with 12 percent so Pam that's a Python does actually not know what to 08:33.590 --> 08:34.250 do here. 08:34.610 --> 08:41.170 And again here we can also use a bit and method and in this case it's the multiplication method. 08:41.180 --> 08:49.410 Top Model and first of all we're selecting the SS they column then we're using the model method and 08:49.410 --> 08:52.360 we're actually passing here within the brackets. 08:52.380 --> 08:59.490 The percentage bonus column and we are defining that pandas for treat all the missing values as being 08:59.490 --> 09:00.150 zero. 09:00.540 --> 09:06.180 So if I first say we have zero and 0 times 20 percent should give us zero 09:09.070 --> 09:13.360 and we can see here we have a zero bonus for Stephen on first day. 09:13.520 --> 09:13.990 All right. 09:13.990 --> 09:19.660 Now we can also calculate the total bonus for the week and they are for first of all 09:23.000 --> 09:29.550 we are selecting the sales column So from Monday to Friday by selecting all columns except that the 09:29.550 --> 09:35.110 very last column where we have fear the percentage bonus and then for each salesman. 09:35.170 --> 09:41.300 So far Stephen Mike Andy and Paula we are calculating the total sales for the whole week by summing 09:41.300 --> 09:43.790 here each row and we can do this. 09:43.830 --> 09:48.380 That's the some method and set the excess equal to one. 09:48.380 --> 09:55.400 So let's do this. 09:56.100 --> 10:01.110 So the total weekly sales for Stephen Barr one hundred and nine for Mike to one to twenty seven and 10:01.110 --> 10:02.340 so on. 10:02.340 --> 10:09.720 And we can see here for Stephen that by default pandas methods like some are mean skip or drop and a 10:09.720 --> 10:10.200 value. 10:10.230 --> 10:12.560 So this is the default behavior. 10:12.570 --> 10:18.900 And this is actually the very good and powerful thing about pandas that by default pandas can handle 10:18.900 --> 10:20.590 missing values. 10:20.610 --> 10:22.410 So now we have fear for its salesmen. 10:22.470 --> 10:30.330 The total weekly says and now we can multiply the total weekly says with the percentage bonus and we 10:30.330 --> 10:33.750 can doing so here by changing the model method. 10:35.040 --> 10:38.690 And we are passing the column say its percentage bonus. 10:38.790 --> 10:44.100 And as we do not have any missing values here there's no need to find a full value. 10:44.120 --> 10:45.980 So let's just run the other side. 10:47.220 --> 10:50.760 And here we are getting the weekly bonus for all four salesmen. 10:50.820 --> 10:54.600 And we can also create a new column called bonus. 10:54.840 --> 10:58.470 And we're actually performing exactly the same calculation as here. 10:58.470 --> 11:06.970 So yeah this is just a copy of here above and let's do this and finally inspect again I would say it's 11:06.970 --> 11:08.110 data frame. 11:08.310 --> 11:14.860 And here we are on the very right side the weekly bonus for all four salesmen. 11:14.870 --> 11:20.420 So this is it for the time being and yeah we're created actually new columns by adding and multiplying 11:20.420 --> 11:21.740 existing columns. 11:21.740 --> 11:26.390 So there's nothing complicated here and we could actually do the same thing in Excel. 11:26.960 --> 11:32.990 But also the pen that we can do all these arithmetic operations quite easily with the one line of code 11:32.990 --> 11:35.330 here. 11:35.340 --> 11:41.000 All right we are finished now and in the next video we will perform some more arithmetic operations. 11:41.070 --> 11:46.800 So also Addison subsection multiplication and division and then we are using a scalar value. 11:46.800 --> 11:52.720 So for example we are adding to a whole column a scale of you like five or ten. 11:52.830 --> 11:56.060 So also this is nothing new and nothing complicated. 11:56.070 --> 11:58.620 And I hope to see your next video by.