Data 3-3 Multiple Pandas Dataframes

Data 3-3 Multiple Pandas Dataframes#

Working with multiple dataframes: Concat, Merge, resetting the index, join types

In this unit we will discuss strategies for dealing with multiple dataframes and combing them into a single dataframe.

1import pandas as pd

Concatenation#

Concatenation appends the rows of one or more dataframes together. This is a row-oriented operation so the resulting datafram will be longer.

Example: 50 rows concatendated to 40 rows == 90 rows.

pd.concat(items: list[pd.DataFrame], ignore_index=False) The argument is a list of Dataframes to concat.

We can choose to keep the current index in each dataframe ignore_index=False or create a new index ignore_index=True

1campus_students = pd.read_csv("https://raw.githubusercontent.com/mafudge/datasets/master/delimited/campus-students.csv")
2campus_students
Name Grade Year
0 Helen NaN Sophomore
1 Iris 10.0 Senior
2 Jimmy 8.0 Freshman
3 Karen NaN Freshman
4 Lynne 10.0 Sophomore
5 Mike 10.0 Sophomore
6 Nico NaN Junior
7 Pete 8.0 Freshman 
1online_students = pd.read_csv("https://raw.githubusercontent.com/mafudge/datasets/master/delimited/online-students.csv")
2online_students
Name Grade Year Location
0 Abby 7.0 Freshman NY
1 Bob 9.0 Sophomore CA
2 Chris 10.0 Senior CA
3 Dave 8.0 Freshman NY
4 Ellen 7.0 Sophomore TX
5 Fran 10.0 Senior FL
6 Greg 8.0 Freshman NY 
1combined_students = pd.concat([campus_students, online_students])
2combined_students
Name Grade Year Location
0 Helen NaN Sophomore NaN
1 Iris 10.0 Senior NaN
2 Jimmy 8.0 Freshman NaN
3 Karen NaN Freshman NaN
4 Lynne 10.0 Sophomore NaN
5 Mike 10.0 Sophomore NaN
6 Nico NaN Junior NaN
7 Pete 8.0 Freshman NaN
0 Abby 7.0 Freshman NY
1 Bob 9.0 Sophomore CA
2 Chris 10.0 Senior CA
3 Dave 8.0 Freshman NY
4 Ellen 7.0 Sophomore TX
5 Fran 10.0 Senior FL
6 Greg 8.0 Freshman NY

concat() - Ignoring the index#

As you can see from the code above the index from the original DataFrames was used. For example Helen and Abby both share the index 0.

While this is acceptable, there are situations where a new index based on combined values is desirable. We will encounter this later when grouping or pivioting data.

To make this happen include the ignore_index=True named argument. This will create a new index from the output DataFrame.

1students = pd.concat([campus_students, online_students], ignore_index=True)
2students
Name Grade Year Location
0 Helen NaN Sophomore NaN
1 Iris 10.0 Senior NaN
2 Jimmy 8.0 Freshman NaN
3 Karen NaN Freshman NaN
4 Lynne 10.0 Sophomore NaN
5 Mike 10.0 Sophomore NaN
6 Nico NaN Junior NaN
7 Pete 8.0 Freshman NaN
8 Abby 7.0 Freshman NY
9 Bob 9.0 Sophomore CA
10 Chris 10.0 Senior CA
11 Dave 8.0 Freshman NY
12 Ellen 7.0 Sophomore TX
13 Fran 10.0 Senior FL
14 Greg 8.0 Freshman NY

Best practice - data lineage#

When combining datasets, its a really good idea to retain data lineage, or a record of where the data came from. This can be done by added a column to each dataframe before concatenating.

In this example we create a student "type" column to track lineage.

1campus_students = pd.read_csv("https://raw.githubusercontent.com/mafudge/datasets/master/delimited/campus-students.csv")
2campus_students['type'] = 'campus'
3online_students = pd.read_csv("https://raw.githubusercontent.com/mafudge/datasets/master/delimited/online-students.csv")
4online_students['type'] = 'online'
5students = pd.concat([campus_students, online_students], ignore_index=True)
6students
Name Grade Year type Location
0 Helen NaN Sophomore campus NaN
1 Iris 10.0 Senior campus NaN
2 Jimmy 8.0 Freshman campus NaN
3 Karen NaN Freshman campus NaN
4 Lynne 10.0 Sophomore campus NaN
5 Mike 10.0 Sophomore campus NaN
6 Nico NaN Junior campus NaN
7 Pete 8.0 Freshman campus NaN
8 Abby 7.0 Freshman online NY
9 Bob 9.0 Sophomore online CA
10 Chris 10.0 Senior online CA
11 Dave 8.0 Freshman online NY
12 Ellen 7.0 Sophomore online TX
13 Fran 10.0 Senior online FL
14 Greg 8.0 Freshman online NY

