Pandas Practice Set 2

In [1]:

import numpy as np
import pandas as pd

In [2]:

all_ages = pd.read_csv("all-ages.csv")

In [3]:

recent_grads = pd.read_csv("recent-grads.csv")

In [4]:

all_ages.head()

Out[4]:

	Major_code	Major	Major_category	Total	Employed	Employed_full_time_year_round	Unemployed	Unemployment_rate	Median	P25th	P75th
0	1100	GENERAL AGRICULTURE	Agriculture & Natural Resources	128148	90245	74078	2423	0.026147	50000	34000	80000.0
1	1101	AGRICULTURE PRODUCTION AND MANAGEMENT	Agriculture & Natural Resources	95326	76865	64240	2266	0.028636	54000	36000	80000.0
2	1102	AGRICULTURAL ECONOMICS	Agriculture & Natural Resources	33955	26321	22810	821	0.030248	63000	40000	98000.0
3	1103	ANIMAL SCIENCES	Agriculture & Natural Resources	103549	81177	64937	3619	0.042679	46000	30000	72000.0
4	1104	FOOD SCIENCE	Agriculture & Natural Resources	24280	17281	12722	894	0.049188	62000	38500	90000.0

In [5]:

all_ages.describe()

Out[5]:

	Major_code	Total	Employed	Employed_full_time_year_round	Unemployed	Unemployment_rate	Median	P25th	P75th
count	173.000000	1.730000e+02	1.730000e+02	1.730000e+02	173.000000	173.000000	173.000000	173.000000	173.000000
mean	3879.815029	2.302566e+05	1.661620e+05	1.263078e+05	9725.034682	0.057355	56816.184971	38697.109827	82506.358382
std	1687.753140	4.220685e+05	3.073244e+05	2.424254e+05	18022.040192	0.019177	14706.226865	9414.524761	20805.330126
min	1100.000000	2.396000e+03	1.492000e+03	1.093000e+03	0.000000	0.000000	35000.000000	24900.000000	45800.000000
25%	2403.000000	2.428000e+04	1.728100e+04	1.272200e+04	1101.000000	0.046261	46000.000000	32000.000000	70000.000000
50%	3608.000000	7.579100e+04	5.656400e+04	3.961300e+04	3619.000000	0.054719	53000.000000	36000.000000	80000.000000
75%	5503.000000	2.057630e+05	1.428790e+05	1.110250e+05	8862.000000	0.069043	65000.000000	42000.000000	95000.000000
max	6403.000000	3.123510e+06	2.354398e+06	1.939384e+06	147261.000000	0.156147	125000.000000	78000.000000	210000.000000

In [6]:

recent_grads.head()

Out[6]:

	Rank	Major_code	Major	Major_category	Total	Sample_size	Men	Women	ShareWomen	Employed	...	Part_time	Full_time_year_round	Unemployed	Unemployment_rate	Median	P25th	P75th	College_jobs	Non_college_jobs	Low_wage_jobs
0	1	2419	PETROLEUM ENGINEERING	Engineering	2339	36	2057	282	0.120564	1976	...	270	1207	37	0.018381	110000	95000	125000	1534	364	193
1	2	2416	MINING AND MINERAL ENGINEERING	Engineering	756	7	679	77	0.101852	640	...	170	388	85	0.117241	75000	55000	90000	350	257	50
2	3	2415	METALLURGICAL ENGINEERING	Engineering	856	3	725	131	0.153037	648	...	133	340	16	0.024096	73000	50000	105000	456	176	0
3	4	2417	NAVAL ARCHITECTURE AND MARINE ENGINEERING	Engineering	1258	16	1123	135	0.107313	758	...	150	692	40	0.050125	70000	43000	80000	529	102	0
4	5	2405	CHEMICAL ENGINEERING	Engineering	32260	289	21239	11021	0.341631	25694	...	5180	16697	1672	0.061098	65000	50000	75000	18314	4440	972

5 rows × 21 columns

In [7]:

recent_grads.describe()

/Users/arunprakash/anaconda/lib/python3.5/site-packages/numpy/lib/function_base.py:3834: RuntimeWarning: Invalid value encountered in percentile
  RuntimeWarning)

Out[7]:

	Rank	Major_code	Total	Sample_size	Men	Women	ShareWomen	Employed	Full_time	Part_time	Full_time_year_round	Unemployed	Unemployment_rate	Median	P25th	P75th	College_jobs	Non_college_jobs	Low_wage_jobs
