TraderJoe-Tract-Stat
Profiling Census Tracts of all the Trader Joe locations
Data Sourcing and Wrangling
-
Copy Pasta Text from PDF to Excel
-
TextWrangler
- Remove Trading Hours
- Remove Alcohol
- Find
\n\n; Replace with\n - Find
-([0-9][0-9][0-9][0-9])\n; Replace with-\1\n~~ - Find
\n; Replace with| - Find
~~. ; Replace with\n - Find
|; Replace with\t
-
Wrangle and clean as needed.
-
You should get a file with
CityName,Address,Phoneof all Trader Joe Locations. -
Use Geocodio and append
Census Featuresto retrieve Census Tracts from Trader Joe (TJ) location addresses. This file is here
Append Census Features to append Census geographies to your location data.
Social Explorer
- Construct a Social Explorer Report to gather census variables from Census. In this case I sourced from ACS 2016 (5-year estimates)
- The report will need to be pruned to preserve relevant columns.
Merge Geocodio and Social Explorer
- Identify TJ census tracts in the Social explorer file by doing a vlookup
Final Data File
Use this File in your analysis.
Links
Measures used to create the Social Explorer report:
- % Total Population: White Alone
- % Total Population: Under 5 Years
- % Total Population: Two or More Races
- % Total Population: Some Other Race Alone
- % Total Population: Native Hawaiian and Other Pacific Islander Alone
- % Total Population: Male
- % Total Population: Female
- % Total Population: Black or African American Alone
- % Total Population: Asian Alone
- % Total Population: American Indian and Alaska Native Alone
- % Total Population: 85 Years and Over
- % Total Population: 75 to 84 Years
- % Total Population: 65 to 74 Years
- % Total Population: 55 to 64 Years
- % Total Population: 5 to 9 Years
- % Total Population: 45 to 54 Years
- % Total Population: 35 to 44 Years
- % Total Population: 25 to 34 Years
- % Total Population: 18 to 24 Years
- % Total Population: 15 to 17 Years
- % Total Population: 10 to 14 Years
- % Renter-Occupied Housing Units: Not Computed
- % Renter-Occupied Housing Units: Less than 10 Percent
- % Renter-Occupied Housing Units: 50 Percent or More
- % Renter-Occupied Housing Units: 30 to 49 Percent
- % Renter-Occupied Housing Units: 10 to 29 Percent
- % Population 25 Years and Over: Some College
- % Population 25 Years and Over: Professional School Degree
- % Population 25 Years and Over: Master's Degree
- % Population 25 Years and Over: Less than High School
- % Population 25 Years and Over: High School Graduate (Includes Equivalency)
- % Population 25 Years and Over: Doctorate Degree
- % Population 25 Years and Over: Bachelor's Degree
- % Households: Nonfamily Households: Female Householder
- % Households: Family Households: Other Family: Male Householder, No Wife Present
- % Households: Family Households: Other Family: Female Householder, No Husband Present
- % Households: Family Households: Other Family
- % Households: Family Households: Married-Couple Family
- % Households: Family Households
- % Civilian Population 18 Years and Over: Veteran: 65 Years and Over
- % Civilian Population 18 Years and Over: Veteran: 18 to 64 Years
- % Civilian Population 18 Years and Over: Veteran
- % Civilian Population 18 Years and Over: Nonveteran: 65 Years and Over
- % Civilian Population 18 Years and Over: Nonveteran: 18 to 64 Years
- % Civilian Population 18 Years and Over: Nonveteran
- % Households: Nonfamily Households: Male Householder
- % Households: Nonfamily Households
- % Total Population: White Alone
- % Total Population: Under 5 Years
- % Total Population: Two or More Races
- % Total Population: Some Other Race Alone
- % Total Population: Native Hawaiian and Other Pacific Islander Alone
- % Total Population: Male
- % Total Population: Female
- % Total Population: Black or African American Alone
- % Total Population: Asian Alone
- % Total Population: American Indian and Alaska Native Alone
- % Total Population: 85 Years and Over
- % Total Population: 75 to 84 Years
- % Total Population: 65 to 74 Years
- % Total Population: 55 to 64 Years
- % Total Population: 5 to 9 Years
- % Total Population: 45 to 54 Years
- % Total Population: 35 to 44 Years
- % Total Population: 25 to 34 Years
- % Total Population: 18 to 24 Years
- % Total Population: 15 to 17 Years
- % Total Population: 10 to 14 Years
- % Renter-Occupied Housing Units: Not Computed
- % Renter-Occupied Housing Units: Less than 10 Percent
- % Renter-Occupied Housing Units: 50 Percent or More
- % Renter-Occupied Housing Units: 30 to 49 Percent
- % Renter-Occupied Housing Units: 10 to 29 Percent
- % Population 25 Years and Over: Some College
- % Population 25 Years and Over: Professional School Degree
- % Population 25 Years and Over: Master's Degree
- % Population 25 Years and Over: Less than High School
- % Population 25 Years and Over: High School Graduate (Includes Equivalency)
- % Population 25 Years and Over: Doctorate Degree
- % Population 25 Years and Over: Bachelor's Degree
- % Households: Nonfamily Households: Female Householder
- % Households: Family Households: Other Family: Male Householder, No Wife Present
- % Households: Family Households: Other Family: Female Householder, No Husband Present
- % Households: Family Households: Other Family
- % Households: Family Households: Married-Couple Family
- % Households: Family Households
- % Civilian Population 18 Years and Over: Veteran: 65 Years and Over
- % Civilian Population 18 Years and Over: Veteran: 18 to 64 Years
- % Civilian Population 18 Years and Over: Veteran
- % Civilian Population 18 Years and Over: Nonveteran: 65 Years and Over
- % Civilian Population 18 Years and Over: Nonveteran: 18 to 64 Years
- % Civilian Population 18 Years and Over: Nonveteran
- % Households: Nonfamily Households: Male Householder
- % Households: Nonfamily Households
Analysis
- I created 2 groups of Census Tracts (TJs and non-TJs) and compared the averages across all the above measures.
- I then exported these group averages to a Google Sheet and Pivoted om them to estimate large differences between these Group Averages.
- For example, the largest differences between the TJ and non-TJ group averages was seen in:
% Total Population: Asian Alone% Population 25 Years and Over: Doctorate Degree% Population 25 Years and Over: Professional School Degree% Total Population: Black or African American Alone% Civilian Population 18 Years and Over: Veteran: 18 to 64 Years% Population 25 Years and Over: Less than High School
- Where there is atleast a 125% difference between TJ and non-TJ group averages.
- I use the TJ averages from thes features to identify eligible non-TJ census tracts
- These eligible non-TJ census tracts form the basis of likely locations for the next Trader Joe's locations.