Adding, Updating, Deleting Table Rows and Columns

Python 3: Automating Your Job Tasks Superhero Level: Automate Data Analysis Tasks with Python 3
14 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.26
List Price:  €129.17
You save:  €36.90
£78.58
List Price:  £110.02
You save:  £31.43
CA$136.53
List Price:  CA$191.15
You save:  CA$54.61
A$150.05
List Price:  A$210.08
You save:  A$60.02
S$134.83
List Price:  S$188.77
You save:  S$53.93
HK$780.51
List Price:  HK$1,092.75
You save:  HK$312.23
CHF 91.40
List Price:  CHF 127.97
You save:  CHF 36.56
NOK kr1,068.48
List Price:  NOK kr1,495.92
You save:  NOK kr427.43
DKK kr688.44
List Price:  DKK kr963.84
You save:  DKK kr275.40
NZ$163.35
List Price:  NZ$228.70
You save:  NZ$65.35
د.إ367.26
List Price:  د.إ514.18
You save:  د.إ146.92
৳11,721.12
List Price:  ৳16,410.04
You save:  ৳4,688.92
₹8,326.41
List Price:  ₹11,657.31
You save:  ₹3,330.90
RM469.20
List Price:  RM656.90
You save:  RM187.70
₦143,785.62
List Price:  ₦201,305.62
You save:  ₦57,520
₨27,847.71
List Price:  ₨38,987.91
You save:  ₨11,140.19
฿3,633.91
List Price:  ฿5,087.62
You save:  ฿1,453.71
₺3,217.97
List Price:  ₺4,505.29
You save:  ₺1,287.32
B$512.23
List Price:  B$717.15
You save:  B$204.91
R1,817.89
List Price:  R2,545.11
You save:  R727.22
Лв180.27
List Price:  Лв252.39
You save:  Лв72.11
₩136,618.14
List Price:  ₩191,270.87
You save:  ₩54,652.72
₪367.26
List Price:  ₪514.18
You save:  ₪146.92
₱5,803.01
List Price:  ₱8,124.45
You save:  ₱2,321.43
¥15,641.03
List Price:  ¥21,898.07
You save:  ¥6,257.04
MX$1,661.48
List Price:  MX$2,326.14
You save:  MX$664.66
QR364.73
List Price:  QR510.64
You save:  QR145.90
P1,348.30
List Price:  P1,887.68
You save:  P539.37
KSh13,198.68
List Price:  KSh18,478.68
You save:  KSh5,280
E£4,678.53
List Price:  E£6,550.13
You save:  E£1,871.60
ብር5,747.26
List Price:  ብር8,046.40
You save:  ብር2,299.13
Kz85,041.49
List Price:  Kz119,061.49
You save:  Kz34,020
CLP$88,840.11
List Price:  CLP$124,379.71
You save:  CLP$35,539.60
CN¥709.27
List Price:  CN¥993.01
You save:  CN¥283.74
RD$5,874.72
List Price:  RD$8,224.84
You save:  RD$2,350.12
DA13,454.48
List Price:  DA18,836.81
You save:  DA5,382.33
FJ$226.07
List Price:  FJ$316.51
You save:  FJ$90.44
Q776.68
List Price:  Q1,087.38
You save:  Q310.70
GY$20,942.51
List Price:  GY$29,320.35
You save:  GY$8,377.84
ISK kr13,848.61
List Price:  ISK kr19,388.61
You save:  ISK kr5,540
DH992.77
List Price:  DH1,389.92
You save:  DH397.15
L1,766.77
List Price:  L2,473.55
You save:  L706.77
ден5,684.23
List Price:  ден7,958.15
You save:  ден2,273.92
MOP$804.15
List Price:  MOP$1,125.85
You save:  MOP$321.69
N$1,813.62
List Price:  N$2,539.14
You save:  N$725.52
C$3,682.63
List Price:  C$5,155.83
You save:  C$1,473.20
रु13,330.40
List Price:  रु18,663.10
You save:  रु5,332.69
S/374.77
List Price:  S/524.70
You save:  S/149.92
K388.70
List Price:  K544.20
You save:  K155.49
SAR375.01
List Price:  SAR525.03
You save:  SAR150.02
ZK2,583.64
List Price:  ZK3,617.21
You save:  ZK1,033.56
L459.02
List Price:  L642.65
You save:  L183.62
Kč2,281.25
List Price:  Kč3,193.84
You save:  Kč912.59
Ft35,715.37
List Price:  Ft50,002.94
You save:  Ft14,287.57
SEK kr1,072.36
List Price:  SEK kr1,501.36
You save:  SEK kr428.99
ARS$88,916.93
List Price:  ARS$124,487.26
You save:  ARS$35,570.33
Bs691.33
List Price:  Bs967.89
You save:  Bs276.56
COP$382,975.61
List Price:  COP$536,181.17
You save:  COP$153,205.56
₡51,262.22
List Price:  ₡71,769.17
You save:  ₡20,506.94
L2,472.61
List Price:  L3,461.75
You save:  L989.14
₲751,156.78
List Price:  ₲1,051,649.55
You save:  ₲300,492.76
$U3,841.99
List Price:  $U5,378.94
You save:  $U1,536.95
zł393.60
List Price:  zł551.05
You save:  zł157.45
Already have an account? Log In

