Python Pandas Tutorial (Part 5): Updating Rows and Columns - Modifying Data Within DataFrames

hey there how's it going everybody in this video we're going to be learning how to alter existing rows and columns in our data frames so in the last video we learned how to filter out specific information and we can use those techniques here to also modify our data so we'll learn how to update the data for our rows and our columns and in the next video we'll also learn how to add and remove rows and columns from our data frames now I'd like to mention that we do have a sponsor for this series of videos and that is brilliant org so I really want to thank brilliant for sponsoring this series and it would be great if you all can check them out using the link in the description section below and support the sponsors and I'll talk more about their services and just a bit so with that said let's go ahead and get started ok so let's look at how to update data within our rows and columns the last couple of videos we've already seen how we can filter specific data but now let's take a look at those same concepts and use them to make changes to our data so let's look at updating columns first and then we'll update some rows so I currently have my snippets file open here that we've seen in previous videos so that we can see what this looks like on a smaller data set before seeing how to do this on our larger data set with that stackoverflow Survey data now before we modify the columns let's take a look at these first using the columns attribute that we've seen in previous videos so let me add a new cell here down at the bottom we can look at the columns just by saying DF. columns and we can see that we just have three columns here so our column names are first last and email so let's say that we wanted to update the columns to be a little more specific so let's say that I wanted this to be first name instead of first so there's a couple of ways that we can do this so first if we want to rename all of the

columns then we can simply do an assignment using the columns attribute that I just used so in order to do this I could just use an assignment here and just pass in a list so I could of all the different columns that I want these to be so I'll call this first name last name and I'll just keep email the same so if I run this let's now look at these columns again and now we can see that those column names have changed and if I actually look at the data frame here then they show up changed in the data frame as well now I almost never use this because this is used for when you're passing in different names for all of your columns I usually only need to change the names of a few different columns now one thing that is a lot more common is the need to change something specific about each column in our data frame so for example maybe your columns are all uppercase and you want them to be lowercase or vice versa or maybe your columns your column names have spaces and you want to replace the spaces in the column names with an underscore in this case we can use a list comprehension so for example let's say that I wanted to uppercase all of the column names here so in order to do this I could use a list comprehension and I could just say DF columns is equal to and now we'll just say like X dot upper for X in DF columns so if I print out that data frame after making that change then we can see that now all of our column names have been translated to uppercase now another thing that you might want to do is remove spaces and replace them with underscores especially if you like using the dot notation to access a column name that doesn't work if there are you know spaces in the column name because that's just not correct syntax so if you wanted to replace spaces with underscores instead then what you could do is just do something similar here and say DF columns is equal to DF dot columns dot STR we're using the STR method on this columns series here and then we can use the replace method from that string class so I can just pass in a space and say that we want to replace all those spaces with underscores now this isn't going to make any changes in our specific data frame because all of ours already have underscores if I reverse this and I did an underscore and then a space then we can see that now we replaced all those underscores with spaces that's probably not what you want though I would rather have underscores instead of spaces so if we do it this way then that's how you replace all of those but let me go ahead and set these columns back to the way that they were before I like lowercase so I'm going to change those back to lowercase there and now everything that we've seen so far applies to every one of our columns but what if we only wanted to change some columns well in this case we can use the rename method and just pass in a dictionary of the columns that we want to change so if I want to set the first name and last name back to what they were before then I could say D F dot rename and now we can just pass in the columns and we're going to pass in a dictionary of what we want here so the key is going to be the old value so I'll set first name back to first so the value is going to be the new value for that column name so we're going to map first name to first and then I will map last name here to last so I just have to put that in as a value there and now if I run this now then it might look like it worked but if I look at my data frame then those changes actually didn't go through this is another one of those methods where if we actually want that place to or if we want that change to take place then we have to say in place is equal to true because it'll just let us kind of see what it would look like if it worked but it's not actually going to change it so saying in place equals to true we'll make that change go through so now if I rerun this again then we can see that those column names are set back to the way that they were okay so that's a quick look at updating our columns now let's take a look at updating the data in our rows and we'll spend the majority of video learning how to update data in our rows since there's so much more that we can do here so first let's look at how to update a single value now in the previous video we saw how we can look up values using loke and I'll oak and we'll use that as our starting point for setting values as well so let's grab the row for John Doe right here it's row number two let's grab that and change his last name to Smith so in order to grab that row we can simply grab that row with the index label too so we can say D F dot Lok of 2 if I run that then we can see that we get that row of first name John last name doe and if you wanted to use a conditional like we saw in the last video to grab that row then that's definitely possible - you can do that as well now we could have said you know give me rows where the first name is equal to John and the last dou if we wanted to do that as a conditional so now in order to update this information there are a couple ways that we can do this we can just pass in all of the new values for this row by passing in a list so I could just say that this row I want to set this equal to and then pass in all the new values so I'll say that I want to keep John the same I want his last name to be equal to Smith and then let's also change his email as well so John Smith at email com so if I run this and then we look at our data frame then we can see that now that last row has a new last name and a new email address now what if we had a lot of columns but we only wanted to change a couple of values so imagine you know with our survey data we have 85 different columns so it would be a pain if we wanted to change a single row and we had to pass in it or a single column this huge list of 85 different values you know that would really suck to need to pass in that entire list just to change those couple of values so in this case we can just specify the exact columns

using lok and again we saw this before when learning how to use the lok to filter data so if I wanted to just change the last name and email then first let's just grab those specific values so I can say D F dot Lok and then I can pass in a 2 for the rows then if you remember that we can also pass in a list as the second value here for the columns that we want so I'll say that I want the last and I want the email so if I run this then we can see that we just get the last name and email and now we can change this as well you using the same method that we used here above but we don't need to pass in this the values that are staying the same so I don't need to pass in that John value anymore since we're not changing that so let's change this back to dough just to make sure that this worked so John Doe at email com so now if I run this that should have made the assignment and now if I look at our data frame then we can see that those were set back so now we have John Doe and John Doe at email comm ok and finally here now let's just look at how to change a single value you might be able to guess how it be done but what we can do is just pass in a single row in a single column and just change that value we don't need to pass in a list so just to change that one value I will copy this line here but now I don't want a list of these columns let's just change the last name so this won't be a list anymore now I'm just saying that I want Row two the column of last and we'll just set that equal to Smith whoops let me put that in the string there so if I run that then we look at our data frame then we can see that it only changed that one value now pandas does have another indexer called @ and this is specifically meant for changing or looking up a single value now honestly I just used dot lok from these most of the time but the option is there if you want it I'm assuming it's there for performance reasons so if I wanted to change this back since this is just a single value then instead of using dot Lok then I could all you also use dot @ so I'll do this exact same assignment here and I'll change this back to doe but instead of dot Lok I'm gonna say dot @ and let's just set that back and then if I look at the data frame then we can see that worked as well and I'll be honest here I'll have to look at the documentation I'm not really sure why we would use dot @ instead of dot lok when we only need to get or set a single value maybe it's for performance reasons but I personally find myself using lok and i lok even for single values i actually did look it up in the pandas documentation but all it says there is that it's there and that it's similar to dot Lok it doesn't provide any actual justification as to why we'd use it but I wanted you know that it's available since it's awfully obviously there for a reason and hasn't been deprecated yet okay so now let me show you one mistake that is very common and that is when people try to change a value without using one of these indexers without using dot lok or dot @ so let me show you what this error or this warning would look like so let's say that we have a large data frame where we want to find this John Doe person and changed their last name to Smith so one way that we could do this is to use a filter to grab that specific row so I will come down here to the bottom and I'll just create a filter here and I will say that the email column of the data frame equals John Doe at email com that is the filter that we're looking for so if I apply this filter to my data frame just you passing it in directly to the brackets like this then we saw in the last video whoops and this is filt not filter since filter is a built-in Python keyword so if I pass in that filt variable there directly into the brackets again we saw this in the last video if I run this then we can see that works for looking up these values so that actually returned a data frame there that just has a single row so we could grab that last name column just by accessing that last name from that data frame that was returned so if I run this then that works as well we can see that we got that doe value and finally you might think that in order to change this last name to Smith that we could just come up here and say okay we got that value now I want to set that equal to Smith so if I run this then you can see that this big warning pops up here and if we look at this warning it says that we get this setting with copy warning okay so we got warning but did it actually make that change to our data frame so let's look at our data frame here okay so we tried to change that last name to Smith I've know we know we've been going back and forth between these last names here but it was doe and then we tried to change it to Smith and that did not work so it didn't make that change when we did the assignment this way now the reason that it didn't work here is a little complicated essentially it's because the way that we're doing it here requires multiple operations in the background which can determine whether pandas returns a view or a copy of our data frame so when our value isn't getting set it's because it's getting set on a temporary object that's just getting tossed out immediately after so pandas does a lot better job of explaining this a specific warning and they have a little link here down to the documentation directly within the warning itself so if you want to learn more about this then you can just click on that warning or go to this link and it explains it in a lot more detail but the moral of the story here is that when you're setting values just use dot lok or the dot at indexers that we've already seen and you shouldn't have any problems so we could rerun this same operation up here at the top and actually let me just copy this so that if anybody I will put this notebook out here on after I'm done with this and if people want to see this exact error then I will leave that cell there so we could rerun this exact same operation but instead of using these brackets directly here I can just say D F dot Lok and then I want to apply those filters to my rows and then I want to grab the last name column as well so if I run it this way instead and then we look at our data frame then we can see that tit changed did go through so we were able to apply that filter and then set that last name equal to Smith in that data frame so if you ever get these warnings like this that just pop-up then definitely don't ignore them because you know in this case it didn't even actually set the value that we thought that we were setting so you really need to be careful with stuff like that okay so that's how we would update a single row of data but how would we update multiple rows of data well there's a couple of different ways that we could do this so for our first example let's just assume that we want to change all of the email addresses so that they are lowercase so this might be something that you want to do to make the email addresses easier to search so to do this we could just assign that column to the lowercase value of itself so first let me grab those lowercase values and to do this we could just say DF and then we could access that email column and then that's going to return a series and then we can just use this dot STR class on the series and use the lower method on that string class so if I run this then we can see what this returns is a lowercase version of all these emails if I look up here in the original data frame then these have mixed casing in there and now these are all lowercase now this just returns the lowercase values of these emails it didn't actually make that change now in order to make that change we can simply assign that column to this value so what I could do here is I could just say DF email is equal to DF e mail dot string dot lowercase so if I run this and then we look at our data frame now those changes actually did go through and all of our emails are now lowercase so that's one way that we can change multiple rows at once but maybe we want to do something a little more advanced so there are several ways that we can do this and we'll go over all four popular methods in order to do this and a lot of people get these four methods confused so let's go over each one individually and try to explain them in detail and there's definitely a good reason why people get these confused because they're very similar in what they do so the four methods that I'm going to be talking about are apply and map and apply map and replace whoops let me spell that correctly place so first let's take a look at apply so apply is used for calling a function on our values and apply can work on either a data frame or a series object and the behavior might be a little different than you expect for each of those different objects so first let's look at how apply works for a series so when we use this on a series it can apply a function to every value in our series so for example let's say that I want to see the length of all of our email addresses maybe we have a website and we want to make sure that none of the email addresses are too long or something like that so I can apply the L en the length function to each value in our series by doing something like this I can say DF and access that email column and then I can say dot apply Lin so we'll apply that Lin function and when I run that what we can see what it's doing here is it saying okay the Lin of the first row email addresses was 23 so I've never actually counted up the characters in my email address but it's telling me it's 23 and then this one says it's 17 and then it says this one is 17 so that's a quick little way that we can use apply to grab some information about our data but we can also use this to update values as well so in this example I'm just going to create a simple function that returns the uppercase version of our email but the function can be as complicated as you want it to be so let me write a simple function here that does what I want it to do so I'm gonna say let's say I'll call this function update email and this will take in an email value here and then I'll just return that email in uppercase so email dot upper so let me run this now again this is a super basic example here if I really wanted to uppercase the email addresses then I would just do the same thing that we did before when we made them lowercase but I just am using this as an example here so when we apply functions it's usually to do some more advanced updates than what we're doing here but let's apply this function to our email column so to do this we could say DF and access that email column dot apply and then we can pass in that update email function that we just wrote now we don't want to execute this function so you don't want to put parentheses here you just want to pass in the function without parentheses so that we're passing in the function itself and not an executed version of itself okay so let me run this cell and we can see that now we're getting back a series here of our email addresses in uppercase now if this doesn't actually change our values or change our rows like we saw before so to do this we can just assign that to our column so I can just take what we wrote here and then I can just set that series equal to that series with that applied function so if I run that and then we look at our data frame then we can see that now our email addresses are all uppercase now again this was a pretty simple function here all we did was return this email dot upper now for simple function so for simple functions like this you might see people use lambda functions as well now if you're not familiar with lambda functions basically they are anonymous functions with a specific name or without a specific name that we can use for things like this so here's what it would look like in this example so let's say that I wanted to convert these back to lowercase so what I could do is I will just grab this here now I'm going to use a pass n a lambda function here to dot apply instead of passing in that other function that we wrote so I'll say lambda and the synth if you're not familiar with Landa functions then the syntax can be a little weird here but basically we just have a no-name function here and then what we want to return so I want to return X dot lower of that argument that we get passed in so if I run that and then look at our data frame again then now we can see that we have the lowercase version of our email so if you're more comfortable writing regular functions then you can do it this way but if you are comfortable writing lambda functions and your function isn't too complicated then you can always do it this way like we did here now we're working with strings here but you can also use this with numbers where we can run any type of calculations that we want as well okay so this is how apply works on series objects so now let's look at how apply works with data frames so far we've only been using this with a series so anytime we access a column like this that returns a series and again when we ran apply on the series it ran a function on all of the values in that series now when we run apply on a data frame it runs a function on each row or column of that data frame so let's see what this looks like and it should make more sense so let me grab the example of above where we ran the Len function right here and let me copy this and paste this down here and then we'll take a look at what this looks like when a data frame so again what this gave us was the length of each value in that email series so you might think that you can run this same apply method on the entire data frame and it will give us the length of each value in the data frame but that's not what it does so let's see what that gives us so instead of accessing a specific column let's just say DF not apply and pass in that Lin function so if I run this then we get this response that you might not expect so what's going on here is that it's not applying the length function to every value in the data frame it's actually applying the length function to each series in the data frame specifically the columns so basically what this is telling us is that our first name column has a length of 3 so if we look up here at first we can see that first has three values it's telling us that last has three values and email has three values so that's just the number of rows in each column and we can get that same result for a specific series if we manually check the length of one of these so if I was to say Lin access one of these columns and ran this then we can see that gives us three so that's basically what apply is doing here on the data frame but it's doing it for every column and you can also have this apply to rows as well if you change the axis so we can change the axis here and say no I want to do this on the rows whoops and I put row I meant to put rows so if I run this oh and I made another mistake here I actually didn't mean to put rows roses the default I meant to put columns so if I put columns then we can see that now we get three as well but now what it's doing here is it's counting it this way so it's saying okay one or row zero has three values but it's saying okay those values are Corrie Schafer and then the email it's not counting it downward so basically we want to use functions that will make sense to be used on a series object when using a ply on an entire data frame so for example let's say that

