import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
from sklearn.cluster import KMeans
import sys
import os
import neo4j
from IPython.display import display
import psycopg2

Raw Data Pull from Github

|:-----------------|:---------|:-----------------|

#Load the data, CSV file format
population = pd.read_csv('https://raw.githubusercontent.com/rfordatascience/tidytuesday/main/data/2023/2023-08-22/population.csv')

Data Exploration

#Initial data exploration
population.head()

#Initial data exploration
population.describe()

#Count the number of nulls in each column
population.isnull().sum()
#Overall, pretty clean data! No nulls in the columns relevant to us

#Number of rows--in other words, number of unique coo-coa connections
len(population)

#Range of Dates through which Refugees Were Tracked

min_date = population['year'].min()
max_date = population['year'].max()

print(f"Date range: {min_date} to {max_date}")

#Find Unique Values in the Column "Country of Origin"

population['coo_name'].unique()

#Find Unique Values in the Column "Country of Asylum"

population['coa_name'].unique()

#Find total number of origin and asylum countries and number of unique countries

country_list = list(population['coo_name'].unique())
print(f'Number of origin countries: {len(country_list)}')
country_list.extend(population['coa_name'].unique())
print(f'Number of asylum countries: {len(population["coa_name"].unique())}')
country_list = list(set(country_list))
print(f'Total number of unique countries: {len(country_list)}')

#Check for non-numeric values in the numeric columns that are relevant to our analysis

non_numeric_rows = pd.DataFrame()

for col in ['refugees', 'asylum_seekers']:
    mask = pd.to_numeric(population[col], errors='coerce').isna()
    non_numeric_rows[col] = mask

#Seems that there are no non-numeric values in these columns

#Drop rows where country of asylum and country of origin are the same, since we are only interested in movement
#between countries


population = population[population['coo_name'] != population['coa_name']].reset_index()
population.head()

#Sum "Refugees" and "Asylum Seekers" Columns to Get Total Number of Refugees
population['total_refugees'] = population['refugees'] + population['asylum_seekers']
population.head()

# Visualize the number of refugees each year'
r_table = pd.pivot_table(population, values='total_refugees', index=['year'], aggfunc="sum")
r_table.reset_index(inplace=True)
# Create the bar chart
# plt.figure(figsize=(20, 20))
fig, ax = plt.subplots(figsize=(10, 6))
bars = ax.bar(r_table['year'], r_table['total_refugees'], color='skyblue', width=0.7)

# Add labels and title
plt.xlabel('Years')
plt.ylabel('Total Refugees')
plt.title('Refugees by Year')
ax.set_yticklabels(['{:,}'.format(int(x)) for x in ax.get_yticks()])

# Display the chart
plt.show()

# Visualize the number of refugees for the top 10 countries of Origin (2010-2022)
o_table = pd.pivot_table(population, values='total_refugees', index=['coo_name'], aggfunc="sum").sort_values(by=['total_refugees'],ascending=False)
o_table.reset_index(inplace=True)
o_table
# Create the bar chart
# plt.figure(figsize=(20, 20))
fig, ax = plt.subplots(figsize=(17, 6))
bars = ax.bar(o_table['coo_name'][0:9], o_table['total_refugees'][0:9], color='skyblue', width=0.7)

# Add labels and title
plt.xlabel('Country of Origin')
plt.ylabel('Total Refugees')
plt.title('Refugees by Top 10 Country of Origin (2010-2022)')
ax.set_yticklabels(['{:,}'.format(int(x)) for x in ax.get_yticks()])

# Display the chart
plt.show()

#Display in Table Format

# Get the top 10 countries by total refugees
top_10_table = o_table.head(10).copy()

# Optionally format numbers with commas for readability
top_10_table['total_refugees'] = top_10_table['total_refugees'].apply(lambda x: f"{int(x):,}")

# Display the table
top_10_table

Connect to Neo4j

driver = neo4j.GraphDatabase.driver(uri="neo4j://neo4j:7687", auth=("neo4j", "ucb_mids_w205"))
session = driver.session(database="neo4j")

Set up Functions

def my_neo4j_wipe_out_database():
    "wipe out database by deleting all nodes and relationships"

    query = "match (node)-[relationship]->() delete node, relationship"
    session.run(query)

    query = "match (node) delete node"
    session.run(query)

def my_neo4j_run_query_pandas(query, **kwargs):
    "run a query and return the results in a pandas dataframe"

    result = session.run(query, **kwargs)

    df = pd.DataFrame([r.values() for r in result], columns=result.keys())

    return df

