In [1]:
%matplotlib inline
import pandas as pd
In [2]:
from IPython.core.display import HTML
css = open('style-table.css').read() + open('style-notebook.css').read()
HTML('<style>{}</style>'.format(css))
Out[2]:
In [3]:
sales1 = pd.read_csv('sales1.csv')
sales1
Out[3]:
Book title
Number sold
Sales price
Royalty paid
0
The Bricklayer’s Bible
8
2.99
0.55
1
Swimrand
2
1.99
0.35
2
Pining For The Fisheries of Yore
28
2.99
0.55
3
The Duck Goes Here
34
2.99
0.55
4
The Tower Commission Report
4
11.50
4.25
In [4]:
sales2 = pd.read_csv('sales2.csv')
# sales2.fillna('')
sales2.fillna('')
Out[4]:
Title
Units sold
List price
Royalty
0
1
Sales report for Q4
2
E-Book Reader US Store
3
Pining for the Fisheries of Yore
80
3.5
14.98
4
Swimrand
1
2.99
0.14
5
The Bricklayer's Bible
17
3.5
5.15
6
The Duck Goes Here
34
2.99
5.78
7
The Tower Commission Report
4
9.5
6.2
8
US royalties (USD)
32.25
9
10
11
Sales report for Q4
12
E-Book Reader UK Store
13
Pining for the Fisheries of Yore
47
2.99
11.98
14
The Bricklayer's Bible
17
2.99
3.5
15
The Tower Commission Report
4
6.5
4.8
16
UK royalties (GBP)
20.28
17
18
19
Sales report for Q4
20
E-Book Reader France Store
21
Swimrand
8
1.99
0.88
22
The Duck Goes Here
12
1.99
1.5
23
France royalties (EUR)
2.38
In [ ]:
In [ ]:
In [5]:
col = sales2.Title.str.extract(r'\((.*)\)')
sales2['currency'] = col.fillna(method='bfill')
sales2
Out[5]:
Title
Units sold
List price
Royalty
currency
0
NaN
NaN
NaN
NaN
USD
1
Sales report for Q4
NaN
NaN
NaN
USD
2
E-Book Reader US Store
NaN
NaN
NaN
USD
3
Pining for the Fisheries of Yore
80
3.50
14.98
USD
4
Swimrand
1
2.99
0.14
USD
5
The Bricklayer's Bible
17
3.50
5.15
USD
6
The Duck Goes Here
34
2.99
5.78
USD
7
The Tower Commission Report
4
9.50
6.20
USD
8
US royalties (USD)
NaN
NaN
32.25
USD
9
NaN
NaN
NaN
NaN
GBP
10
NaN
NaN
NaN
NaN
GBP
11
Sales report for Q4
NaN
NaN
NaN
GBP
12
E-Book Reader UK Store
NaN
NaN
NaN
GBP
13
Pining for the Fisheries of Yore
47
2.99
11.98
GBP
14
The Bricklayer's Bible
17
2.99
3.50
GBP
15
The Tower Commission Report
4
6.50
4.80
GBP
16
UK royalties (GBP)
NaN
NaN
20.28
GBP
17
NaN
NaN
NaN
NaN
EUR
18
NaN
NaN
NaN
NaN
EUR
19
Sales report for Q4
NaN
NaN
NaN
EUR
20
E-Book Reader France Store
NaN
NaN
NaN
EUR
21
Swimrand
8
1.99
0.88
EUR
22
The Duck Goes Here
12
1.99
1.50
EUR
23
France royalties (EUR)
NaN
NaN
2.38
EUR
In [36]:
Result = pd.merge(sales2, sales1, left_on = 'Title',right_on = 'Book title', how ='outer')
Result
# # Result = Result.fillna(0)['List price']
Result['total'] = Result.fillna(0)['Units sold']*Result.fillna(0)['List price'] + Result.fillna(0)['Number sold']*Result.fillna(0)['Sales price']
Result
# sales2
Out[36]:
Title
Units sold
List price
Royalty
currency
Book title
Number sold
Sales price
Royalty paid
total
0
NaN
NaN
NaN
NaN
USD
NaN
NaN
NaN
NaN
0.00
1
NaN
NaN
NaN
NaN
GBP
NaN
NaN
NaN
NaN
0.00
2
NaN
NaN
NaN
NaN
GBP
NaN
NaN
NaN
NaN
0.00
3
NaN
NaN
NaN
NaN
EUR
NaN
NaN
NaN
NaN
0.00
4
NaN
NaN
NaN
NaN
EUR
NaN
NaN
NaN
NaN
0.00
5
Sales report for Q4
NaN
NaN
NaN
USD
NaN
NaN
NaN
NaN
0.00
6
Sales report for Q4
NaN
NaN
NaN
GBP
NaN
NaN
NaN
NaN
0.00
7
Sales report for Q4
NaN
NaN
NaN
EUR
NaN
NaN
NaN
NaN
0.00
8
E-Book Reader US Store
NaN
NaN
NaN
USD
NaN
NaN
NaN
NaN
0.00
9
Pining for the Fisheries of Yore
80
3.50
14.98
USD
NaN
NaN
NaN
NaN
280.00
10
Pining for the Fisheries of Yore
47
2.99
11.98
GBP
NaN
NaN
NaN
NaN
140.53
11
Swimrand
1
2.99
0.14
USD
Swimrand
2
1.99
0.35
6.97
12
Swimrand
8
1.99
0.88
EUR
Swimrand
2
1.99
0.35
19.90
13
The Bricklayer's Bible
17
3.50
5.15
USD
NaN
NaN
NaN
NaN
59.50
14
The Bricklayer's Bible
17
2.99
3.50
GBP
NaN
NaN
NaN
NaN
50.83
15
The Duck Goes Here
34
2.99
5.78
USD
The Duck Goes Here
34
2.99
0.55
203.32
16
The Duck Goes Here
12
1.99
1.50
EUR
The Duck Goes Here
34
2.99
0.55
125.54
17
The Tower Commission Report
4
9.50
6.20
USD
The Tower Commission Report
4
11.50
4.25
84.00
18
The Tower Commission Report
4
6.50
4.80
GBP
The Tower Commission Report
4
11.50
4.25
72.00
19
US royalties (USD)
NaN
NaN
32.25
USD
NaN
NaN
NaN
NaN
0.00
20
E-Book Reader UK Store
NaN
NaN
NaN
GBP
NaN
NaN
NaN
NaN
0.00
21
UK royalties (GBP)
NaN
NaN
20.28
GBP
NaN
NaN
NaN
NaN
0.00
22
E-Book Reader France Store
NaN
NaN
NaN
EUR
NaN
NaN
NaN
NaN
0.00
23
France royalties (EUR)
NaN
NaN
2.38
EUR
NaN
NaN
NaN
NaN
0.00
24
NaN
NaN
NaN
NaN
NaN
The Bricklayer’s Bible
8
2.99
0.55
23.92
25
NaN
NaN
NaN
NaN
NaN
Pining For The Fisheries of Yore
28
2.99
0.55
83.72
In [38]:
Result.groupby(['Title','currency','total']).size()
Out[38]:
Title currency total
E-Book Reader France Store EUR 0.00 1
E-Book Reader UK Store GBP 0.00 1
E-Book Reader US Store USD 0.00 1
France royalties (EUR) EUR 0.00 1
Pining for the Fisheries of Yore GBP 140.53 1
USD 280.00 1
Sales report for Q4 EUR 0.00 1
GBP 0.00 1
USD 0.00 1
Swimrand EUR 19.90 1
USD 6.97 1
The Bricklayer's Bible GBP 50.83 1
USD 59.50 1
The Duck Goes Here EUR 125.54 1
USD 203.32 1
The Tower Commission Report GBP 72.00 1
USD 84.00 1
UK royalties (GBP) GBP 0.00 1
US royalties (USD) USD 0.00 1
dtype: int64
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
Content source: climberwb/pycon-pandas-tutorial
Similar notebooks: