This tutorial was partially adapted from http://enipedia.tudelft.nl/wiki/OpenRefine_Tutorial, where you can learn more about Open Refine. It used to be called Google Refine so try that too when you are searching for information. However, in our case, we use python to do the same thing.

The university data can be downloaed from http://enipedia.tudelft.nl/enipedia/images/f/ff/UniversityData.zip

## What you can learn

The data contains quite a few issues, and this tutorial shows how to do things like:

• Cleaning up inconsistent spelling of terms (i.e. “USA”, “U.S.A”, “U.S.”, etc).
• Converting values that are text descriptions of numeric values (i.e. $123 million) to actual numeric values (i.e. 123000000) which are usable for analysis. • Identifying which rows of a specific column contain a search term • Extracting and cleaning values for dates • Removing duplicate rows • Using a scatterplot to visualize relationships between values in different columns • Finding geographic coordinates for a list of place names (i.e. the names of universities, etc.) • Exporting cleaned data to Excel # Reading Data with Pandas %matplotlib inline import sys import pandas as pd import statsmodels.api as sm from collections import Counter, defaultdict import numpy as np import datetime import matplotlib.pyplot as plt import matplotlib matplotlib.style.use('ggplot')  df = pd.read_csv('universityData.csv', sep = '\t') df.head()  university endowment numFaculty numDoctoral country numStaff established numPostgrad numUndergrad numStudents 0 Paris Universitas 15 5500 8000 France NaN 2005 NaN 25000 70000 1 Paris Universitas 15 5500 8000 France NaN 2005 NaN 25000 70000 2 Lumi%C3%A8re University Lyon 2 121 NaN 1355 France NaN 1835 7046 14851 27393 3 Confederation College 4700000 NaN NaN Canada NaN 1967 not available pre-university students; technical 21160 4 Rocky Mountain College 16586100 NaN NaN United States NaN 1878 66 878 894 print df.university[2]  Lumi%C3%A8re University Lyon 2  Apparently, there are duplicate rows. len(df)  75043  # Deduplicate entries 1.0 df = df.drop_duplicates() len(df)  15534  We already see an issue here where there is both the full name of a country (United States) and its abbreviation (US). To fix this, we can just copy/paste “United States” as the new cell value. # Clean up country names df['country'].unique()  array(['France', 'Canada', 'United States', 'USA', 'Italy', 'South Korea', 'Japan', 'United States of America', 'England, UK', 'United States )', 'Saudi Arabia', 'Honduras', 'United Kingdom', 'England', 'the Netherlands', 'India', 'Russia', 'U.S.', 'Brazil', 'US', 'U.S.A.', 'Philippines', 'Australia', 'England, United Kingdom', 'Wales', ',', 'China', 'South Africa', 'UK', 'Puerto Rico', 'Bulgaria', 'Botswana', 'Taiwan', 'Sri Lanka', 'Colombia', 'Iran', 'Russian Federation', 'Rossija', 'Finland', 'Republic of China', 'Chile', 'Romania', 'Utopia', 'Singapore', 'Lebanon', 'Turkey', 'Canada B1P 6L2', 'Jordan', 'Albania', 'Canada C1A 4P3 Telephone: 902-566-0439 Fax: 902-566-0795', 'Scotland', 'Pakistan', 'Scotland, United Kingdom', 'Denmark', 'Mexico', 'Thailand', 'Argentina', 'Cura%C3%A7ao', 'Sweden', 'Scotland, UK', 'Bangladesh', 'Nepal', 'Netherlands', 'Switzerland', 'Egypt', 'Spain', 'Satellite locations:', 'Nassau, Bahamas Fort Myers, FL Jacksonville, FL Miami, FL Miramar, FL Orlando, FL Palm Beach, FL Tampa, FL'], dtype=object)  country_df = df.groupby('country').size() for k in country_df.index: print(k, country_df[k])  (',', 2) ('Albania', 8) ('Argentina', 1) ('Australia', 49) ('Bangladesh', 27) ('Botswana', 1) ('Brazil', 10) ('Bulgaria', 2) ('Canada', 625) ('Canada B1P 6L2', 40) ('Canada C1A 4P3 Telephone: 902-566-0439 Fax: 902-566-0795', 1) ('Chile', 1) ('China', 4) ('Colombia', 1) ('Cura%C3%A7ao', 1) ('Denmark', 8) ('Egypt', 1) ('England', 338) ('England, UK', 286) ('England, United Kingdom', 64) ('Finland', 2) ('France', 8) ('Honduras', 1) ('India', 40) ('Iran', 9) ('Italy', 2) ('Japan', 112) ('Jordan', 2) ('Lebanon', 5) ('Mexico', 2) ('Nassau, Bahamas Fort Myers, FL Jacksonville, FL Miami, FL Miramar, FL Orlando, FL Palm Beach, FL Tampa, FL', 1) ('Nepal', 4) ('Netherlands', 2) ('Pakistan', 2) ('Philippines', 147) ('Puerto Rico', 2) ('Republic of China', 1) ('Romania', 1) ('Rossija', 2) ('Russia', 1) ('Russian Federation', 2) ('Satellite locations:', 1) ('Saudi Arabia', 1) ('Scotland', 64) ('Scotland, UK', 16) ('Scotland, United Kingdom', 16) ('Singapore', 4) ('South Africa', 7) ('South Korea', 7) ('Spain', 1) ('Sri Lanka', 4) ('Sweden', 24) ('Switzerland', 8) ('Taiwan', 1) ('Thailand', 4) ('Turkey', 4) ('U.S.', 304) ('U.S.A.', 81) ('UK', 83) ('US', 455) ('USA', 5104) ('United Kingdom', 405) ('United States', 6949) ('United States )', 1) ('United States of America', 167) ('Utopia', 1) ('Wales', 2) ('the Netherlands', 2)  us_condition = df['country'].isin(['U.S.', 'U.S.A.', 'US', 'United States', 'United States )', 'United States of America']) df['country'][us_condition] = 'USA'  ca_condition =[] for i in df['country']: if i.__contains__('Canada'): ca_condition.append(True) else: ca_condition.append(False) df['country'][ca_condition] = 'Canada'  ca_condition =[] for i in df['country']: if i.__contains__('China'): ca_condition.append(True) else: ca_condition.append(False) df['country'][ca_condition] = 'China'  ca_condition =[] for i in df['country']: if i.__contains__('England'): ca_condition.append(True) else: ca_condition.append(False) df['country'][ca_condition] = 'England'  ca_condition =[] for i in df['country']: if i.__contains__('Scotland'): ca_condition.append(True) else: ca_condition.append(False) df['country'][ca_condition] = 'England'  uk_condition = df['country'].isin(['UK', 'United Kingdom', 'Wales']) df['country'][uk_condition] = 'England'  ca_condition =[] for i in df['country']: if i.__contains__('Netherlands'): ca_condition.append(True) else: ca_condition.append(False) df['country'][ca_condition] = 'Netherlands'  ca_condition =[] for i in df['country']: if i.__contains__('Russia'): ca_condition.append(True) else: ca_condition.append(False) df['country'][ca_condition] = 'Russia'  ca_condition =[] for i in df['country']: if i.__contains__('Bahamas'): ca_condition.append(True) else: ca_condition.append(False) df['country'][ca_condition] = 'Bahamas'  'Russian Federation'.__contains__('Russia')  True  uk_condition = df['country']=='Cura%C3%A7ao' df['country'][uk_condition] = "Curacao"  df[df['country']==',']  university endowment numFaculty numDoctoral country numStaff established numPostgrad numUndergrad numStudents 2130 Universidad Ju%C3%A1rez Aut%C3%B3noma de Tabasco 23760.0 2188 NaN , 1087 1958-11-20 971 24921 35271 3077 Universidad Ju%C3%A1rez Aut%C3%B3noma de Tabasco 34140.0 2188 NaN , 1087 1958-11-20 971 24921 35271 Universidad Juárez Autónoma de Tabasco is a public institution of higher learning located in Villahermosa, Tabasco, Mexico. uk_condition = df['country']==',' df['country'][uk_condition] = "Mexico"  df[df['country']=='Satellite locations:']  university endowment numFaculty numDoctoral country numStaff established numPostgrad numUndergrad numStudents 75009 Nova Southeastern University US$64.5 million 2083 NaN Satellite locations: 4319 1964 22060 6397 28457

