6.28. DataFrame Join
pd.concat().merge().join().melt()- stack columns
Warning
DataFrame.append() and Series.append() have been deprecated and will be removed in Pandas 2.0. Use pandas.concat() instead [1]
6.28.1. SetUp
>>> import pandas as pd
>>> import numpy as np
>>> np.random.seed(0)
>>>
>>> pd.set_option('display.width', 250)
>>> pd.set_option('display.max_columns', 20)
>>> pd.set_option('display.max_rows', 30)
>>>
>>>
>>> a = pd.DataFrame([
... {'firstname': 'Alice', 'lastname': 'Apricot', 'age': 30},
... {'firstname': 'Bob', 'lastname': 'Blackthorn', 'age': 31},
... {'firstname': 'Carol', 'lastname': 'Corn', 'age': 32},
... ])
>>>
>>> b = pd.DataFrame([
... {'firstname': 'Dave', 'lastname': 'Durian', 'age': 33},
... {'firstname': 'Eve', 'lastname': 'Elderberry', 'age': 34},
... {'firstname': 'Mallory', 'lastname': 'Melon', 'age': 15},
... ])
>>>
>>> a
firstname lastname age
0 Alice Apricot 30
1 Bob Blackthorn 31
2 Carol Corn 32
>>>
>>> b
firstname lastname age
0 Dave Durian 33
1 Eve Elderberry 34
2 Mallory Melon 15
6.28.2. Concatenate
Useful for merging data from two files or datasources
>>> pd.concat([a,b])
firstname lastname age
0 Alice Apricot 30
1 Bob Blackthorn 31
2 Carol Corn 32
0 Dave Durian 33
1 Eve Elderberry 34
2 Mallory Melon 15
>>> pd.concat([a,b], ignore_index=True)
firstname lastname age
0 Alice Apricot 30
1 Bob Blackthorn 31
2 Carol Corn 32
3 Dave Durian 33
4 Eve Elderberry 34
5 Mallory Melon 15
6.28.3. Join
Join columns of another DataFrame.
Join columns with other DataFrame either on index or on a key column.
Efficiently join multiple DataFrame objects by index at once by passing a list.
rfuffix- If two columns has the same name, add suffix to rightlfuffix- If two columns has the same name, add suffix to lefthttps://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.join.html
Figure 6.18. Pandas DataFrame Joins
>>> users = pd.DataFrame([
... {'firstname': 'Alice', 'lastname': 'Apricot'},
... {'firstname': 'Bob', 'lastname': 'Blackthorn'},
... {'firstname': 'Carol', 'lastname': 'Corn'},
... {'firstname': 'Dave', 'lastname': 'Durian'},
... {'firstname': 'Eve', 'lastname': 'Elderberry'},
... {'firstname': 'Mallory', 'lastname': 'Melon'},
... ], index=['alice', 'bob', 'carol', 'dave', 'eve', 'mallory'])
>>>
>>> accounts = pd.DataFrame([
... {'email': 'alice@example.com', 'active': True},
... {'email': 'bob@example.com', 'active': True},
... {'email': 'carol@example.com', 'active': True},
... {'email': 'dave@example.org', 'active': True},
... {'email': 'eve@example.org', 'active': True},
... {'email': 'mallory@example.net', 'active': False},
... ], index=['alice', 'bob', 'carol', 'dave', 'eve', 'mallory'])
>>> users
firstname lastname
alice Alice Apricot
bob Bob Blackthorn
carol Carol Corn
dave Dave Durian
eve Eve Elderberry
mallory Mallory Melon
>>>
>>> accounts
email active
alice alice@example.com True
bob bob@example.com True
carol carol@example.com True
dave dave@example.org True
eve eve@example.org True
mallory mallory@example.net False
>>> users.join(accounts)
firstname lastname email active
alice Alice Apricot alice@example.com True
bob Bob Blackthorn bob@example.com True
carol Carol Corn carol@example.com True
dave Dave Durian dave@example.org True
eve Eve Elderberry eve@example.org True
mallory Mallory Melon mallory@example.net False
6.28.4. Merge
Merge DataFrame or named Series objects with a database-style join.
The join is done on columns or indexes.
If joining columns on columns, the DataFrame indexes will be ignored.
Otherwise if joining indexes on indexes or indexes on a column or columns, the index will be passed on.
>>> users = pd.DataFrame([
... {'username': 'alice', 'firstname': 'Alice', 'lastname': 'Apricot'},
... {'username': 'bob', 'firstname': 'Bob', 'lastname': 'Blackthorn'},
... {'username': 'carol', 'firstname': 'Carol', 'lastname': 'Corn'},
... {'username': 'dave', 'firstname': 'Dave', 'lastname': 'Durian'},
... {'username': 'eve', 'firstname': 'Eve', 'lastname': 'Elderberry'},
... {'username': 'mallory', 'firstname': 'Mallory', 'lastname': 'Melon'},
... ])
>>>
>>> accounts = pd.DataFrame([
... {'login': 'alice', 'email': 'alice@example.com', 'active': True},
... {'login': 'bob', 'email': 'bob@example.com', 'active': True},
... {'login': 'carol', 'email': 'carol@example.com', 'active': True},
... {'login': 'dave', 'email': 'dave@example.org', 'active': True},
... {'login': 'eve', 'email': 'eve@example.org', 'active': True},
... {'login': 'mallory', 'email': 'mallory@example.net', 'active': False},
... ])
>>>
>>>
>>> users
username firstname lastname
0 alice Alice Apricot
1 bob Bob Blackthorn
2 carol Carol Corn
3 dave Dave Durian
4 eve Eve Elderberry
5 mallory Mallory Melon
>>>
>>> accounts
login email active
0 alice alice@example.com True
1 bob bob@example.com True
2 carol carol@example.com True
3 dave dave@example.org True
4 eve eve@example.org True
5 mallory mallory@example.net False
>>>
>>>
>>> users.merge(accounts, left_on='username', right_on='login')
username firstname lastname login email active
0 alice Alice Apricot alice alice@example.com True
1 bob Bob Blackthorn bob bob@example.com True
2 carol Carol Corn carol carol@example.com True
3 dave Dave Durian dave dave@example.org True
4 eve Eve Elderberry eve eve@example.org True
5 mallory Mallory Melon mallory mallory@example.net False
Merge on Index:
>>> users = pd.DataFrame([
... {'firstname': 'Alice', 'lastname': 'Apricot', 'comment': ''},
... {'firstname': 'Bob', 'lastname': 'Blackthorn', 'comment': ''},
... {'firstname': 'Carol', 'lastname': 'Corn', 'comment': ''},
... {'firstname': 'Dave', 'lastname': 'Durian', 'comment': ''},
... {'firstname': 'Eve', 'lastname': 'Elderberry', 'comment': ''},
... {'firstname': 'Mallory', 'lastname': 'Melon', 'comment': ''},
... ], index=['alice', 'bob', 'carol', 'dave', 'eve', 'mallory'])
>>>
>>> accounts = pd.DataFrame([
... {'email': 'alice@example.com', 'active': True, 'comment': ''},
... {'email': 'bob@example.com', 'active': True, 'comment': ''},
... {'email': 'carol@example.com', 'active': True, 'comment': ''},
... {'email': 'dave@example.org', 'active': True, 'comment': ''},
... {'email': 'eve@example.org', 'active': True, 'comment': ''},
... {'email': 'mallory@example.net', 'active': False, 'comment': ''},
... ], index=['alice', 'bob', 'carol', 'dave', 'eve', 'mallory'])
>>>
>>>
>>> users
firstname lastname comment
alice Alice Apricot
bob Bob Blackthorn
carol Carol Corn
dave Dave Durian
eve Eve Elderberry
mallory Mallory Melon
>>>
>>> accounts
email active comment
alice alice@example.com True
bob bob@example.com True
carol carol@example.com True
dave dave@example.org True
eve eve@example.org True
mallory mallory@example.net False
>>>
>>>
>>> users.merge(accounts, left_index=True, right_index=True, suffixes=('_users', '_accounts'))
firstname lastname comment_users email active comment_accounts
alice Alice Apricot alice@example.com True
bob Bob Blackthorn bob@example.com True
carol Carol Corn carol@example.com True
dave Dave Durian dave@example.org True
eve Eve Elderberry eve@example.org True
mallory Mallory Melon mallory@example.net False
Merge on index and a column:
>>> users = pd.DataFrame([
... {'firstname': 'Alice', 'lastname': 'Apricot', 'comment': ''},
... {'firstname': 'Bob', 'lastname': 'Blackthorn', 'comment': ''},
... {'firstname': 'Carol', 'lastname': 'Corn', 'comment': ''},
... {'firstname': 'Dave', 'lastname': 'Durian', 'comment': ''},
... {'firstname': 'Eve', 'lastname': 'Elderberry', 'comment': ''},
... {'firstname': 'Mallory', 'lastname': 'Melon', 'comment': ''},
... ], index=['alice', 'bob', 'carol', 'dave', 'eve', 'mallory'])
>>>
>>> accounts = pd.DataFrame([
... {'login': 'alice', 'email': 'alice@example.com', 'active': True},
... {'login': 'bob', 'email': 'bob@example.com', 'active': True},
... {'login': 'carol', 'email': 'carol@example.com', 'active': True},
... {'login': 'dave', 'email': 'dave@example.org', 'active': True},
... {'login': 'eve', 'email': 'eve@example.org', 'active': True},
... {'login': 'mallory', 'email': 'mallory@example.net', 'active': False},
... ])
>>>
>>>
>>> users
firstname lastname comment
alice Alice Apricot
bob Bob Blackthorn
carol Carol Corn
dave Dave Durian
eve Eve Elderberry
mallory Mallory Melon
>>>
>>> accounts
login email active
0 alice alice@example.com True
1 bob bob@example.com True
2 carol carol@example.com True
3 dave dave@example.org True
4 eve eve@example.org True
5 mallory mallory@example.net False
>>>
>>>
>>> users.merge(accounts, left_index=True, right_on='login')
firstname lastname comment login email active
0 Alice Apricot alice alice@example.com True
1 Bob Blackthorn bob bob@example.com True
2 Carol Corn carol carol@example.com True
3 Dave Durian dave dave@example.org True
4 Eve Elderberry eve eve@example.org True
5 Mallory Melon mallory mallory@example.net False