combine_clean_acra.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "Overview\n",
    "\n",
    "download latest acra data\n",
    "data are separated into multiple files alphabetically\n",
    "combine multiple files into one\n",
    "clean and reorganise columns to make it user friendly as columns are in a messy order\n",
    "drop columns that are mostly missing values, using a user defined threshold\n",
    "write clean df to file with the last data update date\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# imports\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import glob\n",
    "import requests\n",
    "import os\n",
    "import re\n",
    "from zipfile import ZipFile\n",
    "from io import BytesIO\n",
    "from pathlib import Path\n",
    "from tqdm.notebook import tqdm_notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define acra data download url\n",
    "# https://data.gov.sg/dataset/acra-information-on-corporate-entities\n",
    "acra_download_url = 'https://data.gov.sg/dataset/21d477f2-6e1b-4232-82b3-59e804dc2f6a/download'\n",
    "# define folder to extract acra data files\n",
    "acra_folder = 'acra-information-on-corporate-entities'\n",
    "# define metadata file name\n",
    "metadata_filename = 'metadata-acra-information-on-corporate-entities.txt'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# download data from url\n",
    "acra_data_zip = requests.get(acra_download_url)\n",
    "# extract files from zip into a folder\n",
    "acra_data_zip = ZipFile(BytesIO(acra_data_zip.content))\n",
    "acra_data_zip.extractall(acra_folder)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read metadata as one string\n",
    "metadata = Path(os.path.join(acra_folder, metadata_filename)).read_text()\n",
    "# define regex pattern to extract last updated date\n",
    "last_updated_date_pattern = 'Last Updated: \\'(\\d{4}-\\d{2}-\\d{2})'\n",
    "# get first instance of 'Last Updated' date\n",
    "last_updated = re.findall(last_updated_date_pattern, metadata)[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get a list of all csv files\n",
    "list_of_files = glob.glob(acra_folder+'\\\\*.csv')\n",
    "\n",
    "# create empty dataframe to store data\n",
    "full_df = pd.DataFrame()\n",
    "# loop through each file and concat to df\n",
    "for file in tqdm_notebook(list_of_files):\n",
    "    # read csv\n",
    "    df = pd.read_csv(file)\n",
    "    # concat df to full_df\n",
    "    full_df = pd.concat([full_df, df])\n",
    "\n",
    "# replace 'na' with nan\n",
    "full_df = full_df.replace('na', np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define date columns\n",
    "date_columns = ['uen_issue_date', 'registration_incorporation_date', 'account_due_date', 'annual_return_date']\n",
    "# convert datetime columns to date\n",
    "full_df[date_columns] = full_df[date_columns].apply(pd.to_datetime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define organised list of columns\n",
    "clean_columns = [\n",
    "    'uen',\n",
    "    'uen_issue_date',\n",
    "    'registration_incorporation_date',\n",
    "    'issuance_agency_id',\n",
    "    'entity_name',\n",
    "    'entity_status_description',\n",
    "    'entity_type_description',\n",
    "    'business_constitution_description',\n",
    "    'company_type_description',\n",
    "    'primary_ssic_code',\n",
    "    'primary_ssic_description',\n",
    "    'primary_user_described_activity',\n",
    "    'secondary_ssic_code',\n",
    "    'secondary_ssic_description',\n",
    "    'secondary_user_described_activity',\n",
    "    'address_type',\n",
    "    'block',\n",
    "    'street_name',\n",
    "    'building_name',\n",
    "    'level_no',\n",
    "    'unit_no',\n",
    "    'postal_code',\n",
    "    'other_address_line1',\n",
    "    'other_address_line2',\n",
    "    'account_due_date',\n",
    "    'annual_return_date',\n",
    "    'no_of_charges',\n",
    "    'no_of_officers',\n",
    "    'paf_constitution_description',\n",
    "    'uen_of_audit_firm1',\n",
    "    'name_of_audit_firm1',\n",
    "    'uen_of_audit_firm2',\n",
    "    'name_of_audit_firm2',\n",
    "    'uen_of_audit_firm3',\n",
    "    'name_of_audit_firm3',\n",
    "    'uen_of_audit_firm4',\n",
    "    'name_of_audit_firm4',\n",
    "    'uen_of_audit_firm5',\n",
    "    'name_of_audit_firm5',\n",
    "    'former_entity_name1',\n",
    "    'former_entity_name2',\n",
    "    'former_entity_name3',\n",
    "    'former_entity_name4',\n",
    "    'former_entity_name5',\n",
    "    'former_entity_name6',\n",
    "    'former_entity_name7',\n",
    "    'former_entity_name8',\n",
    "    'former_entity_name9',\n",
    "    'former_entity_name10',\n",
    "    'former_entity_name11',\n",
    "    'former_entity_name12',\n",
    "    'former_entity_name13',\n",
    "    'former_entity_name14',\n",
    "    'former_entity_name15',\n",
    "    'paid_up_capital1_currency',\n",
    "    'paid_up_capital1_ordinary',\n",
    "    'paid_up_capital1_others',\n",
    "    'paid_up_capital1_preference',\n",
    "    'paid_up_capital2_currency',\n",
    "    'paid_up_capital2_ordinary',\n",
    "    'paid_up_capital2_others',\n",
    "    'paid_up_capital2_preference',\n",
    "    'paid_up_capital3_currency',\n",
    "    'paid_up_capital3_ordinary',\n",
    "    'paid_up_capital3_others',\n",
    "    'paid_up_capital3_preference',\n",
    "    'paid_up_capital4_currency',\n",
    "    'paid_up_capital4_ordinary',\n",
    "    'paid_up_capital4_others',\n",
    "    'paid_up_capital4_preference',\n",
    "    'paid_up_capital5_currency',\n",
    "    'paid_up_capital5_ordinary',\n",
    "    'paid_up_capital5_others',\n",
    "    'paid_up_capital5_preference',\n",
    "    'paid_up_capital6_currency',\n",
    "    'paid_up_capital6_ordinary',\n",
    "    'paid_up_capital6_others',\n",
    "    'paid_up_capital6_preference',\n",
    "    'paid_up_capital7_currency',\n",
    "    'paid_up_capital7_ordinary',\n",
    "    'paid_up_capital7_others',\n",
    "    'paid_up_capital7_preference',\n",
    "    'paid_up_capital8_currency',\n",
    "    'paid_up_capital8_ordinary',\n",
    "    'paid_up_capital8_others',\n",
    "    'paid_up_capital8_preference',\n",
    "    'paid_up_capital9_currency',\n",
    "    'paid_up_capital9_ordinary',\n",
    "    'paid_up_capital9_others',\n",
    "    'paid_up_capital9_preference',\n",
    "    'paid_up_capital10_currency',\n",
    "    'paid_up_capital10_ordinary',\n",
    "    'paid_up_capital10_others',\n",
    "    'paid_up_capital10_preference'\n",
    "    ]\n",
    "\n",
    "# reorganise columns\n",
    "full_df = full_df[clean_columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create df to store columns and the count percentage of nulls\n",
    "missing_values_perc = pd.DataFrame(full_df.isnull().sum() / len(full_df)).reset_index().rename(columns={'index':'Column', 0:'Percentage Missing Values'})\n",
    "\n",
    "\n",
    "##### adjust threshold here #####\n",
    "\n",
    "# define threshold for missing values to drop column\n",
    "# e.g. if 0.9 of rows in a column are missing values, drop the column\n",
    "missing_value_threshold = 0.9 \n",
    "\n",
    "##### adjust threshold here #####\n",
    "\n",
    "\n",
    "# get list of columns to drop\n",
    "list_of_columns_to_drop = list(missing_values_perc['Column'].loc[(missing_values_perc['Percentage Missing Values'] >= missing_value_threshold)])\n",
    "\n",
    "# drop columns\n",
    "clean_df = full_df.drop(list_of_columns_to_drop, axis=1).reset_index(drop=True)\n",
    "# print list of columns to drop\n",
    "print(f'The {len(list_of_columns_to_drop)} columns below have been dropped as at least {missing_value_threshold * 100}% of the values in the column are missing.')\n",
    "print('\\n')\n",
    "print(list_of_columns_to_drop)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# display\n",
    "display(clean_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write to file\n",
    "clean_df.to_csv(acra_folder+'-cleaned-'+last_updated+'.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "679b7062dbcce6defa5eb60bd296f7870af52ac44ea245ea1a0a9db6ebb5bb0e"
  },
  "kernelspec": {
   "display_name": "Python 3.8.8 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}