def my_neo4j_nodes_relationships():
    "print all the nodes and relationships"

    print("-------------------------")
    print("  Nodes:")
    print("-------------------------")

    query = """
        match (n)
        return n.name as node_name, labels(n) as labels
        order by n.name
    """

    df = my_neo4j_run_query_pandas(query)

    number_nodes = df.shape[0]

    display(df)

    print("-------------------------")
    print("  Relationships:")
    print("-------------------------")

    query = """
        match (n1)-[r]->(n2)
        return n1.name as node_name_1, labels(n1) as node_1_labels,
            type(r) as relationship_type, n2.name as node_name_2, labels(n2) as node_2_labels
        order by node_name_1, node_name_2
    """

    df = my_neo4j_run_query_pandas(query)

    number_relationships = df.shape[0]

    display(df)

    density = (2 * number_relationships) / (number_nodes * (number_nodes - 1))

    print("-------------------------")
    print("  Density:", f'{density:.1f}')
    print("-------------------------")

def my_neo4j_create_node(country_name):
    "create a node with label Country Name"

    query = """

    CREATE (:Country {name: $country_name})

    """

    session.run(query, country_name=country_name)

def my_neo4j_number_nodes_relationships():
    "print the number of nodes and relationships"


    query = """
        match (n)
        return n.name as node_name, labels(n) as labels
        order by n.name
    """

    df = my_neo4j_run_query_pandas(query)

    number_nodes = df.shape[0]


    query = """
        match (n1)-[r]->(n2)
        return n1.name as node_name_1, labels(n1) as node_1_labels,
            type(r) as relationship_type, n2.name as node_name_2, labels(n2) as node_2_labels
        order by node_name_1, node_name_2
    """

    df = my_neo4j_run_query_pandas(query)

    number_relationships = df.shape[0]

    print("-------------------------")
    print("  Nodes:", number_nodes)
    print("  Relationships:", number_relationships)
    print("-------------------------")

Graph 1: Neo4j - Country of Origin to Country of Asylum (emigrating)

my_neo4j_wipe_out_database()

#Create Nodes for Each Country
for country in country_list:
    my_neo4j_create_node(country)

my_neo4j_nodes_relationships()

#No Relationships Yet, As Expected
my_neo4j_number_nodes_relationships()

#Number of Nodes Matches Country Count Above

#Create one-way relationship from coo to coa

def my_neo4j_create_relationship_one_way(country_origin, country_asylum, weight, year):
    "create a relationship one way between two stations with a weight"

    query = """

    MATCH (from:Country),
          (to:Country)
    WHERE from.name = $country_origin and to.name = $country_asylum
    CREATE (from)-[:asylum {weight: $weight, year: $year}]->(to)

    """

    session.run(query, country_origin=country_origin, country_asylum=country_asylum, weight=weight, year=year)

#Create Relationships between Nodes, One-Directional from Country of Origin to Country of Asylum
#With Weight as Total_Refugees (Refugees plus Asylum Seekers)
for row in range(len(population)):
    country_origin = population['coo_name'][row]
    country_asylum = population['coa_name'][row]
    total_refugees = population['total_refugees'][row]
    year = population['year'][row]


    my_neo4j_create_relationship_one_way(country_origin=country_origin, country_asylum=country_asylum, weight=total_refugees, year=year)

my_neo4j_nodes_relationships()

my_neo4j_number_nodes_relationships()

# Sanity Check: Print Emigration Relationships in Descending Order (Highest Number of Refugees Going from Country
#of Origin to Country of Asylum Appears First)

query = """
MATCH (n1:Country)-[r:asylum]->(n2:Country)
RETURN n1.name AS origin, n2.name AS asylum, r.weight AS total_refugees, r.year AS year
ORDER BY total_refugees DESC
LIMIT 10
"""
df = my_neo4j_run_query_pandas(query)
display(df)

## Top countries by total refugees for Emigration

query = """
MATCH (n1:Country)-[r:asylum]->(n2:Country)
RETURN n1.name AS origin, sum(r.weight) AS total_refugees
ORDER BY total_refugees DESC
LIMIT 10
"""
df = my_neo4j_run_query_pandas(query)
display(df)

## Check Yearly Trend

query = """
MATCH (o:Country)-[r:asylum]->(a:Country)
RETURN r.year AS year, sum(r.weight) AS total_refugees_that_year
ORDER BY year
"""
df = my_neo4j_run_query_pandas(query)
display(df)

Algorithms for Graph Depicting Emigration

Algorithm # 1: Use PageRank to Find Most Influential Countries in Granting Asylum

query = "CALL gds.graph.drop('ds_graph', false) yield graphName"
session.run(query)

query = "CALL gds.graph.project('ds_graph', 'Country', 'asylum', {relationshipProperties: 'weight'})"
session.run(query)

query = """

CALL gds.pageRank.stream('ds_graph',
                         { maxIterations: $max_iterations,
                           dampingFactor: $damping_factor}
                         )
YIELD nodeId, score
RETURN gds.util.asNode(nodeId).name AS name, score as page_rank
ORDER BY page_rank DESC, name ASC

"""

max_iterations = 20
damping_factor = 0.05

my_neo4j_run_query_pandas(query, max_iterations=max_iterations, damping_factor=damping_factor)

Algorithm # 2: Degree Centrality (Countries Most Involved in Refugee Movement)

#Create Graph in Memory to Run Degree Centrality Algorithm
query = "CALL gds.graph.drop('refugee_graph', false) yield graphName"
session.run(query)

query = "CALL gds.graph.project('refugee_graph', 'Country', 'asylum', {relationshipProperties: 'total_refugees'})"
session.run(query)

query = """
CALL gds.degree.stream('refugee_graph')
YIELD nodeId, score
RETURN gds.util.asNode(nodeId).name AS country, score
ORDER BY score DESC
LIMIT 10
"""
df = my_neo4j_run_query_pandas(query)
print("Degree Centrality (Most connected countries):")
display(df)

Algorithm 3: Betweenness Centrality (Countries that are Frequent Intermediaries)

#Make weights the inverse of the original to give larger weights more influence in the all pairs shortest path
#calculation

query = """
MATCH ()-[r:asylum]->()
WHERE r.weight <> 0
SET r.inv_weight = 1.0 / r.weight
RETURN r.weight, r.inv_weight
LIMIT 10

"""

df = my_neo4j_run_query_pandas(query)
display(df)

# Sanity Check to make sure weights were inverted

query = """
MATCH (n1:Country)-[r:asylum]->(n2:Country)
RETURN n1.name AS origin,
       n2.name AS asylum,
       r.weight AS total_refugees,
       r.inv_weight AS inv_weight,
       r.year AS year
ORDER BY total_refugees DESC
LIMIT 10
"""
df = my_neo4j_run_query_pandas(query)
display(df)

#Create Graph in Memory to Run Betweenness Centrality Algorithm
# Drop the existing graph if it exists
query = "CALL gds.graph.drop('refugee_graph_inv', false) YIELD graphName"
session.run(query)

# Project a new graph using 'inv_weight' as the relationship weight
query = """
CALL gds.graph.project(
  'refugee_graph_inv',
  'Country',
  {
    asylum: {
      properties: ['inv_weight']
    }
  }
)
"""
session.run(query)

#Run Betweenness Centrality Algorithm

query = """
CALL gds.betweenness.stream('refugee_graph_inv', {
  relationshipWeightProperty: 'inv_weight'
})
YIELD nodeId, score
RETURN gds.util.asNode(nodeId).name AS country, score
ORDER BY score DESC
LIMIT 10
"""
df = my_neo4j_run_query_pandas(query)
print("Betweenness Centrality (countries that are strong intermediaries):")
display(df)

Graph 2: Neo4j - Country of Asylum to Country of Origin (Immigrating)

#Wipe out database to create new graph

my_neo4j_wipe_out_database()

#Create Nodes for Each Country
for country in country_list:
    my_neo4j_create_node(country)

my_neo4j_nodes_relationships()

#No Relationships Yet, As Expected
my_neo4j_number_nodes_relationships()

#Number of Nodes Matches Country Count Above

#Set up function

def my_neo4j_create_relationship_reverse(country_asylum, country_origin, weight, year):
    query = """
    MATCH (from:Country), (to:Country)
    WHERE from.name = $country_asylum AND to.name = $country_origin
    CREATE (from)-[:ORIGIN {weight: $weight, year: $year}]->(to)
    """
    session.run(query, country_asylum=country_asylum, country_origin=country_origin, weight=weight, year=year)

#Create Relationships between Nodes, One-Directional from Country of Asylum to Country of Origin
#With Weight as Total_Refugees (Refugees plus Asylum Seekers)

for row in range(len(population)):
    country_asylum = population['coa_name'][row]
    country_origin = population['coo_name'][row]
    total_refugees = population['total_refugees'][row]
    year = population['year'][row]


    my_neo4j_create_relationship_reverse(country_asylum=country_asylum, country_origin=country_origin, weight=total_refugees, year=year)

my_neo4j_nodes_relationships()

my_neo4j_number_nodes_relationships()

## Top countries by total refugees (top countries in creating refugees)

query = """
MATCH (n1:Country)<-[r:ORIGIN]-(n2:Country)
RETURN n2.name AS asylum, sum(r.weight) AS total_refugees
ORDER BY total_refugees DESC
LIMIT 10
"""
df = my_neo4j_run_query_pandas(query)
display(df)

Algorithm for Graph 2: Use PageRank to Find Most Influential Countries in Creating Refugees

query = "CALL gds.graph.drop('ds_graph_2', false) yield graphName"
session.run(query)

query = "CALL gds.graph.project('ds_graph_2', 'Country', 'ORIGIN', {relationshipProperties: 'weight'})"
session.run(query)

query = """

CALL gds.pageRank.stream('ds_graph_2',
                         { maxIterations: $max_iterations,
                           dampingFactor: $damping_factor}
                         )
YIELD nodeId, score
RETURN gds.util.asNode(nodeId).name AS name, score as page_rank
ORDER BY page_rank DESC, name ASC

"""

max_iterations = 20
damping_factor = 0.05

my_neo4j_run_query_pandas(query, max_iterations=max_iterations, damping_factor=damping_factor)