Nova Southeastern University (NSU) is a private nonprofit university, with a main campus located on 300 acres (120 ha) in Davie, in the US state of Florida. Formerly referred to as “Nova” and now commonly called “NSU”, the university currently consists of 18 colleges and schools offering over 175 programs of study with more than 250 majors.

uk_condition = df['country']=='Satellite locations:'
df['country'][uk_condition]  = "USA"

country_df = df.groupby('country').size()
for k in country_df.index:
print(k, country_df[k])

('Albania', 8)
('Argentina', 1)
('Australia', 49)
('Bahamas', 1)
('Botswana', 1)
('Brazil', 10)
('Bulgaria', 2)
('Chile', 1)
('China', 5)
('Colombia', 1)
('Curacao', 1)
('Denmark', 8)
('Egypt', 1)
('England', 1274)
('Finland', 2)
('France', 8)
('Honduras', 1)
('India', 40)
('Iran', 9)
('Italy', 2)
('Japan', 112)
('Jordan', 2)
('Lebanon', 5)
('Mexico', 4)
('Nepal', 4)
('Netherlands', 4)
('Pakistan', 2)
('Philippines', 147)
('Puerto Rico', 2)
('Romania', 1)
('Rossija', 2)
('Russia', 3)
('Saudi Arabia', 1)
('Singapore', 4)
('South Africa', 7)
('South Korea', 7)
('Spain', 1)
('Sri Lanka', 4)
('Sweden', 24)
('Switzerland', 8)
('Taiwan', 1)
('Thailand', 4)
('Turkey', 4)
('USA', 13062)
('Utopia', 1)