we wanted to grab the minimum value from each column well series objects have a min method so we could pass that in to apply and see the minimum value for each series now in our sample data frame we have all string values so if we grab the minimum value from a series of strings then it'll just return the one the first one in alphabetical order so let's see what this would look like so we can come down here and we can say D F dot apply and now let me pass in that series men method so I could say P D is what we imported pandas eyes P D series dot min so if I run this then we can see okay it's saying that the one that comes first in the alphabet in the first category is quarry the one that comes first and the last names is doe so that is you know that's a d these two are s's that make sense and then my email comes before these other two emails now it probably would have been better to take a look at this using a series of numerical data instead because with numerical data this stuff you know obviously makes more sense so if we used series dot min on numerical data that would obviously give us the minimum values for each of those numbers and we can use lambda functions with this as well but you just have to remember that the lambda will be working on a series object so if I come down here I can say D F dot apply and now let's pass in a lambda now this X here this X is going to be a series it's not value so what methods do a series have well a series has a min method so let's return the minimum value and run that and we can see that gives us the same response that this one up here gave us now like I said this is kind of a contrived example because we could get these same results by using the data frames min method but I just wanted to point out how this actually works so this is way more useful when your data frame contains numerical data so for example we could use numpy to apply the square root for all of our series objects or any type of numerical analysis like that okay so running apply on a series applies a function to every value in the series and running apply to a data frame like we did here applies a function to every series in the data frame but you might be wondering if there's a way that

