-
Pandas Basics 5
-
Pandas BasicsLecture1.1
-
Pandas DataFramesLecture1.2
-
ApplyLecture1.3
-
Boolean IndexingLecture1.4
-
Null ValuesLecture1.5
-
-
Data Transformations 6
-
Log TransformationLecture2.1
-
ClipLecture2.2
-
StackLecture2.3
-
Multi-IndexLecture2.4
-
PivotLecture2.5
-
Window FunctionsLecture2.6
-
-
Statistics 4
-
IntroductionLecture3.1
-
Descriptive StatisticsLecture3.2
-
Bollinger BandsLecture3.3
-
Bollinger Bands Part 2Lecture3.4
-
-
Reading and Writing Data 3
-
Reading and Writing DataLecture4.1
-
Using osLecture4.2
-
Reading in ChunksLecture4.3
-
-
Joins 5
-
IntroductionLecture5.1
-
JoinsLecture5.2
-
Joining with DuplicatesLecture5.3
-
Multi-index JoinsLecture5.4
-
Column CollisionsLecture5.5
-
-
Grouping 4
-
IntroductionLecture6.1
-
Combining Groupby and JoinLecture6.2
-
Multi-index GroupingLecture6.3
-
Grouping and ApplyLecture6.4
-
-
Introduction to Numpy 4
-
IntroductionLecture7.1
-
Extending to 2 DimensionsLecture7.2
-
Extending to 3 DimensionsLecture7.3
-
ReshapingLecture7.4
-
-
Randomness 2
-
Random SeedLecture8.1
-
Random FunctionsLecture8.2
-
-
Numpy Data Functionality 1
-
Numpy Data FunctionalityLecture9.1
-
Combining Groupby and Join
Example of Using Groupby and Join¶
You can combine this with a join to do something like find the percent of daily sales that each row is contributing. The steps are the following:
- Find the total sales by day
In [5]:
#Find the daily sales
daily_sales = sales.groupby('Day')['Sales'].sum()
print(daily_sales)
Day
1 313
2 283
3 241
Name: Sales, dtype: int64
- Give this series a name to be called when we join it.
In [6]:
#Give the name to the series
daily_sales.name = "Total Daily Sales"
print(daily_sales)
Day
1 313
2 283
3 241
Name: Total Daily Sales, dtype: int64
- Join this data into our main dataset. Keep in mind that daily_sales has an index of day, but there is no index for our main data. Instead of setting the index on our main data we can use the keyword on and give the column to use as the index.
In [7]:
#Join the daily sales data
sales = sales.join(daily_sales, on="Day")
print(sales)
Day Store Product Sales Total Daily Sales
0 1 1 1 10 313
1 1 1 2 74 313
2 1 1 3 27 313
3 1 2 1 41 313
4 1 2 2 66 313
5 1 2 3 95 313
6 2 1 1 1 283
7 2 1 2 23 283
8 2 1 3 67 283
9 2 2 1 86 283
10 2 2 2 87 283
11 2 2 3 19 283
12 3 1 1 6 241
13 3 1 2 30 241
14 3 1 3 55 241
15 3 2 1 68 241
16 3 2 2 32 241
17 3 2 3 50 241
- Create a new column which divides sales by total daily sales.
In [8]:
sales['Percent of Daily Sales'] = sales['Sales'] / sales['Total Daily Sales']
print(sales)
Day Store Product Sales Total Daily Sales Percent of Daily Sales
0 1 1 1 10 313 0.031949
1 1 1 2 74 313 0.236422
2 1 1 3 27 313 0.086262
3 1 2 1 41 313 0.130990
4 1 2 2 66 313 0.210863
5 1 2 3 95 313 0.303514
6 2 1 1 1 283 0.003534
7 2 1 2 23 283 0.081272
8 2 1 3 67 283 0.236749
9 2 2 1 86 283 0.303887
10 2 2 2 87 283 0.307420
11 2 2 3 19 283 0.067138
12 3 1 1 6 241 0.024896
13 3 1 2 30 241 0.124481
14 3 1 3 55 241 0.228216
15 3 2 1 68 241 0.282158
16 3 2 2 32 241 0.132780
17 3 2 3 50 241 0.207469
Prev
Introduction
Next
Multi-index Grouping