# Clean up values for the number of students

We need to clean the data for the number of students. Not all of the values are numeric, and many of them contain bits of text in addition to the actual number of the students. To figure out which entries need to be fixed, we need to use a Numeric facet:

df.describe()

count 15534 15504 13541 602 15534 3166 15534 15183 15532 13830
unique 1085 1172 601 63 47 352 448 887 1104 698
top Pennsylvania State University 1.708E9 8864 60 USA 3373 1855 800 77,179 Total 800
freq 8960 4480 8960 192 13062 216 8970 651 896 564
df.numStudents.unique()

array(['70000', '27393', '21160', '894', '15553', '62801', '65234', nan,
'15424', '4533', '5756', '24424', '2426', '7730', '2987', '10477',
'13773', '14388', '14522', '32472', '40829', '17101', '7497',
'2400', '7306', '17500', '4463', '2200', '6448', '4863', '900+',
'13570', '4927', '32739', '1604', '2499', '13785', '16317', '339',
'1874', '10790', '4494', '4836', '4176', '4352', '30819', '6314',
'26851', '3251', '616', '49589', '50116', '3224', '52405',
'~50,000', '7773', '3785', '3822', '5000', '20712', '21000',
'21048', '7787', '17231', '18502', '6158', '11804', '36041',
'38912', '29390', '1207', '3090', '3249', '24370', '2654', '2815',
'15705', '14575', '1144', '10235', '1015', '3657', '1033', '1062',
'9840', '32762', '14806', '10623', '13438', '18900', '23420',
'3192', '2480', '12125', '4800', '30930', '7848', '5560', '12130',
'25063', '25277', '16567', '12270', '2566', '7881', '1972', '1779',
'2715', '4179', '3200', '971', '7538', '10133', '6214', '14196',
'35271', '11034', '17055', '14153', '998', '4238', '9251', '23500',
'4666', '17502', '11722', '47430', '4600', '9000', '7110', '3120',
'20655', '2766', '29290', '3740', '8394', '600', '3927', '1836',
'7313', '4061', '2073', '2183', '1315', '15484', '3345', '735',
'15872', '11308', '15889', '7885', '1066', '1331', '3574', '6840',
'1737', '11604', '6058', '1917', '5079', '23859', '23930', '2110',
'2640', '14000', '2942', '3483', '4854', '5930', '23175', '23855',
'2355', '9625', '4500', '1000', '779', '1760', '1885', '37641',
'3739', '17000', 'http://www.brocku.ca/athletics/quickfacts.php',
'20762', '21080', '757', '1116', '2000', '9748', '14177', '2242',
'2488', '19000', '2305', '16548', '16809', '38140', '13863', '4900',
'10363', '21016', '27008', '18,234 -', '26,101 -', '36000', '4200',
'21091', '110436', '18396', '18448', '9558', '7.07184E7',
'7.71264E7', '10000', '1650', '5097', '5471', '5008', '7542',
'8800', '1791', '23122', '30328', '6080', '2776', '14235', '2636',
'2744', '4250', '8094', '13600', '34767', '3709', '22125', '24875',
'2225', '9203', '27676', '8955', '25215', '29549', '30000', '11659',
'17', '3168', '17849', '22280', '19020', '800', '2725', '32275',
'33825', '4337', '3974', '12114', '23135', '3000', '3485', '29703',
'8831', '30303', '11476', '11842', '602', '8649', '1703', '3500',
'5201', '3719', '42099', '42595', '5760', '6082', '2175', '2231',
'22479', '4183', '47122', '5790', '4014', '28602', '42513', '1118',
'15405', '18442', '5033', '4600800.0', '10534', '28525', '55014',
'7751', '7755', '58698', '7567', '11201', '2100', '24531', '1500',
'1,500+', '46422', '46423', '754', '17290', '39165', '9300',
'41674', '42716', '42761', '42907', '49020', '12445', '19333',
'22405', '24662', '18572', '11593', '3209', '23162', '23590',
'8742', '5552', '5,552 total', '1415', '16822', '7942', '6957',
'11733', '28311', '5200', '13995', '14070', '30823', '31766',
'10688', '12557', '21225', '25313', '12024', '12683', '2124',
'5500', '5514', '5525', '12725', '9517', '35274', '37132', '450',
'23315', '24735', '10502', '20407', '8034', '1210', '46302',
'13959', '24849', '13490', '15265', '8606', '29462', '7615',
'24170', '8603', '8645', '9135', '8878', '9370', '29616', '22707',
'2334', '28018', '33585', '21210', '3439', '19700', '25890',
'26073', '9555', '13351', '13676', '4278', '35200', '14560', '7943',
'8005', '12714', '12002', '23761', '24977', '12400', '82', '3432',
'24594', '2648', '19890', '10820', '6101', '2565', '29894', '29952',
'5260', '22275', '16000', '20222', '21011', '37426', '19391',
'25700', '26960', '5379', '24593', '14765', '2900',
'faculty to student ratio: 12:1', '9150', '1600', '35204', '4000',
'47966', '1683', '~1,610', '363', '44', '32304', '6945', '51611',
'51721', '51853', '30540', '32500', '13183', '9940', '18475',
'38934', '57409', '24225', '22805', '30461', '44595', '49000',
'4991', '9339', '23085', '23336', '17455', '10383', '12000', '811',
'3246', '21827', '2282', '11646', '15642', '8810', '23600', '24192',
'8298', '23470', '13089', '19415', '19740', '41215', '2836', '3700',
'3770', '3829', '3721', '4525', '5365', '3050', '2345', '7700',
'7257', '2263', '19780', '2589', '2542', '29898', '2120', '2300',
'3600', '4384', '1748', '7928', '7005', '7277', '7740', '2270',
'1130.0', '1841.0', '6919', '47878', '34000', '9106', '8278',
'3378', '7131', '19966', '6964', '5809', '19664', '19993', '3420',
'2658', '2609', '1938', '2442', '42606', '2435', '10074', '20212',
'20,000+', '1460', '6276', '1308', '2719', '40000', '11065', '2660',
'3633', '80022011', '20487', '111329', '852', '131403', '24595',
'28', '1371', '3800', '23883', '3137', '33788', '28394', '30377',
'5.29092E7', '5.49E7', '14706', '720', '2674', '2886', '23103',
'8000', '10482', '6', '1890', '1872', '34255', '53337', '27523',
'18498', '11922', '6398', '15189', '70440', '72254', '23588',
'24378', '8985', '3461', '2', '27209', '22974', '11581', '47800',
'47954', '14769', '15195', '1771', '16355',
'One MEELLLLIOONNN DOLL HAIRS', '19500', '17499', '10894', '33500',
'15319', '3480', '28094', '8846', '6726', '27606', '20939', '20330',
'21535', '6647', '1454', '20643', '21329', '13925', '4496', '17261',
'17333', '21559', '630', '795', '6290', '8524', '33977', '44817',
'95833', '630 Dickinson School of Law', '800 College of Medicine',
'8,524 World Campus', '44,817 University Park', '795 Great Valley',
'95,833 Total', '33,977 Commonwealth Campuses',
'6,290 PA College of Tech', '25045', '29887', '31040', '27816',
'87274', '17999', '17950', '4197033329', '41710', '15649', '11956',
'28766', '28823', '2724', '45963', '978', '11180', '12312', '19721',
'5233', '29689', '4072', '11867', '38010', '18630', '27195',
'15657', '14820', '13381', '13893', '3245', '15064', '15473',
'7521', '5913', '5998', '33602', '31899', '39271', '39922', '33490',
'24125', '28994', '28290', '1115', '17351', '2284', '17533',
'18971', '39697', '39726', '27269', '19379', '13410', '2203',
'19728', '3005', '3045', '3540', '2459', '18004', '45126', '45954',
'2559', '15446', '32611', '56868', '28203', '20956', '3537', '6654',
'285392012', '15920', '34870', '8289', '25469', '10549', '18762',
'14713', '28091', '55115', '32653', '5152', '380', '14754', '7764',
'15951', '16040', '15536', '9799', '9352', '28457'], dtype=object)

float('5.29092E7')

52909200.0

df.numStudents = [str(i).replace('+', '').replace('~', '') for i in df.numStudents]
df.numStudents = [str(i).replace(',', '').replace('-', '').strip() for i in df.numStudents]
df.numStudents = [i.split(' ')[0] for i in df.numStudents]

# https://stackoverflow.com/questions/4138202/using-isdigit-for-floats
def isDigit(x):
try:
float(x)
return True
except ValueError:
return False

isDigit('1130.0')

True

str.isdigit('1130.0')

False

df['numStudents'] = df['numStudents'].apply(lambda x: np.float(x)
if isDigit(x)
else np.nan)

odds = {}
for i in df['numStudents']:
if not isDigit(i):
try:
odds[i] += 1
except:
odds[i] = 1

len(df)

15534

odds

{}

df.numStudents.iloc[0]

70000.0

df.numStudents.unique()[:10]

array([ 70000.,  27393.,  21160.,    894.,  15553.,  62801.,  65234.,
nan,  15424.,   4533.])

df.numStudents.max(), df.numStudents.min()

(4197033329.0, 2.0)

# df.numStudents.astype('float')

# pd.to_numeric(df.numStudents)#, errors='ignore')

df.describe()

numStudents
count 1.380000e+04
mean 3.934969e+05
std 3.585477e+07
min 2.000000e+00
25% 1.065000e+03
50% 1.068800e+04
75% 3.397700e+04
max 4.197033e+09

