This is an ungraded assignment to give you practice subsetting data in R. There’s nothing to turn in. You are strongly encouraged to attempt the assignment before looking at the four provided solutions for the assigned dataset from previous DATA 607 students:

Hand -on Lab - Subsetting Datasets

Your task is to work with the Pittsburgh bridges dataset: https://archive.ics.uci.edu/ml/datasets/Pittsburgh+Bridges You should first study the data and its associated description (i.e. “data dictionary”). You should take the data, and create an R data frame with a subset of the columns in the dataset. You should provide relevant column names.
Your deliverable is the R Markdown code to perform these transformation tasks.

Solution

Read data from url

url <- 'https://archive.ics.uci.edu/ml/machine-learning-databases/bridges/bridges.data.version1'
bridges <- read.table(url, sep=",", header=FALSE, stringsAsFactors = FALSE)
head(bridges)
##   V1 V2 V3   V4       V5   V6 V7 V8      V9  V10    V11 V12  V13
## 1 E1  M  3 1818  HIGHWAY    ?  2  N THROUGH WOOD  SHORT   S WOOD
## 2 E2  A 25 1819  HIGHWAY 1037  2  N THROUGH WOOD  SHORT   S WOOD
## 3 E3  A 39 1829 AQUEDUCT    ?  1  N THROUGH WOOD      ?   S WOOD
## 4 E5  A 29 1837  HIGHWAY 1000  2  N THROUGH WOOD  SHORT   S WOOD
## 5 E6  M 23 1838  HIGHWAY    ?  2  N THROUGH WOOD      ?   S WOOD
## 6 E7  A 27 1840  HIGHWAY  990  2  N THROUGH WOOD MEDIUM   S WOOD

Provide Column Names

I have saved the dictionary from https://archive.ics.uci.edu/ml/machine-learning-databases/bridges/bridges.names in a Dictionary.txt file

urlfile <- 'Dictionary.txt'
bridgesdict <- read.table(urlfile, sep=".", header=TRUE, stringsAsFactors = FALSE)
bridgesdict
##       COLUMNS
## 1  IDENTIFIER
## 2       RIVER
## 3    LOCATION
## 4     ERECTED
## 5     PURPOSE
## 6      LENGTH
## 7       LANES
## 8     CLEAR-G
## 9      T-OR-D
## 10   MATERIAL
## 11       SPAN
## 12      REL-L
## 13       TYPE

Rename the Column Names for the Bridge data frame.

colnames(bridges) <- bridgesdict$COLUMNS
head(bridges)
##   IDENTIFIER RIVER LOCATION ERECTED  PURPOSE LENGTH LANES CLEAR-G  T-OR-D
## 1         E1     M        3    1818  HIGHWAY      ?     2       N THROUGH
## 2         E2     A       25    1819  HIGHWAY   1037     2       N THROUGH
## 3         E3     A       39    1829 AQUEDUCT      ?     1       N THROUGH
## 4         E5     A       29    1837  HIGHWAY   1000     2       N THROUGH
## 5         E6     M       23    1838  HIGHWAY      ?     2       N THROUGH
## 6         E7     A       27    1840  HIGHWAY    990     2       N THROUGH
##   MATERIAL   SPAN REL-L TYPE
## 1     WOOD  SHORT     S WOOD
## 2     WOOD  SHORT     S WOOD
## 3     WOOD      ?     S WOOD
## 4     WOOD  SHORT     S WOOD
## 5     WOOD      ?     S WOOD
## 6     WOOD MEDIUM     S WOOD

A little cleanup “?” change for “NA”

str(bridges)
## 'data.frame':    108 obs. of  13 variables:
##  $ IDENTIFIER: chr  "E1" "E2" "E3" "E5" ...
##  $ RIVER     : chr  "M" "A" "A" "A" ...
##  $ LOCATION  : chr  "3" "25" "39" "29" ...
##  $ ERECTED   : int  1818 1819 1829 1837 1838 1840 1844 1846 1848 1851 ...
##  $ PURPOSE   : chr  "HIGHWAY" "HIGHWAY" "AQUEDUCT" "HIGHWAY" ...
##  $ LENGTH    : chr  "?" "1037" "?" "1000" ...
##  $ LANES     : chr  "2" "2" "1" "2" ...
##  $ CLEAR-G   : chr  "N" "N" "N" "N" ...
##  $ T-OR-D    : chr  "THROUGH" "THROUGH" "THROUGH" "THROUGH" ...
##  $ MATERIAL  : chr  "WOOD" "WOOD" "WOOD" "WOOD" ...
##  $ SPAN      : chr  "SHORT" "SHORT" "?" "SHORT" ...
##  $ REL-L     : chr  "S" "S" "S" "S" ...
##  $ TYPE      : chr  "WOOD" "WOOD" "WOOD" "WOOD" ...
bridges$LENGTH[bridges$LENGTH=="?"] <- ""
bridges[bridges=="?"] <- "NA"

