In [1]:

    

import pandas as pd, numpy as np, json
data={}


    

In [2]:

    

for year in range(2003,2018):
    data[year]=pd.read_html('http://www.shanghairanking.com/ARWU'+repr(year)+'.html', header=0)[0].sort_index(axis=1)
    print year


    

    




2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017

In [3]:

    

#save input data for later editing
DATA={}
for year in range(2003,2018):
    DATA[year]=data[year].to_json()
file('data2017.json','w').write(json.dumps(DATA))


    

In [6]:

    

#load data if already saved
DATA = json.loads(open('data2017.json').read())
data={}
for year in range(2003,2018):
    data[year]=pd.read_json(DATA[repr(year)]).sort_index(axis=1)


    

In [4]:

    

u={}
v={}
locs=['Institution',u'Institution*']
years=[[2003,2004,2011],[2005,2006,2007,2008,2009,2010,2012,2013,2014,2015,2016,2017]]
for r in range(2):
    for year in years[r]:
        unis=[]
        for i in range(data[year]['World Rank'].count()):
            uni={}
            uni['name']=data[year].loc[i][locs[r]]
            if uni['name'] not in v:v[uni['name']]={'years':[],'ranks':[]}
            v[uni['name']]['years'].append(year)
            uni['rank']=data[year].loc[i]['World Rank']
            v[uni['name']]['ranks'].append(uni['rank'])
            unis.append(uni)
        u[year]=unis


    

In [5]:

    

exceptions=[]


    

In [21]:

    

from pygeocoder import Geocoder
apik='AIzaSyDybC2OroTE_XDJTuxjKruxFpby5VDhEGk'
counter=0
for k in v.keys():
    counter+=1
    try:
        #results = Geocoder(apik).geocode(k+' university')
        results = Geocoder(apik).geocode(k)
        v[k]["coord"]=(results[0].coordinates)
        print counter,'OK'
    except:
        print counter,'ERROR'


    

    