# Clean up values for the endowment

First remove the numeric facet for numStudents and create a new numeric facet for endowment. Select only the non-numeric values, as was done for the number of students. Already we see issues like “US$1.3 billion” and “US$186 million”

df['endowment']

0                       15
2                      121
3                  4700000
4                 16586100
5                 16586100
6                 40200750
7                 40200750
8                 40200750
9                 40200750
10                40200750
11                40200750
12                40200750
13                40200750
14                40200750
15                40200750
16                40200750
17                40200750
18               562000000
19               562000000
22                     NaN
23                     NaN
24                     NaN
25                     NaN
26                     NaN
27                   1.3E7
28                   1.0E7
29                   3.5E8
30                   4.5E8
31                     0.0
32                     0.0
...
74996        $2,17 billion 74997$2,17 billion
74998        $2,17 billion 74999$2,17 billion
75000        $2,17 billion 75001$2,17 billion
75002        $2,17 billion 75003$2,17 billion
75004    US $401.2 million 75006 US$213.2 million
75007      US $381 million 75008 US$64.5 million
75009     US $64.5 million 75010 US$64.5 million
75011    US $716.8 million 75012 US$716.8 million
75013    US $716.8 million 75014 US$716.8 million
75015    US $716.8 million 75016 US$716.8 million
75017    US $716.8 million 75018 US$716.8 million
75019    US $716.8 million 75020 US$716.8 million
75021    US $716.8 million 75022 US$716.8 million
75023    US $716.8 million 75024 US$716.8 million
75025    US $716.8 million 75026 US$716.8 million
Name: endowment, dtype: object

np.sum(odds.values())

0.0

odds = {}
for i in df['endowment']:
if not isDigit(i):
try:
odds[i] += 1
except:
odds[i] = 1

odds.items()[:3]

[('US$226 million', 1), ('US$6.44 billion', 16),
('1,5 billion \xe2\x82\xac', 1)]

np.float('1E6')

1000000.0

df.endowment = [str(i).replace('US $', '').replace('US$', '') for i in df.endowment]
df.endowment = [str(i).replace('USD$', '').replace('USD$', '') for i in df.endowment]
df.endowment = [str(i).replace('U.S. $', '').replace(',', '').strip() for i in df.endowment] endowment = [] for i in df.endowment: if i.__contains__('$'):
endowment.append(str(i).split('$')[1]) else: endowment.append(i) df.endowment = endowment  df.endowment = [str(i).replace(' million', 'E6').replace(' billion', 'E9').strip() for i in df.endowment] df.endowment = [str(i).replace('million', 'E6').replace('billion', 'E9').strip() for i in df.endowment] df.endowment = [str(i).replace(' Million', 'E6').replace(' Billion', 'E9').strip() for i in df.endowment] df.endowment = [str(i).split(' ')[0] for i in df.endowment] df.endowment = [str(i).replace('M', 'E6').strip() for i in df.endowment] df.endowment = [str(i).replace(';', '').replace('+', '').strip() for i in df.endowment] # df.endowment = [str(i).split('xbf')[1] for i in df.endowment] # df.endowment = [str(i).split('xb')[1] for i in df.endowment] # df.endowment = [str(i).split('xa')[1] for i in df.endowment]  After most of this has been cleaned up, select the non-numeric values, and delete them, just as was done for the numStudents. df['endowment'] = df['endowment'].apply(lambda x: np.float(x) if isDigit(x) else np.nan)  df.describe()  endowment numStudents count 1.490400e+04 1.380000e+04 mean 2.149103e+09 3.934969e+05 std 1.927573e+10 3.585477e+07 min 0.000000e+00 2.000000e+00 25% 2.430000e+08 1.065000e+03 50% 1.546000e+09 1.068800e+04 75% 1.708000e+09 3.397700e+04 max 1.545840e+12 4.197033e+09 • 巴西雷亚尔的符号 R$
• CANADA DOLLARS C$• 澳元的货币符号 A$

Both “million” and “Million” are in the values, so it’s useful to convert all the values to lowercase instead of cleaning this up twice.

df.head()