Challenge 3-3-1#

Classic use case for concatenation.#

https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/json-samples/employees-dict.json

Observe the output from the three cells below. The issue with the JSON data is that there are employees under keys by department "accounting", "sales", "marketing"

This is the classic use-case for pd.concat() as there is no practical way to use pd.json_normalize() to get all the employees under each department.

for each department:
    create a dataframe for that department (e.g. from the json under the department)
    add lineage to the dataframe (e.g. add the department name)
    add the dataframe to a list of departments
concat the list of departments together one dataframe
print dataframe

expected output:

firstName lastName  age        dept
0      John      Doe   23  accounting
1      Mary    Smith   32  accounting
2     Sally    Green   27       sales
3       Jim   Galley   41       sales
4       Tom    Brown   28   marketing

1import requests
2response = requests.get("https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/json-samples/employees-dict.json")
3employees = response.json()
4employees

{'accounting': [{'firstName': 'John', 'lastName': 'Doe', 'age': 23},
  {'firstName': 'Mary', 'lastName': 'Smith', 'age': 32}],
 'sales': [{'firstName': 'Sally', 'lastName': 'Green', 'age': 27},
  {'firstName': 'Jim', 'lastName': 'Galley', 'age': 41}],
 'marketing': [{'firstName': 'Tom', 'lastName': 'Brown', 'age': 28}]}

1print("departments", employees.keys())

departments dict_keys(['accounting', 'sales', 'marketing'])

1acct_emp_df =pd.json_normalize(employees, record_path='accounting')
2acct_emp_df
firstName lastName age
0 John Doe 23
1 Mary Smith 32

Deduplication#

Sometimes after a pd.conact() you will have duplicate rows.

You can use df.drop_duplicates() to remove repeated rows of data.

Without an argument, this will scan the entire row of data to determine if the row is the same.

If your data has a natural key, you can specify that with the subset= named argument. This will improve performance.

1# example
2o1 = pd.read_csv("https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/dedupe/orders1.csv")
3o2 = pd.read_csv("https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/dedupe/orders2.csv")
4orders = pd.concat([o1, o2], ignore_index=True)
5orders.sort_values('orderid')
orderid orderdate custname custemail custcountry orderstatus ordertotal ordercreditcard ordershipvia shippingtotal
0 2 2023-03-24 Frayda Pepperd fpepperd0@sciencedaily.com Canada delivered 228.39 Discover RPS 12.05
10 2 2023-03-24 Frayda Pepperd fpepperd0@sciencedaily.com Canada delivered 228.39 Discover RPS 12.05
11 3 2020-02-23 Loy Siberry lsiberry1@so-net.ne.jp Canada delivered 76.87 Discover USPS 6.27
12 4 2022-04-28 Carree Henworth NaN Canada pending 152.30 Discover USPS 12.74
1 4 2022-04-28 Carree Henworth NaN Canada pending 152.30 Discover USPS 12.74
2 5 2019-11-22 Goldina Godsafe ggodsafe3@dailymail.co.uk United States shipped 182.17 Amex UPS 5.44
3 6 2022-05-03 Marris Chatten mchatten4@csmonitor.com Mexico pending 208.28 Discover RPS 2.16
13 6 2022-05-03 Marris Chatten mchatten4@csmonitor.com Mexico pending 208.28 Discover RPS 2.16
4 7 2022-12-19 Logan Jacobsson ljacobsson5@wufoo.com United States delivered 112.15 Amex USPS 11.52
14 7 2022-12-19 Logan Jacobsson ljacobsson5@wufoo.com United States delivered 112.15 Amex USPS 11.52
15 8 2019-06-05 Lilli Feares lfeares6@shop-pro.jp Mexico pending 237.90 Discover FedEX 4.48
16 9 2019-02-17 Lowrance Sigsworth lsigsworth7@youtube.com United States delivered 141.94 Discover USPS 7.31
17 10 2023-04-19 Libbi Spadari lspadari8@dot.gov Mexico pending 160.79 Discover RPS 16.52
5 10 2023-04-19 Libbi Spadari lspadari8@dot.gov Mexico pending 160.79 Discover RPS 16.52
6 11 2020-01-20 Renato Hue rhue9@un.org Canada delivered 120.52 Visa USPS 5.57
7 12 2022-03-03 Lucky Helstrip lhelstripa@tmall.com Mexico delivered 202.07 Amex UPS 18.57
18 12 2022-03-03 Lucky Helstrip lhelstripa@tmall.com Mexico delivered 202.07 Amex UPS 18.57
8 13 2021-09-04 Debi Myrie dmyrieb@unc.edu United States delivered 131.62 Amex UPS 2.37
19 13 2021-09-04 Debi Myrie dmyrieb@unc.edu United States delivered 131.62 Amex UPS 2.37
20 14 2022-02-27 Hyacinth Aveyard haveyardc@ucoz.com United States pending 209.86 Amex USPS 8.69
9 15 2019-01-11 Crin Blanket cblanketd@newsvine.com United States delivered 85.46 Visa UPS 14.22 
1# uses the entire row to check for duplicates
2orders.drop_duplicates().sort_values("orderid")
3
4# uses the natural key in the data set  to check for duplicates
5orders.drop_duplicates(subset="orderid").sort_values("orderid")
orderid orderdate custname custemail custcountry orderstatus ordertotal ordercreditcard ordershipvia shippingtotal
0 2 2023-03-24 Frayda Pepperd fpepperd0@sciencedaily.com Canada delivered 228.39 Discover RPS 12.05
11 3 2020-02-23 Loy Siberry lsiberry1@so-net.ne.jp Canada delivered 76.87 Discover USPS 6.27
1 4 2022-04-28 Carree Henworth NaN Canada pending 152.30 Discover USPS 12.74
2 5 2019-11-22 Goldina Godsafe ggodsafe3@dailymail.co.uk United States shipped 182.17 Amex UPS 5.44
3 6 2022-05-03 Marris Chatten mchatten4@csmonitor.com Mexico pending 208.28 Discover RPS 2.16
4 7 2022-12-19 Logan Jacobsson ljacobsson5@wufoo.com United States delivered 112.15 Amex USPS 11.52
15 8 2019-06-05 Lilli Feares lfeares6@shop-pro.jp Mexico pending 237.90 Discover FedEX 4.48
16 9 2019-02-17 Lowrance Sigsworth lsigsworth7@youtube.com United States delivered 141.94 Discover USPS 7.31
5 10 2023-04-19 Libbi Spadari lspadari8@dot.gov Mexico pending 160.79 Discover RPS 16.52
6 11 2020-01-20 Renato Hue rhue9@un.org Canada delivered 120.52 Visa USPS 5.57
7 12 2022-03-03 Lucky Helstrip lhelstripa@tmall.com Mexico delivered 202.07 Amex UPS 18.57
8 13 2021-09-04 Debi Myrie dmyrieb@unc.edu United States delivered 131.62 Amex UPS 2.37
20 14 2022-02-27 Hyacinth Aveyard haveyardc@ucoz.com United States pending 209.86 Amex USPS 8.69
9 15 2019-01-11 Crin Blanket cblanketd@newsvine.com United States delivered 85.46 Visa UPS 14.22

Returning a dataframe of duplicates#

To get a dataframe of just the duplicated values you can use the df.duplicated() boolean index.

This should be appled to the concatenated dataframe.

1dupes = orders.duplicated(subset=['orderid'])
2orders[dupes]
orderid orderdate custname custemail custcountry orderstatus ordertotal ordercreditcard ordershipvia shippingtotal
10 2 2023-03-24 Frayda Pepperd fpepperd0@sciencedaily.com Canada delivered 228.39 Discover RPS 12.05
12 4 2022-04-28 Carree Henworth NaN Canada pending 152.30 Discover USPS 12.74
13 6 2022-05-03 Marris Chatten mchatten4@csmonitor.com Mexico pending 208.28 Discover RPS 2.16
14 7 2022-12-19 Logan Jacobsson ljacobsson5@wufoo.com United States delivered 112.15 Amex USPS 11.52
17 10 2023-04-19 Libbi Spadari lspadari8@dot.gov Mexico pending 160.79 Discover RPS 16.52
18 12 2022-03-03 Lucky Helstrip lhelstripa@tmall.com Mexico delivered 202.07 Amex UPS 18.57
19 13 2021-09-04 Debi Myrie dmyrieb@unc.edu United States delivered 131.62 Amex UPS 2.37

Merges#

A Merge combines to dataframes based on a common column. The resulting dataframe is wider (has more columns) than the original dataframe.

pd.merge(left: pd.DataFrame, right:pd.Dataframe, how:str, left_on:str, right_on:str)

  • how specifies the join operation:

    • "inner" - returns ONLY rows when values of left_on match right_on

    • "left" - returns ALL rows from left and ONLY rows from right when values of left_on match right_on

    • "right" - returns ALL rows from right and ONLY rows from left when values of left_on match right_on

    • "outer" - returns ALL rows from left and right and rows when values of left_on match right_on

1bbplayers = pd.read_csv("https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/delimited/bbplayers.csv")
2bbplayers
player_id player_name career_pts player_team_id
0 101 Jordan 32292 1.0
1 102 Pippen 18940 1.0
2 103 Bryant 33643 2.0
3 104 O'Neal 28596 2.0
4 105 Fudge 0 NaN 
1bbteams = pd.read_csv("https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/delimited/bbteams.csv")
2bbteams
team_id team_name team_location
0 1 Bulls Chicago, IL
1 2 Lakers Los Angeles, CA
2 3 Tropics Flint, MI 
1# INNER join: ONLY rows that match the bbplayer.player_team_id and bbteam.team_id and bbteam will be included
2# we lose Player 105 and team 3 because there are no matches.
3combined = pd.merge(bbplayers, bbteams, left_on='player_team_id', right_on='team_id', how='inner')
4combined
player_id player_name career_pts player_team_id team_id team_name team_location
0 101 Jordan 32292 1.0 1 Bulls Chicago, IL
1 102 Pippen 18940 1.0 1 Bulls Chicago, IL
2 103 Bryant 33643 2.0 2 Lakers Los Angeles, CA
3 104 O'Neal 28596 2.0 2 Lakers Los Angeles, CA 
1# LEFT join:  All rows from the left (every player) and ONLY rows from team that match. 
2# we now see Player 105 despite no team match
3combined = pd.merge(bbplayers, bbteams, left_on='player_team_id', right_on='team_id', how='left')
4combined
player_id player_name career_pts player_team_id team_id team_name team_location
0 101 Jordan 32292 1.0 1.0 Bulls Chicago, IL
1 102 Pippen 18940 1.0 1.0 Bulls Chicago, IL
2 103 Bryant 33643 2.0 2.0 Lakers Los Angeles, CA
3 104 O'Neal 28596 2.0 2.0 Lakers Los Angeles, CA
4 105 Fudge 0 NaN NaN NaN NaN 
1# RIGHT join:  All rows from the right (every team) and ONLY rows from players that match. 
2# we now see Team 3 despite no player match
3combined = pd.merge(bbplayers, bbteams, left_on='player_team_id', right_on='team_id', how='right')
4combined
player_id player_name career_pts player_team_id team_id team_name team_location
0 101.0 Jordan 32292.0 1.0 1 Bulls Chicago, IL
1 102.0 Pippen 18940.0 1.0 1 Bulls Chicago, IL
2 103.0 Bryant 33643.0 2.0 2 Lakers Los Angeles, CA
3 104.0 O'Neal 28596.0 2.0 2 Lakers Los Angeles, CA
4 NaN NaN NaN NaN 3 Tropics Flint, MI 
1# OUTER join:  All rows from both tables are included. inner join + non-matching rows from both tables
2# we now see Team 3 and player 105 
3combined = pd.merge(bbplayers, bbteams, left_on='player_team_id', right_on='team_id', how='outer')
4combined
player_id player_name career_pts player_team_id team_id team_name team_location
0 101.0 Jordan 32292.0 1.0 1.0 Bulls Chicago, IL
1 102.0 Pippen 18940.0 1.0 1.0 Bulls Chicago, IL
2 103.0 Bryant 33643.0 2.0 2.0 Lakers Los Angeles, CA
3 104.0 O'Neal 28596.0 2.0 2.0 Lakers Los Angeles, CA
4 NaN NaN NaN NaN 3.0 Tropics Flint, MI
5 105.0 Fudge 0.0 NaN NaN NaN NaN

Challenge 3-3-2#

Who is not buying from minimart?#

Observe the following data: mafudge/datasets

You have been hired to build a UI to display names of customers who did not buy from minimart in any given month.

Provide a Streamlit UI with a drop down selection for the month. (jan, feb, mar, apr)

It then outputs a dataframe of customers who did not buy anything in that month.

HINT

Access the raw data from this base url: https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/minimart/