Get data from CEIC

hk_export_us = Ceic.series_data([“563507567_SR250004867”], lang=“en”) hk_import_us = Ceic.series_data([“41394601_SR571098”], lang=“en”)

ceic_result = Ceic.series([“”], lang = “en”)

Convert to pandas DataFrames

Extract data from the CEIC API response structure

export_data = [(tp.date, tp.value) for tp in hk_export_us.data[0].time_points] export_df = pd.DataFrame(export_data, columns=[‘Time Period’, ‘Value’])

import_data = [(tp.date, tp.value) for tp in hk_import_us.data[0].time_points] import_df = pd.DataFrame(import_data, columns=[‘Time Period’, ‘Value’])

Merge dataframes on time period

merged_df = pd.merge( export_df, import_df, left_on=‘Time Period’, right_on=‘Time Period’, suffixes=(’_export’, ’_import’) )

Calculate trade balance

merged_df[‘trade_balance’] = merged_df[‘Value_export’] - merged_df[‘Value_import’]

Melt the dataframe for better usage with Altair

melted_df = pd.melt( merged_df, id_vars=[‘Time Period’], value_vars=[‘Value_export’, ‘Value_import’, ‘trade_balance’], var_name=‘Metric’, value_name=‘Value’ )

Map column names to more readable labels

melted_df[‘Metric’] = melted_df[‘Metric’].replace({ ‘Value_export’: ‘Export’, ‘Value_import’: ‘Imports’, ‘trade_balance’: ‘Balance’ })

melted_df.head(5)

Convert Time Period to datetime for better handling

melted_df[‘Time Period’] = pd.to_datetime(melted_df[‘Time Period’])

Process data to get annual totals

melted_df[‘Year’] = melted_df[‘Time Period’].dt.year

Group by year and metric, and sum the values

annual_df = melted_df.groupby([‘Year’, ‘Metric’])[‘Value’].sum().reset_index()

Convert values to millions

annual_df[‘Value_Millions’] = annual_df[‘Value’] / 1000000

Create a chart that positions imports below the x-axis

chart = alt.Chart(annual_df[annual_df[‘Metric’] != ‘Balance’]).mark_bar().encode( x=alt.X(‘Year:O’, title=‘Year’), y=alt.Y(‘adjusted:Q’, title=‘Value (Million USD)’, scale=alt.Scale(zero=True)), color=alt.Color(‘Metric:N’, scale=alt.Scale( domain=[‘Export’, ‘Imports’], range=[‘#1f77b4’, ‘#ff7f0e’] )), tooltip=[‘Year:O’, ‘Metric:N’, ‘Value_Millions:Q’] ).transform_calculate( # Make imports negative to appear below x-axis adjusted=“datum.Metric === ‘Imports’ ? -datum.Value_Millions : datum.Value_Millions” )

Create a line chart for the balance

line = alt.Chart(annual_df[annual_df[‘Metric’] == ‘Balance’]).mark_line( color=‘white’, strokeWidth=2 ).encode( x=‘Year:O’, y=‘Value_Millions:Q’, tooltip=[‘Year:O’, ‘Value_Millions:Q’] )

Define years and events separately for easy updating

events_data = { 1997: ‘Handover; Asian financial crisis’, 2000: ‘China centers WTO’, 2008: ‘Global financial crisis’, 2014: ‘Occupy Central’, 2016: ‘Trump 1.0’, 2019: ‘Anti-extradition bill protests’, 2020: ‘National security law’ }

Create a DataFrame from the events data

events_df = pd.DataFrame({ ‘Year’: list(events_data.keys()), ‘Event’: list(events_data.values()), ‘y’: [-15000, -13500, -12000, -11000, -9000, -6000, -3000] # Adjusted y positions })

Create vertical rules for key years with annotations

rules = alt.Chart(events_df).mark_rule(strokeDash=[3, 3], color=‘gray’).encode( x=‘Year:O’ )

Add text annotations

annotations = alt.Chart(events_df).mark_text( align=‘left’, baseline=‘bottom’, dx=0, fontSize=10, angle=270 ).encode( x=‘Year:O’, y=‘y:Q’, text=‘Event’ )

Combine the charts

combined_chart = alt.layer(chart, line, rules, annotations).properties( width=760, height=500, title=‘Hong Kong-US Annual Trade Balance’ )

combined_chart