0 Paris Universitas 15.0 5500 8000 France NaN 2005 NaN 25000 70000.0
2 Lumi%C3%A8re University Lyon 2 121.0 NaN 1355 France NaN 1835 7046 14851 27393.0
3 Confederation College 4700000.0 NaN NaN Canada NaN 1967 not available pre-university students; technical 21160.0
4 Rocky Mountain College 16586100.0 NaN NaN USA NaN 1878 66 878 894.0
5 Rocky Mountain College 16586100.0 NaN NaN USA NaN 1878 66 878 894.0

# Others

## numFaculty

df.numFaculty = [str(i).replace('Total: ', '').replace(',', '') for i in df.numFaculty]
df.numFaculty = [str(i).replace('>', '').replace('~', '') for i in df.numFaculty]
df.numFaculty = [str(i).split(' ')[0] for i in df.numFaculty]


df['numFaculty'] = df['numFaculty'].apply(lambda x: np.float(x)
if isDigit(x)
else np.nan)

odds = {}
for i in df['numFaculty']:
if not isDigit(i):
try:
odds[i] += 1
except:
odds[i] = 1

odds.items()

[]


## numDoctoral

odds = {}
for i in df['numDoctoral']:
if not isDigit(i):
try:
odds[i] += 1
except:
odds[i] = 1

odds.items()

[('available', 1),
('N\\A', 27),
('not available', 40),
('N.A', 16),
('~25', 4)]

df.numDoctoral = [str(i).replace('~', '') for i in df.numFaculty]

df['numDoctoral'] = df['numDoctoral'].apply(lambda x: np.float(x)
if isDigit(x)
else np.nan)


## numStaff

odds = {}
for i in df['numStaff']:
if not isDigit(i):
try:
odds[i] += 1
except:
odds[i] = 1

odds.items()

[('incl. 1,403 academics and 150 researchers', 2),
('Total: 873', 48),
('Approximately 7,170', 1),
('approximately 30', 1),
('Around 120', 27),
('~500', 4),
('190 researchers & lecturers, 153 administrative & technical staff', 2),
('Approximately 500', 1),
('4,478 employees', 64),
('Around 5,000', 48),
('>21,000', 2),
('?', 1),
('~100', 4),
('full-time, part-time', 1),
('Total: 1,608', 2),
('Total: 1,600', 16),
('Full-time: 1,469', 2),
('156 full-time; 229 part-time', 1),
('Part-time: 139', 2),
('960 full-time, 460 part-time', 16),
('appx. 20', 8),
('~', 1)]

df.numStaff = [str(i).replace('Total: ', '').replace(',', '') for i in df.numStaff]
df.numStaff = [str(i).replace('>', '').replace('~', '') for i in df.numStaff]
df.numStaff = [str(i).replace('Around ', '').replace('appx. ', '') for i in df.numStaff]
df.numStaff = [str(i).replace(' employees', '').replace('Approximately ', '') for i in df.numStaff]
df.numStaff = [str(i).replace('Full-time: ', '').replace('Part-time: ', '') for i in df.numStaff]
df.numStaff = [str(i).replace('approximately ', '') for i in df.numStaff]
df.numStaff = [str(i).split(' ')[0] for i in df.numStaff]


df['numStaff'] = df['numStaff'].apply(lambda x: np.float(x)
if isDigit(x)
else np.nan)


odds = {}
if not isDigit(i):
try:
odds[i] += 1
except:
odds[i] = 1

odds.items()

[('268 full-time MBA', 24),
('approx. 300', 1),
('~650', 4),
('n/a', 1),
('Aprx. 2,000', 2),
('630 Dickinson School of Law', 640),
('120+', 8),
('795 Great Valley', 640),
('Does not offer postgraduate studies', 1),
('~3,914', 1),
('4,937 full-time, 1,446 part-time', 2),
('800 College of Medicine', 640),
('not available', 1),
('~3,200', 2),
('Some postdoctoral students and visiting scholars', 1),
('~160', 3),
('142 Ph.D. students', 2),
('1,682 Commonwealth Campuses', 640),
('available', 1),
('71 MLHR', 24),
('14,020 Total', 640),
('Approximately 1000', 1),
('Approx. 600', 1),
('----', 4),
('~500', 4),
('~5,500', 2),
('~60', 4),
('325 part-time MBA', 24),
('over 1,300', 1),
('N\\A', 9),
('none', 1),
('6,223 University Park', 640),
('ca. 3,230', 2),
('95 MAcc', 24),
('9,957 \xe2\x80\x93 Vancouver', 24),
('531 \xe2\x80\x93 Okanagan', 24),
('3,890 World Campus', 640),
('TBD', 2)]

df.numPostgrad = [str(i).replace('~', '').replace(',', '') for i in df.numPostgrad]


