I can do it!!

He can do! She can do! why cannot me? i can do it!

개발/sk infosec cloud ai 전문가 양성과정

[pandas를 활용한 데이터분석]SK infosec 클라우드 AI 전문가 양성과정 실습과제

gogoriver 2020. 9. 8. 09:06
Ecommerce Purchases Exercise - 복사본

Import pandas and read in the Ecommerce Purchases csv file and set it to a DataFrame called ecom.

In [2]:
import pandas as pd

Check the head of the DataFrame.

In [4]:
df = pd.read_csv('C:/Users/ka030/Documents/GitHub/python_analysis/sources/Day4/workbook/Ecommerce Purchases.csv')
df.head()
Out[4]:
Address Lot AM or PM Browser Info Company Credit Card CC Exp Date CC Security Code CC Provider Email Job IP Address Language Purchase Price
0 16629 Pace Camp Apt. 448\r\nAlexisborough, NE ... 46 in PM Opera/9.56.(X11; Linux x86_64; sl-SI) Presto/2... Martinez-Herman 6011929061123406 02/20 900 JCB 16 digit pdunlap@yahoo.com Scientist, product/process development 149.146.147.205 el 98.14
1 9374 Jasmine Spurs Suite 508\r\nSouth John, TN... 28 rn PM Opera/8.93.(Windows 98; Win 9x 4.90; en-US) Pr... Fletcher, Richards and Whitaker 3337758169645356 11/18 561 Mastercard anthony41@reed.com Drilling engineer 15.160.41.51 fr 70.73
2 Unit 0065 Box 5052\r\nDPO AP 27450 94 vE PM Mozilla/5.0 (compatible; MSIE 9.0; Windows NT ... Simpson, Williams and Pham 675957666125 08/19 699 JCB 16 digit amymiller@morales-harrison.com Customer service manager 132.207.160.22 de 0.95
3 7780 Julia Fords\r\nNew Stacy, WA 45798 36 vm PM Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_0 ... Williams, Marshall and Buchanan 6011578504430710 02/24 384 Discover brent16@olson-robinson.info Drilling engineer 30.250.74.19 es 78.04
4 23012 Munoz Drive Suite 337\r\nNew Cynthia, TX... 20 IE AM Opera/9.58.(X11; Linux x86_64; it-IT) Presto/2... Brown, Watson and Andrews 6011456623207998 10/25 678 Diners Club / Carte Blanche christopherwright@gmail.com Fine artist 24.140.33.94 es 77.82

How many rows and columns are there?

In [5]:
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10000 entries, 0 to 9999
Data columns (total 14 columns):
Address             10000 non-null object
Lot                 10000 non-null object
AM or PM            10000 non-null object
Browser Info        10000 non-null object
Company             10000 non-null object
Credit Card         10000 non-null int64
CC Exp Date         10000 non-null object
CC Security Code    10000 non-null int64
CC Provider         10000 non-null object
Email               10000 non-null object
Job                 10000 non-null object
IP Address          10000 non-null object
Language            10000 non-null object
Purchase Price      10000 non-null float64
dtypes: float64(1), int64(2), object(11)
memory usage: 1.1+ MB

What is the average Purchase Price?

In [6]:
df['Purchase Price'].mean()
Out[6]:
50.34730200000025

What were the highest and lowest purchase prices?

In [7]:
df['Purchase Price'].max()
Out[7]:
99.99
In [8]:
df['Purchase Price'].min()
Out[8]:
0.0

How many people have English 'en' as their Language of choice on the website?

In [12]:
df[df['Language']=='en'].count()
Out[12]:
Address             1098
Lot                 1098
AM or PM            1098
Browser Info        1098
Company             1098
Credit Card         1098
CC Exp Date         1098
CC Security Code    1098
CC Provider         1098
Email               1098
Job                 1098
IP Address          1098
Language            1098
Purchase Price      1098
dtype: int64
In [94]:
Out[94]:
Address             1098
Lot                 1098
AM or PM            1098
Browser Info        1098
Company             1098
Credit Card         1098
CC Exp Date         1098
CC Security Code    1098
CC Provider         1098
Email               1098
Job                 1098
IP Address          1098
Language            1098
Purchase Price      1098
dtype: int64

How many people have the job title of "Lawyer" ?

In [16]:
df[df['Job']=='Lawyer'].count()
# df[df['Job']=='Lawyer'].value_counts()
Out[16]:
Address             30
Lot                 30
AM or PM            30
Browser Info        30
Company             30
Credit Card         30
CC Exp Date         30
CC Security Code    30
CC Provider         30
Email               30
Job                 30
IP Address          30
Language            30
Purchase Price      30
dtype: int64
In [95]:
 

<class 'pandas.core.frame.DataFrame'>
Int64Index: 30 entries, 470 to 9979
Data columns (total 14 columns):
Address             30 non-null object
Lot                 30 non-null object
AM or PM            30 non-null object
Browser Info        30 non-null object
Company             30 non-null object
Credit Card         30 non-null int64
CC Exp Date         30 non-null object
CC Security Code    30 non-null int64
CC Provider         30 non-null object
Email               30 non-null object
Job                 30 non-null object
IP Address          30 non-null object
Language            30 non-null object
Purchase Price      30 non-null float64
dtypes: float64(1), int64(2), object(11)
memory usage: 3.5+ KB

How many people made the purchase during the AM and how many people made the purchase during PM ?

(Hint: Check out value_counts() )

In [17]:
df['AM or PM'].value_counts()
Out[17]:
PM    5068
AM    4932
Name: AM or PM, dtype: int64
In [96]:
Out[96]:
PM    5068
AM    4932
Name: AM or PM, dtype: int64

What are the 5 most common Job Titles?

In [19]:
df['Job'].value_counts().head()
Out[19]:
Interior and spatial designer        31
Lawyer                               30
Social researcher                    28
Purchasing manager                   27
Research officer, political party    27
Name: Job, dtype: int64
In [97]:
Out[97]:
Interior and spatial designer    31
Lawyer                           30
Social researcher                28
Purchasing manager               27
Designer, jewellery              27
Name: Job, dtype: int64

Someone made a purchase that came from Lot: "90 WT" , what was the Purchase Price for this transaction?

In [20]:
df[df['Lot']=="90 WT"]['Purchase Price']
Out[20]:
513    75.1
Name: Purchase Price, dtype: float64
In [99]:
Out[99]:
513    75.1
Name: Purchase Price, dtype: float64

What is the email of the person with the following Credit Card Number: 4926535242672853

In [22]:
df[df['Credit Card']==4926535242672853]['Email']
Out[22]:
1234    bondellen@williams-garza.com
Name: Email, dtype: object
In [100]:
Out[100]:
1234    bondellen@williams-garza.com
Name: Email, dtype: object

How many people have American Express as their Credit Card Provider and made a purchase above $95 ?

In [26]:
df[(df['Purchase Price']>95) & (df['CC Provider']=="American Express")].count()
Out[26]:
Address             39
Lot                 39
AM or PM            39
Browser Info        39
Company             39
Credit Card         39
CC Exp Date         39
CC Security Code    39
CC Provider         39
Email               39
Job                 39
IP Address          39
Language            39
Purchase Price      39
dtype: int64
In [101]:
Out[101]:
Address             39
Lot                 39
AM or PM            39
Browser Info        39
Company             39
Credit Card         39
CC Exp Date         39
CC Security Code    39
CC Provider         39
Email               39
Job                 39
IP Address          39
Language            39
Purchase Price      39
dtype: int64

Hard: How many people have a credit card that expires in 2025?

In [27]:
df['CC Exp Date']
Out[27]:
0       02/20
1       11/18
2       08/19
3       02/24
4       10/25
        ...  
9995    03/22
9996    07/25
9997    05/21
9998    11/17
9999    02/19
Name: CC Exp Date, Length: 10000, dtype: object
In [38]:
def cc_expire(x):
    if '25' in x.split('/'):
#         print(x)
        return True
    else:
        return False
# cc_expire('02/25')
sum(df['CC Exp Date'].apply(lambda x: cc_expire(x)))
Out[38]:
1033
In [102]:
Out[102]:
1033

Hard: What are the top 5 most popular email providers/hosts (e.g. gmail.com, yahoo.com, etc...)

In [39]:
df['Email']
Out[39]:
0                    pdunlap@yahoo.com
1                   anthony41@reed.com
2       amymiller@morales-harrison.com
3          brent16@olson-robinson.info
4          christopherwright@gmail.com
                     ...              
9995            iscott@wade-garner.com
9996                mary85@hotmail.com
9997                 tyler16@gmail.com
9998           elizabethmoore@reid.net
9999             rachelford@vaughn.com
Name: Email, Length: 10000, dtype: object
In [55]:
email_prov =[]

def e_provider(x):
    result = x.split('@')
    email_prov.append(result[1])

df['Email'].apply(lambda x:e_provider(x) )
# print(email_prov)
df['email_provider'] = email_prov
df.head()
Out[55]:
Address Lot AM or PM Browser Info Company Credit Card CC Exp Date CC Security Code CC Provider Email Job IP Address Language Purchase Price email_provier email_provider
0 16629 Pace Camp Apt. 448\r\nAlexisborough, NE ... 46 in PM Opera/9.56.(X11; Linux x86_64; sl-SI) Presto/2... Martinez-Herman 6011929061123406 02/20 900 JCB 16 digit pdunlap@yahoo.com Scientist, product/process development 149.146.147.205 el 98.14 None yahoo.com
1 9374 Jasmine Spurs Suite 508\r\nSouth John, TN... 28 rn PM Opera/8.93.(Windows 98; Win 9x 4.90; en-US) Pr... Fletcher, Richards and Whitaker 3337758169645356 11/18 561 Mastercard anthony41@reed.com Drilling engineer 15.160.41.51 fr 70.73 None reed.com
2 Unit 0065 Box 5052\r\nDPO AP 27450 94 vE PM Mozilla/5.0 (compatible; MSIE 9.0; Windows NT ... Simpson, Williams and Pham 675957666125 08/19 699 JCB 16 digit amymiller@morales-harrison.com Customer service manager 132.207.160.22 de 0.95 None morales-harrison.com
3 7780 Julia Fords\r\nNew Stacy, WA 45798 36 vm PM Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_0 ... Williams, Marshall and Buchanan 6011578504430710 02/24 384 Discover brent16@olson-robinson.info Drilling engineer 30.250.74.19 es 78.04 None olson-robinson.info
4 23012 Munoz Drive Suite 337\r\nNew Cynthia, TX... 20 IE AM Opera/9.58.(X11; Linux x86_64; it-IT) Presto/2... Brown, Watson and Andrews 6011456623207998 10/25 678 Diners Club / Carte Blanche christopherwright@gmail.com Fine artist 24.140.33.94 es 77.82 None gmail.com
In [57]:
df['email_provider'].value_counts().head()
Out[57]:
hotmail.com     1638
yahoo.com       1616
gmail.com       1605
smith.com         42
williams.com      37
Name: email_provider, dtype: int64
In [56]:
Out[56]:
hotmail.com     1638
yahoo.com       1616
gmail.com       1605
smith.com         42
williams.com      37
Name: Email, dtype: int64

Great Job!

저작자표시

'개발 > sk infosec cloud ai 전문가 양성과정' 카테고리의 다른 글

[CNN&비지도학습]SK infosec 클라우드 AI 전문가 양성과정 실습  (0) 2020.09.08
[CNN&비지도학습]SK infosec 클라우드 AI 전문가 양성과정 수업필기본  (0) 2020.09.08
[pandas를 활용한 데이터분석]SK infosec 클라우드 AI 전문가 양성과정 수업필기본  (0) 2020.09.08
[Pandas를 이용한 데이터 분석mnist-fashion, svm, decision tree]SK infosec 클라우드 AI 전문가 양성과정 수업 실습내용  (0) 2020.09.08
[PYTHON데이터분석 2020/09/07-2] SK infosec 클라우드 AI 전문가 양성과정 수업필기본  (0) 2020.09.07

'개발/sk infosec cloud ai 전문가 양성과정'의 다른글

  • 현재글[pandas를 활용한 데이터분석]SK infosec 클라우드 AI 전문가 양성과정 실습과제

관련글

티스토리툴바