In this tutorial, you\u2019ll learn various ways in which multiple DataFrames could be merged in python using Pandas library.<\/p>\n\n\n\n
Have you ever tried solving a Kaggle challenge? If yes, you might have noticed that in most of the challenges, the data provided to you is present in multiple files, with some of the columns present in more than one files. Well, what is the first thing that comes to your mind? Join them of course!<\/p>\n\n\n\n
Joining and merging DataFrames is the core process to start with data analysis and machine learning tasks. It is one of the toolkits which every Data Analyst or Data Scientist should master because in almost all the cases data comes from multiple source and files. You may need to bring all the data in one place by some sort of join logic and then start your analysis. People who work with SQL like query languages might know the importance of this task. Even if you want to build some machine learning models on some data, you may need to merge multiple Thankfully you have the most popular library in python, In this tutorial, you will practice a few standard techniques. More specifically, you will learn to:<\/p>\n\n\n\n Along the way, you will also learn a few tricks which you require before and after joining.<\/p>\n\n\n\n Start by importing the library you will be using throughout the tutorial: You will be performing all the operations in this tutorial on the dummy DataFrames that you will create. To create a DataFrame you can use python dictionary like:<\/p>\n\n\n\n Here the keys of the dictionary As you can notice, you now have a DataFrame with 3 columns To simply concatenate the DataFrames along the row you can use the You can notice that the two DataFrames Now the row labels are correct!<\/p>\n\n\n\n Mentioning the keys also makes it easy to retrieve data corresponding to a particular DataFrame. You can retrieve the data of DataFrame You can also pass a dictionary to It is worth noting that To concatenate DataFrames along column, you can specify the Another ubiquitous operation related to DataFrames is the merging operation. Two DataFrames might hold different kinds of information about the same entity and linked by some common feature\/column. To join these DataFrames, pandas provides multiple functions like You can join DataFrames You can notice that the DataFrames are now merged into a single DataFrame based on the common values present in the It might happen that the column on which you want to merge the DataFrames have different names (unlike in this case). For such merges, you will have to specify the arguments You can also append rows to a DataFrame by passing a Series or dict to In this section, you will practice the various join logics available to merge pandas DataFrames based on some common column\/key. The logic behind these joins is very much the same that you have in SQL when you join tables.<\/p>\n\n\n\n The You can notice that the resulting DataFrame had all the entries from both the tables with The The The Sometimes you may have to perform the join on the indexes or the row labels. To do so, you have to specify Pandas provides special functions for merging Time-series DataFrames. Perhaps the most useful and popular one is the Optionally an asof merge can perform a group-wise merge. This matches the For example, you might have trades and quotes, and you want to asof merge them. Here the left DataFrame is chosen as csv<\/code> files together in a single DataFrame.<\/p>\n\n\n\npandas<\/code> to your rescue! pandas<\/code> provides various facilities for easily combining together Series, DataFrames, and Panel objects with various kinds of set logic for the indexes and relational algebra functionality in the case of join \/ merge-type operations.<\/p>\n\n\n\nConcatenate DataFrames<\/h2>\n\n\n\n
pandas<\/code><\/p>\n\n\n\nimport pandas as pd\n<\/code><\/pre>\n\n\n\ndummy_data1 = {\n 'id': ['1', '2', '3', '4', '5'],\n 'Feature1': ['A', 'C', 'E', 'G', 'I'],\n 'Feature2': ['B', 'D', 'F', 'H', 'J']}\n<\/code><\/pre>\n\n\n\ndummy_data1<\/code> are the column names and the values in the list are the data corresponding to each observation or row. To transform this into a pandas DataFrame, you will use the DataFrame()<\/code> function of pandas, along with its columns<\/code> argument to name your columns:<\/p>\n\n\n\ndf1 = pd.DataFrame(dummy_data1, columns = ['id', 'Feature1', 'Feature2'])\n\ndf1\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td><\/tr> 2<\/th> 3<\/td> E<\/td> F<\/td><\/tr> 3<\/th> 4<\/td> G<\/td> H<\/td><\/tr> 4<\/th> 5<\/td> I<\/td> J<\/td><\/tr><\/tbody><\/table>\n\n\n\n id<\/code>, Feature1<\/code>, and Feature2<\/code>. There is an additional un-named column which pandas intrinsically creates as the row labels. Similar to the previous DataFrame df1<\/code>, you will create two more DataFrames df2<\/code> and df3<\/code> :<\/p>\n\n\n\ndummy_data2 = {\n 'id': ['1', '2', '6', '7', '8'],\n 'Feature1': ['K', 'M', 'O', 'Q', 'S'],\n 'Feature2': ['L', 'N', 'P', 'R', 'T']}\n<\/code><\/pre>\n\n\n\ndf2 = pd.DataFrame(dummy_data2, columns = ['id', 'Feature1', 'Feature2'])\n\ndf2\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th><\/tr><\/thead> 0<\/th> 1<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 6<\/td> O<\/td> P<\/td><\/tr> 3<\/th> 7<\/td> Q<\/td> R<\/td><\/tr> 4<\/th> 8<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n dummy_data3 = {\n 'id': ['1', '2', '3', '4', '5', '7', '8', '9', '10', '11'],\n 'Feature3': [12, 13, 14, 15, 16, 17, 15, 12, 13, 23]}\n<\/code><\/pre>\n\n\n\ndf3 = pd.DataFrame(dummy_data3, columns = ['id', 'Feature3'])\n\ndf3\n<\/code><\/pre>\n\n\n\nid<\/th> Feature3<\/th><\/tr><\/thead> 0<\/th> 1<\/td> 12<\/td><\/tr> 1<\/th> 2<\/td> 13<\/td><\/tr> 2<\/th> 3<\/td> 14<\/td><\/tr> 3<\/th> 4<\/td> 15<\/td><\/tr> 4<\/th> 5<\/td> 16<\/td><\/tr> 5<\/th> 7<\/td> 17<\/td><\/tr> 6<\/th> 8<\/td> 15<\/td><\/tr> 7<\/th> 9<\/td> 12<\/td><\/tr> 8<\/th> 10<\/td> 13<\/td><\/tr> 9<\/th> 11<\/td> 23<\/td><\/tr><\/tbody><\/table>\n\n\n\n concat()<\/code> function in pandas<\/code>. You will have to pass the names of the DataFrames in a list as the argument to the concat()<\/code> function:<\/p>\n\n\n\ndf_row = pd.concat([df1, df2])\n\ndf_row\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td><\/tr> 2<\/th> 3<\/td> E<\/td> F<\/td><\/tr> 3<\/th> 4<\/td> G<\/td> H<\/td><\/tr> 4<\/th> 5<\/td> I<\/td> J<\/td><\/tr> 0<\/th> 1<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 6<\/td> O<\/td> P<\/td><\/tr> 3<\/th> 7<\/td> Q<\/td> R<\/td><\/tr> 4<\/th> 8<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n df1<\/code> and df2<\/code> are now concatenated into a single DataFrame df_row<\/code> along the row. However, the row labels seem to be wrong! If you want the row labels to adjust automatically according to the join, you will have to set the argument ignore_index<\/code> as True<\/code> while calling the concat()<\/code> function:<\/p>\n\n\n\ndf_row_reindex = pd.concat([df1, df2], ignore_index=True)\n\ndf_row_reindex\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td><\/tr> 2<\/th> 3<\/td> E<\/td> F<\/td><\/tr> 3<\/th> 4<\/td> G<\/td> H<\/td><\/tr> 4<\/th> 5<\/td> I<\/td> J<\/td><\/tr> 5<\/th> 1<\/td> K<\/td> L<\/td><\/tr> 6<\/th> 2<\/td> M<\/td> N<\/td><\/tr> 7<\/th> 6<\/td> O<\/td> P<\/td><\/tr> 8<\/th> 7<\/td> Q<\/td> R<\/td><\/tr> 9<\/th> 8<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n pandas<\/code> also provides you with an option to label the DataFrames, after the concatenation, with a key so that you may know which data came from which DataFrame. You can achieve the same by passing additional argument keys<\/code> specifying the label names of the DataFrames in a list. Here you will perform the same concatenation with keys as x<\/code> and y<\/code> for DataFrames df1<\/code> and df2<\/code>respectively.<\/p>\n\n\n\nframes = [df1,df2]\ndf_keys = pd.concat(frames, keys=['x', 'y'])\n\ndf_keys\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th><\/tr><\/thead> x<\/th> 0<\/th> 1<\/td> A<\/td> B<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td><\/tr> 2<\/th> 3<\/td> E<\/td> F<\/td><\/tr> 3<\/th> 4<\/td> G<\/td> H<\/td><\/tr> 4<\/th> 5<\/td> I<\/td> J<\/td><\/tr> y<\/th> 0<\/th> 1<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 6<\/td> O<\/td> P<\/td><\/tr> 3<\/th> 7<\/td> Q<\/td> R<\/td><\/tr> 4<\/th> 8<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n df2<\/code> which had the label y<\/code> by using the loc<\/code> method:<\/p>\n\n\n\ndf_keys.loc['y']\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th><\/tr><\/thead> 0<\/th> 1<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 6<\/td> O<\/td> P<\/td><\/tr> 3<\/th> 7<\/td> Q<\/td> R<\/td><\/tr> 4<\/th> 8<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n concat()<\/code>, in which case the dictionary keys will be used for the keys<\/code> argument (unless other keys are specified):<\/p>\n\n\n\npieces = {'x': df1, 'y': df2}\n\ndf_piece = pd.concat(pieces)\n\ndf_piece\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th><\/tr><\/thead> x<\/th> 0<\/th> 1<\/td> A<\/td> B<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td><\/tr> 2<\/th> 3<\/td> E<\/td> F<\/td><\/tr> 3<\/th> 4<\/td> G<\/td> H<\/td><\/tr> 4<\/th> 5<\/td> I<\/td> J<\/td><\/tr> y<\/th> 0<\/th> 1<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 6<\/td> O<\/td> P<\/td><\/tr> 3<\/th> 7<\/td> Q<\/td> R<\/td><\/tr> 4<\/th> 8<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n concat()<\/code> makes a full copy of the data, and continuosly reusing this function can create a significant performance hit. If you need to use the operation over several datasets, use a list comprehension.<\/p>\n\n\n\nframes = [ process_your_file(f) for f in files ]\nresult = pd.concat(frames)\n<\/code><\/pre>\n\n\n\naxis<\/code> parameter as 1<\/code> :<\/p>\n\n\n\ndf_col = pd.concat([df1,df2], axis=1)\n\ndf_col\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th> id<\/th> Feature1<\/th> Feature2<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td> 1<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td> 2<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 3<\/td> E<\/td> F<\/td> 6<\/td> O<\/td> P<\/td><\/tr> 3<\/th> 4<\/td> G<\/td> H<\/td> 7<\/td> Q<\/td> R<\/td><\/tr> 4<\/th> 5<\/td> I<\/td> J<\/td> 8<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n Merge DataFrames<\/h2>\n\n\n\n
concat()<\/code>, merge()<\/code> , join()<\/code>, etc. In this section, you will practice using merge()<\/code> function of pandas.<\/p>\n\n\n\ndf_row<\/code> (which you created by concatenating df1<\/code> and df2<\/code> along the row) and df3<\/code> on the common column (or key) id<\/code>. To do so, pass the names of the DataFrames and an additional argument on<\/code> as the name of the common column, here id<\/code>, to the merge()<\/code>function:<\/p>\n\n\n\ndf_merge_col = pd.merge(df_row, df3, on='id')\n\ndf_merge_col\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th> Feature3<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td> 12<\/td><\/tr> 1<\/th> 1<\/td> K<\/td> L<\/td> 12<\/td><\/tr> 2<\/th> 2<\/td> C<\/td> D<\/td> 13<\/td><\/tr> 3<\/th> 2<\/td> M<\/td> N<\/td> 13<\/td><\/tr> 4<\/th> 3<\/td> E<\/td> F<\/td> 14<\/td><\/tr> 5<\/th> 4<\/td> G<\/td> H<\/td> 15<\/td><\/tr> 6<\/th> 5<\/td> I<\/td> J<\/td> 16<\/td><\/tr> 7<\/th> 7<\/td> Q<\/td> R<\/td> 17<\/td><\/tr> 8<\/th> 8<\/td> S<\/td> T<\/td> 15<\/td><\/tr><\/tbody><\/table>\n\n\n\n id<\/code> column of both the DataFrames. For example, here id<\/code> value 1<\/code>was present with both A<\/code>, B<\/code> and K<\/code>, L<\/code> in the DataFrame df_row<\/code> hence this id<\/code> got repeated twice in the final DataFrame df_merge_col<\/code> with repeated value 12<\/code> of Feature3<\/code> which came from DataFrame df3<\/code>.<\/p>\n\n\n\nleft_on<\/code> as the left DataFrame name and right_on<\/code> as the right DataFrame name, like :<\/p>\n\n\n\ndf_merge_difkey = pd.merge(df_row, df3, left_on='id', right_on='id')\n\ndf_merge_difkey\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th> Feature3<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td> 12<\/td><\/tr> 1<\/th> 1<\/td> K<\/td> L<\/td> 12<\/td><\/tr> 2<\/th> 2<\/td> C<\/td> D<\/td> 13<\/td><\/tr> 3<\/th> 2<\/td> M<\/td> N<\/td> 13<\/td><\/tr> 4<\/th> 3<\/td> E<\/td> F<\/td> 14<\/td><\/tr> 5<\/th> 4<\/td> G<\/td> H<\/td> 15<\/td><\/tr> 6<\/th> 5<\/td> I<\/td> J<\/td> 16<\/td><\/tr> 7<\/th> 7<\/td> Q<\/td> R<\/td> 17<\/td><\/tr> 8<\/th> 8<\/td> S<\/td> T<\/td> 15<\/td><\/tr><\/tbody><\/table>\n\n\n\n append()<\/code> function which returns a new DataFrame:<\/p>\n\n\n\nadd_row = pd.Series(['10', 'X1', 'X2', 'X3'],\n index=['id','Feature1', 'Feature2', 'Feature3'])\n\ndf_add_row = df_merge_col.append(add_row, ignore_index=True)\n\ndf_add_row\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1<\/th> Feature2<\/th> Feature3<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td> 12<\/td><\/tr> 1<\/th> 1<\/td> K<\/td> L<\/td> 12<\/td><\/tr> 2<\/th> 2<\/td> C<\/td> D<\/td> 13<\/td><\/tr> 3<\/th> 2<\/td> M<\/td> N<\/td> 13<\/td><\/tr> 4<\/th> 3<\/td> E<\/td> F<\/td> 14<\/td><\/tr> 5<\/th> 4<\/td> G<\/td> H<\/td> 15<\/td><\/tr> 6<\/th> 5<\/td> I<\/td> J<\/td> 16<\/td><\/tr> 7<\/th> 7<\/td> Q<\/td> R<\/td> 17<\/td><\/tr> 8<\/th> 8<\/td> S<\/td> T<\/td> 15<\/td><\/tr> 9<\/th> 10<\/td> X1<\/td> X2<\/td> X3<\/td><\/tr><\/tbody><\/table>\n\n\n\n Join DataFrames<\/h2>\n\n\n\n
Full Outer Join<\/h3>\n\n\n\n
FULL OUTER JOIN<\/code> combines the results of both the left and the right outer joins. The joined DataFrame will contain all records from both the DataFrames and fill in NaN<\/code>s for missing matches on either side. You can perform a full outer join by specifying the how<\/code> argument as outer<\/code> in the merge()<\/code> function:<\/p>\n\n\n\ndf_outer = pd.merge(df1, df2, on='id', how='outer')\n\ndf_outer\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1_x<\/th> Feature2_x<\/th> Feature1_y<\/th> Feature2_y<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 3<\/td> E<\/td> F<\/td> NaN<\/td> NaN<\/td><\/tr> 3<\/th> 4<\/td> G<\/td> H<\/td> NaN<\/td> NaN<\/td><\/tr> 4<\/th> 5<\/td> I<\/td> J<\/td> NaN<\/td> NaN<\/td><\/tr> 5<\/th> 6<\/td> NaN<\/td> NaN<\/td> O<\/td> P<\/td><\/tr> 6<\/th> 7<\/td> NaN<\/td> NaN<\/td> Q<\/td> R<\/td><\/tr> 7<\/th> 8<\/td> NaN<\/td> NaN<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n NaN<\/code>values for missing matches on either side. However, one more thing to notice is the suffix which got appended to the column names to show which column came from which DataFrame. The default suffixes are x<\/code> and y<\/code>, however, you can modify them by specifying the suffixes<\/code>argument in the merge()<\/code> function:<\/p>\n\n\n\ndf_suffix = pd.merge(df1, df2, left_on='id',right_on='id',how='outer',suffixes=('_left','_right'))\n\ndf_suffix\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1_left<\/th> Feature2_left<\/th> Feature1_right<\/th> Feature2_right<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 3<\/td> E<\/td> F<\/td> NaN<\/td> NaN<\/td><\/tr> 3<\/th> 4<\/td> G<\/td> H<\/td> NaN<\/td> NaN<\/td><\/tr> 4<\/th> 5<\/td> I<\/td> J<\/td> NaN<\/td> NaN<\/td><\/tr> 5<\/th> 6<\/td> NaN<\/td> NaN<\/td> O<\/td> P<\/td><\/tr> 6<\/th> 7<\/td> NaN<\/td> NaN<\/td> Q<\/td> R<\/td><\/tr> 7<\/th> 8<\/td> NaN<\/td> NaN<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n Inner Join<\/h3>\n\n\n\n
INNER JOIN<\/code> produces only the set of records that match in both DataFrame A and DataFrame B. You have to pass inner<\/code> in the how<\/code> argument of merge()<\/code> function to do inner join:<\/p>\n\n\n\ndf_inner = pd.merge(df1, df2, on='id', how='inner')\n\ndf_inner\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1_x<\/th> Feature2_x<\/th> Feature1_y<\/th> Feature2_y<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td> M<\/td> N<\/td><\/tr><\/tbody><\/table>\n\n\n\n Right Join<\/h3>\n\n\n\n
RIGHT JOIN<\/code> produces a complete set of records from DataFrame B (right DataFrame), with the matching records (where available) in DataFrame A (left DataFrame). If there is no match, the right side will contain null. You have to pass right<\/code> in the how<\/code> argument of merge()<\/code> function to do right join:<\/p>\n\n\n\ndf_right = pd.merge(df1, df2, on='id', how='right')\n\ndf_right\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1_x<\/th> Feature2_x<\/th> Feature1_y<\/th> Feature2_y<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 6<\/td> NaN<\/td> NaN<\/td> O<\/td> P<\/td><\/tr> 3<\/th> 7<\/td> NaN<\/td> NaN<\/td> Q<\/td> R<\/td><\/tr> 4<\/th> 8<\/td> NaN<\/td> NaN<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n Left Join<\/h3>\n\n\n\n
LEFT JOIN<\/code> produces a complete set of records from DataFrame A (left DataFrame), with the matching records (where available) in DataFrame B (right DataFrame). If there is no match, the left side will contain null. You have to pass left<\/code> in the how<\/code> argument of merge()<\/code> function to do left join:<\/p>\n\n\n\ndf_left = pd.merge(df1, df2, on='id', how='left')\n\ndf_left\n<\/code><\/pre>\n\n\n\nid<\/th> Feature1_x<\/th> Feature2_x<\/th> Feature1_y<\/th> Feature2_y<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 3<\/td> E<\/td> F<\/td> NaN<\/td> NaN<\/td><\/tr> 3<\/th> 4<\/td> G<\/td> H<\/td> NaN<\/td> NaN<\/td><\/tr> 4<\/th> 5<\/td> I<\/td> J<\/td> NaN<\/td> NaN<\/td><\/tr><\/tbody><\/table>\n\n\n\n Joining on index<\/h3>\n\n\n\n
right_index<\/code> (for the indexes of the right DataFrame) and left_index<\/code> (for the indexes of the left DataFrame) as True<\/code> :<\/p>\n\n\n\ndf_index = pd.merge(df1, df2, right_index=True, left_index=True)\n\ndf_index\n<\/code><\/pre>\n\n\n\nid_x<\/th> Feature1_x<\/th> Feature2_x<\/th> id_y<\/th> Feature1_y<\/th> Feature2_y<\/th><\/tr><\/thead> 0<\/th> 1<\/td> A<\/td> B<\/td> 1<\/td> K<\/td> L<\/td><\/tr> 1<\/th> 2<\/td> C<\/td> D<\/td> 2<\/td> M<\/td> N<\/td><\/tr> 2<\/th> 3<\/td> E<\/td> F<\/td> 6<\/td> O<\/td> P<\/td><\/tr> 3<\/th> 4<\/td> G<\/td> H<\/td> 7<\/td> Q<\/td> R<\/td><\/tr> 4<\/th> 5<\/td> I<\/td> J<\/td> 8<\/td> S<\/td> T<\/td><\/tr><\/tbody><\/table>\n\n\n\n Time-series friendly merging<\/h2>\n\n\n\n
merge_asof()<\/code> function. The merge_asof()<\/code> is similar to an ordered left-join except that you match on nearest key rather than equal keys. For each row in the left DataFrame, you select the last row in the right DataFrame whose on<\/code> key is less than the left\u2019s key. Both DataFrames must be sorted by the key.<\/p>\n\n\n\nby<\/code> key equally, in addition to the nearest match on the on<\/code> key.<\/p>\n\n\n\ntrades<\/code> and right DataFrame as quotes<\/code>. They are asof merged on key time<\/code> and group-wise merged by their ticker<\/code> symbol.<\/p>\n\n\n\ntrades = pd.DataFrame({\n 'time': pd.to_datetime(['20160525 13:30:00.023',\n '20160525 13:30:00.038',\n '20160525 13:30:00.048',\n '20160525 13:30:00.048',\n '20160525 13:30:00.048']),\n 'ticker': ['MSFT', 'MSFT','GOOG', 'GOOG', 'AAPL'],\n 'price': [51.95, 51.95,720.77, 720.92, 98.00],\n 'quantity': [75, 155,100, 100, 100]},\n columns=['time', 'ticker', 'price', 'quantity'])\n\nquotes = pd.DataFrame({\n 'time': pd.to_datetime(['20160525 13:30:00.023',\n '20160525 13:30:00.023',\n '20160525 13:30:00.030',\n '20160525 13:30:00.041',\n '20160525 13:30:00.048',\n '20160525 13:30:00.049',\n '20160525 13:30:00.072',\n '20160525 13:30:00.075']),\n 'ticker': ['GOOG', 'MSFT', 'MSFT','MSFT', 'GOOG', 'AAPL', 'GOOG','MSFT'],\n 'bid': [720.50, 51.95, 51.97, 51.99,720.50, 97.99, 720.50, 52.01],\n 'ask': [720.93, 51.96, 51.98, 52.00,720.93, 98.01, 720.88, 52.03]},\n columns=['time', 'ticker', 'bid', 'ask'])\n<\/code><\/pre>\n\n\n\ntrades\n<\/code><\/pre>\n\n\n\ntime<\/th> ticker<\/th> price<\/th> quantity<\/th><\/tr><\/thead> 0<\/th> 2016-05-25 13:30:00.023<\/td> MSFT<\/td> 51.95<\/td> 75<\/td><\/tr> 1<\/th> 2016-05-25 13:30:00.038<\/td> MSFT<\/td> 51.95<\/td> 155<\/td><\/tr> 2<\/th> 2016-05-25 13:30:00.048<\/td> GOOG<\/td> 720.77<\/td> 100<\/td><\/tr> 3<\/th> 2016-05-25 13:30:00.048<\/td> GOOG<\/td> 720.92<\/td> 100<\/td><\/tr> 4<\/th> 2016-05-25 13:30:00.048<\/td> AAPL<\/td> 98.00<\/td> 100<\/td><\/tr><\/tbody><\/table>\n\n\n\n quotes\n<\/code><\/pre>\n\n\n\n