Transcript

Hi, and welcome back. In this lecture, you're going to learn how to add, update and delete table rows and columns. From a table from a data frame. I have created a new Jupiter notebook. I have imported the pandas module and loaded the same JSON file as we did earlier. And first let's start with adding a column to a data frame.

For this, the JSON in between square brackets and in between double quotes, you enter the name of the new column, so batch ID, let's say equals and now you should enter a list where each element will be a value for each of the rows in the table. So we are creating the new column and we are also populating the column for each employee in the table. let me paste this in. I have added some sample batch IDs. Also. Let's see the JSON once again, Shift Enter Enter.

And as you can see, we have a new column added at the end of the table with a value for each of the employees. Now what if you want to add a new row or multiple rows to a data frame? For this, you will use the append method. And I'm going to paste this in as well. So this is the syntax, we have the JSON equals the JSON dot append. So we are updating the table in place basically, and in between the parentheses of the append method.

The first argument will be a list of dictionaries, where each dictionary is a row in the table, and each key value pair in the dictionary is made up of the column name and the value associated to that column for that particular row. And in my case, I am adding three new rows to the table. That's why I have three dictionaries inside this list. The second argument is this ignore index equals true which by default, set to false. This basically ignores the default indexing system of the table in case you have other indexes set up for the table. Okay, let's do a shift, Enter.

And now let's see our table once again. And as you can see, we have these three new rows being added to the table. Okay, now let's see how to update rows and columns in the table, thus modifying the data frame in place. First, let's start with updating a column. So let's say that for the badge ID column, instead of these numbers right here, we want a combination between the first name of the employee plus the year, let's say 2019. Okay, in order to do that, the Jason the name of the column to update so badge ID equals and now let's combine the first name and the year.

First of all, DJ son of first name plus a string Let's say 2019. Okay, Shift Enter. Let's see the table. And as you can see the badge IDs have been modified to the first name of each employee plus the year. Okay, great. Now what if you want to update a certain column in your table and insert the same value for all the rows in the table?

Let's say that, for instance, we want all the first names of our employees to be set to jack. Of course, this is not the best example. But in certain use cases, this can be pretty useful. So let's try this. The Jason first name, the name of the column to update equals, and now I'm going to use a trick and I'm going to explain it right away. So the JSON that shape of index zero times the new value to be inserted.

Let's say jack, shift, enter. Let's see are they Now, and indeed, the first name of each of our employees has been updated to jack. Why did I use this DJ some shape, because I wanted to get the number of rows in the table that needs to be updated. And let's remember that DJ son that shape returns the number of rows and columns in the table. And if we use an index, for instance, the JSON dot shape of zero, which is the forest value inside the parentheses right here, this will return the number of rows. Of course, this is pretty useful when you have a pretty large data set with maybe hundreds or thousands of rows, and you want to make sure that all the rows get updated.

Next, let's see how to update multiple rows based on a condition. Let's say that for each employee in the IT department, we want the salary to be set to $90,000 In order to do that, we have the JSON dot lock. And in between the square brackets of lock, we enter the condition. So the Jason of department equals equals ID, comma. Now we enter the column to be updated salary, and finally equals, as I said, 90,000. And now let's also print out the table.

And as we can see, all the employees in the IT department now have a greater salary, which is kind of cool. Okay, what about being more granular and using multiple conditions? So let's say that for all the employees in the IT department, who have networking skills, we want the salary to be updated to $100,000. So that would be only this employee right here, the lucky one. Let's see how to do that. I'm going to copy this and updated in this new cell.

So the first condition remains the same. Let's include it inside parentheses. And now let's use the AND operator and enter the second condition, which is the J, son of skills, equals equals networking. And the salary, as I said, will be updated to 100,000. Let's also print out the table, Shift Enter. And as you can see, the salary for this employee has been updated.

