import pandas as pd
df = pd.DataFrame(data = [[2018, "a", "b", 500, 1500, 700],
[2018, "a", "c", 500, 400, 50],
[2018, "b", "a", 1500, 500, 700],
[2018, "b", "d", 1500, 750, 200],
[2018, "c", "d", 400, 750, 375]],
columns = ["year", "exporter", "importer", "importer_gdp", "exporter_gdp", "distance"])
df
year | exporter | importer | importer_gdp | exporter_gdp | distance | |
---|---|---|---|---|---|---|
0 | 2018 | a | b | 500 | 1500 | 700 |
1 | 2018 | a | c | 500 | 400 | 50 |
2 | 2018 | b | a | 1500 | 500 | 700 |
3 | 2018 | b | d | 1500 | 750 | 200 |
4 | 2018 | c | d | 400 | 750 | 375 |
df2 = df[['year', 'importer', 'exporter','exporter_gdp','distance']]
df = df[['year','exporter','importer','importer_gdp','distance']]
df.columns = ['year','country','partner','partner_gdp','distance']
df2.columns = ['year','country','partner','partner_gdp','distance']
df.append(df2, ignore_index=True)
df.drop_duplicates(inplace=True)
df.head()
/Users/lucy/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:6: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
year | country | partner | partner_gdp | distance | |
---|---|---|---|---|---|
0 | 2018 | a | b | 500 | 700 |
1 | 2018 | a | c | 500 | 50 |
2 | 2018 | b | a | 1500 | 700 |
3 | 2018 | b | d | 1500 | 200 |
4 | 2018 | c | d | 400 | 375 |
df['dist_w'] = df['partner_gdp'] / df['distance']
remoteness = df.groupby('country')['dist_w'].sum().reset_index()
remoteness['remoteness'] = 1 / remoteness['dist_w']
remoteness
country | dist_w | remoteness | |
---|---|---|---|
0 | a | 10.714286 | 0.093333 |
1 | b | 9.642857 | 0.103704 |
2 | c | 1.066667 | 0.937500 |