Python Data Science Tutorial #16 - Pandas Merging Data Frames

Key Takeaways

what is going on guys from welcome to despite in the Tory series for data science in today's video we're going to learn how to merge data frames together how to join them together like SQL tables you can join SQL tables together how do we do that in pandas and what different types of joints or merges do we have so let us get into the code so as always we start by importing pandas as PD and now what we're going to do is we're going to create two separate data frames that have the same column social security number but we're going to create one data frame with the names and one data frame with the ages of a person so we're going to say names equals now we're going to create a dictionary and here we have the social security number and we're going to choose I don't know two five seven and eight and then we have the name of the person and the name is Ana bop John and Mike so these are the names and now I'm going to create the ages with the same social security number call Youm but different values and this is the key point here because we're going to join them together to us to to a new data frame that contains names and ages but we have to have the same index column or one column that's the same so that we can join them together on this column and what I'm going to do here is I'm not going to have the exact same social security numbers I'm going to have different social security numbers so some are going to overlap some are not going to overlap I'm going to start with one two which overlaps then maybe three and then five so these are the social security numbers here and I'm now going to say H and now let's define some age of 28 34 45 62 these are now or two dictionaries we're just going to convert them into data frames real quick so DF 1 equals P D dot data frame names in DF 2 equals PD dot data frame ages and now what we're going to do is we're going to create a new data frame DF that contains both these values merge together in one data frame and to do that of course we just say DF equals P dot merge and that's the function that we use to merge two data frames into one data frame to join them together now of course we have to specify the two data frames here so we say DF 1 and DF 2 but besides that we also need to specify on which column we're going to merge them and also how we're going to merge them and the column is obvious because we have the social security number as the index column as the column that both data frames have so we're going to say on equals as this n and now it gets tricky or not tricky but now we can choose a lot of different or actually four different ways to merge these data frames together so we have a left join an inner join and outer join and a right join so basically what we're saying is some of the security numbers social security numbers that we have here we don't have here and also the other way around so which one are we going to neglect out which one are we going to neglect how many of them are we going to throw out are we going to take all of them are we only going to take those values that are contained in both dictionaries or data frames which ones are we going to look at and now if I say how equals outer for example also known as the full join outer join full join what happens here is I basically say take all of them take one two three five seven and eight and just display all the values so if I just go ahead and say of course I would have to do set index here so set index as this N in place equals true so that'll be happen index because it's not automatically the case and now we're going to print a data frame what you're going to see is that we have all the values of course they're not sorted but we have all the individual values and they're where we have in the cases that we have both Social Security numbers and both data frames are what happens is that the values get linked together so anna has the social security number too and also the age 34 so it's one row now if we have John for example that has not which has the or who has the Social Security number seven he does not occur in the ages so we just say nan for not a number and an outer join basically does exactly that we take all the values in the where values are missing we just fill up with Nan's with nada numbers also for the ages of course we have the age of 28 which is the social security number one but we don't have a name for that so this would be an outer join the opposite would be an inner join an inner join would only give us the columns or duros actually where we have all the information so two and five basically because two and five for Social Security numbers that occur in the first dictionary and also in the second dictionary so an inner join only gives us the rows that overlap now another way to do that would be to say left or right join so left join would take all from the first column or actually the first dictionary sorry and then add up or fill it up with the right column and then fill the empty values with nan so what we're doing here is we take all the names so all these forces Social Security numbers it doesn't matter if they occur here we take all of these and then if they occur here we fill them up with the values and otherwise we just set them to nen the opposite would be the right joint just taking all of these here and filling up with these values so here we would have all the ages but not all the names as you can see one and three have no names so that's basically the right join and that's how you merge data frames and pandas so that's it for today's video I hope you learned something I hope you enjoyed it if so hit the like button to support this channel and see future videos for free also feel free to ask questions and give feedback in the comment section down below and of course subscribe to this channel if you want to see more in the future so thank you very much for watching see you in the next video and bye [Music]