Okay, pretty cool. Now, what if we want to update multiple fields on the same row? Well, it's pretty simple. Let me show you. Let's say that for the employee whose ID is nine, so this would be jack Drake right here. We want to update the salary to $80,000 and also the phone number to another value.

So let's see how to do this. We have the JSON dot lock. And in between its square brackets, we enter First, the conditions. So the JSON of ID equals equals nine, comma. And then we have a list as the second argument, where each element is a column that we want to update the value for. So first of all, salary, comma, and phone.

Now we have equals, and now we have yet another list containing the updated values. So for the salary, let's say 80, thousands, comma and for the phone number, let's say all sevens. And also let's print out the table to the screen. Shift Enter. And if we look at jack Drake right here, we can see the updated phone number and salary. Great.

Okay, now let's see how to do it. rows and columns from a table from a data frame. And let's start by deleting a row by its label. And first, let's, for example, set the JSON dot set index of badge ID in place equals true, in order to update this table, the indexing of this table, Shift Enter. Now let's see the JSON. Okay, and now let's delete a row by its label.

And let's see how to do that the JSON dot drop, and in between the parentheses of drop, we should enter the label let's say look 2019. So the first row in the table, look 2019 Shift Enter. And you can see that this employee this row is now gone from the table. Now if by any chance you have a row label with the same name, as a column in your table, Then you can explicitly specify that you want to delete the row with that label and other column with the same label by passing zero or index as an argument. So instead of the JSON drop of Luke 2019, I could have used an additional argument, either 02 specify that this is a row, or index in between quotes or double quotes, again to specify that this is a row. Of course now if we check the JSON, once again, we can see that look is still in the table.

That's because our change was not made in place. In order to make this change in place, all you have to do is enter in place equals true and then the row labeled as look 2019 will be permanently deleted from the table. Okay, now moving forward, what if we want to explicitly delete column by using its label. Well, that's pretty simple as well, the JSON dot drop. And let's say that we want to drop the phone column from the table. In this case, in between double quotes, we enter the name of the column to drop phone with a capital P, comma, and we can specify either one, or columns as the second argument to specify that this is indeed a column.

And of course, if you want to make this modification in place, then you should also add in place equals true, but in our case, I don't want that. So let me run this as it is, Shift Enter. And you can notice that the phone column is now gone from the table. Great. You can also delete rows and columns by indexes. And let me show you how to do that.

For instance, looking at our table, let's say that we want to delete the row with index Number four, that will be counting from 00123 and four, so we want to delete Emily 2019 from the table. In order to do that, the JSON dot drop, and in between the parentheses of drop, we just enter the JSON dot index, and in between square brackets, the index for Shift Enter. And now we can see that Emily is now gone from the table. Also, you can use a slice to delete multiple rows at the same time. For instance, let's say that we want to delete the first four rows in the table. So these four rows right here.

To do that, the JSON dot drop in between parentheses we have the JSON dot index, and in between square brackets, we have zero up to but not including index four. So that would be index 012, and three, the first four rows in the table. Let's see test this shift enter. And indeed, we have these four rows removed from the table. However, you can see that we have Emily 2019 back into this table because we haven't made the modification in place when we deleted Emily from the table up here. Similarly, let's see how to delete columns by indexes.

So let's say that we want to delete the department column, that would be the second column of the table, the one at index one. So let's see the JSON dot drop of the JSON dot columns. And let's specify the index that will be index number one, Shift Enter. Sorry, I forgot an argument here. So we should enter after the comma one to specify that this is a column, shift, enter again. And indeed, we have the department column, gone from the table.

And now as we did with room We can delete multiple columns at the same time by using a slice. So let's see the Jason at this moment, Shift Enter. And let's say that we want to delete the last three columns in the table, that would be columns at indexes. This is 0123456, and seven. So we should use the JSON dot drop. In between the parentheses we have the JSON dot columns, and all the slides starting at index five, colon all the way to the end of the table.

And now let's enter a comma and enter one because we are referring to columns, Shift Enter. Okay, I mistyped the method is columns not column. Now let's run it once again. And indeed, the last three columns in the table have been removed. Okay, so that's about it with all the operations and methods that the panda's module provides for dealing with data and data analysis. Of course, there are many, many others.

But for now, I think we've covered quite a lot. And in the next lecture, we are going to build an application using the concepts and skills that you've learned throughout this section. I'll see you in the next video.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.