we can apply a function to every individual element in the data frame and that's what apply map is used for an apply map only works on data frames series objects don't have the apply map method so let me show you how this is different so again let's use that same built-in length function and pass that in to apply map so I'll say D F dot apply map and now let's pass in that built-in length function so if I run this then we can see that what this does is that it's now applying that length function to each individual value in our data frame so the first-name had these many characters so first name mine was Cory so Co re Y that's five characters and then Jane and then John so those are four characters and we saw the length of the emails before three 1717 so that's what this is doing here it's applying that function to every individual element of our data frame so this might be how some of you expected the apply method to work on a data frame but instead we use apply map for this now I know that this can be a bit confusing but hopefully after seeing those differences it makes a little bit more sense of when we would want to use which so for example since we have an entire data frame full of strings in this example if I wanted all of them to be lowercase then I could just do something like this I could say D F dot apply map and then I can just pass in the string dot lower method so if I run this then we can see that now all of the values in that data frame are lowercase now if you had numerical data in your data frame then you would get an error here since you can't run string methods on numbers so you need to pass in a more complicated function that handles that appropriately ok so now we've looked at apply and apply map and hopefully those make more sense to you now so now let's look at the map method now the map method only works on a series so map is used for substituting each value in a series with another value so for example let's say that we wanted to substitute a couple of our first names so to do this I could say DF and access that first name column which is also a series and then I can use the map method on this and now I'm going to pass in a dictionary of the values that we want to substitute so let's say instead of quarry I want to pass in Chris and instead of Jane I wanted to pass in a value of Mary so if I run this then we can see that returns a series where those first names were substituted out so quarry was the first value here and now it's Chris Jane it was is now Mary now one thing that I do want to point out here is that the values that we didn't substitute were converted to in a in values not a number of values now that may or may not be what we want and I'll show an example of this when we look at some real-world Stack Overflow examples now in this example we likely wouldn't want to get rid of these other names so you might be thinking okay well what if I wanted to keep John but just substitute these other names so if that's the case then instead of using map we can instead use the replace method so instead of doing what we did here I'll just copy this paste this in here instead of using map I can use replace and if I run this then now we can see that it's basically the exact same result here except now we actually have it didn't replace John within na in value and again everything that we've done here doesn't actually change the data frame if we wanted to set this to the actual column then we would have to do something like this we could say that DF first column is equal to that replaced version of the data frame so if I run this and then I looked at our entire data frame then now we can see that those values were substituted okay so now that we've looked at a lot of different ways of updating information in our rows and columns now let's go over to our larger data set and look at some real-world examples of how we can apply what we've learned here so let me go over to my Stack Overflow survey data here so we have our Stack Overflow survey notebook open here that we've been using throughout this series and again if you'd like to download these notebooks or the Stack Overflow data in order to follow along then the links for all this are in the description section below ok so let's apply some of what we learned here to this data set so in the last video where we covered filtering we looked at filtering salaries over a certain amount and the column name for salary it is over here somewhere it is this one here converted comp now I'm assuming that short for converted compensation and that is converted to United States dollars so let's say that we wanted to rename that column to where it was a bit more clear so instead I'm going to rename this column to salary USD instead of converted comp so if we remember from earlier in the video we can do this with the rename method so if I come down here then I can say D F dot rename and then we can just pass in the columns that we want to rename so I'll pass in a dictionary here but this is only going to have one value so we want to change converted comp and we want to set that to I'll call this salary USD so if I run this then let me go over here and see if this looks good okay so that looks good that changed now after you make sure that it made the change that you wanted it to make and that it's not a mistake then we can actually apply that to our data frame by setting in place is equal to true now that's why in place equal to true is actually a good idea because sometimes you know when you're working in pandas we're always doing these different types of renames and filters and things like that and sometimes we're going to do things wrong so it's always better to check and make sure that it made the change that you meant to make first and then apply that change to your data frame to actually make the solidify those changes so now let's see if we have access to that salary USD column and if I look at that then we can see that we do have a salary USD column in this data frame so that change did work okay so what is something else that we can do here to see what we have already learned ok so here's an example here so we've looked at this hobbyist column a few times in this series this is a column where people answered on the survey whether they code as a hobby in their free time and let's look at this column real quick we can kind of see it here it has a bunch of yes no values let me actually print this out down here so I will access that hobbyist column and we can see that this is a bunch of yes/no values here so let's say that we wanted to convert these values and map all of the yes responses to a true boolean value and he knows to false so how would we do this well one way that we could do this is with the map method that we just learned about so what we could do is we could say DF hobbiest dot map and then pass in a dictionary here of the substitutions that we want to make so for the yes values I will convert this to a boolean of true and for the no false so if I run this then if we compare the results that we got up here and the results that we're getting down here we can see that all the yeses are mapped to true and all the noes are mapped to false now I don't believe there's actually an in-place argument for map so what we can do here is we can just set that series and set that equal to the mapped version of that series so now if I run this and look at our data frame now we can see that entire column is now true false values instead of yes/no values now remember when we use map than anything that isn't in our dictionary here so anything outside of a yes or no answer would be converted to an na n value I think there are other columns in this survey where there are yes/no and not sure answers or something like that so if we only wanted to replace the yes-or-no values and leave the others untouched then instead we could use the replace method instead but map works for this example because I know that this column only has yes and no okay so that is a quick review of some of what we learned in this video I'm not going to go over everything again since this video is already getting a little long but I did want to show you some examples of how updating values works when and how this can be applied to real-world data like this survey okay so before we end here I would like to mention that we do have a sponsor for this video and that sponsor is brilliant org so in this series we've been learning about pandas and how to analyze data and python and brilliant would be an excellent way to supplement what you learn here with their hands-on courses they have some excellent courses and lessons that do a deep dive on how to think about and analyze data correctly for data analysis fundamentals I would really recommend checking out their statistics course which shows you how to analyze graphs and determine significance in the data and I would also recommend their machine learning course which takes data analysis to a new level well you'll learn about the techniques being used that allow machines to make decisions where there's just too many variables for a human to consider so to support my channel and learn more about brilliant you can go to brilliant org Forge slash CMS to sign up for free and also the first 200 people they go to that link will get 20% off the annual premium subscription and you can find that link in the description section below again that's brilliant org forge slash CMS ok so I think that's going to do it for this panda svitavy contains in specific spots and then going a little more advanced when we learned about apply map and apply map now I know that those can be confusing to some people but hopefully you feel like you got an understanding of how each of those work now originally I was also going to show how to add and remove rows and columns in this video but this is getting a bit long so I'm just going to save that for our next video so in the next video we'll learn how to add and remove rows and columns from our data frame and that should be a much shorter video but if anyone has any questions about what we covered in this video then feel free to ask in the comment section below and I'll do my best to answer those and if you enjoy these tutorials and would like to support them then there are several ways you can do that the easiest ways to simply like the video and give it a thumbs up and it's always a huge help to share these videos with anyone who you think would find them useful and if you have the means you can contribute through patreon and there's a link to that page and at scripts in section below be sure to subscribe for future videos and thank you all for watching you