Change column data types to more realistic

bridges <- transform(bridges, LOCATION = as.numeric(LOCATION), ERECTED = as.numeric(ERECTED), LENGTH = as.numeric(LENGTH), LANES = as.numeric(LANES))
head(bridges)
##   IDENTIFIER RIVER LOCATION ERECTED  PURPOSE LENGTH LANES CLEAR.G  T.OR.D
## 1         E1     M        3    1818  HIGHWAY     NA     2       N THROUGH
## 2         E2     A       25    1819  HIGHWAY   1037     2       N THROUGH
## 3         E3     A       39    1829 AQUEDUCT     NA     1       N THROUGH
## 4         E5     A       29    1837  HIGHWAY   1000     2       N THROUGH
## 5         E6     M       23    1838  HIGHWAY     NA     2       N THROUGH
## 6         E7     A       27    1840  HIGHWAY    990     2       N THROUGH
##   MATERIAL   SPAN REL.L TYPE
## 1     WOOD  SHORT     S WOOD
## 2     WOOD  SHORT     S WOOD
## 3     WOOD     NA     S WOOD
## 4     WOOD  SHORT     S WOOD
## 5     WOOD     NA     S WOOD
## 6     WOOD MEDIUM     S WOOD

Some info to play with

Summary for LENGTH of bridges

summary(bridges$LENGTH)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
##     804    1000    1300    1567    2000    4558      27

Summary of ERECTED of bridges

summary(bridges$ERECTED)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##    1818    1884    1903    1905    1928    1986

Summary of LANES of bridges

summary(bridges$LANES)
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
##    1.00    2.00    2.00    2.63    4.00    6.00      16

Final Presentation of Table in html format.

