{
"cells": [
{
"cell_type": "code",
"execution_count": 2,
"id": "803ee3d0-11db-4b3c-901f-c09969c42a99",
"metadata": {},
"outputs": [],
"source": [
"# !wget https://pages.cs.wisc.edu/~harter/cs544/data/hdma-wi-2021.zip\n",
"# !unzip hdma-wi-2021.zip"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "dc2864a3-8f39-440e-9cc1-a88e6fcf764b",
"metadata": {},
"outputs": [],
"source": [
"import pyarrow as pa\n",
"import pyarrow.csv\n",
"import pyarrow.parquet"
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "ddb42c11-e332-42cd-be7f-4ce2ce74ed6b",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 1.09 s, sys: 810 ms, total: 1.9 s\n",
"Wall time: 506 ms\n"
]
}
],
"source": [
"%%time\n",
"t = pa.csv.read_csv(\"hdma-wi-2021.csv\")"
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "a582a756-1c75-4869-8f27-b849320213e1",
"metadata": {},
"outputs": [],
"source": [
"pa.parquet.write_table(t, \"hdma-wi-2021.parquet\")"
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "80d2c0a6-9c6e-475d-a56b-99a4e7fbb05d",
"metadata": {},
"outputs": [],
"source": [
"# point 1: we don't need to do slow schema inference with parquet"
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "c2f34f52-60c6-48c5-b29f-4f6131fb593e",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 400 ms, sys: 118 ms, total: 517 ms\n",
"Wall time: 157 ms\n"
]
}
],
"source": [
"%%time\n",
"t = pa.parquet.read_table(\"hdma-wi-2021.parquet\")"
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "99005a4b-4eb8-4970-a1cd-65422bb6dad6",
"metadata": {},
"outputs": [],
"source": [
"# point 2: parquet uses a binary encoding"
]
},
{
"cell_type": "code",
"execution_count": 13,
"id": "abc45de9-6b4d-4b4e-a2ff-127a69a9e3c0",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"b'activity_year,lei,derived_msa-md,state_code,county_code,census_tract,conforming_loan_limit,derived_l'\n"
]
}
],
"source": [
"with open(\"hdma-wi-2021.csv\", \"rb\") as f:\n",
" print(f.read(100))"
]
},
{
"cell_type": "code",
"execution_count": 14,
"id": "e642e241-078c-45ba-8cf0-6a7587720e17",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"b'PAR1\\x15\\x04\\x15\\x10\\x15\\x14L\\x15\\x02\\x15\\x00\\x12\\x00\\x00\\x08\\x1c\\xe5\\x07\\x00\\x00\\x00\\x00\\x00\\x00\\x15\\x00\\x15\\x1a\\x15\\x1e,\\x15\\x8e\\xce6\\x15\\x10\\x15\\x06\\x15\\x06\\x1c\\x18\\x08\\xe5\\x07\\x00\\x00\\x00\\x00\\x00\\x00\\x18\\x08\\xe5\\x07\\x00\\x00\\x00\\x00\\x00\\x00\\x16\\x00(\\x08\\xe5\\x07\\x00\\x00\\x00\\x00\\x00\\x00\\x18\\x08\\xe5\\x07\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\r0\\x04\\x00\\x00\\x00\\x8e\\xce6'\n"
]
}
],
"source": [
"with open(\"hdma-wi-2021.parquet\", \"rb\") as f:\n",
" print(f.read(100))"
]
},
{
"cell_type": "code",
"execution_count": 15,
"id": "912485a1-23c5-49cd-bd75-16bd860f8d3a",
"metadata": {},
"outputs": [],
"source": [
"# point 3: parquet is column oriented"
]
},
{
"cell_type": "code",
"execution_count": 17,
"id": "4095de52-6859-440c-b614-cdeb9c28ebe6",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 26.8 ms, sys: 4.41 ms, total: 31.2 ms\n",
"Wall time: 22.3 ms\n"
]
}
],
"source": [
"%%time\n",
"t2 = pa.parquet.read_table(\"hdma-wi-2021.parquet\", columns=[\"lei\", \"census_tract\"])"
]
},
{
"cell_type": "code",
"execution_count": 16,
"id": "d277e063-d31c-495d-8fe1-92883447c451",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"pyarrow.Table\n",
"activity_year: int64\n",
"lei: string\n",
"derived_msa-md: int64\n",
"state_code: string\n",
"county_code: int64\n",
"census_tract: int64\n",
"conforming_loan_limit: string\n",
"derived_loan_product_type: string\n",
"derived_dwelling_category: string\n",
"derived_ethnicity: string\n",
"derived_race: string\n",
"derived_sex: string\n",
"action_taken: int64\n",
"purchaser_type: int64\n",
"preapproval: int64\n",
"loan_type: int64\n",
"loan_purpose: int64\n",
"lien_status: int64\n",
"reverse_mortgage: int64\n",
"open-end_line_of_credit: int64\n",
"business_or_commercial_purpose: int64\n",
"loan_amount: double\n",
"loan_to_value_ratio: string\n",
"interest_rate: string\n",
"rate_spread: string\n",
"hoepa_status: int64\n",
"total_loan_costs: string\n",
"total_points_and_fees: string\n",
"origination_charges: string\n",
"discount_points: string\n",
"lender_credits: string\n",
"loan_term: string\n",
"prepayment_penalty_term: string\n",
"intro_rate_period: string\n",
"negative_amortization: int64\n",
"interest_only_payment: int64\n",
"balloon_payment: int64\n",
"other_nonamortizing_features: int64\n",
"property_value: string\n",
"construction_method: int64\n",
"occupancy_type: int64\n",
"manufactured_home_secured_property_type: int64\n",
"manufactured_home_land_property_interest: int64\n",
"total_units: string\n",
"multifamily_affordable_units: string\n",
"income: int64\n",
"debt_to_income_ratio: string\n",
"applicant_credit_score_type: int64\n",
"co-applicant_credit_score_type: int64\n",
"applicant_ethnicity-1: int64\n",
"applicant_ethnicity-2: int64\n",
"applicant_ethnicity-3: int64\n",
"applicant_ethnicity-4: int64\n",
"applicant_ethnicity-5: int64\n",
"co-applicant_ethnicity-1: int64\n",
"co-applicant_ethnicity-2: int64\n",
"co-applicant_ethnicity-3: int64\n",
"co-applicant_ethnicity-4: int64\n",
"co-applicant_ethnicity-5: null\n",
"applicant_ethnicity_observed: int64\n",
"co-applicant_ethnicity_observed: int64\n",
"applicant_race-1: int64\n",
"applicant_race-2: int64\n",
"applicant_race-3: int64\n",
"applicant_race-4: int64\n",
"applicant_race-5: int64\n",
"co-applicant_race-1: int64\n",
"co-applicant_race-2: int64\n",
"co-applicant_race-3: int64\n",
"co-applicant_race-4: int64\n",
"co-applicant_race-5: int64\n",
"applicant_race_observed: int64\n",
"co-applicant_race_observed: int64\n",
"applicant_sex: int64\n",
"co-applicant_sex: int64\n",
"applicant_sex_observed: int64\n",
"co-applicant_sex_observed: int64\n",
"applicant_age: string\n",
"co-applicant_age: string\n",
"applicant_age_above_62: string\n",
"co-applicant_age_above_62: string\n",
"submission_of_application: int64\n",
"initially_payable_to_institution: int64\n",
"aus-1: int64\n",
"aus-2: int64\n",
"aus-3: int64\n",
"aus-4: int64\n",
"aus-5: int64\n",
"denial_reason-1: int64\n",
"denial_reason-2: int64\n",
"denial_reason-3: int64\n",
"denial_reason-4: int64\n",
"tract_population: int64\n",
"tract_minority_population_percent: double\n",
"ffiec_msa_md_median_family_income: int64\n",
"tract_to_msa_income_percentage: int64\n",
"tract_owner_occupied_units: int64\n",
"tract_one_to_four_family_homes: int64\n",
"tract_median_age_of_housing_units: int64\n",
"----\n",
"activity_year: [[2021,2021,2021,2021,2021,...,2021,2021,2021,2021,2021],[2021,2021,2021,2021,2021,...,2021,2021,2021,2021,2021],[2021,2021,2021,2021,2021,...,2021,2021,2021,2021,2021],[2021,2021,2021,2021,2021,...,2021,2021,2021,2021,2021]]\n",
"lei: [[\"54930034MNPILHP25H80\",\"54930034MNPILHP25H80\",\"54930034MNPILHP25H80\",\"54930034MNPILHP25H80\",\"54930034MNPILHP25H80\",...,\"254900X6OAHFW6BUT219\",\"254900X6OAHFW6BUT219\",\"254900X6OAHFW6BUT219\",\"254900X6OAHFW6BUT219\",\"254900X6OAHFW6BUT219\"],[\"254900X6OAHFW6BUT219\",\"254900X6OAHFW6BUT219\",\"254900X6OAHFW6BUT219\",\"254900X6OAHFW6BUT219\",\"254900X6OAHFW6BUT219\",...,\"549300KY533JFETOYG46\",\"549300KY533JFETOYG46\",\"549300KY533JFETOYG46\",\"549300KY533JFETOYG46\",\"549300KY533JFETOYG46\"],[\"549300KY533JFETOYG46\",\"549300KY533JFETOYG46\",\"549300KY533JFETOYG46\",\"549300KY533JFETOYG46\",\"549300KY533JFETOYG46\",...,\"ZF85QS7OXKPBG52R7N18\",\"ZF85QS7OXKPBG52R7N18\",\"ZF85QS7OXKPBG52R7N18\",\"ZF85QS7OXKPBG52R7N18\",\"ZF85QS7OXKPBG52R7N18\"],[\"ZF85QS7OXKPBG52R7N18\",\"ZF85QS7OXKPBG52R7N18\",\"ZF85QS7OXKPBG52R7N18\",\"ZF85QS7OXKPBG52R7N18\",\"ZF85QS7OXKPBG52R7N18\",...,\"54930034MNPILHP25H80\",\"54930034MNPILHP25H80\",\"54930034MNPILHP25H80\",\"54930034MNPILHP25H80\",\"54930034MNPILHP25H80\"]]\n",
"derived_msa-md: [[99999,99999,99999,29404,11540,...,33460,20740,33460,33460,99999],[99999,33460,33460,33460,20740,...,99999,33340,33340,33340,33340],[99999,33340,39540,33340,39540,...,36780,36780,11540,33340,33340],[29100,31540,99999,22540,99999,...,31540,99999,31540,99999,31540]]\n",
"state_code: [[\"WI\",\"WI\",\"WI\",\"WI\",\"WI\",...,\"WI\",\"WI\",\"WI\",\"WI\",\"WI\"],[\"WI\",\"WI\",\"WI\",\"WI\",\"WI\",...,\"WI\",\"WI\",\"WI\",\"WI\",\"WI\"],[\"WI\",\"WI\",\"WI\",\"WI\",\"WI\",...,\"WI\",\"WI\",\"WI\",\"WI\",\"WI\"],[\"WI\",\"WI\",\"WI\",\"WI\",\"WI\",...,\"WI\",\"WI\",\"WI\",\"WI\",\"WI\"]]\n",
"county_code: [[55027,55001,55013,55059,55087,...,55109,55017,55093,55109,55033],[55095,55109,55109,55109,55017,...,55027,55079,55133,55133,55079],[55027,55133,55101,55079,55101,...,55139,55139,55087,55131,55079],[55063,55021,55011,55039,55097,...,55025,55029,55025,55051,55021]]\n",
"census_tract: [[55027961800,55001950501,55013970400,55059002000,55087013300,...,55109121000,55017011100,55093960700,55109120904,55033970400],[55095960500,55109120700,55109121000,55109120904,55017010700,...,55027961500,55079090300,55133203305,55133203406,55079000303],[55027960800,55133201600,55101000901,55079016100,55101002402,...,55139001100,55139001803,55087012100,55131450104,55079150301],[55063010201,55021970100,55011960400,55039041300,55097960600,...,55025011301,55029100800,55025012300,55051180300,55021970300]]\n",
"conforming_loan_limit: [[\"C\",\"C\",\"C\",\"C\",\"C\",...,\"C\",\"C\",\"C\",\"C\",\"C\"],[\"C\",\"C\",\"C\",\"C\",\"C\",...,\"C\",\"C\",\"C\",\"C\",\"C\"],[\"C\",\"C\",\"C\",\"C\",\"C\",...,\"C\",\"C\",\"C\",\"C\",\"C\"],[\"C\",\"C\",\"C\",\"C\",\"C\",...,\"C\",\"C\",\"C\",\"C\",\"C\"]]\n",
"derived_loan_product_type: [[\"Conventional:First Lien\",\"Conventional:First Lien\",\"Conventional:First Lien\",\"Conventional:First Lien\",\"Conventional:First Lien\",...,\"Conventional:First Lien\",\"Conventional:First Lien\",\"FSA/RHS:First Lien\",\"Conventional:Subordinate Lien\",\"Conventional:First Lien\"],[\"Conventional:First Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",...,\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:First Lien\"],[\"Conventional:First Lien\",\"Conventional:First Lien\",\"Conventional:First Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",...,\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\"],[\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:Subordinate Lien\",\"Conventional:First Lien\",...,\"Conventional:First Lien\",\"Conventional:First Lien\",\"Conventional:First Lien\",\"Conventional:First Lien\",\"Conventional:First Lien\"]]\n",
"derived_dwelling_category: [[\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",...,\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\"],[\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",...,\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\"],[\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",...,\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\"],[\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",...,\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\",\"Single Family (1-4 Units):Site-Built\"]]\n",
"derived_ethnicity: [[\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Joint\",...,\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\"],[\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Ethnicity Not Available\",\"Not Hispanic or Latino\",...,\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Hispanic or Latino\"],[\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Hispanic or Latino\",\"Hispanic or Latino\",...,\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\"],[\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Ethnicity Not Available\",...,\"Ethnicity Not Available\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\",\"Not Hispanic or Latino\"]]\n",
"..."
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"t"
]
},
{
"cell_type": "code",
"execution_count": 18,
"id": "41baa49f-51dd-4c6f-9c32-33ba44f4de83",
"metadata": {},
"outputs": [],
"source": [
"# point 4: Parquet files are compressed with snappy by default"
]
},
{
"cell_type": "code",
"execution_count": 19,
"id": "5a043407-2a33-4c0c-8adf-bef72156d3ca",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"total 204M\n",
"-rw-r----- 1 tharter tharter 167M Nov 1 2022 hdma-wi-2021.csv\n",
"-rw-rw-r-- 1 tharter tharter 16M Feb 24 11:08 hdma-wi-2021.parquet\n",
"-rw-rw-r-- 1 tharter tharter 21M Jan 5 2023 hdma-wi-2021.zip\n",
"-rw-rw-r-- 1 tharter tharter 18K Feb 24 10:11 lec1.ipynb\n",
"-rw-rw-r-- 1 tharter tharter 16K Feb 24 11:14 lec2.ipynb\n"
]
}
],
"source": [
"!ls -lh"
]
},
{
"cell_type": "code",
"execution_count": 23,
"id": "2ff0caa9-6f57-45b0-8ff4-910eb0ad2359",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 716 ms, sys: 24.2 ms, total: 740 ms\n",
"Wall time: 754 ms\n"
]
}
],
"source": [
"%%time\n",
"pa.parquet.write_table(t, \"hdma-wi-2021.parquet\", compression=\"snappy\")"
]
},
{
"cell_type": "code",
"execution_count": 24,
"id": "ea976df1-d258-4446-a277-9a9d750bfc49",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 2.15 s, sys: 15.7 ms, total: 2.17 s\n",
"Wall time: 2.17 s\n"
]
}
],
"source": [
"%%time\n",
"pa.parquet.write_table(t, \"hdma-wi-2021-gzip.parquet\", compression=\"gzip\")"
]
},
{
"cell_type": "code",
"execution_count": 25,
"id": "c587e2e5-2167-4663-ab5c-5df51f3f9937",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"total 216M\n",
"-rw-r----- 1 tharter tharter 167M Nov 1 2022 hdma-wi-2021.csv\n",
"-rw-rw-r-- 1 tharter tharter 13M Feb 24 11:15 hdma-wi-2021-gzip.parquet\n",
"-rw-rw-r-- 1 tharter tharter 16M Feb 24 11:15 hdma-wi-2021.parquet\n",
"-rw-rw-r-- 1 tharter tharter 21M Jan 5 2023 hdma-wi-2021.zip\n",
"-rw-rw-r-- 1 tharter tharter 18K Feb 24 10:11 lec1.ipynb\n",
"-rw-rw-r-- 1 tharter tharter 16K Feb 24 11:14 lec2.ipynb\n"
]
}
],
"source": [
"!ls -lh"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "e525a734-ac33-40b4-a8c1-dc4c30b06b02",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"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.12.3"
}
},
"nbformat": 4,
"nbformat_minor": 5
}