Interactive Linked Scatter plot

# Import required libraries
import pandas as pd
import numpy as np

# Read the Excel file
df = pd.read_excel("AidDatasGlobalChineseDevelopmentFinanceDataset_v3.0.xlsx", 
                   sheet_name="GCDF_3.0")

# Select specific columns - equivalent to the dplyr select()
columns_to_keep = [
    "AidData Record ID", "Financier Country", "Recipient",
    "Recipient Region", "Commitment Year", "Completion Year",
    "Title", "Description", "Status", "Intent",
    "Flow Type Simplified", "Flow Class", "Sector Name",
    "Infrastructure", "Funding Agencies Type",
    "Implementing Agencies Type",
    "Adjusted Amount (Constant USD 2021)",
    "Location Narrative", "OECD ODA Income Group",
    "Geographic Level of Precision Available",
    "Geospatial Feature Available",
    "Interest Rate",
    "Grace Period",
    "Financial Distress"
]

df_filtered = df[columns_to_keep]

# Apply filters - equivalent to the dplyr filter()
df_filtered = df_filtered[
    (df_filtered['Flow Class'] != "Vague (Official Finance)") &
    (df_filtered['Adjusted Amount (Constant USD 2021)'] > 100000000.00) &
    (df_filtered['Geographic Level of Precision Available'] == "Precise") &
    (df_filtered['Geospatial Feature Available'] == "Yes")
]

# Define hover tool with less verbose information
hover_tool = HoverTool(
    tooltips=[
        ('Region', '@{Recipient Region}'),
        ('Title', '@Title'),
        ('Amount', '@{Adjusted Amount (Constant USD 2021)}{0.00a}')
    ],
    mode='mouse',  # Only show one point's information at a time
    point_policy='snap_to_data'  # This ensures it only shows the nearest point
)

from bokeh.transform import jitter
from bokeh.models import SingleIntervalTicker

output_notebook()


# When preparing the data, set initial alpha to be more prominent
distressed_projects = df_filtered[df_filtered['Financial Distress'] == 'Yes'].copy()
unfinished_projects = df_filtered[
    df_filtered['Status'].isin(['Implementation', 'Pipeline: Commitment'])
].copy()

# Set initial alpha to be more visible
distressed_projects['alpha'] = 0.7  # Increased from 0.5
unfinished_projects['alpha'] = 0.7  # Increased from 0.5

source_distressed = ColumnDataSource(distressed_projects)
source_unfinished = ColumnDataSource(unfinished_projects)

# Create hover tool
scatter_hover = HoverTool(
    tooltips=[('Region', '@{Recipient Region}'), ('Title', '@Title'),
              ('Amount', '@{Adjusted Amount (Constant USD 2021)}{0.00a}')],
    mode='mouse'
)

# Create first scatter plot with enhanced visual properties
p1 = figure(width=600, height=400, title='Financially Distressed Projects',
            tools=['box_select', 'lasso_select', 'tap', 'reset', scatter_hover])

p1.scatter('Commitment Year', 'Adjusted Amount (Constant USD 2021)',
          source=source_distressed, size=11, color='red',
          alpha='alpha',
          line_color='darkred',  # Add outline for better visibility
          line_width=1,
          nonselection_color='red',
           x=jitter('Commitment Year', width=0.2)) 

p1.xaxis.axis_label = 'Commitment Year'
p1.yaxis.axis_label = 'Amount (Billions USD)'
p1.yaxis.formatter = NumeralTickFormatter(format="0.00a")

# Create second scatter plot with enhanced visual properties
p2 = figure(width=600, height=400, title='Unfinished Projects',
            tools=['box_select', 'lasso_select', 'tap', 'reset', scatter_hover])

p2.scatter('Commitment Year', 'Adjusted Amount (Constant USD 2021)',
          source=source_unfinished, size=11, color='blue',
          alpha='alpha',
          line_color='navy',  # Add outline for better visibility
          line_width=1,
          nonselection_color='blue',
          y=jitter('Commitment Year', width=0.2))

p2.xaxis.axis_label = 'Commitment Year'
p2.yaxis.axis_label = 'Amount (Billions USD)'
p2.yaxis.formatter = NumeralTickFormatter(format="0.00a")

# Set consistent y-range
p1.y_range.start, p1.y_range.end = 0, 7_000_000_000
p2.y_range = p1.y_range

# Line plot with scatter points: Yearly counts
p3 = figure(width=1200, height=400, title='Number of Projects by Year',
            tools=['box_zoom', 'reset'], tooltips=[('Year', '@{Commitment Year}'), ('Count', '@count')])

