TranStats is a new website for transportation researchers and
analysts aimed at providing "one-stop shopping" for transportation
data. The premise is fairly
simple: By reducing the time needed to
gather data, more time is made available for analysis. And easy linkages across many data sets can
provide new insights. Ultimately, this
should result in more timely and more informed decisions, and a better
transportation system.
Use the following URL:
http://www.bts.gov/transtats
|
|
·
Locate data tables via transportation mode, subject,
agency, or content keywords.
·
Get information about the data, including database
names, database profiles, data tables included in each database and data table profiles, including data
frequency and most recent updates for each data table
·
Retrieve support tables of coding schemes for
category fields.
·
Obtain data source contact information as well as BTS
support contact information.
|
|
|
·
Retrieve data in the comma-delimited format from a
variety of intermodal data systems.
·
Download lookup tables for categorical variables to
go with the data.
·
Dynamically filter data based on geography and
time.
·
Select specific fields to download from a data table.
|
|
|
·
Perform interactive analysis for knowledge discovery.
·
Dynamically select dependent variables, category
fields, summary statistics and use time and geography filters.
·
Produce the results both in tables, charts, and, if
applicable, maps.
·
Print or download the analysis results for reports or
further studies.
|
Metadata
Metadata is data about data. TranStats provides online metadata for
all databases either resided on its website or linked.
Metadata describes the data you are interested in and help you
decide whether the data is appropriate, and if it is, which data table or which
fields within a table are relevant.
For coded fields, such as airports, you can further drill down to
find out what each code represents in the data. In the case of airports, the codes are the same as those used by
the industry but in other instances, the codes may be arbitrary.
How do you use metadata
To use the metadata in TranStats, follow these steps:
1.
From TranStats homepage, locate the mode, subject, or agency.
TranStats categorizes data by a combination of modes (that
is, transportation methods), subjects, and agencies that provide the data. You can find the categories either in the Data Library or under Data
Finder on the sidebar.
2.
Click the selected mode, subject, or agency.
Figure 2.1. Data Library on the Homepage
or use the sidebar when you
are on other pages.
Figure 2.2. Data Finder on the Sidebar
All databases in the selected
mode, subject, or agency are displayed, as shown below:
Figure 2.3. All Databases Relevant to
Aviation
In TranStats, data are grouped in databases. Each database
contains one or more related data tables. The short description helps you
identify the data you are trying to locate.
3.
To further narrow down the database list, select a specific
subject from the Filter Subject dropdown list. For example, if you are interested in Passenger Travel, just
select it and you obtain a focus list as shown below:
Figure 2.4. Databases Relevant to Safety
and Aviation
4.
To find more information about a particular database and the
tables within it, click Profile. The following shows the database
profile for Large
Air Carrier Statistics, which is also known as Form 41 Traffic
Data:
Figure 2.5. Database Profile
You can obtain a PDF file by clicking the PDF icon on the top
and print out hard copies of part or the whole of the profile.
To read it online, use the sidebar navigation.
The document contains hypertext links that allow you to go to
the data tables, summary tables, and various related websites. It also provides
contact information for the data provider as well as BTS customer service.
5. If
you know which database you need, click the database name and you will have
access to all the data tables within the database. For example, if you click Large Air Carrier
Statistics, the following screen appears:
Figure 2.6. All Data Tables in Large Air
Carrier Statistics
TranStats provides easy navigation. You can come to this page
by clicking Data Tables on the Database
Profile page; similarly you can view the Database
Profile or Databases pages
simply by clicking the links right under the header bar.
6. To
obtain the data table profile, which includes data frequency and latest update
information, click Profile
for the data table. For example, clicking Profile for T-100 Domestic
Market leads to the following page:
Figure 2.7. Data Table Profile
7. To view what data fields are included in a
table, click the table name. You
can also click Table Contents on the Table Profile page shown above. The Table Contents
page for T-100
Domestic Market, including a description of each field, is
displayed as follows:
Figure 2.8. All Fields in T-100 Domestic
Market Table
The fields that are categorical (or discrete) variables, such
as Carrier or Class,
are shown as hypertext links to their coding schemes.
8. To
find out what each code represents for a specific categorical variable, click
on the field name; for example, you will find out codes for all service classes
if you click Class. The resulting
lookup table (or support table) is shown below:
Figure 2.9. Lookup Table for Service
Class in T-100 Domestic Market
You will need this information when you interpret your Summary Analysis or construct your queries for Distributed Computing. To download the
lookup table, click Download Lookup Table;
to produce a hard copy, click Format results for printing
and print the results.
Data Retrieval
TranStats allows you to retrieve data in a flexible way. You can selectively download data in the
comma-delimited format from the Web server or download summarized data from
interactive analyses.
The basic download function is available on data table basis. Determine the table, fields, time period
and, if applicable, geographical location that are relevant to your study.
Downloading
data from the Web site
To download data, you must be on the Data
Tables screen, for example, the screen displaying all tables
available within the Large Air Carrier Statistics
database as shown below:
Figure 3.
1
Start Download
Use the following steps to download a table:
1. Locate
the table that is relevant to your study.
For example, T100-Domestic Market.
2.
Click Download at the end of the row.
The following figure shows all the fields in T-100 Domestic
Market for you to specify what to download.
Figure 3.2
Download Data from T-100 Domestic Market
3.
To see data density, click Show Percentage Missing in Table.
The percentage displayed for each field refers to the null-percentage of that
field in the entire table.
Figure 3.3
Obtain Missing Data Percentage
This table has very few missing data.
4.
Specify Year and Period using the
drop-down lists. Period can either be
one quarter (or all quarters) or one month (or all months), whichever is
appropriate to the data; for this example, keep the default, which is All
Quarters of the year 2000.
5.
Specify Geography if the drop-down list is populated. You can select a country (or all countries)
or a state (or all states) whichever is appropriate to the data.
6.
Select all the fields relevant to your study.
Note: For very
large tables, TranStats does now allow you to select All Months or All Fields
to reduce network traffic. If you need all data from those tables, contact BTS
customer service.
7.
To retrieve lookup tables, click Get Lookup Table on the right
side. For example, you might want to get all the codes for Carrier.
Lookup tables are downloaded separately.
8.
When you have make all the selections and downloaded all the
lookup tables you need, click Download to download the selected data.
9.
The Windows File Download dialog box appears.
Figure 3.4
File Download
10. Select Save this file to
disk and click OK.
The Save As dialog box appears as shown in the following
figure.
Figure 3.5
Save As Dialog Box
The default filename should have an extension of csv.
With this extension, you will be able to open it directly in MS Excel.
Note: If the
default name appears as something else, you may need to download the latest
patch for your browser. Meanwhile, manually change the extension to csv as the
saved file will be in that format (comma-delimited).
11.
Select a folder on your workstation and click Save.
12.
When Download Complete is displayed, click Close.
The file you get is in the comma-delimited format supported by most
applications that work with data. If
you have saved the file with an csv extension, you can double-click the file
and open it directly in MS Excel. After
opening it, you still may need to adjust the column widths and perform special
formatting for some data such as dates. See relevant steps below.
If you have saved the file in any other extension or you have an
earlier version of MS Excel that does not support csv as a default file type,
follow these steps:
1. From
the menu bar, select File > Open.
The Open dialog box
appears.
2.
In the Look in field, specify the folder where you
have downloaded the file,
3.
In the File of type field, change the default to All Files (*.*).
The Open dialog box displays all the files in the specified
folder, as shown in the following figure.
Figure 3.6
Open the Downloaded File
4. Select
the file you have downloaded, in this case T_T100_MARKET.csv and click Open.
Text
Import Wizard – Step 1 of 3 appears.
Figure 3.7
Text Import Wizard (Excel)
5.
Select Delimited as the original data type if it is
not the default and click Next.
Text
Import Wizard – Step 2 of 3 appears.
Figure 3.8
Text Import Wizard (Excel) – 2
6.
Select Comma and deselect all other check boxes in
the window.
The window displays a data preview for you to check for
errors in how columns are separated.
7.
Click Next.
Text Import Wizard – Step 3 of 3
appears.
Figure 3.9
Text Import Wizard (Excel) - 3
This window allows you to format each column. For Microsoft Excel, the General
format is adequate.
8.
Click Finish. The downloaded data is imported into a
Microsoft Excel spreadsheet.
Figure 3.10
Data in Excel Spreadsheet
In the previous figure, the third column is displayed in
scientific notation (for example, 3.64E+04). Sometimes, it is because
the column width is too small; simply dragging the right border of the column
to enlarge it and the numbers may be displayed in full. Other times, you may
need to change the numeric format.
9. To
change the numeric values from the scientific format to a format easier to read,
highlight the column(s) and select from the menu bar Format > Cells.
The Format Cell window
appears.
Figure 3.11
Change Column Format in Excel
Note: Changing
the format of the numeric columns does not change the values in the data.
Summary Analysis
TranStats provides most frequently used summary analyses of its
data. Use these analyses to explore the
data and discover fields of interest.
Summary analysis is a descriptive analysis that yields detailed
information about the distribution of continuous variables or counts, for
example, ton-miles or number of passengers.
Those variables are dependent variables.
TranStats provides Table Analysis, Time Series, and Crossties. You
can display results in tables, charts, or maps where mapping information is
available.
Summary analysis in TranStats is table-based though some cross-table
variables can be used to calculate rates and it is highly interactive. To start
interactive analysis, select a table and go to the Table
Contents page.
Follow these steps to start interactive analysis:
1. Click
Analysis
for a selected variable on the Table Contents page.
If the selected variable is categorical (or discrete) and can
be used as a grouping variable, such as Carrier,
TranStats produces a table showing the distribution of a default variable
across that categorical variable. The default variable is chosen based on the
actual usage of the data table. For example, most aviation data tables have the
number of Passengers as the default variable.
The following figure shows the results of an analysis of
Carriers in T-T100 Domestic Market.
Figure 4.1
Table Analysis – Selecting a Categorical Variable
On the other hand, if the selected variable is continuous,
such as Passengers (Number of Passengers) and can be used as a dependent variable, you
get a one-line summary.
Figure 4.2
Table Analysis – Selecting a Continuous Variable
Note that the Filter Categories
box shows None.
This is useful when you only want the total without caring
for distribution across any specific categorical variable.
2.
Use the filters for interactive analysis
The first analysis you perform uses default variables,
default statistic (which, for most variables, is Sum), and default year (which
is the last year). You can use the
filter boxes on the top to change the default values and also change the
category you would like to analyze.
1) To
display another statistic, select one from the Filter Statistics drop-down
list. The list changes according to the
kind of variable you have selected. The following figure shows the list
available for most continuous variables.
Figure 4.3 Filter Statistics
Drop-Down List
Note: Avg (Average) is the Sum divided by the number
of records. You must know what a record in that data table represents before
you can correctly interpret this statistic.
Count is for record count ONLY. To count the
number of occurrences, such as Highway Fatalities, you may need to use Sum.
A second type of statistics list consists of items shown in
the following figure.
Figure 4.4 Filter Statistics
Drop-Down List - 2
This list is for those variables that have yes/no (or 0/1)
values, for example, ArrDel15 (arrival delayed 15 minutes or more).
The Sum of such a
variable provides the number of delays while Proportion
provides the percentage of delays out of total flights. Again Count is valid only for record counts.
2)
To analyze another dependent variable, select one from the
Filter Variables drop-down list.
Figure 4.5 Filter Variables
Drop-Down List
Note: The list
includes all continuous variables and variables with dichotomous values that
are amenable to analysis.
When you select one of the variables in this box, the Filter Statistics
box changes accordingly so that you can perform appropriate summary analysis on
the selected variable.
Items with an asterisk (*) in front do not exist in the data table. They have
been inserted for your convenience. Apart from *Records,
which exits for all tables for you to do a record count, there are those
user-defined functions, such as *Loadfactor (the
ratio of total passenger miles to total seat miles), which is an accepted
indicator in aviation industry.
3)
Use the Filter Categories box to perform an analysis
of a new categorical variable. All fields in the data table that can be used to
show different distribution of the dependent variable are listed. Dichotomous
variables are also included.
4)
After you have made selections in the drop-down lists, click Recalculate.
The results of the new analysis are displayed in a similar
table.
3.
Use the Analysis Summary menu on the sidebar to change
the way the summary is displayed.
For all analysis pages in
TranStats, there is a sidebar that allows you to display the summary statistics
in a way you need. The following figure shows the sidebar for the Table Analysis page.
Figure 4.6
Table Analysis Sidebar
To change the display, do the following:
1) Click
Top N
to rank the Summary table by value; it is equivalent to Ranking the results in
the descending order. The following
screen shows Top N of the T-100 Domestic Market analysis we produced earlier.
Figure 4.7 Analysis Results Sorted
by Value
Similarly, you can click Sort
Descending to sort the Code in the descending order.
2) Click
Percent of Total to display the percentage instead of the values. It is useful
when you study market share or similar problems. The following figure shows the
percentage as results.
Figure 4.8 Display Percentage in
Table Analysis
The Summary Type menu allows you to display charts and maps simply
by clicking on the item:
1. Click
Chart
to produce a bar chart corresponding to the table on the screen. You have
access to the same items under Analysis Summary. The following figure shows
the chart that ranks the percentage in
descending order.
Figure 4.9
Display Chart for a Table Analysis
When you move the mouse over a single bar, the percentage
pops up. The filter boxes on the top work in the same way as in the Table
format.
2.
If the data table has mapping information, Map is available on the
Analysis
Type menu. The following figure shows the map produced for the
previous analysis.
Figure 4.10
Display Map for a Table Analysis
The usual zoom and pan tools are available on the bottom bar.
They are useful especially when you produce a County map.
On this page, you have the same filter boxes as on the Table Analysis page and by selecting different
values from the drop-down lists, you can produce maps without going to the Table Analysis page.
If the data table you are analyzing has more than one year of data,
you can produce a time series analysis simply by clicking Time Series on the
sidebar.
1. On
the Table Analysis or Map page, click Time Series to produce the time series
analysis.
The screen below shows the
same dependent variable and the same categorical variable as the previous
Table, Chart, and Map.
Figure 4.11
Time Series Analysis
The description of the codes is no longer on the page but you
can call it up by moving your mouse over the code, as shown in the above
screen.
2.
If the last year’s data is not complete, you can choose to
compare the same period across years. In the above screen, as the message
states, Latest available 2002 data is March.
To compare data from the
beginning of the year to March, select Year to
March in the Filter Periods list
and click Recalculate. The results now compare
all years up to March.
Figure 4.12
Compare the Same Period in Time Series
Other filters are the same as those on the Table Analysis page.
3.
Use the sidebar to change the displayed items in Time Series.
The sidebar for Time Series is pertinent to Time Series; for
example, it has Value Change and Percent
Change. The following figure shows the Time Series sidebar.
Figure 4.13
Time Series Sidebar
Note that Top N and Bottom N rank last year’s value only.
The following figure shows the results when you click Percent Change on the Analysis
Summary menu.
Figure 4.14
Display Percentage Change in Time Series
Note: The first
year (1990) in the above table shows all dashes as there is no percentage
change to calculate. In TranStats, as a rule, dashes indicate either no data or
data cannot be calculated.
4.
To produce a Time Series chart, click Chart for a specific
code. The following chart shows data for all years for DL (Delta Airlines).
Figure 4.15
Time Series Chart
The filter boxes are also available on this chart page and
you can produce charts for the same categorical code (group) using different
variables.
TranStats provides the function for performing analysis of one
dependent variable grouped by two categorical variables, that is, a crosstabs
analysis.
1. Click
Crosstabs
on a sidebar (on a Table Analysis, Time Series, Map, or Chart
page) to produce the Crosstabs analysis.
The categorical variable in use is treated as the row
variable and a randomly selected categorical variable in the table is used as
the column variable. The following figure shows the results of the default
Crosstabs analysis from the previous Table Analysis. You can produce this table
from the table, the chart, the map, or the Time Series page.
Figure 4.16
Crosstabs Analysis
Similar to Time Series, you can move your mouse over any code
to find the description. Consistent with other analysis pages, the filter boxes
allow you to select new variables as row or column grouping fields.
2.
Use the sidebar to display row percent, column percent and
total percent.
The Crosstabs sidebar lists these items under Analysis
Summary as shown in the following figure.
Figure 4.17
Crosstabs Sidebar
Click Row Percent and the
values in the Crosstabs analysis change as shown in the following figure.
Figure 4.18
Display Row Percentage in Crosstabs
3.
Use the search function.
All analysis pages have a Search the
Results box that allows you to produce a focus table. In
Crosstabs, you can narrow down the results both row wise and column wise.
To do so, select Row or/and Column above the search box, type in all
the codes or partial descriptions (though they are not displayed), each
separated by a comma. The following figure shows a search for “america,united,dl,us”
in rows.
Figure 4.19
Crosstabs Search Box
After you press Enter or click Go,
TranStats produces a table in
much-reduced size as shown in the following figure.
Figure 4.20
Search Results in Crosstabs
As the popup description of TZ shows, TranStats did a
search on both the codes and the descriptions.
Note that the values displayed for All Rows
and All Columns remain the sum of all rows or
columns in the entire table, not that of the displayed rows and columns.
The Search the Results function
is available on Table Analysis, Time
Series, as well as Lookup Table
pages. Of course, on those pages, you only search the rows.
In many studies, for example, those of fatalities, rates (per
population or per registered vehicles) may be more significant than the
absolute numbers. TranStats allows the application of rate.
The following figure shows a Table Analysis page of the Accident
table in Fatality Analysis Reporting System.
Figure
4.21
Apply Rate in TranStats
As rate information is available and rates are appropriate for this
data, an Apply Rate box appears on the sidebar
as shown in the following figure.
Figure 4.22 Apply
Rate Box
The rates in the list box changes according to the categorical
variable you have selected. As the current categorical variable is State, the list contains the following items:
Per 100K Pop by State
Per 100 Million Miles Traveled by State
Per 100K Registered Vehicles by State
Per 100K Licensed Drivers by State
If the selected categorical variable is County Fips, then the
available rate would be:
Per 100K Pop by County
and for all other categorical variables, the rate would be:
Per 100K Pop US
Per 100 Million Miles Traveled US
Per 100K Registered Vehicles US
Per 100K Licensed Drivers US
In the above table analysis, if you select Per 100K
Pop by State, TranStats calculates the rates and displays which
rate is used. See the following figure.
Figure 4.23
Rates Displayed in Table Analysis
You can rank rates in Table Analysis.
TranStats provides the function for you to format your interactive
analysis for printing or download the results in comma-delimited format (with a
csv extension) for you to open in Excel.
To print the results, click Format
results for printing. The printer version expands the description
for each code and tries to layout the results in a table format. For large
tables, you need to print one page at a time and then piece them together, much
in the same way as Excel handles large tables.
The following figure shows a sample of a crosstabs analysis after a
search.
Figure
4.24
Results Formatted for Printing
To download the results, click Download
results. The download process is much the same as you use to
download data from data tables.