IDENTIFIER RIVER LOCATION ERECTED PURPOSE LENGTH LANES CLEAR.G T.OR.D MATERIAL SPAN REL.L TYPE
1 E1 M 3 1818 HIGHWAY 2 N THROUGH WOOD SHORT S WOOD
2 E2 A 25 1819 HIGHWAY 1037 2 N THROUGH WOOD SHORT S WOOD
3 E3 A 39 1829 AQUEDUCT 1 N THROUGH WOOD NA S WOOD
4 E5 A 29 1837 HIGHWAY 1000 2 N THROUGH WOOD SHORT S WOOD
5 E6 M 23 1838 HIGHWAY 2 N THROUGH WOOD NA S WOOD
6 E7 A 27 1840 HIGHWAY 990 2 N THROUGH WOOD MEDIUM S WOOD
7 E8 A 28 1844 AQUEDUCT 1000 1 N THROUGH IRON SHORT S SUSPEN
8 E9 M 3 1846 HIGHWAY 1500 2 N THROUGH IRON SHORT S SUSPEN
9 E10 A 39 1848 AQUEDUCT 1 N DECK WOOD NA S WOOD
10 E11 A 29 1851 HIGHWAY 1000 2 N THROUGH WOOD MEDIUM S WOOD
11 E12 A 39 1853 RR 2 N DECK WOOD NA S WOOD
12 E14 M 6 1856 HIGHWAY 1200 2 N THROUGH WOOD MEDIUM S WOOD
13 E13 A 33 1856 HIGHWAY 2 N THROUGH WOOD NA S WOOD
14 E15 A 28 1857 RR 2 N THROUGH WOOD NA S WOOD
15 E16 A 25 1859 HIGHWAY 1030 2 N THROUGH IRON MEDIUM S-F SUSPEN
16 E17 M 4 1863 RR 1000 2 N THROUGH IRON MEDIUM NA SIMPLE-T
17 E18 A 28 1864 RR 1200 2 N THROUGH IRON SHORT S SIMPLE-T
18 E19 A 29 1866 HIGHWAY 1000 2 N THROUGH WOOD MEDIUM S WOOD
19 E20 A 32 1870 HIGHWAY 1000 2 N THROUGH WOOD MEDIUM S WOOD
20 E21 M 16 1874 RR 2 NA THROUGH IRON NA NA SIMPLE-T
21 E23 M 1 1876 HIGHWAY 1245 NA THROUGH STEEL LONG F SUSPEN
22 E22 A 24 1876 HIGHWAY 1200 4 G THROUGH WOOD SHORT S WOOD
23 E24 O 45 1878 RR 2 G NA STEEL NA NA SIMPLE-T
24 E25 M 10 1882 RR 2 G NA STEEL NA NA SIMPLE-T
25 E27 A 39 1883 RR 2 G THROUGH STEEL NA F SIMPLE-T
26 E26 M 12 1883 RR 1150 2 G THROUGH STEEL MEDIUM S SIMPLE-T
27 E30 A 31 1884 RR 2 G THROUGH STEEL MEDIUM F SIMPLE-T
28 E29 A 26 1884 HIGHWAY 1080 2 G THROUGH STEEL MEDIUM NA SUSPEN
29 E28 M 3 1884 HIGHWAY 1000 2 G THROUGH STEEL MEDIUM S ARCH
30 E32 A 30 1887 HIGHWAY 2 G THROUGH IRON MEDIUM F SIMPLE-T
31 E31 M 8 1887 RR 1161 2 G THROUGH STEEL MEDIUM S SIMPLE-T
32 E34 O 41 1888 RR 4558 2 G THROUGH STEEL LONG F SIMPLE-T
33 E33 M 19 1889 HIGHWAY 1120 G THROUGH IRON MEDIUM F SIMPLE-T
34 E36 O 45 1890 HIGHWAY 2 G THROUGH IRON SHORT F SIMPLE-T
35 E35 A 27 1890 HIGHWAY 1000 2 G THROUGH STEEL MEDIUM F SIMPLE-T
36 E38 M 17 1891 HIGHWAY 2 G THROUGH IRON MEDIUM F SIMPLE-T
37 E37 M 18 1891 RR 1350 2 G THROUGH STEEL MEDIUM S SIMPLE-T
38 E39 A 25 1892 HIGHWAY 2 G THROUGH STEEL MEDIUM F SIMPLE-T
39 E4 A 27 1892 AQUEDUCT 1092 1 N THROUGH WOOD SHORT S WOOD
40 E40 M 22 1893 HIGHWAY 2 G THROUGH STEEL MEDIUM F SIMPLE-T
41 E41 M 11 1894 HIGHWAY 2 G THROUGH IRON MEDIUM F SIMPLE-T
42 E42 M 9 1895 HIGHWAY 2367 2 G THROUGH STEEL LONG F SIMPLE-T
43 E44 O 48 1896 HIGHWAY 2 G THROUGH STEEL LONG F SUSPEN
44 E43 M 7 1896 HIGHWAY 1040 2 G THROUGH STEEL LONG F ARCH
45 E46 A 37 1897 RR 4000 2 G DECK STEEL LONG F SIMPLE-T
46 E45 M 14 1897 RR 2264 G THROUGH STEEL NA F SIMPLE-T
47 E47 M 15 1898 RR 2000 2 G THROUGH STEEL MEDIUM S SIMPLE-T
48 E58 A 33 1900 HIGHWAY 1200 2 G THROUGH STEEL MEDIUM F SIMPLE-T
49 E48 A 38 1900 HIGHWAY 2000 2 G THROUGH STEEL MEDIUM F SIMPLE-T
50 E94 M 13 1901 RR 2 G THROUGH STEEL LONG F SIMPLE-T
51 E49 A 34 1902 HIGHWAY 1850 2 G THROUGH STEEL MEDIUM F CANTILEV
52 E95 M 16 1903 RR 1300 2 G THROUGH STEEL MEDIUM S SIMPLE-T
53 E87 A 35 1903 RR 3000 2 G THROUGH STEEL MEDIUM S SIMPLE-T
54 E51 M 6 1903 RR 1417 2 G THROUGH STEEL MEDIUM F SIMPLE-T
55 E50 M 21 1903 RR 1154 G THROUGH STEEL LONG F SIMPLE-T
56 E89 M 4 1904 RR 1200 2 G THROUGH STEEL MEDIUM S-F SIMPLE-T
57 E53 A 28 1904 RR 965 4 G THROUGH STEEL MEDIUM S-F SIMPLE-T
58 E52 M 2 1904 RR 1504 G THROUGH STEEL LONG F CANTILEV
59 E54 Y 1908 HIGHWAY 1240 G NA STEEL MEDIUM F SIMPLE-T
60 E56 M 23 1909 HIGHWAY G THROUGH STEEL MEDIUM F SIMPLE-T
61 E55 A 36 1909 HIGHWAY 1730 2 G THROUGH STEEL LONG F SIMPLE-T
62 E57 O 49 1910 RR 1620 2 G THROUGH STEEL LONG F CANTILEV
63 E59 O 43 1911 HIGHWAY 1652 2 G THROUGH STEEL LONG F CANTILEV
64 E107 A 39 1914 RR G NA STEEL NA F NIL
65 E92 M 10 1914 RR 2210 G THROUGH STEEL MEDIUM F SIMPLE-T
66 E61 O 41 1915 RR 2822 2 G THROUGH STEEL LONG F SIMPLE-T
67 E60 A 24 1915 HIGHWAY 1000 4 G THROUGH STEEL LONG F SIMPLE-T
68 E62 A 37 1918 RR 2300 2 N DECK STEEL LONG F CONT-T
69 E63 A 31 1920 RR 2122 2 G THROUGH STEEL MEDIUM F SIMPLE-T
70 E65 A 30 1921 WALK G THROUGH STEEL NA F SUSPEN
71 E64 A 29 1923 HIGHWAY 885 4 G THROUGH STEEL MEDIUM F ARCH
72 E66 A 32 1924 HIGHWAY 2365 4 G THROUGH STEEL MEDIUM S ARCH
73 E70 A 27 1926 HIGHWAY 860 4 G THROUGH STEEL MEDIUM S-F SUSPEN
74 E69 A 26 1926 HIGHWAY 884 4 G THROUGH STEEL MEDIUM S-F SUSPEN
75 E101 O 46 1927 HIGHWAY 1770 2 G THROUGH STEEL LONG S-F CANTILEV
76 E73 A 38 1927 HIGHWAY 1508 G THROUGH STEEL MEDIUM S ARCH
77 E72 M 5 1927 HIGHWAY 2663 4 N DECK STEEL MEDIUM S-F CANTILEV
78 E67 M 1 1927 HIGHWAY 1330 4 G THROUGH STEEL LONG F CANTILEV
79 E75 A 30 1928 HIGHWAY 2678 4 G DECK STEEL MEDIUM F ARCH
80 E74 M 20 1928 HIGHWAY 2220 2 G DECK STEEL MEDIUM S-F CANTILEV
81 E71 A 25 1928 HIGHWAY 860 4 G THROUGH STEEL MEDIUM S-F SUSPEN
82 E68 M 17 1928 HIGHWAY 2250 2 G THROUGH STEEL MEDIUM S SIMPLE-T
83 E78 O 40 1931 HIGHWAY 1365 4 G THROUGH STEEL LONG F ARCH
84 E77 O 42 1931 HIGHWAY 1450 4 N THROUGH STEEL LONG F ARCH
85 E76 M 6 1931 HIGHWAY 1500 4 G THROUGH STEEL LONG F SUSPEN
86 E93 M 11 1937 HIGHWAY 1690 4 N DECK STEEL LONG S-F CONT-T
87 E79 A 34 1939 HIGHWAY 1800 4 G DECK STEEL MEDIUM F CANTILEV
88 E108 A 39.5 1945 HIGHWAY 1060 4 G DECK STEEL MEDIUM S-F CONT-T
89 E107N A 39.7 1945 RR 840 2 G THROUGH STEEL MEDIUM S-F SIMPLE-T
90 E105 A 38.5 1945 HIGHWAY 1710 2 N DECK STEEL MEDIUM S-F CONT-T
91 E103 O 48 1945 HIGHWAY 2160 2 G THROUGH STEEL LONG F CANTILEV
92 E97 Y 52 1945 HIGHWAY G THROUGH STEEL MEDIUM S ARCH
93 E96 Y 51 1945 RR G THROUGH STEEL MEDIUM F SIMPLE-T
94 E99 M 23 1950 HIGHWAY 1320 2 G THROUGH STEEL MEDIUM S-F SIMPLE-T
95 E98 M 22 1951 HIGHWAY 900 4 G THROUGH STEEL MEDIUM F CONT-T
96 E81 M 14 1951 HIGHWAY 2423 4 G DECK STEEL LONG F CONT-T
97 E80 M 19 1951 HIGHWAY 1031 4 G THROUGH STEEL LONG F CANTILEV
98 E88 A 37 1955 HIGHWAY 2300 4 N DECK STEEL LONG F CONT-T
99 E82 O 42 1955 HIGHWAY 804 G THROUGH STEEL NA F SIMPLE-T
100 E102 O 47 1959 HIGHWAY 1700 2 G THROUGH STEEL LONG F CONT-T
101 E83 M 1 1959 HIGHWAY 1000 6 G THROUGH STEEL LONG F ARCH
102 E86 A 33 1961 HIGHWAY 980 4 G DECK STEEL MEDIUM S-F CONT-T
103 E85 M 9 1962 HIGHWAY 2213 4 G DECK STEEL LONG F CONT-T
104 E84 A 24 1969 HIGHWAY 870 6 G THROUGH STEEL MEDIUM F ARCH
105 E91 O 44 1975 HIGHWAY 3756 6 G THROUGH STEEL LONG F ARCH
106 E90 M 7 1978 HIGHWAY 950 6 G THROUGH STEEL LONG F ARCH
107 E100 O 43 1982 HIGHWAY G NA NA NA F NA
108 E109 A 28 1986 HIGHWAY G NA NA NA F NA