1 OK
2 OK
3 OK
4 OK
5 OK
6 OK
7 OK
8 OK
9 OK
10 OK
11 OK
12 OK
13 OK
14 OK
15 OK
16 OK
17 OK
18 OK
19 OK
20 OK
21 OK
22 OK
23 OK
24 OK
25 OK
26 OK
27 OK
28 OK
29 OK
30 OK
31 OK
32 OK
33 OK
34 OK
35 OK
36 OK
37 OK
38 ERROR
39 OK
40 OK
41 OK
42 OK
43 OK
44 OK
45 OK
46 OK
47 OK
48 OK
49 OK
50 OK
51 OK
52 OK
53 OK
54 OK
55 OK
56 OK
57 OK
58 OK
59 OK
60 OK
61 OK
62 OK
63 OK
64 OK
65 OK
66 OK
67 OK
68 OK
69 OK
70 OK
71 OK
72 OK
73 OK
74 OK
75 OK
76 OK
77 OK
78 OK
79 OK
80 OK
81 OK
82 OK
83 ERROR
84 OK
85 OK
86 OK
87 OK
88 OK
89 OK
90 OK
91 OK
92 OK
93 OK
94 OK
95 OK
96 OK
97 OK
98 OK
99 OK
100 OK
101 OK
102 OK
103 OK
104 OK
105 OK
106 OK
107 ERROR
108 OK
109 OK
110 OK
111 OK
112 OK
113 OK
114 OK
115 OK
116 OK
117 OK
118 OK
119 OK
120 OK
121 OK
122 OK
123 OK
124 OK
125 OK
126 OK
127 OK
128 OK
129 OK
130 OK
131 OK
132 OK
133 OK
134 OK
135 OK
136 OK
137 OK
138 OK
139 OK
140 OK
141 OK
142 OK
143 OK
144 OK
145 OK
146 OK
147 OK
148 OK
149 OK
150 OK
151 OK
152 OK
153 OK
154 OK
155 OK
156 OK
157 OK
158 OK
159 OK
160 OK
161 OK
162 OK
163 OK
164 OK
165 OK
166 OK
167 OK
168 OK
169 OK
170 OK
171 OK
172 OK
173 OK
174 OK
175 OK
176 OK
177 OK
178 OK
179 OK
180 OK
181 OK
182 OK
183 OK
184 OK
185 OK
186 OK
187 OK
188 OK
189 OK
190 OK
191 OK
192 OK
193 OK
194 OK
195 OK
196 OK
197 OK
198 OK
199 OK
200 OK
201 OK
202 OK
203 OK
204 OK
205 OK
206 OK
207 OK
208 OK
209 OK
210 OK
211 OK
212 OK
213 OK
214 OK
215 OK
216 OK
217 OK
218 OK
219 OK
220 OK
221 OK
222 OK
223 OK
224 OK
225 OK
226 OK
227 OK
228 OK
229 OK
230 OK
231 OK
232 OK
233 OK
234 OK
235 OK
236 OK
237 ERROR
238 OK
239 OK
240 OK
241 OK
242 OK
243 OK
244 OK
245 OK
246 OK
247 OK
248 OK
249 OK
250 OK
251 OK
252 OK
253 OK
254 OK
255 OK
256 OK
257 OK
258 OK
259 OK
260 OK
261 OK
262 OK
263 OK
264 OK
265 OK
266 OK
267 OK
268 OK
269 OK
270 OK
271 OK
272 OK
273 OK
274 OK
275 OK
276 OK
277 OK
278 OK
279 OK
280 OK
281 OK
282 OK
283 OK
284 OK
285 OK
286 OK
287 OK
288 OK
289 OK
290 OK
291 OK
292 OK
293 OK
294 OK
295 OK
296 OK
297 OK
298 OK
299 OK
300 OK
301 OK
302 OK
303 OK
304 OK
305 OK
306 OK
307 OK
308 OK
309 OK
310 OK
311 OK
312 OK
313 OK
314 OK
315 OK
316 OK
317 OK
318 OK
319 OK
320 OK
321 OK
322 OK
323 OK
324 OK
325 OK
326 OK
327 OK
328 OK
329 OK
330 OK
331 OK
332 OK
333 OK
334 OK
335 OK
336 OK
337 OK
338 OK
339 OK
340 OK
341 OK
342 OK
343 OK
344 OK
345 OK
346 OK
347 OK
348 OK
349 OK
350 OK
351 OK
352 OK
353 OK
354 OK
355 OK
356 OK
357 OK
358 OK
359 OK
360 OK
361 OK
362 OK
363 OK
364 OK
365 OK
366 OK
367 OK
368 OK
369 OK
370 OK
371 OK
372 OK
373 OK
374 OK
375 OK
376 OK
377 OK
378 OK
379 OK
380 OK
381 OK
382 OK
383 OK
384 OK
385 OK
386 OK
387 OK
388 OK
389 OK
390 OK
391 OK
392 OK
393 OK
394 OK
395 OK
396 OK
397 OK
398 OK
399 OK
400 OK
401 OK
402 OK
403 OK
404 OK
405 OK
406 OK
407 OK
408 OK
409 OK
410 OK
411 OK
412 OK
413 OK
414 OK
415 OK
416 OK
417 OK
418 OK
419 OK
420 OK
421 OK
422 ERROR
423 OK
424 OK
425 OK
426 OK
427 OK
428 OK
429 OK
430 OK
431 OK
432 OK
433 OK
434 OK
435 OK
436 OK
437 OK
438 OK
439 OK
440 OK
441 OK
442 OK
443 OK
444 OK
445 OK
446 OK
447 OK
448 OK
449 OK
450 OK
451 OK
452 OK
453 OK
454 OK
455 OK
456 OK
457 OK
458 OK
459 OK
460 OK
461 OK
462 OK
463 OK
464 OK
465 OK
466 OK
467 OK
468 OK
469 OK
470 OK
471 OK
472 OK
473 OK
474 OK
475 OK
476 OK
477 OK
478 OK
479 OK
480 OK
481 OK
482 OK
483 OK
484 OK
485 OK
486 OK
487 OK
488 OK
489 OK
490 OK
491 OK
492 OK
493 OK
494 OK
495 OK
496 OK
497 OK
498 OK
499 OK
500 OK
501 OK
502 OK
503 OK
504 OK
505 OK
506 OK
507 OK
508 OK
509 OK
510 OK
511 OK
512 OK
513 OK
514 OK
515 OK
516 OK
517 OK
518 OK
519 OK
520 OK
521 OK
522 OK
523 OK
524 OK
525 OK
526 OK
527 OK
528 OK
529 OK
530 OK
531 OK
532 OK
533 OK
534 OK
535 OK
536 OK
537 OK
538 OK
539 OK
540 OK
541 OK
542 OK
543 OK
544 OK
545 OK
546 OK
547 OK
548 OK
549 OK
550 OK
551 OK
552 OK
553 OK
554 OK
555 OK
556 OK
557 OK
558 OK
559 OK
560 OK
561 OK
562 OK
563 OK
564 OK
565 OK
566 OK
567 OK
568 OK
569 OK
570 OK
571 OK
572 OK
573 OK
574 OK
575 OK
576 OK
577 OK
578 OK
579 OK
580 OK
581 OK
582 OK
583 OK
584 OK
585 OK
586 OK
587 OK
588 OK
589 OK
590 OK
591 OK
592 OK
593 OK
594 OK
595 OK
596 OK
597 OK
598 OK
599 OK
600 OK
601 OK
602 OK
603 OK
604 OK
605 OK
606 OK
607 OK
608 OK
609 OK
610 OK
611 OK
612 OK
613 OK
614 ERROR
615 OK
616 OK
617 OK
618 OK
619 OK
620 OK
621 OK
622 OK
623 OK
624 OK
625 OK
626 OK
627 OK
628 OK
629 OK
630 OK
631 OK
632 OK
633 OK
634 OK
635 OK
636 OK
637 OK
638 OK
639 ERROR
640 OK
641 OK
642 OK
643 OK
644 OK
645 OK
646 OK
647 OK
648 OK
649 OK
650 OK
651 OK
652 OK
653 OK
654 OK
655 OK
656 OK
657 OK
658 OK
659 OK
660 OK
661 OK
662 OK
663 OK
664 OK
665 OK
666 OK
667 OK
668 OK
669 OK
670 OK
671 OK
672 OK
673 OK
674 OK
675 OK
676 OK
677 OK
678 OK
679 OK
680 OK
681 OK
682 OK
683 OK
684 OK
685 OK
686 OK
687 OK
688 OK
689 OK
690 OK
691 OK
692 OK
693 OK
694 OK
695 OK
696 OK
697 OK
698 OK
699 OK
700 OK
701 OK
702 OK
703 OK
704 OK
705 OK
706 OK
707 OK
708 OK
709 OK
710 OK
711 OK
712 OK
713 OK
714 OK
715 OK
716 OK
717 OK
718 OK
719 OK
720 OK
721 OK
722 OK
723 OK
724 OK
725 OK
726 OK
727 OK
728 OK
729 OK
730 OK
731 OK
732 OK
733 OK
734 OK
735 OK
736 OK
737 OK
738 OK
739 OK
740 OK

In [22]:

    

for k in v.keys():
    if "coord" not in v[k]:
        print k
        if k=='Tongji University':tocode='Shanghai'
        if k=='University of Electronic Science and Technology of China':tocode='Chengdu'
        if k=='Huazhong University of Science and Technology':tocode='Wuhan'
        if k=='Hacettepe University':tocode='Ankara'
        if k=='King Abdulaziz University':tocode='Jeddah'
        results = Geocoder(apik).geocode(tocode)
        v[k]["coord"]=(results[0].coordinates)  
    elif k=='University of Cambridge':
        print k
        tocode='Cambridge, United Kingdom'
        results = Geocoder(apik).geocode(tocode)
        v[k]["coord"]=(results[0].coordinates)


    

    




University of Cambridge

In [23]:

    

#save results
file('u2017.json','w').write(json.dumps(u))#rankings
file('v2017.json','w').write(json.dumps(v))#universities