# Customize x-axis to show all years in a smaller, rotated format
p3.xaxis.ticker = SingleIntervalTicker(interval=1)  
p3.xaxis.major_label_orientation = 0.7  
p3.xaxis.major_label_text_font_size = '8pt'  

# Add lines with corresponding scatter points
# Distressed Projects - red line and markers
p3.line('Commitment Year', 'count', source=source_distressed_yearly, 
        color='red', legend_label='Distressed Projects', line_width=2)
p3.scatter('Commitment Year', 'count', source=source_distressed_yearly, 
          color='red', size=8, legend_label='Distressed Projects', marker='circle')

# Unfinished Projects - blue line and markers
p3.line('Commitment Year', 'count', source=source_unfinished_yearly, 
        color='blue', legend_label='Unfinished Projects', line_width=2)
p3.scatter('Commitment Year', 'count', source=source_unfinished_yearly, 
          color='blue', size=8, legend_label='Unfinished Projects', marker='circle')

# Total Projects - green line and markers
p3.line('Commitment Year', 'count', source=source_total_yearly, 
        color='green', legend_label='Total Projects', line_width=2)
p3.scatter('Commitment Year', 'count', source=source_total_yearly, 
          color='green', size=8, legend_label='Total Projects', marker='circle')

# Configure axes and legend
p3.legend.click_policy = 'hide'
p3.xaxis.axis_label = 'Commitment Year'
p3.yaxis.axis_label = 'Number of Projects'


# Enhanced JavaScript callback with more dramatic alpha contrast
callback_code = """
    const source = cb_obj === s1.selected ? s1 : s2;
    const target = source === s1 ? s2 : s1;
    
    const source_ids = source.data['AidData Record ID'];
    const target_ids = target.data['AidData Record ID'];
    const selected_indices = cb_obj.indices;
    
    // Set very low alpha for non-matching points
    const new_target_alphas = new Array(target_ids.length).fill(0.05);  // Much lower alpha for non-matches
    
    if (selected_indices.length > 0) {
        const selected_ids = new Set(
            selected_indices.map(i => source_ids[i])
        );
        
        // Set high alpha for matching points
        target_ids.forEach((id, index) => {
            if (selected_ids.has(id)) {
                new_target_alphas[index] = 0.8;  // Higher alpha for matches
            }
        });
    } else {
        // Reset to default visibility
        new_target_alphas.fill(0.8);  // Reset to higher default alpha
    }
    
    target.data['alpha'] = new_target_alphas;
    target.change.emit();
"""

# Attach callbacks to both sources
source_distressed.selected.js_on_change('indices', CustomJS(
    args=dict(s1=source_distressed, s2=source_unfinished),
    code=callback_code
))

source_unfinished.selected.js_on_change('indices', CustomJS(
    args=dict(s1=source_distressed, s2=source_unfinished),
    code=callback_code
))

# Step 5: Layout and Display
layout = column(
    row(p1, p2, spacing=0),
    p3
)

show(layout)

Loading BokehJS ...

Correlation between distressed and unfinished:

Most of the highlighted points in both plots cluster between 2010-2017.

This period shows both the highest frequency of financial distress and corresponding unfinished projects。

The overlap suggests that projects facing financial difficulties during this period often remained unfinished. While we do not observe significant correlation between a project being financial distressed and uncompleted.

Time trends in number of projects:

The growth in total projects is mirrored by unfinished projects but not entirely by distressed projects, indicating that most unfinished projects are not necessarily distressed.

In early years (2000-2005), most projects were successfully completed with few issues. During 2010 to approximately 2017, a significant portion of total projects faced financial distress.

There is a post-2020 decline that while financial distress has decreased, completion issues have become more prevalent

The sharp decline in total projects after 2017 might indicate a more cautious approach to project approval or changes in investment strategies. Despite this decline, the proportion of unfinished projects remains relatively high, suggesting persistent challenges in project completion.

The worsening sovereign debt crisis in Europe and sluggish recovery in the U.S. economy had directly depressed external market demand for China. On the other hand, domestic excess production capacity in manufacturing sectors resulting from persisting high investment for decades, has reduced industrial investment, and in turn its output growth.

China's economy cooled in the fourth quarter under pressure from faltering domestic demand and bruising U.S. tariffs, dragging 2018 growth to the lowest level in nearly three decades and pressuring Beijing to roll out more stimulus to avert a sharper slowdown. Growing signs of weakness in China -- which has generated nearly a third of global growth in recent years -- are fueling anxiety about risks to the world economy.