pandas<\/code> library’s following functions to carry out these operations.<\/p>\n\n\n\n\npandas.concat()<\/code><\/li>\n\n\n\npandas.merge()<\/code><\/li>\n\n\n\npandas.DataFrame.join()<\/code><\/li>\n<\/ul>\n\n\n\n<\/a>Preparing Sample Data<\/h2>\n\n\n\nWe’ll create sample datasets using pandas.DataFrame()<\/code> function and then perform concatenating operations on them.<\/p>\n\n\n\n![\"Sample](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-4.jpeg\")
<\/figure>\n\n\n\nThe code in the image will generate two datasets from data<\/code> and data1<\/code> using pd.DataFrame(data)<\/code> and pd.DataFrame(data1)<\/code> and store them in the variables df1<\/code> and df2<\/code>.<\/p>\n\n\n\nThen, using the .to_csv()<\/code> function, df1<\/code> and df2<\/code> will be saved in the CSV<\/code> format as 'employee.csv'<\/code> and 'employee1.csv'<\/code> respectively.<\/p>\n\n\n\nHere, the data that we created looks as shown in the following image.<\/p>\n\n\n\n![\"Data](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-5.jpeg\")
<\/figure>\n\n\n\n<\/a>Combining Data Using concat()<\/h1>\n\n\n\nWe can use the pandas<\/code> library to analyze, modify, and do other things with our CSV<\/strong> (comma-separated value) data. The library includes the concat()<\/code> function which we will use to perform the concatenation of multiple datasets.<\/p>\n\n\n\nThere are two axes on which the datasets can be concatenated: the row axis<\/strong> and the column axis<\/strong>.<\/p>\n\n\n\n![\"Visual](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-6.jpeg\")
<\/figure>\n\n\n\n<\/a>Combine Data Along the Row Axis<\/h2>\n\n\n\nWe previously created two datasets named 'employee.csv'<\/code> and 'employee1.csv'<\/code>. We’ll concatenate them horizontally, which means the data will be spliced across the rows.<\/p>\n\n\n\ncombine = pd.concat([dt, dt1])<\/pre><\/div>\n\n\n\nThe above code demonstrates the basic use of the concat()<\/code> function. We passed a list of datasets(objects<\/code>) that will be combined along the row axis<\/strong> by default.<\/p>\n\n\n\n![\"Concatenated](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-7.jpeg\")
<\/figure>\n\n\n\nThe concat()<\/code> function accepts some parameters that affect the concatenation of the data.<\/p>\n\n\n\nThe indices of the data are taken from their corresponding data, as seen in the above output. How do we create a new data index?<\/p>\n\n\n\n
<\/a>The<\/strong> ignore_index Parameter<\/strong><\/h3>\n\n\n\nWhen ignore_index=True<\/code> is set, a new index from 0<\/code> to n-1<\/code> is created. The default value is False<\/code>, which is why the indices were repeated in the above example.<\/p>\n\n\n\nset_index = pd.concat([dt, dt1], ignore_index=True)<\/pre><\/div>\n\n\n\n![\"Created](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-8.jpeg\")
<\/figure>\n\n\n\nAs shown in the image above, the dataset contains a new index ranging from 0<\/code> to 7<\/code>.<\/p>\n\n\n\n<\/a>The<\/strong> join Parameter<\/strong><\/h3>\n\n\n\nIn the above image, we can see that the first four data points for the Salary<\/code> and No_of_awards<\/code> columns are missing.<\/p>\n\n\n\nThis is due to the join<\/code> parameter, which by default is set to \"outer\"<\/code> which joins the data exactly as it is. If it is set to \"inner\"<\/code>, data that does not match another dataset is removed.<\/p>\n\n\n\ninner_join = pd.concat([dt, dt1], join=\"inner\")<\/pre><\/div>\n\n\n\n![\"Inner](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-9.jpeg\")
<\/figure>\n\n\n\n<\/a>The keys Parameter<\/h3>\n\n\n\nThe keys<\/code> parameter creates an index from the keys<\/strong> which is used to differentiate and identify the original data in the concatenated objects.<\/p>\n\n\n\nkeys = pd.concat([dt, dt1], keys=[\"Dataset1\", \"Dataset2\"])<\/pre><\/div>\n\n\n\n![\"Key](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-10.jpeg\")
<\/figure>\n\n\n\nThe datasets were concatenated, and a multi-level index was created, with the first level representing the outermost index (Dataset1<\/code> and Dataset2<\/code> from the keys<\/code>) and the second level representing the original index.<\/p>\n\n\n\n<\/a>Combine Data Along the Column Axis<\/h2>\n\n\n\nThe datasets were concatenated along the row axis or horizontally in the previous section, but in this approach, we will stitch them vertically<\/strong> or along the column axis<\/strong> using the axis<\/code> parameter.<\/p>\n\n\n\nThe axis<\/code> parameter is set to 0<\/code> or \"index\"<\/code> by default, which concatenates the datasets along the row axis<\/strong>, but if we change its value to 1<\/code> or \"columns\"<\/code>, it concatenates the datasets along the column axis<\/strong>.<\/p>\n\n\n\ncombine_vertically = pd.concat([dt, dt1], axis=\"columns\")\n#---------------------------OR---------------------------#\ncombine_vertically = pd.concat([dt, dt1], axis=1)<\/pre><\/div>\n\n\n\n![\"Datasets](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-11.jpeg\")
<\/figure>\n\n\n\n<\/a>Merging Data Using merge()<\/h1>\n\n\n\nThe pandas.merge()<\/code> function merges data from one or more datasets based on common columns or indices.<\/p>\n\n\n\nWe’ll operate on a different dataset that we created and contains the information shown in the following image.<\/p>\n\n\n\n![\"Sample](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-12.jpeg\")
<\/figure>\n\n\n\nThe merge()<\/code> function takes left<\/code> and right<\/code> parameters which are datasets to be merged.<\/p>\n\n\n\n<\/a>The how Parameter<\/h2>\n\n\n\nWe can now specify the type of merge we want to perform on these datasets by providing the how<\/code> parameter. The how<\/code> parameter allows for five different types of merges:<\/p>\n\n\n\n\ninner<\/code>: Default. It only includes the values that match from both datasets.<\/li>\n\n\n\nouter<\/code>: It includes all of the values from both datasets but fills the missing values with NaN<\/code> (Not a Number<\/strong>).<\/li>\n\n\n\nleft<\/code>: It includes all of the values from the left dataset and replaces any missing values in the right dataset with NaN<\/code>.<\/li>\n\n\n\nright<\/code>: It includes all of the values from the right dataset and replaces any missing values in the left dataset with NaN<\/code>.<\/li>\n\n\n\ncross<\/code>: It creates the Cartesian product which means that the number of rows created will be equal to the product of the row counts of both datasets. If both datasets have four rows, then four times four (4 * 4<\/code>) equals sixteen (16<\/code>) rows.<\/li>\n<\/ul>\n\n\n\n<\/a>Examples<\/h3>\n\n\n\nPerforming<\/strong> inner<\/code> merge<\/strong><\/p>\n\n\n\ninner_merging = pd.merge(dt1, dt2, how=\"inner\")<\/pre><\/div>\n\n\n\n![\"Inner](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-13.jpeg\")
<\/figure>\n\n\n\nWe can see that only values with the same Id<\/code> from both datasets have been included.<\/p>\n\n\n\nPerforming<\/strong> outer<\/code> merge<\/strong><\/p>\n\n\n\nouter_merging = pd.merge(dt1, dt2, how=\"outer\")<\/pre><\/div>\n\n\n\n![\"Outer](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-14.jpeg\")
<\/figure>\n\n\n\nIn the case of an outer merge<\/strong>, all of the values from both datasets were included, and the missing fields were filled in with NaN<\/code>.<\/p>\n\n\n\nPerforming<\/strong> left<\/code> merge<\/strong><\/p>\n\n\n\nleft_merging = pd.merge(dt1, dt2, how=\"left\")<\/pre><\/div>\n\n\n\n![\"Left](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-15.jpeg\")
<\/figure>\n\n\n\nThe matching values of the right dataset (dt2<\/code>) were merged in the left dataset (dt1<\/code>) and the values of the last four columns (Project_id_final<\/code>, Age<\/code>, Salary<\/code>, and No_of_awards<\/code>) were not found for A4<\/code>, so they were filled in with NaN<\/code>.<\/p>\n\n\n\nPerforming<\/strong> right<\/code> merge<\/strong><\/p>\n\n\n\nright_merging = pd.merge(dt1, dt2, how=\"right\")<\/pre><\/div>\n\n\n\n![\"Right](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-16.jpeg\")
<\/figure>\n\n\n\nThe matching values of the left dataset (dt1<\/code>) were merged in the right dataset (dt2<\/code>) and the values of the first five columns (Project_id_initial<\/code>, Name<\/code>, Role<\/code>, Experience<\/code>, and Qualification<\/code>) were not found for A6<\/code>, so they were filled in with NaN<\/code>.<\/p>\n\n\n\n<\/a>Cross Merging the Datasets<\/strong><\/h3>\n\n\n\nThe how<\/code> parameter has five different types of merge, one of which is a cross<\/code> merge.<\/p>\n\n\n\nAs previously stated, it generates the Cartesian product, with the number of rows formed equal to the product of row counts from both datasets. Take a look at the illustration below to get a better understanding.<\/p>\n\n\n\n![\"Visual](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-17.jpeg\")
<\/figure>\n\n\n\ncross_merging = pd.merge(dt1, dt2, how=\"cross\")<\/pre><\/div>\n\n\n\n![\"Cross](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-18.jpeg\")
<\/figure>\n\n\n\nBoth datasets have four rows each, and each row from dt1<\/code> is repeated four times (row count of dt2<\/code>), resulting in a data set of sixteen rows.<\/p>\n\n\n\n<\/a>The on, left_on & right_on Parameters<\/h2>\n\n\n\nThe on<\/code> parameter accepts the name of a column or index(row) to join on. It could be a single name or a list of names.<\/p>\n\n\n\nThe left_on<\/code> and right_on<\/code> parameter takes a column or index(row) name from the left and right dataset to join on. They are used when both datasets have different column names to join on.<\/p>\n\n\n\n<\/a>Merging Datasets on the Same Column<\/h3>\n\n\n\nTo merge the datasets based on the same column, we can use the on<\/code> parameter and pass the common column name that both datasets must have.<\/p>\n\n\n\nmerging_on_same_column = pd.merge(dt1, dt2, on='Id')<\/pre><\/div>\n\n\n\nWe are merging datasets dt1<\/code> and dt2<\/code> based on the 'Id'<\/code> column that they both share.<\/p>\n\n\n\n![\"Data](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-19.jpeg\")
<\/figure>\n\n\n\nThe matching Id<\/code> column values from both datasets were merged, and the non-matching values were removed.<\/p>\n\n\n\n<\/a>Merging Datasets on Different Columns<\/h3>\n\n\n\nTo merge different columns in the left and right datasets, use the left_on<\/code> and right_on<\/code> parameters.<\/p>\n\n\n\nleft_right_merging = pd.merge(dt1, dt2, left_on=\"Project_id_initial\", right_on='Project_id_final')<\/pre><\/div>\n\n\n\nThe joining column is the \"Project_id_initial\"<\/code> column from the left dataset (dt1<\/code>) and the \"Project_id_final\"<\/code> column from the right dataset (dt2<\/code>). The values shared by both columns will be used to merge them.<\/p>\n\n\n\n![\"Dataset](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/image-20.jpeg\")
<\/figure>\n\n\n\nAs we can see, the dataset includes both columns, as well as matching rows based on the common values in both the \"Project_id_initial\"<\/code> and \"Project_id_final\"<\/code> columns.<\/p>\n\n\n\n<\/a>Changing the Suffix of the Column<\/h2>\n\n\n\nIf you notice that the merged dataset has two Id<\/code> columns labeled Id_x<\/code> and Id_y<\/code>, this is due to the