Combine two pandas Data Frames join on a common column

Question

I have 2 dataframes   restaurant ids dataframe   Data columns  total 13 columns   business id      4503  non-null values categories       4503  non-null values city             4503  non-null values full address     4503  non-null values latitude         4503  non-null values longitude        4503  non-null values name             4503  non-null values neighborhoods    4503  non-null values open             4503  non-null values review count     4503  non-null values stars            4503  non-null values state            4503  non-null values type             4503  non-null values dtypes  bool 1   float64 3   int64 1   object 8     and   restaurant review frame  Int64Index  158430 entries  0 to 229905 Data columns  total 8 columns   business id    158430  non-null values date           158430  non-null values review id      158430  non-null values stars          158430  non-null values text           158430  non-null values type           158430  non-null values user id        158430  non-null values votes          158430  non-null values dtypes  int64 1   object 7    I would like to join these two DataFrames to make them into a single dataframe using the DataFrame join   command in pandas   I have tried the following line of code    the following line of code creates a left join of restaurant ids frame and   restaurant review frame on the column  business id  restaurant review frame join other restaurant ids dataframe on  business id  how  left     But when I try this I get the following error   Exception  columns overlap  Index  business id  stars  type   dtype object    I am very new to pandas and have no clue what I am doing wrong as far as executing the join statement is concerned   any help would be much appreciated

User · Answer

Joining fails if the DataFrames have some column names in common. The simplest way around it is to include an lsuffix or rsuffix keyword like so:

restaurant_review_frame.join(restaurant_ids_dataframe, on='business_id', how='left', lsuffix="_review")

This way, the columns have distinct names. The documentation addresses this very problem.

Or, you could get around this by simply deleting the offending columns before you join. If, for example, the stars in restaurant_ids_dataframe are redundant to the stars in restaurant_review_frame, you could del restaurant_ids_dataframe['stars'].

User · Answer

In case anyone needs to try and merge two dataframes together on the index  instead of another column   this also works   T1 and T2 are dataframes that have the same indices  import pandas as pd T1   pd merge T1  T2  on T1 index  how  outer     P S  I had to use merge because append would fill NaNs in unnecessarily

User · Answer

You can use merge to combine two dataframes into one   import pandas as pd pd merge restaurant ids dataframe  restaurant review frame  on  business id   how  outer     where on specifies field name that exists in both dataframes to join on  and how defines whether its inner outer left right join  with outer using  union of keys from both frames  SQL  full outer join    Since you have  star  column in both dataframes  this by default will create two columns star x and star y in the combined dataframe  As  DanAllan mentioned for the join method  you can modify the suffixes for merge by passing it as a kwarg  Default is suffixes    x     y    if you wanted to do something like star restaurant id and star restaurant review  you can do    pd merge restaurant ids dataframe  restaurant review frame  on  business id   how  outer   suffixes    restaurant id     restaurant review      The parameters are explained in detail in this link

[python] Combine two pandas Data Frames (join on a common column)

Examples related to python

Examples related to pandas

Examples related to dataframe

Examples related to merge

Examples related to left-join