Practice #3 PivotTable

1. Read a excel file : read_excel()

import pandas as pd

df1 = pd.read_excel("E03EXAMPLE.xlsx", sheet_name=1)
df1

	Name	Cafe Name	Drink	Quantity
0	Louis	Starbucks	Americano	1
1	Harvey	CoffeeBean	CafeLatte	1
2	G-dragon	CoffeeBean	IcedTea	1
3	Lola	Starbucks	IcedTea	1
4	Jorge	CoffeeBean	Americano	1
5	Piona	CoffeeBean	CafeLatte	1
6	Mitchy	CoffeeBean	CafeLatte	1
7	Fibio	Starbucks	IcedTea	1
8	Kim	CoffeeBean	IcedTea	1
9	Stacy	CoffeeBean	Americano	1
10	Grace	CoffeeBean	Americano	1
11	TL	CoffeeBean	IcedTea	1
12	Sanchez	CoffeeBean	CafeLatte	1
13	Zhen	Starbucks	Americano	1
14	James	Starbucks	CafeLatte	1
15	Coline	Starbucks	IcedTea	1
16	Gorila	Starbucks	CafeLatte	1
17	Sunny	CoffeeBean	IcedTea	1
18	Conner	Starbucks	CafeLatte	1

df2 = pd.read_excel("E03EXAMPLE.xlsx", sheet_name=2)
df2

	Cafe Name	Drink	Price
0	Starbucks	Americano	4400
1	Starbucks	CafeLatte	4700
2	Starbucks	IcedTea	5000
3	CoffeeBean	Americano	4900
4	CoffeeBean	CafeLatte	4200
5	CoffeeBean	IcedTea	4500

2. Merge two dataframes : merge()

df3 = df1.merge(df2, how='left')
df3

	Name	Cafe Name	Drink	Quantity	Price
0	Louis	Starbucks	Americano	1	4400
1	Harvey	CoffeeBean	CafeLatte	1	4200
2	G-dragon	CoffeeBean	IcedTea	1	4500
3	Lola	Starbucks	IcedTea	1	5000
4	Jorge	CoffeeBean	Americano	1	4900
5	Piona	CoffeeBean	CafeLatte	1	4200
6	Mitchy	CoffeeBean	CafeLatte	1	4200
7	Fibio	Starbucks	IcedTea	1	5000
8	Kim	CoffeeBean	IcedTea	1	4500
9	Stacy	CoffeeBean	Americano	1	4900
10	Grace	CoffeeBean	Americano	1	4900
11	TL	CoffeeBean	IcedTea	1	4500
12	Sanchez	CoffeeBean	CafeLatte	1	4200
13	Zhen	Starbucks	Americano	1	4400
14	James	Starbucks	CafeLatte	1	4700
15	Coline	Starbucks	IcedTea	1	5000
16	Gorila	Starbucks	CafeLatte	1	4700
17	Sunny	CoffeeBean	IcedTea	1	4500
18	Conner	Starbucks	CafeLatte	1	4700

3. Restructuring the chart : pivot_table()

pdf1 = df3.pivot_table("Quantity", index=["Cafe Name", "Drink"], aggfunc="count")
pdf1

		Quantity
Cafe Name	Drink
CoffeeBean	Americano	3
	CafeLatte	4
	IcedTea	4
Starbucks	Americano	2
	CafeLatte	3
	IcedTea	3

pdf2 = df3.pivot_table("Price", index="Cafe Name", aggfunc="sum")
pdf2

	Price
Cafe Name
CoffeeBean	49500
Starbucks	37900

4. Copy to the clipboard : to_clipboar()

pdf1.to_clipboard(index=False)

pdf2.to_clipboard(index=False)

5. Total code

import pandas as pd

df1 = pd.read_excel("E03EXAMPLE.xlsx", sheet_name=1)
df2 = pd.read_excel("E03EXAMPLE.xlsx", sheet_name=2)
df3 = df1.merge(df2, how="left")
pdf1 = df3.pivot_table("Quantity", index=["Cafe Name", "Drink"], aggfunc="count")
pdf2 = df3.pivot_table("Price", index="Cafe Name", aggfunc="sum")
pdf1.to_clipboard(index=False)
pdf2.to_cllpboard(index=False)