Data Automobile make model years

Automobile make model year

Last imported 09:57AM UTC on Tuesday, March 26, 2013. See log

Automobile make models made in a particular year (e.g. 2007 Honda Civic).

We derive this data from our automobile make model year variant data.

Citing this data

To cite this data, reference Brighter Planet, "Automobile make model years," <http://data.brighterplanet.com/automobile_make_model_years>, retrieved 2013-05-21 15:39:58 UTC.

Download

Total: 11,000 records.

Random data sample

Sample: 5 records.

name	make name	model name	year	fuel code	fuel efficiency city	fuel efficiency city units	fuel efficiency highway	fuel efficiency highway units	type name	weighting
Acura 2.2CL/3.0CL 1997	Acura	2.2CL/3.0CL	1997	R	8.41731	kilometres_per_litre	11.2552	kilometres_per_litre	Passenger cars	0.0289
Acura 2.3CL/3.0CL 1998	Acura	2.3CL/3.0CL	1998	R	8.24289	kilometres_per_litre	11.5446	kilometres_per_litre	Passenger cars	0.0347
Acura 2.3CL/3.0CL 1999	Acura	2.3CL/3.0CL	1999	R	8.25488	kilometres_per_litre	11.5665	kilometres_per_litre	Passenger cars	0.041
Acura 2.5TL 1995	Acura	2.5TL	1995	P	7.4694	kilometres_per_litre	9.78303	kilometres_per_litre	Passenger cars	0.0189
Acura 2.5TL/3.2TL 1996	Acura	2.5TL/3.2TL	1996	P	7.30894	kilometres_per_litre	9.48641	kilometres_per_litre	Passenger cars	0.0244

Import steps

Click a step for more detail.

Ensure AutomobileMakeModelYearVariant is populated
```
AutomobileMakeModelYearVariant.run_data_miner!
```

Derive names AutomobileMakeModelYearVariant

::Earth::Utils.insert_ignore(:src => (AutomobileMakeModelYearVariant), :dest => (AutomobileMakeModelYear), :cols => ({ [:make_name, :model_name, :year] => :name, :make_name => :make_name, :model_name => :model_name, :year => :year }))

Identify hybrid vehicles

where("model_name LIKE '%HYBRID%'").update_all(:hybridity => (true))
where("make_name = 'Infiniti' AND model_name REGEXP '[A-Z][0-9]{2}H'").update_all(:hybridity => (true))
where("make_name = 'Lexus' AND model_name REGEXP '[A-Z]{2}[0-9]{3}H'").update_all(:hybridity => (true))
where(:make_name => "Chevrolet", :model_name => "VOLT").update_all(:hybridity => (true))
where(:make_name => "Honda", :model_name => "INSIGHT").update_all(:hybridity => (true))
where(:make_name => "Honda", :model_name => "CR-Z").update_all(:hybridity => (true))
where("make_name = 'Toyota' AND model_name LIKE 'PRIUS%'").update_all(:hybridity => (true))
where(:hybridity => (nil)).update_all(:hybridity => (false))

Derive fuel codes from AutomobileMakeModelYearVariant

model_years = arel_table
variants = AutomobileMakeModelYearVariant.arel_table
join_relation = variants[:make_name].eq(model_years[:make_name]).and(variants[:model_name].eq(model_years[:model_name])).and(variants[:year].eq(model_years[:year]))
update_all("fuel_code = (#{variants.project("GROUP_CONCAT(DISTINCT fuel_code)").where(join_relation).to_sql})")
update_all("alt_fuel_code = (#{variants.project("GROUP_CONCAT(DISTINCT alt_fuel_code)").where(join_relation).to_sql})")
where(:fuel_code => (["R,P", "P,R"])).update_all("fuel_code = 'G'")

Calculate city and highway fuel efficiency from AutomobileMakeModelYearVariants

model_years = arel_table
variants = AutomobileMakeModelYearVariant.arel_table
join_relation = model_years[:make_name].eq(variants[:make_name]).and(model_years[:model_name].eq(variants[:model_name])).and(model_years[:year].eq(variants[:year]))
["fuel_efficiency_city", "fuel_efficiency_highway"].each do |fe|
  update_all(" #{fe} = (#{variants.project(variants["#{fe}"].average).where(join_relation).to_sql}), #{fe}_units = 'kilometres_per_litre' ")
  where("alt_fuel_code IS NOT NULL").update_all(" alt_#{fe} = (#{variants.project(variants["alt_#{fe}"].average).where(join_relation).to_sql}), alt_#{fe}_units = 'kilometres_per_litre' ")
end

Derive type name from AutomobileMakeModelYearVariant

safe_find_each do |ammy|
  type_names = AutomobileMakeModelYearVariant.where(:make_name => (ammy.make_name), :model_name => (ammy.model_name), :year => (ammy.year)).map(&:type_name).uniq
  ammy.update_attributes!(:type_name => (type_names.first if type_names.one?))
end

Derive weighting from AutomobileYear

connection.select_values("SELECT DISTINCT year FROM #{quoted_table_name}").each do |year|
  where(:year => (year)).update_all("weighting = #{AutomobileYear.weighting(year)}")
end

Table structure

CREATE TABLE automobile_make_model_years
  (
     name                              CHARACTER VARYING(255) NOT NULL PRIMARY KEY,
     make_name                         CHARACTER VARYING(255),
     model_name                        CHARACTER VARYING(255),
     year                              INTEGER,
     hybridity                         BOOLEAN,
     fuel_code                         CHARACTER VARYING(255),
     fuel_efficiency_city              FLOAT,
     fuel_efficiency_city_units        CHARACTER VARYING(255),
     fuel_efficiency_highway           FLOAT,
     fuel_efficiency_highway_units     CHARACTER VARYING(255),
     alt_fuel_code                     CHARACTER VARYING(255),
     alt_fuel_efficiency_city          FLOAT,
     alt_fuel_efficiency_city_units    CHARACTER VARYING(255),
     alt_fuel_efficiency_highway       FLOAT,
     alt_fuel_efficiency_highway_units CHARACTER VARYING(255),
     type_name                         CHARACTER VARYING(255), /* whether the vehicle is a passenger car or light-duty truck */
     weighting                         FLOAT                   /* for calculating AutomobileMakeModel fuel efficiencies */
  );

Brighter Planet