{
"cells": [
{
"cell_type": "code",
"execution_count": 2,
"id": "2dbe2890-3b90-4978-9648-625dc8d7a949",
"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": 1,
"id": "3a4cfa35-6b4e-48bc-acc6-09d426cd6c3e",
"metadata": {},
"outputs": [],
"source": [
"import pyarrow as pa\n",
"import pyarrow.csv\n",
"import pyarrow.parquet"
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "e5acbdd4-a266-477a-b720-a68e16e6c8f6",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 1.18 s, sys: 996 ms, total: 2.18 s\n",
"Wall time: 575 ms\n"
]
}
],
"source": [
"%%time\n",
"t = pa.csv.read_csv(\"hdma-wi-2021.csv\")"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "dd5941ac-fc66-4037-81f8-4f32b5b9e4bc",
"metadata": {},
"outputs": [],
"source": [
"pa.parquet.write_table(t, \"hdma-wi-2021.parquet\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ed698328-e057-41a5-89d9-351166331a6e",
"metadata": {},
"outputs": [],
"source": [
"# point 1: Parquet lets us skip slow schema inference"
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "3c3b8e74-d1d6-412e-9dc9-f6f1cc252ce7",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 396 ms, sys: 102 ms, total: 498 ms\n",
"Wall time: 147 ms\n"
]
}
],
"source": [
"%%time\n",
"t = pa.parquet.read_table(\"hdma-wi-2021.parquet\")"
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "d582cd7a-27ed-4c09-a22e-8663bd24e231",
"metadata": {},
"outputs": [],
"source": [
"# point 2: Parquet is byte encoded"
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "9e2d33ce-3a07-405a-b379-ef86e6e0e076",
"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": 11,
"id": "76debfbf-a7ca-48f6-8ca0-c9a00fbd3dd9",
"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": null,
"id": "d891d58c-8d8b-4dc9-bb2f-d12a03db465e",
"metadata": {},
"outputs": [],
"source": [
"# point 3: Parquet files are column oriented"
]
},
{
"cell_type": "code",
"execution_count": 13,
"id": "3159c561-122d-46af-96fb-0093368f6086",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 26.3 ms, sys: 2.61 ms, total: 29 ms\n",
"Wall time: 20.8 ms\n"
]
}
],
"source": [
"%%time\n",
"t2 = pa.parquet.read_table(\"hdma-wi-2021.parquet\", columns=[\"lei\", \"census_tract\"])"
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "51ad1ba1-9ea7-4188-b96d-aaa1847f3e95",
"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": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"t"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "28a0a2d6-95a1-4c86-9577-ef62721c19d6",
"metadata": {},
"outputs": [],
"source": [
"# point 4: Parquet is compressed, with snappy by default"
]
},
{
"cell_type": "code",
"execution_count": 15,
"id": "9ee9812e-76d9-4bf3-9194-4556f83bf0ff",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 698 ms, sys: 18.6 ms, total: 717 ms\n",
"Wall time: 730 ms\n"
]
}
],
"source": [
"%%time\n",
"pa.parquet.write_table(t, \"hdma-wi-2021.parquet\", compression=\"snappy\")"
]
},
{
"cell_type": "code",
"execution_count": 16,
"id": "487c9b6f-559c-4777-b498-b4f41cd8667a",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CPU times: user 2.21 s, sys: 13.9 ms, total: 2.22 s\n",
"Wall time: 2.22 s\n"
]
}
],
"source": [
"%%time\n",
"pa.parquet.write_table(t, \"hdma-wi-2021-gzip.parquet\", compression=\"gzip\")"
]
},
{
"cell_type": "code",
"execution_count": 17,
"id": "f2c3e5a3-7bf2-4f92-8019-e13b8cc28c28",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"total 216M\n",
"-rw-rw-r-- 1 tharter tharter 333 Feb 20 15:45 Dockerfile\n",
"-rw-r----- 1 tharter tharter 167M Nov 1 2022 hdma-wi-2021.csv\n",
"-rw-rw-r-- 1 tharter tharter 13M Feb 24 09:04 hdma-wi-2021-gzip.parquet\n",
"-rw-rw-r-- 1 tharter tharter 16M Feb 24 09:03 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 17K Feb 24 09:03 lec1.ipynb\n",
"drwxrwxr-x 3 tharter tharter 4.0K Feb 21 09:26 old\n",
"-rw-rw-r-- 1 tharter tharter 1.8K Feb 20 15:45 requirements.txt\n",
"drwxrwxr-x 3 tharter tharter 4.0K Feb 21 11:39 shared\n"
]
}
],
"source": [
"!ls -lh"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "92459058-9a4b-4da7-8820-65a6ebb70801",
"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
}