count	173.000000	173.000000	173.000000	173.000000	173.000000	173.000000	173.000000	173.000000	173.000000	173.000000	173.000000	173.000000	172.000000	173.000000	173.000000	173.000000	173.000000	173.000000	173.000000
mean	87.000000	3879.815029	39167.716763	356.080925	16637.358382	22530.358382	0.522550	31192.763006	26029.306358	8832.398844	19694.427746	2416.329480	0.068587	40151.445087	29501.445087	51494.219653	12322.635838	13284.497110	3859.017341
std	50.084928	1687.753140	63354.613919	618.361022	28063.394844	40966.381219	0.230572	50675.002241	42869.655092	14648.179473	33160.941514	4112.803148	0.029967	11470.181802	9166.005235	14906.279740	21299.868863	23789.655363	6944.998579
min	1.000000	1100.000000	124.000000	2.000000	119.000000	0.000000	0.000000	0.000000	111.000000	0.000000	111.000000	0.000000	0.000000	22000.000000	18500.000000	22000.000000	0.000000	0.000000	0.000000
25%	44.000000	2403.000000	4361.000000	39.000000	2110.000000	1784.000000	0.339671	3608.000000	3154.000000	1030.000000	2453.000000	304.000000	NaN	33000.000000	24000.000000	42000.000000	1675.000000	1591.000000	340.000000
50%	87.000000	3608.000000	15058.000000	130.000000	5347.000000	8284.000000	0.535714	11797.000000	10048.000000	3299.000000	7413.000000	893.000000	NaN	36000.000000	27000.000000	47000.000000	4390.000000	4595.000000	1231.000000
75%	130.000000	5503.000000	38844.000000	338.000000	14440.000000	22456.000000	0.702020	31433.000000	25147.000000	9948.000000	16891.000000	2393.000000	NaN	45000.000000	33000.000000	60000.000000	14444.000000	11783.000000	3466.000000
max	173.000000	6403.000000	393735.000000	4212.000000	173809.000000	307087.000000	0.968954	307933.000000	251540.000000	115172.000000	199897.000000	28169.000000	0.177226	110000.000000	95000.000000	125000.000000	151643.000000	148395.000000	48207.000000

Summarizing eacy categories¶

In [8]:

aa_cat_counts = dict()
rg_cat_counts = dict()


all_cat = all_ages["Major_category"].unique()
all_rec = recent_grads["Major_category"].unique()

In [9]:

all_cat

Out[9]:

array(['Agriculture & Natural Resources', 'Biology & Life Science',
       'Engineering', 'Humanities & Liberal Arts',
       'Communications & Journalism', 'Computers & Mathematics',
       'Industrial Arts & Consumer Services', 'Education',
       'Law & Public Policy', 'Interdisciplinary', 'Health',
       'Social Science', 'Physical Sciences', 'Psychology & Social Work',
       'Arts', 'Business'], dtype=object)

In [10]:

all_rec

Out[10]:

array(['Engineering', 'Business', 'Physical Sciences',
       'Law & Public Policy', 'Computers & Mathematics',
       'Agriculture & Natural Resources',
       'Industrial Arts & Consumer Services', 'Arts', 'Health',
       'Social Science', 'Biology & Life Science', 'Education',
       'Humanities & Liberal Arts', 'Psychology & Social Work',
       'Communications & Journalism', 'Interdisciplinary'], dtype=object)

In [11]:

for i in range(1,len(recent_grads)):
    if recent_grads.loc[i,"Major_category"] in rg_cat_counts:
        val = rg_cat_counts[recent_grads.loc[i,"Major_category"]]
        rg_cat_counts[recent_grads.loc[i,"Major_category"]] = recent_grads.loc[i,"Total"] +val
    else:
        rg_cat_counts[recent_grads.loc[i,"Major_category"]] = recent_grads.loc[i,"Total"]
        
for i in range(1,len(all_ages)):
    if all_ages.loc[i,"Major_category"] in aa_cat_counts:
        val = aa_cat_counts[all_ages.loc[i,"Major_category"]]
        aa_cat_counts[all_ages.loc[i,"Major_category"]] = all_ages.loc[i,"Total"] +val
    else:
        aa_cat_counts[all_ages.loc[i,"Major_category"]] = all_ages.loc[i,"Total"]
    

In [12]:

aa_cat_counts

Out[12]:

{'Agriculture & Natural Resources': 504289,
 'Arts': 1805865,
 'Biology & Life Science': 1338186,
 'Business': 9858741,
 'Communications & Journalism': 1803822,
 'Computers & Mathematics': 1781378,
 'Education': 4700118,
 'Engineering': 3576013,
 'Health': 2950859,
 'Humanities & Liberal Arts': 3738335,
 'Industrial Arts & Consumer Services': 1033798,
 'Interdisciplinary': 45199,
 'Law & Public Policy': 902926,
 'Physical Sciences': 1025318,
 'Psychology & Social Work': 1987278,
 'Social Science': 2654125}

In [13]:

rg_cat_counts

Out[13]:

