In [21]:
import pandas as pd
import numpy as np
import json
import geopy
import time
import math
import logging
In [22]:
p3_cantons_data = pd.read_pickle('P3_Cantons.pickle')
p3_cantons_data
Out[22]:
Funding Instrument
Funding Instrument Hierarchy
Institution
University
Start Date
End Date
Approved Amount
Canton Longname
Canton Shortname
0
Project funding (Div. I-III)
Project funding
NaN
Nicht zuteilbar - NA
01.10.1975
30.09.1976
11619.00
N/A
N/A
1
Project funding (Div. I-III)
Project funding
Faculté de Psychologie et des Sciences de l'Ed...
Université de Genève - GE
01.10.1975
30.09.1976
41022.00
Genève
GE
2
Project funding (Div. I-III)
Project funding
Kommission für das Corpus philosophorum medii ...
NPO (Biblioth., Museen, Verwalt.) - NPO
01.03.1976
28.02.1985
79732.00
Zurich
ZH
3
Project funding (Div. I-III)
Project funding
Abt. Handschriften und Alte Drucke Bibliothek ...
Universität Basel - BS
01.10.1975
30.09.1976
52627.00
Basel-Stadt
BS
4
Project funding (Div. I-III)
Project funding
Schweiz. Thesauruskommission
NPO (Biblioth., Museen, Verwalt.) - NPO
01.01.1976
30.04.1978
120042.00
Zurich
ZH
5
Project funding (Div. I-III)
Project funding
Séminaire de politique économique, d'économie ...
Université de Fribourg - FR
01.01.1976
31.12.1978
53009.00
Fribourg
FR
6
Project funding (Div. I-III)
Project funding
Institut für ökumenische Studien Université de...
Université de Fribourg - FR
01.01.1976
31.12.1976
25403.00
Fribourg
FR
7
Project funding (Div. I-III)
Project funding
Ostasiatisches Seminar Universität Zürich
Universität Zürich - ZH
01.10.1975
31.03.1977
47100.00
Zürich
ZH
8
Project funding (Div. I-III)
Project funding
NaN
Université de Lausanne - LA
01.10.1975
31.03.1977
25814.00
Vaud
VD
9
Project funding (Div. I-III)
Project funding
Laboratoire de Didactique et Epistémologie des...
Université de Genève - GE
01.10.1975
30.09.1978
360000.00
Genève
GE
10
Project funding (Div. I-III)
Project funding
Klinische Psychologie und Psychotherapie Insti...
Université de Fribourg - FR
01.10.1975
31.12.1978
153886.00
Fribourg
FR
11
Project funding (Div. I-III)
Project funding
Schweizerische Rechtsquellen c/o Universität Z...
NPO (Biblioth., Museen, Verwalt.) - NPO
01.10.1975
30.09.1978
862200.00
Zurich
ZH
12
Project funding (Div. I-III)
Project funding
Département de Sociologie Faculté des Sciences...
Université de Genève - GE
01.10.1975
30.09.1977
116991.00
Genève
GE
13
Project funding (Div. I-III)
Project funding
Institut für Volkswirtschaft WWZ / FGS Univers...
Universität Basel - BS
01.10.1975
31.12.1978
112664.00
Basel-Stadt
BS
14
Project funding (Div. I-III)
Project funding
Soziologisches Institut Universität Zürich
Universität Zürich - ZH
01.10.1975
31.12.1975
5000.00
Zürich
ZH
15
Project funding (Div. I-III)
Project funding
Forschungszentrum FAL
Université de Fribourg - FR
01.10.1975
30.09.1977
204018.00
Fribourg
FR
16
Project funding (Div. I-III)
Project funding
Faculté de Psychologie et des Sciences de l'Ed...
Université de Genève - GE
01.10.1975
30.09.1977
149485.00
Genève
GE
17
Project funding (Div. I-III)
Project funding
Soziologisches Institut Universität Zürich
Universität Zürich - ZH
01.01.1976
31.08.1977
83983.00
Zürich
ZH
18
Project funding (Div. I-III)
Project funding
NaN
Nicht zuteilbar - NA
01.01.1976
31.12.1976
38152.00
N/A
N/A
19
Project funding (Div. I-III)
Project funding
NaN
Université de Lausanne - LA
01.12.1975
31.01.1977
14138.00
Vaud
VD
20
Project funding (Div. I-III)
Project funding
Faculté de Psychologie et des Sciences de l'Ed...
Université de Genève - GE
01.10.1975
30.09.1978
164602.00
Genève
GE
21
Project funding (Div. I-III)
Project funding
NaN
Universität Bern - BE
01.10.1975
30.04.1978
147795.00
Canton of Bern
BE
22
Project funding (Div. I-III)
Project funding
Forschungsbereich Politisches Verhalten und Oe...
Universität Zürich - ZH
01.01.1976
30.06.1977
24552.00
Zürich
ZH
23
Project funding (Div. I-III)
Project funding
Département des langues et des littératures fr...
Université de Genève - GE
01.10.1975
30.09.1978
44802.00
Genève
GE
24
Project funding (Div. I-III)
Project funding
NaN
Nicht zuteilbar - NA
01.01.1976
31.12.1979
56000.00
N/A
N/A
25
Project funding (Div. I-III)
Project funding
NaN
Nicht zuteilbar - NA
01.10.1975
30.09.1977
152535.00
N/A
N/A
26
Project funding (Div. I-III)
Project funding
Schweizerische Gesellschaft für Volkskunde
NPO (Biblioth., Museen, Verwalt.) - NPO
01.10.1975
30.09.1976
225000.00
Zurich
ZH
27
Project funding (Div. I-III)
Project funding
Kuratorium Carl J. Burckhardt
NPO (Biblioth., Museen, Verwalt.) - NPO
01.10.1975
30.04.1978
179124.00
Zurich
ZH
28
Project funding (Div. I-III)
Project funding
NaN
Universität Zürich - ZH
01.10.1975
31.03.1976
20000.00
Zürich
ZH
29
Project funding (Div. I-III)
Project funding
Eidg. Forschungsanstalt für Wald, Schnee und L...
Eidg. Forschungsanstalt für Wald,Schnee,Land -...
01.10.1975
30.09.1978
445198.00
Vaud
VD
...
...
...
...
...
...
...
...
...
...
53029
Publication grants
Science communication
Institut Sozialmanagement und Sozialpolitik Ho...
Hochschule Luzern - HSLU
01.08.2016
31.07.2017
1898.00
Lucerne
LU
53030
International short research visits
Careers
Stiftung Bibliothek Werner Oechslin
NaN
01.07.2016
30.09.2016
9300.00
N/A
N/A
53031
International short research visits
Careers
School of Geographical Sciences University of ...
NaN
01.08.2016
31.10.2016
9500.00
N/A
N/A
53032
International short research visits
Careers
NaN
NaN
01.07.2016
31.07.2016
4000.00
N/A
N/A
53033
International short research visits
Careers
CSIRO Marine and Atmospheric Research
NaN
01.08.2016
31.10.2016
11000.00
N/A
N/A
53034
International short research visits
Careers
NaN
NaN
01.07.2016
31.08.2016
7500.00
N/A
N/A
53035
International short research visits
Careers
Departement Geographie, SIE Universität Zürich
NaN
01.08.2016
30.09.2016
8000.00
N/A
N/A
53036
International short research visits
Careers
CReAM Department of Economics University Colle...
NaN
01.09.2016
30.11.2016
9300.00
N/A
N/A
53037
International short research visits
Careers
NaN
NaN
01.08.2016
30.09.2016
7000.00
N/A
N/A
53038
International short research visits
Careers
Eawag
NaN
01.09.2016
31.10.2016
6000.00
N/A
N/A
53039
International short research visits
Careers
Catholic University of Bukavu
NaN
01.09.2016
30.09.2016
4500.00
N/A
N/A
53040
Scientific Conferences
Science communication
Fondation Jean Monnet pour l'Europe Centre de ...
NPO (Biblioth., Museen, Verwalt.) - NPO
01.10.2016
31.12.2016
2800.00
Zurich
ZH
53041
NRP 68 Sustainable Use of Soil as a Resource
Programmes;National Research Programmes (NRPs)
Planning of Landscape and Urban Systems Instit...
ETH Zürich - ETHZ
01.10.2016
30.09.2017
99952.00
Zurich
ZH
53042
NRP 68 Sustainable Use of Soil as a Resource
Programmes;National Research Programmes (NRPs)
Forschungsanstalt Agroscope Reckenholz-Tänikon...
Forschungsanstalten Agroscope - AGS
01.10.2016
31.12.2017
98040.00
Canton of Bern
BE
53043
NRP 68 Sustainable Use of Soil as a Resource
Programmes;National Research Programmes (NRPs)
Forschung und Beratung in Wirtschaft und Polit...
Firmen/Privatwirtschaft - FP
01.09.2016
28.02.2018
100000.00
Aargau
AG
53044
International short research visits
Careers
Zoologisches Institut Universität Basel
NaN
01.09.2016
30.11.2016
10500.00
N/A
N/A
53045
International Exploratory Workshops
Science communication
Zentrum für Religion, Wirtschaft und Politik T...
Universität Basel - BS
01.10.2016
31.12.2016
10030.00
Basel-Stadt
BS
53046
International short research visits
Careers
Abteilung für Neuropsychologie Zentrum für Kog...
NaN
01.08.2016
31.10.2016
7870.00
N/A
N/A
53047
International short research visits
Careers
NaN
NaN
01.08.2016
31.08.2016
3150.00
N/A
N/A
53048
Scientific Conferences
Science communication
Abteilung für Indologie Universität Zürich
Universität Zürich - ZH
01.10.2016
31.12.2016
10600.00
Zürich
ZH
53049
Scientific Conferences
Science communication
Institut Religions, Cultures, Modernité FTSR U...
Université de Lausanne - LA
01.10.2016
31.12.2016
2515.00
Vaud
VD
53050
International short research visits
Careers
NaN
NaN
01.10.2016
31.10.2016
3500.00
N/A
N/A
53051
International short research visits
Careers
Department of Physics University of Massachusetts
NaN
01.10.2016
31.10.2016
4000.00
N/A
N/A
53052
International short research visits
Careers
NaN
NaN
01.09.2016
30.09.2016
3550.00
N/A
N/A
53053
Scientific Conferences
Science communication
Haute École d'art et de design - Genève HES-SO
HES de Suisse occidentale - HES-SO
01.10.2016
31.12.2016
3000.00
Canton of Solothurn
SO
53054
Scientific Conferences
Science communication
Departement Design Zürcher Hochschule der Küns...
Zürcher Fachhochschule (ohne PH) - ZFH
01.10.2016
31.12.2016
1000.00
Zurich
ZH
53055
Scientific Conferences
Science communication
Institute Performing Arts and Film Zürcher Hoc...
Zürcher Fachhochschule (ohne PH) - ZFH
01.09.2016
30.11.2016
2000.00
Zurich
ZH
53056
International short research visits
Careers
Department of Electrical Engineering and Compu...
NaN
01.09.2016
31.12.2016
10500.00
N/A
N/A
53057
International short research visits
Careers
Département de Biologie Faculté des Sciences U...
NaN
01.09.2016
30.11.2016
9500.00
N/A
N/A
53058
International short research visits
Careers
The Forsyth Institute Department of Microbiolo...
NaN
01.10.2016
30.11.2016
7000.00
N/A
N/A
53059 rows × 9 columns
In [23]:
#find all possible short names
cantons = p3_cantons_data['Canton Shortname'].unique()
#create new dataframe with canton indexes
final_df= pd.Series(0, index=cantons)
final_df = final_df.to_frame(name='Total Sum')
final_df['Canton Name'] = final_df.index
final_df = final_df.reset_index(drop=True)
final_df
Out[23]:
Total Sum
Canton Name
0
0
N/A
1
0
GE
2
0
ZH
3
0
BS
4
0
FR
5
0
VD
6
0
BE
7
0
NE
8
0
SG
9
0
AG
10
0
GR
11
0
LU
12
0
TI
13
0
SO
14
0
VS
15
0
SZ
16
0
TG
17
0
ZG
18
0
SH
In [24]:
#delete irrelevant columns in the dataset to clean data
p3_cantons_data = p3_cantons_data.drop(['Funding Instrument', 'Canton Longname', 'End Date', 'Start Date', 'Institution', 'University', 'Funding Instrument Hierarchy'], axis=1)
# Drop all grants that are not associated with a canton
p3_cantons_data = p3_cantons_data[p3_cantons_data['Canton Shortname'] != 'N/A']
p3_cantons_data
Out[24]:
Approved Amount
Canton Shortname
1
41022.00
GE
2
79732.00
ZH
3
52627.00
BS
4
120042.00
ZH
5
53009.00
FR
6
25403.00
FR
7
47100.00
ZH
8
25814.00
VD
9
360000.00
GE
10
153886.00
FR
11
862200.00
ZH
12
116991.00
GE
13
112664.00
BS
14
5000.00
ZH
15
204018.00
FR
16
149485.00
GE
17
83983.00
ZH
19
14138.00
VD
20
164602.00
GE
21
147795.00
BE
22
24552.00
ZH
23
44802.00
GE
26
225000.00
ZH
27
179124.00
ZH
28
20000.00
ZH
29
445198.00
VD
30
85938.00
ZH
31
8902.00
ZH
32
19134.00
BS
33
54509.00
ZH
...
...
...
52995
16000.00
ZH
52996
4600.00
GE
52997
251554.00
LU
52998
5700.00
GE
52999
10000.00
VD
53003
12416.00
ZH
53005
1600.00
BS
53006
3200.00
BS
53007
120000.00
ZH
53008
6300.00
ZH
53009
1500.00
ZH
53010
11782.00
VD
53011
2600.00
ZH
53012
7700.00
FR
53017
2500.00
FR
53018
5900.00
BE
53022
7500.00
BE
53024
3000.00
GE
53025
3600.00
VD
53029
1898.00
LU
53040
2800.00
ZH
53041
99952.00
ZH
53042
98040.00
BE
53043
100000.00
AG
53045
10030.00
BS
53048
10600.00
ZH
53049
2515.00
VD
53053
3000.00
SO
53054
1000.00
ZH
53055
2000.00
ZH
47866 rows × 2 columns
In [25]:
type(p3_cantons_data['Approved Amount'][1])
Out[25]:
str
In [26]:
p3_cantons_data['Approved Amount'] = (p3_cantons_data['Approved Amount']).astype(float)
type(p3_cantons_data['Approved Amount'][1])
Out[26]:
numpy.float64
In [27]:
p3_cantons_sum = p3_cantons_data.groupby(['Canton Shortname']).sum()
In [29]:
# Save the sums in a csv file to use it later
try:
p3_cantons_sum.to_csv('P3_Cantons_Sum.csv', encoding='utf-8')
except PermissionError:
print("Couldn't access to the file. Maybe close Excel and try again :)")
In [30]:
# Maybe a pickle file is better !
In [31]:
p3_cantons_sum.to_pickle('P3_Cantons_Sum.pickle')
Content source: Merinorus/adaisawesome
Similar notebooks: