df.merge()
df.merge(right, on=column, left_on=left_column, right_on=right_column, left_index=False, right_index=False)
Merges two DataFrames by specified columns or indexes.
- Input:
- right : DataFrame
- DataFrame to merge with.
- on : label or list
- Column or index names to join on.
- left_on : label, list, or array-like
- Column or index names to join on the in the left DataFrame.
- right_on : label, list, or array-like
- Column or index names to join on the in the right DataFrame.
- left_index : boolean, default False
- Use index from left DataFrame as join key(s).
- right_index : boolean, default False
- Use index from right DataFrame as join key(s).
- Returns:
- df_merged - A new DataFrame with the two DataFrames merged on the specified input combinations.
- Return Type:
- DataFrame
- Note:
- The number of columns merged on the left and right DataFrames must be the same.
- left_on and left_index cannot happen at the same time. Same as right_on and right_index.
pets_info = bpd.DataFrame().assign(
Pet_ID=['dog_001', 'cat_001', 'cat_002', 'dog_002', 'dog_003', 'ham_001'],
Breed=['Labrador', 'Sphinx', 'Siamese', 'Chihuahua', 'Labrador', 'Roborovski'],
Owner=['Jason', 'Lauren', 'Cassidy', 'Bobby', 'Dhruv', 'Cassidy']
)
pets_info
| Index | Pet_ID | Breed | Owner |
|---|---|---|---|
| 0 | dog_001 | Labrador | Jason |
| 1 | cat_001 | Sphinx | Lauren |
| 2 | cat_002 | Siamese | Cassidy |
| 3 | dog_002 | Chihuahua | Bobby |
| 4 | dog_003 | Labrador | Dhruv |
| 5 | ham_001 | Roborovski | Cassidy |
pets.merge(pets_info, left_on='ID', right_on='Pet_ID')
| Index | ID | Species | Color | Weight | Age | Is_Cat | Owner_Comment | Pet_ID | Breed | Owner |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | dog_001 | dog | black | 40 | 5 | False | There are no bad dogs, only bad owners. | dog_001 | Labrador | Jason |
| 1 | cat_001 | cat | golden | 1.5 | 0.2 | True | My best birthday present ever!!! | cat_001 | Sphinx | Lauren |
| 2 | cat_002 | cat | black | 15 | 9 | True | ****All you need is love and a cat.**** | cat_002 | Siamese | Cassidy |
| 3 | dog_002 | dog | white | 80 | 2 | False | Love is a wet nose and a wagging tail. | dog_002 | Chihuahua | Bobby |
| 4 | dog_003 | dog | black | 25 | 0.5 | False | Be the person your dog thinks you are. | dog_003 | Labrador | Dhruv |
| 5 | ham_001 | hamster | black | 1 | 3 | False | No, thank you! | ham_001 | Roborovski | Cassidy |
pets_info = pets_info.set_index('Pet_ID')
pets.merge(pets_info, left_on='ID', right_index=True)
| Index | ID | Species | Color | Weight | Age | Is_Cat | Owner_Comment | Breed | Owner |
|---|---|---|---|---|---|---|---|---|---|
| 0 | dog_001 | dog | black | 40 | 5 | False | There are no bad dogs, only bad owners. | Labrador | Jason |
| 1 | cat_001 | cat | golden | 1.5 | 0.2 | True | My best birthday present ever!!! | Sphinx | Lauren |
| 2 | cat_002 | cat | black | 15 | 9 | True | ****All you need is love and a cat.**** | Siamese | Cassidy |
| 3 | dog_002 | dog | white | 80 | 2 | False | Love is a wet nose and a wagging tail. | Chihuahua | Bobby |
| 4 | dog_003 | dog | black | 25 | 0.5 | False | Be the person your dog thinks you are. | Labrador | Dhruv |
| 5 | ham_001 | hamster | black | 1 | 3 | False | No, thank you! | Roborovski | Cassidy |
pets_info2 = pets_info.assign(ID = pets_info.index)
pets.merge(pets_info2, on='ID')
| Index | ID | Species | Color | Weight | Age | Is_Cat | Owner_Comment | Breed | Owner |
|---|---|---|---|---|---|---|---|---|---|
| 0 | dog_001 | dog | black | 40 | 5 | False | There are no bad dogs, only bad owners. | Labrador | Jason |
| 1 | cat_001 | cat | golden | 1.5 | 0.2 | True | My best birthday present ever!!! | Sphinx | Lauren |
| 2 | cat_002 | cat | black | 15 | 9 | True | ****All you need is love and a cat.**** | Siamese | Cassidy |
| 3 | dog_002 | dog | white | 80 | 2 | False | Love is a wet nose and a wagging tail. | Chihuahua | Bobby |
| 4 | dog_003 | dog | black | 25 | 0.5 | False | Be the person your dog thinks you are. | Labrador | Dhruv |
| 5 | ham_001 | hamster | black | 1 | 3 | False | No, thank you! | Roborovski | Cassidy |