{'Agriculture & Natural Resources': 79981,
 'Arts': 357130,
 'Biology & Life Science': 453862,
 'Business': 1302376,
 'Communications & Journalism': 392601,
 'Computers & Mathematics': 299008,
 'Education': 559129,
 'Engineering': 535244,
 'Health': 463230,
 'Humanities & Liberal Arts': 713468,
 'Industrial Arts & Consumer Services': 229792,
 'Interdisciplinary': 12296,
 'Law & Public Policy': 179107,
 'Physical Sciences': 185479,
 'Psychology & Social Work': 481007,
 'Social Science': 529966}

In [14]:

# Unique values in Major_category column.
print(all_ages['Major_category'].unique())

aa_cat_counts = dict()
rg_cat_counts = dict()
def calculate_major_cat_totals(df):
    cats = df['Major_category'].unique()
    counts_dictionary = dict()

    for c in cats:
        major_df = df[df["Major_category"] == c]
        total = major_df["Total"].sum()
        counts_dictionary[c] = total
    return counts_dictionary

aa_cat_counts = calculate_major_cat_totals(all_ages)
rg_cat_counts = calculate_major_cat_totals(recent_grads)

['Agriculture & Natural Resources' 'Biology & Life Science' 'Engineering'
 'Humanities & Liberal Arts' 'Communications & Journalism'
 'Computers & Mathematics' 'Industrial Arts & Consumer Services'
 'Education' 'Law & Public Policy' 'Interdisciplinary' 'Health'
 'Social Science' 'Physical Sciences' 'Psychology & Social Work' 'Arts'
 'Business']

In [15]:

aa_cat_counts

Out[15]:

{'Agriculture & Natural Resources': 632437,
 'Arts': 1805865,
 'Biology & Life Science': 1338186,
 'Business': 9858741,
 'Communications & Journalism': 1803822,
 'Computers & Mathematics': 1781378,
 'Education': 4700118,
 'Engineering': 3576013,
 'Health': 2950859,
 'Humanities & Liberal Arts': 3738335,
 'Industrial Arts & Consumer Services': 1033798,
 'Interdisciplinary': 45199,
 'Law & Public Policy': 902926,
 'Physical Sciences': 1025318,
 'Psychology & Social Work': 1987278,
 'Social Science': 2654125}

In [16]:

rg_cat_counts

Out[16]:

{'Agriculture & Natural Resources': 79981,
 'Arts': 357130,
 'Biology & Life Science': 453862,
 'Business': 1302376,
 'Communications & Journalism': 392601,
 'Computers & Mathematics': 299008,
 'Education': 559129,
 'Engineering': 537583,
 'Health': 463230,
 'Humanities & Liberal Arts': 713468,
 'Industrial Arts & Consumer Services': 229792,
 'Interdisciplinary': 12296,
 'Law & Public Policy': 179107,
 'Physical Sciences': 185479,
 'Psychology & Social Work': 481007,
 'Social Science': 529966}

In [25]:

# All majors, common to both DataFrames
majors = recent_grads['Major'].unique()
rg_lower_count = 0


for m in majors:
    recent = recent_grads[recent_grads["Major"] == m]
    all_a = all_ages[all_ages["Major"] == m]
    if recent.iloc[0]["Unemployment_rate"] < all_a.iloc[0]["Unemployment_rate"]:
        rg_lower_count += 1

In [26]:

recent.iloc[0]["Unemployment_rate"]

Out[26]:

0.10494571800000001

In [33]:

# All majors, common to both DataFrames
majors = recent_grads['Major'].unique()
print(len(majors))
rg_lower_count = 0
count = 0
for m in majors:
    print(count)
    count +=1
    recent_grads_row = recent_grads[recent_grads['Major'] == m]
    all_ages_row = all_ages[all_ages['Major'] == m]
    
    rg_unemp_rate = recent_grads_row.iloc[0]['Unemployment_rate']
    aa_unemp_rate = all_ages_row.iloc[0]['Unemployment_rate']
    
    if rg_unemp_rate < aa_unemp_rate:
        rg_lower_count += 1

print(rg_lower_count)

173
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
43

In [31]:

recent_grads_row.iloc[0]['Unemployment_rate']

Out[31]:

0.10494571800000001

In [32]:

recent_grads_row

Out[32]:

	Rank	Major_code	Major	Major_category	Total	Sample_size	Men	Women	ShareWomen	Employed	...	Part_time	Full_time_year_round	Unemployed	Unemployment_rate	Median	P25th	P75th	College_jobs	Non_college_jobs	Low_wage_jobs
172	173	3501	LIBRARY SCIENCE	Education	1098	2	134	964	0.87796	742	...	237	410	87	0.104946	22000	20000	22000	288	338	192

1 rows × 21 columns