df['numPostgrad'] = df['numPostgrad'].apply(lambda x: np.float(x)
if isDigit(x)
else np.nan)


odds = {}
if not isDigit(i):
try:
odds[i] += 1
except:
odds[i] = 1

odds.items()

[('4,747 full-time', 96),
('over 2,000', 1),
('Approximately 10,000', 1),
('2,000+', 8),
('approximately 1,500', 1),
('n/a', 1),
('Aprx. 2,000', 2),
('School of Liberal Arts; School of Science & Technology; School of Graduate & Professional Studies; Shannon School of Business',
20),
('approx. 2,000', 1),
('36,518 \xe2\x80\x93 Vancouver', 24),
('~25,000', 2),
('65 per year', 1),
('77,179 Total', 896),
('approx. 1,200', 8),
('Approx. 12,000', 2),
('38,594  University Park', 896),
('32,295 Commonwealth Campuses', 896),
('Approximately 2,300', 1),
('7,004 \xe2\x80\x93 Okanagan', 24),
('~400', 1),
('pre-university students; technical', 1),
('Around 10,000', 2),
('available', 3),
('21,726 -', 24),
('None', 1),
('900+', 1),
('ca. 3,046', 2),
('Approx. 7,100', 1),
('6,290 PA College of Tech', 896),
('4,634 World Campus', 896),
('28,477 full-time, 2,102 part-time', 2),
('none', 1),
('approx. 2,150', 4),
('Approximately 730', 1),
('~13,000', 2),
('~2,000', 4),
('diploma, degree available', 1),
('Approx. 13,000', 1),
('~1,560', 1),
('???', 1),
('~1,550', 4)]

df.numUndergrad = [str(i).replace('~', '').replace(',', '') for i in df.numUndergrad]

df['numUndergrad'] = df['numUndergrad'].apply(lambda x: np.float(x)
if isDigit(x)
else np.nan)


## established

df.established

0                            2005
2                            1835
3                            1967
4                            1878
5                            1878
6                            1901
7                            1901
8                            1947
9                            1947
10                           1963
11                           1963
12       1963 - university status
13       1963 - university status
14       1947 - four-year college
15       1947 - four-year college
16                         1901 -
17                         1901 -
18                           1924
19                           1924
22                           1970
23                     1918-05-01
24                           1925
25               Established 1985
26                 Chartered 1984
27                           1994
28                           1947
29                           1948
30                           1878
31                     2004-09-30
32                     2004-09-30
...
74996                        1848
74997                        1848
74998                        1848
74999                        1848
75000                        1848
75001                        1848
75002                        1848
75003                        1848
75004                  1881-08-28
75006                  1855-10-15
75007                        1911
75008                        1964
75009                        1964
75010                        1964
75011                        1851
75012                        1851
75013                        1851
75014                        1851
75015                        1851
75016                        1851
75017                        1851
75018                        1851
75019                        1851
75020                        1851
75021                        1851
75022                        1851
75023                        1851
75024                        1851
75025                        1851
75026                        1851
Name: established, dtype: object

odds = {}
for i in df['established']:
if not isDigit(i):
try:
odds[i] += 1
except:
odds[i] = 1

odds.items()[:5]

[]

import re

def getYear(s):
try:
match = re.match(r'.*([1-3][0-9]{3})', s)
return np.int(match.group(1))
except:
return np.nan

df.established = [getYear(i) for i in df.established]

df.describe()

count 1.490400e+04 1.348700e+04 1.348700e+04 3.161000e+03 15186.000000 1.513800e+04 1.550000e+04 1.380000e+04
mean 2.149103e+09 8.695432e+03 8.695432e+03 1.057127e+04 1865.196036 1.104538e+04 2.762582e+05 3.934969e+05
std 1.927573e+10 1.286384e+05 1.286384e+05 2.483439e+05 65.865596 3.622826e+05 2.581618e+07 3.585477e+07
min 0.000000e+00 1.000000e+00 1.000000e+00 2.000000e+00 1066.000000 0.000000e+00 0.000000e+00 2.000000e+00
25% 2.430000e+08 1.953000e+03 1.953000e+03 1.592000e+03 1855.000000 8.000000e+02 6.290000e+03 1.065000e+03
50% 1.546000e+09 8.864000e+03 8.864000e+03 2.799000e+03 1855.000000 3.067000e+03 1.782100e+04 1.068800e+04
75% 1.708000e+09 8.864000e+03 8.864000e+03 5.000000e+03 1881.000000 6.223000e+03 3.667500e+04 3.397700e+04
max 1.545840e+12 1.407201e+07 1.407201e+07 1.280201e+07 2012.000000 2.998201e+07 3.198523e+09 4.197033e+09



# University

df['university'].unique(), df['university'].unique().size

