\[ \begin{align}\begin{aligned}\newcommand\blank{~\underline{\hspace{1.2cm}}~}\\% Bold symbols (vectors) \newcommand\bs[1]{\mathbf{#1}}\\% Poor man's siunitx \newcommand\unit[1]{\mathrm{#1}} \newcommand\num[1]{#1} \newcommand\qty[2]{#1~\unit{#2}}\\\newcommand\per{/} \newcommand\squared{{}^2} \newcommand\cubed{{}^3} % % Scale \newcommand\milli{\unit{m}} \newcommand\centi{\unit{c}} \newcommand\kilo{\unit{k}} \newcommand\mega{\unit{M}} % % Percent \newcommand\percent{\unit{\%}} % % Angle \newcommand\radian{\unit{rad}} \newcommand\degree{\unit{{}^\circ}} % % Time \newcommand\second{\unit{s}} \newcommand\s{\second} \newcommand\minute{\unit{min}} \newcommand\hour{\unit{h}} % % Distance \newcommand\meter{\unit{m}} \newcommand\m{\meter} \newcommand\inch{\unit{in}} \newcommand\foot{\unit{ft}} % % Force \newcommand\newton{\unit{N}} \newcommand\kip{\unit{kip}} % kilopound in "freedom" units - edit made by Sri % % Mass \newcommand\gram{\unit{g}} \newcommand\g{\gram} \newcommand\kilogram{\unit{kg}} \newcommand\kg{\kilogram} \newcommand\grain{\unit{grain}} \newcommand\ounce{\unit{oz}} % % Temperature \newcommand\kelvin{\unit{K}} \newcommand\K{\kelvin} \newcommand\celsius{\unit{{}^\circ C}} \newcommand\C{\celsius} \newcommand\fahrenheit{\unit{{}^\circ F}} \newcommand\F{\fahrenheit} % % Area \newcommand\sqft{\unit{sq\,\foot}} % square foot % % Volume \newcommand\liter{\unit{L}} \newcommand\gallon{\unit{gal}} % % Frequency \newcommand\hertz{\unit{Hz}} \newcommand\rpm{\unit{rpm}} % % Voltage \newcommand\volt{\unit{V}} \newcommand\V{\volt} \newcommand\millivolt{\milli\volt} \newcommand\mV{\milli\volt} \newcommand\kilovolt{\kilo\volt} \newcommand\kV{\kilo\volt} % % Current \newcommand\ampere{\unit{A}} \newcommand\A{\ampere} \newcommand\milliampereA{\milli\ampere} \newcommand\mA{\milli\ampere} \newcommand\kiloampereA{\kilo\ampere} \newcommand\kA{\kilo\ampere} % % Resistance \newcommand\ohm{\Omega} \newcommand\milliohm{\milli\ohm} \newcommand\kiloohm{\kilo\ohm} % correct SI spelling \newcommand\kilohm{\kilo\ohm} % "American" spelling used in siunitx \newcommand\megaohm{\mega\ohm} % correct SI spelling \newcommand\megohm{\mega\ohm} % "American" spelling used in siunitx % % Inductance \newcommand\henry{\unit{H}} \newcommand\H{\henry} \newcommand\millihenry{\milli\henry} \newcommand\mH{\milli\henry} % % Power \newcommand\watt{\unit{W}} \newcommand\W{\watt} \newcommand\milliwatt{\milli\watt} \newcommand\mW{\milli\watt} \newcommand\kilowatt{\kilo\watt} \newcommand\kW{\kilo\watt} % % Energy \newcommand\joule{\unit{J}} \newcommand\J{\joule} % % Composite units % % Torque \newcommand\ozin{\unit{\ounce}\,\unit{in}} \newcommand\newtonmeter{\unit{\newton\,\meter}} % % Pressure \newcommand\psf{\unit{psf}} % pounds per square foot \newcommand\pcf{\unit{pcf}} % pounds per cubic foot \newcommand\pascal{\unit{Pa}} \newcommand\Pa{\pascal} \newcommand\ksi{\unit{ksi}} % kilopound per square inch \newcommand\bar{\unit{bar}} \end{aligned}\end{align} \]

Oct 24, 2024 | 456 words | 5 min read

3.3.2. Task 2

Learning Objectives

By the end of this task, you will be able to access and import large datasets from a text file, filter and clean the data to address missing or erroneous entries, and update specific data cells as needed. You will also perform descriptive statistical analysis on weather data and use MS Excel conditional functions to extract meaningful insights. This task will enhance your data management skills, enabling you to work with real-world datasets effectively and make informed decisions based on the results.

Instructions

When working with large datasets, some of the data collected can be missing or erroneous. In this problem you are given a slice of a dataset that contains hourly weather data collected from a station in Bedford, Indiana in 2022. This dataset and a description of the data it contains can be found here. In this task you will be working with just the date, time, and maximum and minimum temperatures.

Note

Some of the temperature values are missing from this dataset. Those values have been given a placeholder: \(-9999\).

Task Instructions

1. Open the answer sheet ex3_ind_2_username.xlsx. Save it with your Purdue username replacing username in the file name.

2. In the Input Section of the sheet, import only the UTC date, UTC time, maximum temperature and minimum temperature columns from the file ex3_ind_2_CRNH0203-2022-IN_Bedford_5_WNW.txt. Refer to the table inside the note section for the column header information.

3. Under the Calculation Section, in Table 2,

   1. The first column, T_MAX_CLEANED, should contain a copy of the T_MAX column where any cells containing a \(-9999\) have to be replaced by the average value of the cells directly above and below.

   2. The second column, T_MIN_CLEANED, should contain a copy of the T_MIN column where any cells containing a \(-9999\) have to be replaced by the average value of the cells directly above and below.

   3. The third column, T_AVERAGE, should contain the average value of T_MAX_CLEANED and T_MIN_CLEANED.

4. Under the Calculation Section, in Table 3,

   1. Use built-in MS Excel functions to calculate the descriptive statistics for the T_AVERAGE data; including minimum, maximum, range, mean, median, mode, variance, and standard deviation.

5. Copy the following questions into the Output Section of the spreadsheet and then add your answers.

   1. Number of times when T_MAX is below the overall mean temperature

   2. Number of times when T_MIN is above the overall mean temperature

   3. How many cells were changed when cleaning the data?

6. Save the ex3_ind_2_username.xlsx file as ex3_ind_2_values_username.pdf displaying the values and ex3_ind_2_formulas_username.pdf displaying the formula.

7. Submit both files to Gradescope.

Note

You can get the detailed information about the dataset from the website mentioned above. In the below Table 3.8, only the column information in provided for convenience.

Table 3.8 Hourly weather data column information

Column#	Name	Units
1	WBANNO	XXXXX
2	UTC_DATE	YYYYMMDD
3	UTC_TIME	HHmm
4	LST_DATE	YYYYMMDD
5	LST_TIME	HHmm
6	CRX_VN	XXXXXX
7	LONGITUDE	Decimal_degrees
8	LATITUDE	Decimal_degrees
9	T_CALC	Celsius
10	T_HR_AVG	Celsius
11	T_MAX	Celsius
12	T_MIN	Celsius
13	P_CALC	mm
14	SOLARAD	W/m^2
15	SOLARAD_FLAG	X
16	SOLARAD_MAX	W/m^2
17	SOLARAD_MAX_FLAG	X
18	SOLARAD_MIN	W/m^2
19	SOLARAD_MIN_FLAG	X
20	SUR_TEMP_TYPE	X
21	SUR_TEMP	Celsius
22	SUR_TEMP_FLAG	X
23	SUR_TEMP_MAX	Celsius
24	SUR_TEMP_MAX_FLAG	X
25	SUR_TEMP_MIN	Celsius
26	SUR_TEMP_MIN_FLAG	X
27	RH_HR_AVG	%
28	RH_HR_AVG_FLAG	X
29	SOIL_MOISTURE_5	m^3/m^3
30	SOIL_MOISTURE_10	m^3/m^3
31	SOIL_MOISTURE_20	m^3/m^3
32	SOIL_MOISTURE_50	m^3/m^3
33	SOIL_MOISTURE_100	m^3/m^3
34	SOIL_TEMP_5	Celsius
35	SOIL_TEMP_10	Celsius
36	SOIL_TEMP_20	Celsius
37	SOIL_TEMP_50	Celsius
38	SOIL_TEMP_100	Celsius