Merge & Concat

Lesson 17 of 19 in Coddy's Pandas Analytics course.

In the real world, data often comes from multiple tables or files. To connect and analyze these data, we must be able to combine datasets. To combine dataframe, we can use .merge(), .join(), and .concat().

.merge() is similar to the SQL JOIN operation; it connects columns or indexes in a dataframe based on one or more keys:

merged_df = df1.merge(df2, on='common_column')

For example, these two tables:

ID	VALUE
1	"val1"
2	"val2

ID	POINTS
1	9

Will become:

ID	VALUE	POINTS
1	"val1"	9

.concat() function is used to append rows of one dataframe to the end of another dataframe, returning a new dataframe. This operation is similar to the 'UNION' operation in SQL:

concatenated_df = pd.concat([df1, df2])

Challenge

Easy

The CSV file visits.csv contains information about how many visits were made at a particular location at a particular time.

Here is the first 5 lines of the file:

location_id,visits,timestamp
8,1771,27498
9,4187,79919
0,4959,54228
6,7721,41588

Calculate the min, max, mean, std, sum, and median for each location id and combine them into one dataframe. Rename the columns to:

sum_visits, min_visits, max_visits, mean_visits, std_visits and median_visits.

The final dataframe should look like:

location_id	sum_visits	min_visits	max_visits	mean_visits	std_visits	median_visits
...	...	...	...	...	...	...

Save the result to df.

Try it yourself

# pandas as pd is already imported
df = pd.read_csv("./visits.csv")
sum_visits = df.groupby("location_id").sum()
min_visits = df.groupby("location_id").min()
max_visits = df.groupby("location_id").max()
mean_visits = df.groupby("location_id").mean()
std_visits = df.groupby("location_id").std()
median_visits = df.groupby("location_id").median()
# Write your code below

All lessons in Pandas Analytics

1Introduction

Why use Pandas?Data Structures in Pandas Loading Data into a DataFrame

4Data Analysis with Pandas

Descriptive Statistics Grouping and Aggregating Data Different AggregationsMerge & Concat

2Working with the DataFrame

Understanding DataFrames Accessing Data Data Cleaning - Missing data Data Cleaning - More tools

5Challenges

Answer Seeker Car to Buy

3Data Manipulation with Pandas

Return Requested Result Filter Data Add & Delete Modify Data Modify Strings Custom Modifications