(array(['Paris Universitas', 'Lumi%C3%A8re University Lyon 2',
'Confederation College', ..., 'University of San Francisco',
'Loyola Marymount University', 'Nova Southeastern University'], dtype=object),
1085)

# university_df = df.groupby('university').size()
# for k in university_df.index:
#     print(k, university_df[k])

df.university = [str(i).replace('%2C', '').replace(',', '') for i in df.university]
df.university = [str(i).replace('%C3%89', 'e').replace('%C3%A9', 'e') for i in df.university]
df.university = [str(i).replace('%E2%80%93', ' ').replace('%22', '') for i in df.university]
df.university = [str(i).replace('%C3%A3', 'a').replace('%C3%A7', 'c') for i in df.university]
df.university = [str(i).replace('%C3%AA', 'e').replace('%C3%BA', 'u') for i in df.university]
df.university = [str(i).replace('%C3%A1', 'a').replace('%C3%B3', 'o') for i in df.university]
df.university = [str(i).replace('%CA%BB', "'").replace('%C4%81', 'a') for i in df.university]

df.head()

0 Paris Universitas 15.0 5500.0 5500.0 France NaN 2005.0 NaN 25000.0 70000.0
2 Lumi%C3%A8re University Lyon 2 121.0 NaN NaN France NaN 1835.0 7046.0 14851.0 27393.0
3 Confederation College 4700000.0 NaN NaN Canada NaN 1967.0 NaN NaN 21160.0
4 Rocky Mountain College 16586100.0 NaN NaN USA NaN 1878.0 66.0 878.0 894.0
5 Rocky Mountain College 16586100.0 NaN NaN USA NaN 1878.0 66.0 878.0 894.0
len(df)

15534


# Deduplicate entries 2.0

df = df.drop_duplicates()
len(df)

4919

df.head()

0 Paris Universitas 15.0 5500.0 5500.0 France NaN 2005.0 NaN 25000.0 70000.0
2 Lumi%C3%A8re University Lyon 2 121.0 NaN NaN France NaN 1835.0 7046.0 14851.0 27393.0
3 Confederation College 4700000.0 NaN NaN Canada NaN 1967.0 NaN NaN 21160.0
4 Rocky Mountain College 16586100.0 NaN NaN USA NaN 1878.0 66.0 878.0 894.0
6 Idaho State University 40200750.0 838.0 838.0 USA 1269.0 1901.0 2661.0 12892.0 15553.0
df.describe()

count 4.585000e+03 3.650000e+03 3.650000e+03 2.159000e+03 4677.000000 4.568000e+03 4.887000e+03 3.734000e+03
mean 3.128695e+09 1.101300e+04 1.101300e+04 1.302496e+04 1879.911268 2.654159e+04 8.144919e+05 1.390889e+06
std 3.311783e+10 2.472490e+05 2.472490e+05 3.004688e+05 95.221953 6.592566e+05 4.597518e+07 6.892540e+07
min 0.000000e+00 1.000000e+00 1.000000e+00 2.000000e+00 1066.000000 0.000000e+00 0.000000e+00 2.000000e+00
25% 2.900000e+07 5.315000e+02 5.315000e+02 1.178000e+03 1855.000000 1.305000e+03 4.634000e+03 7.542000e+03
50% 1.892540e+08 1.099000e+03 1.099000e+03 2.211000e+03 1887.000000 3.364000e+03 1.348000e+04 1.933300e+04
75% 1.295000e+09 3.694000e+03 3.694000e+03 4.478000e+03 1924.000000 6.614250e+03 2.332800e+04 2.570000e+04
max 1.545840e+12 1.407201e+07 1.407201e+07 1.280201e+07 2012.000000 2.998201e+07 3.198523e+09 4.197033e+09
plt.matshow(df.corr())
plt.show()


def correlation_matrix(df):
from matplotlib import pyplot as plt
from matplotlib import cm as cm

fig = plt.figure(figsize=(7, 7),facecolor='white')
cmap = cm.get_cmap('jet', 30)
cax = ax1.imshow(df.corr(), interpolation="nearest", cmap=cmap)
ax1.grid(True)
plt.title('Correlation')
labels=['','endowment','numFaculty','numDoctoral','numStaff',\
ax1.set_xticklabels(labels,fontsize=16, rotation = 90)
ax1.set_yticklabels(labels,fontsize=16)
# Add colorbar, make sure to specify tick locations to match desired ticklabels
fig.colorbar(cax)
plt.show()

correlation_matrix(df)


plt.plot(df.numStaff, df.numPostgrad, 'o')
plt.yscale('log')
plt.xscale('log')
plt.xlabel('$Staff$', fontsize = 20)
plt.ylabel('$Post\;Graduates$', fontsize = 20)
plt.show()


# Write data

df.to_csv(‘universityDataClean.csv’, index = False)

Updated: