IFSM 300
Name:______________
Analyzing a larger data file.
Bold edit this file.
Download the j1000000.txt file from your instructor's computer
at 192.168.x.y
What is the size as reported by Windows Explorer,
in KB: ______
in MB: ______
in bytes:_______________
Open the file in Notepad.
turn on the Status Bar.
How many lines does the file have:_________
What separates the fields in each line:_______
Go to the luckiest line (777,777). What is its 6-letter word:_______
How many times does yapoke exist in the file:_____
Where is 1254779513 in the file:___________
Notepad could change data in the file, but we won't do any.
This is about all that an editor like Notepad can do with this data.
Import the file into Excel.
Format column E so that it matches the original data from the text file
and so that the decimal points line up.
Format the text columns to a fixed-width font like Courier New so that
it is clear how many characters are in each word. Maybe bold them too.
Center column D, the single letter field.
Make a new column that is the product of the third and fifth columns,
i.e. columns C and E. Format it the same as column E.
Show the sum, average, median, min, and max of the four numeric columns (A,C,E,G)
--present this in a tabular format, looking good as a table should,
on a sheet by itself, the stats formatted to reflect the data they derive from.
Median is another kind of averge; it is the middle value: half the values
are bigger than it, half are smaller. For some data, median is a better
kind of average than the Average (i.e. arithmetic mean). If there is an even number
of values, the average of the two middle ones is taken as the median.
Average of A:_________ Median of A:_________
Average of C:_________ Median of C:_________
Average of E:_________ Median of E:_________
Average of G:_________ Median of G:_________
Put this table on a sheet by itself.
Use the CORREL function to calculate the correlation between some pairs of columns.
Calculate it between columns C and E, C and G, and E and G.
(correlation is a measure of how much, if any, two paired measures or
variables, or here, columns, move up or down together;
a correlation around 0 means the two measures aren't "connected",
a correlation near 1 means the two measures seem to move together,
and so might be related somehow).
Put these correlation coefficients on the stats sheet.
C and E:______
C and G:______
E and G:______
Print the stats sheet.
What is the 6-letter word of the row with the smallest column A value:_______
Any chart of this huge amount of data won't be useful or usable.
Add a new column H that is just 1 2 3 ...
(we can use this column after sorting on other columns to get back our
original order. This column is essentially just a row number).
Copy the first 1000 rows to a new sheet.
[Turn this 1000-row sheet into a table.]
We will assume this is a random sample of the population of the data
and so by the miracle of random sampling, information about the sample
will be probabilistically accurate about the population, i.e all the data.
Make a scatterchart of column C so its distribution can be visualized
and thus understood. Improve the chart's axes, (no) legend, title, markers.
This is a uniform distribution,
the values are from everywhere in the range from the min to the max
and all the values seem to be equally likely.
Make a scatterchart of column E so its distribution can be visualized
and thus understood. Improve the chart.
Make a scatterchart of column G so its distribution can be visualized
and thus understood. Improve the chart.
E and G are not uniformly distributed.
E clearly doesn't have values everywhere
but rather at particular values only, like 1, .5, .33, .25 etc.;
its range is from 0 to 1 but its average is less than .1.
Make a scatter chart of columns E and G. Make improvements to the chart.
In Table Tools Layout tab, Analysis, Trendline, More..., choose Linear trendline
and Display R-squared value. (R is the correlation coefficient).
Print the four charts.
Back to the 1M row data sheet.
We can find out how many different values there are in a column.
Copy the three text columns to a new sheet. On that sheet, in Data tab,
Remove Duplicates of each column by itself.
How many unique values are there of column D:________
How many unique values are there of column F:________
How many unique values are there of column B:________
Sort the columns to see that there are no duplicates.
What's the first 6-letter word:_________, the last:__________
What's the first 4-letter word:_________, the last:__________
How many different 6-letter words are there that start with 'to':_______
Copy column A's million values to this new sheet. Remove its duplicates.
How many unique values are there of column A:_________
Would you rather do this on a Dell Precision 7910 Tower:___
Back to the 1M row data sheet.
Turn it into a table.
How many rows have column E value of the median value of column E:_______
Save your file.
What is the size of the Excel file, in KB:_______ and MB:_____