Transcript Introduction to Econ424

Notes of Econ424
Part 1: Excel
Fall 2007
Ginger Z. Jin
http://www.glue.umd.edu/~ginger/
1
Class 1: Introduction
•
•
•
•
•
Goal of this class
Syllabus
First-class questionnaire
Logins
A peek at the data collected by the
questionnaire
2
"There are three kinds of lies –
lies, damned lies and statistics."
--- (?) Mark Twain
Winston Churchill
Benjamin Disraeli
3
Statistical manipulation
– Randomness behind each statistics
• News tend to report outliers or new observation that deviates
from the status quo
• Newsworthy is not equal to good statistics
– Correlation and causation
• Harvard graduates earn more than high school dropouts, this
does not mean these dropouts, if given a Harvard diploma,
will earn as much
– Report favorable information while hide measurement
error / variable definition
4
Examples of statistical
manipulation
•
Executives at XYZ Corporation make an average annual salary of
$250,000.
– one earns $2 mill, the other 11 earn $90,000.
•
88% of those surveyed prefer QRS brand potato chips.
– only surveyed 9 people, 8 said yes.
•
ABC ink jet printers use 22% less ink.
– compared to what?
•
One drug treatment program has a 90% success rate.
– Drug free at the end of the program, or x months after finishing the program?
•
Graph manipulation:
– same data but different scale
Source: http://www.effectivemeetings.com/productivity/communication/statmanipulation.asp
5
Real examples
•
Kerry: Now, the president has presided over an economy where we've
lost 1.6 million jobs.
– there are by 1.6 million private sector job loss since Bush took office, but the
drop in total payroll employment -- including teachers, firemen, policemen and
other federal, state and local government employees is down by 821,000.
Source: http://www.factcheck.org/article275.html
•
An internet ad by the Democratic Senatorial Campaign Committee:
there were "four times as many terror attacks in 2005“ (as compared to
2004).
– National Counter Terrorism Center: The previously used statutory definition of
"international terrorism" ("involving citizens or territory of more than one
country") resulted in hundreds of incidents per year; the currently used statutory
definition of "terrorism" ("premeditated, politically motivated violence perpetrated
against noncombatant targets") results in many thousands of incidents per year.
Source: http://www.factcheck.org/article417.html
6
My goal
At the end of the semester:
• You feel comfortable collecting, locating
and analyzing real data
• You are able to read, interpret and criticize
statistics generated by other people
• Given a real data set, you are able to
generate basic statistics by yourself and
give them meaningful interpretations
7
Syllabus is at:
http://www.glue.umd.edu/~ginger/
Click on “ECON424” at the bottom
Assign project 1
8
Class 2: Introduction to Excel
Handout from UMD peer training
• Open a file in N:\ and save it (as .xls or .csv) in
M:\.
• Define observation and variable
• Hide/unhide rows/columns, freeze panes
• Change excel settings (Tools+options)
• Change cell/column/row formats
• Highlight (shift+, ctrl+)
• Formulas/dragdowns
• Charts
9
Class 2: More on Excel
Three more items on excel:
• Text to columns
• Insert text box in excel
• Transport excel table and chart into MS
words
10
Class 3: Data Collection
(e.stat chapter 3)
• Start with a question
– characterize facebook usage among 18-25
• Define observation
– an individual, a class, a school?
• Define variables
– use or not, intensity of usage, scope of usage
• Define sample
– Students enrolled? Students attending the
first class? Students attending the first class
and use facebook?
11
Data Collection
(continued)
• Methods of data collection
– Field collection by hand
– Experiment
– Survey / Questionnaire
•
•
•
•
In class
By phone
By email
Follow up survey
– Existing data sources (library, internet, etc.)
12
Data Collection
(continued)
• Things that need special attention
– Measurement error
• data collector’s preference, if revealed in the survey, may
bias respondent answer
• self report may be biased in a specific direction
• anonymous vs. identity-revealing
– Sample selection
• sample not representative by design (those attending first
class are different from those who don’t attend)
• missing values generate sample selection (need a follow
up?)
– Sample size and balance
• variations in the studied variables
• trade off between statistical power and cost of data collection
• similar size of comparable subsamples
13
Role playing
• To better understand subprime mortgage financial
crisis, what statistics would you generate and analyze if
you are the head of:
–
–
–
–
–
–
Federal Reserve
Wall Street Journal
New York Stock Exchange
Countrywide Financial
National Association of Realtors
European Central Bank
14
Background of
subprime mortgage financial crisis
final borrower with poor credit
subprime loans (low introductory rates, insufficient income
check, backed up by house value)
house boom
Bank / Mortgage companies
depositors
investors on
corporate debt
………
seeking
high
returns
banks issuing
loans
investors on
mortgage
loans
………
………
Fed sets low
interest rate
15
Role playing tasks
• Specify a question
• Define a data set that will help you answer the
question. What statistics do you want most from
this data set?
• How would you interpret the statistics if it turns
out to be …. (imagine the possible outcomes)?
• What cannot the statistics say about?
• Identify another statistics that would help you
most but you cannot get internally
16
Class 4: Data Description
Before summarizing the data, clean it first!
• Sample – every student that attended 1st class
(may be different from the official roster)
• Unit of observation?
• # of observations?
• Variable(s)?
• Missing values?
• Abnormal values?
– Delete them, clean them? Be aware of the
assumptions you are making.
• What’s the # of observations after all the cleaning?
• Take a note of all the above!
17
Class 4: Data Description
(e.stat chapter 4)
• Mean
N
unweighted:
x
x
Excel: =average(data)
i
i 1
N
N
weighted:
w x
i
x
i
i 1
N
w
i
i 1
Example: compute GPA (e.stat Figure 4.7)
18
Class 4: Data Description
• Median
Define: middle point in the data set
50% observations >= median
50% observations <= median
Excel: median(data)
If the distribution is symmetric, median=mean.
Unlike mean, median is insensitive to outliers.
Example: e.stat Figure 4.8
19
Class 4: Data Description
• Trimmed mean:
Ignores a percentage of values that are
extreme and compute mean for the rest.
Excel: trimmean(data, percent)
Example: e.stat Figure 4.10
20
Class 4: Data Description
• Order statistics
1st quartile (25% obs below)
=quartile(data,1)
2nd quartile = median
=quartile(data,2)
3rd quartile (75% obs below)
=quartile(data,3)
4th quartile = maximum
=quartile(data,4)=max(data)
60th percentile (60% obs below) =percentile(data,0.6)
0 percentile = minimum
=min(data)
range = max - min
Interquartile range = 3rd quartile – 1st quartile
Interquartile ratio = 3rd quartile / 1st quartile
Example: e.stat Figure 4.15
21
Class 4: Data Description
• Sample variance and standard deviation
N
Var(x):
2
(
x

x
)
 i
=var(data)
i 1
N 1
Std dev:
var( x)
=stdev(data)
Note: sample variance not equal to population
variance
Example: e.stat Figure 4.15
22
Class 4: Data Description
• Other jargons
Mode: the most common value
=mode(data)
Skewness: asymmetry,
long right tail = positively skewed
=skew(data)
Kurtosis: peakedness, positive if peakier than normal
distribution
=kurt(data)
Example: e.stat sections 4-13, 4-18, 4-20.
23
Class 4: Data Description
Exercise: N:\share\notes\datasummary-exercise.xls
Variable: age, gender, # of friends using facebook
Exercise: use facebook or note, # friends listed
24
Class 5: Histogram
(e.stat Section 4-3)
• Histogram: A column chart in which line segments are graphed for
the frequencies of classes across the class intervals (bins) and
then each segment is connected to the X-axis to form a rectangle.
Count of obs in each bin
Frequency of scores
8
6
4
2
0
25
50
75
100
Upper limit of each bin
25
Class 5: Histogram
• Steps in drawing histogram:
– Define bins
• Must cover all the data (start from min or less, stop at max or more)
• Equal width
• The number of bins is between 5 and 20
– Count frequency in each bin
• Method 1: =countif(..)
• Method 2: =frequency(data, bins)
• Method 3: Tools – data analysis – histogram
– Plot histogram
• Note: histogram is a frequency chart that shows the
distribution of the raw data. It is not equal to highlighting the
raw data and plotting them directly.
26
Relative frequency polygon
•
•
•
(Absolute) frequency: number of observations per bin
 draw histogram as a column bar chart
Relative frequency: percentage of observations per bin
 draw relative frequency polygon as a line chart
Relative frequency polygon is more convenient to compare two data
sets, especially when they differ in the number of observations.
Relative freq
Relative frequency polygons
0.6
0.5
0.4
0.3
0.2
0.1
0
0
25
50
75
100
125
Upper limit of each bin
•
N:\share\notes\data-summary-example-fall2007.xls
27
Class 6-7: Probability Theory
(e.stat Chapters 5 and 6)
• Population: entire set of events that occur in a given
universe.
–
–
–
–
–
Event probability
Probability density function (PDF, f ( x) )
Cumulative density function (CDF, F ( x) )
Population statistics (mean, variance, etc.)
Certainty about a random process
• Sample: a subset of a population
– Data analysis
– Random by nature
– Sample statistics are random variables, but population
statistics are not!
28
Population mean
• For discrete
x
:
 prob( x )  x
E ( x) 
i
i
events
• For continuous
x
:
E ( x )   xi  f ( x )dx
x
29
Population variance
and standard deviation
• For discrete
2 
x
:
2
prob
(
x
)

(
x

E
(
x
))

i
events
• For continuous
x
:
   ( x  E ( x )) f ( x )dx
2
2
x
30
Compute E ( x),  ,  by hand or excel:
2
• Bernoulli distribution (flip a coin)
1
x
0
With probability
p
With probability 1 
• Flip n coins and define
• Roll a die
x  1,2,3,4,5,6
p
x  x1  x2 .... xn
?
Each value with probability 1 / 6
• Roll two dice?
• Roll n dice?
31
How to simulate in excel?
Bernoulli Distribution
1
x
0
With probability
p
With probability 1 
p
Hint: =rand() provides a random number between 0 and 1.
You can use rand(), but your formula must return integer
0 or 1.
Answer: =if(rand()<p,1,0)
What about flip n coins?
32
How to simulate in excel?
Roll a die
x  1,2,3,4,5,6
Each value with probability 1 / 6
Hint: Your formula must return an integer between 1 and 6,
with equal probability.
Answer: =round(rand()*6+0.5,0), or =randbetween(1,6)
Note: randbetween function may not exist in some versions of Excel.
What about roll n dice?
33
Compute E ( x),  ,  by hand or excel:
2
• uniform distribution between (a,b)
1
f ( x) 
ba
Answer:
ab
E ( x) 
2
2
(
b

a
)
2 
12
pdf
1/(b-a)
a
b
x
34
Normal distribution
• Normal distribution:
x ~ N ( ,  )
prob(     x     )  0.67
prob(   2  x    2 )  0.95
prob(   3  x    3 )  0.99
Example:
x ~ N (100,10)
f(x), cum(p(x))
Normal PDF
1.000
0.750
0.500
0.250
0.000
70. 80. 90. 100 110 120 130
0
0 0 .0 .0 .0 .0
x
35
What about
x  x1  x 2
?
E ( x1  x2 )  E ( x1 )  E ( x2 )
Var ( x1  x2 )  Var ( x1 )  Var ( x2 )  2Cov( x1 , x2 )
 ( x1  x2 )  Var ( x1 )  Var ( x2 )  2Cov( x1 , x2 )
Note: if both x1 and x2 are normally distributed, so is
x1+x2. But if x1 and x2 are uniformly distributed,
x1+x2 is not uniformly distributed.
36
Normalize
• If
x~
uniform on (a,b) then
xa
~ uniform on (0,1)
ba
• If
x ~ N ( ,  ) then
x

~ N (0,1)
37
How to simulate in excel?
•
Uniform on (a,b):
•
Hint: rand() gives you uniform on (0,1). You need to
adjust it to fit in the range of (a,b).
•
Answer: =a+rand()*(b-a)
•
Normal:
•
Answer: =norminv(rand(),miu, sigma)
N( ,  )
38
Class 8: Central limit theorem
(e.stat section 10-05)
Given any population with mean  and standard
deviation
, for a large sample (N>30), we
have:

Distribution of the Sample Mean
N
)
probability
x ~ N ( ,

p(x)
p(xbar)
0.2
0.15
0.1
0.05
0
0
50
100
X, Xbar
Simulation: show-central-limit-theorem.xls
Assign project 2
39
Show CLT in Excel (1)
• Choose a population
– type of distribution, e.g. Bernoulli
– distribution parameters, e.g. p=0.3.
• Simulate data
– 200 samples
– each sample of size N
• Lock in the simulated data so the samples
do not change later on
– copy, paste special
40
Show CLT in Excel (2)
• Calculate sample mean for each sample
• Compare the distributions of (1) all the raw
data and (2) all the sample means
– bin range must be wide enough to cover the
most dispersed distribution
– bin width must be narrow enough so that
there are at least 4-5 bins for the most
concentrated distribution
41
Class 9: Mean estimation
• According to CLT, sample mean is an
unbiased estimate of population mean, but
with some errors.
• What is the distribution of x if
E ( x)  0,   10, N  100 ?
Answer: x ~ N ( E ( x),

N
)  N (0,1)
42
distribution of sample mean (xbar)
with E(x)=0
PDF
2
E ( x) 
N
E ( x) 
95% chance within this range
2
N
0.4
0.2
0
-3
-2.2
-1.4
-0.6
0.2
1
1.8
2.6
xbar
43
But …
• Usually we try to guess what E ( x) is
2
2 

prob E ( x ) 
 x  E ( x) 
  0.95
N
N

2
2 

 prob x 
 E ( x)  x 

N
N

• We don’t know  either
• Use the sample to take a guess on

44
Confidence Interval
• Given confidence level
  0.95
(for example)
ts
ts 

prob x 
 E ( x)  x 

N
N

• Where s=est. std. dev.=stdev(data)
t= t value = tinv(1-alpha, N-1)
• Or xbar +/- confidence(1-alpha, stddev, size)
• Note: In Excel, confidence function assumes we know population
standard deviation and therefore does not use t-value
• Exercise: e.stat problems 12.1 and 12.2
45
Class 10: Hypothesis testing
• Null hypothesis
H0: E ( x)    0
• Alternative hypothesis:
H1:
E ( x)    0
two-tail test
E ( x)    0
one-tail test
E ( x)    0
E ( x )    1
46
Logic
•
•
•
•
Assume H0 is right
Choose a confidence level   0.95
Compute prob(get the sample mean)
Reject H0 if prob(..) is too small, otherwise
accept H0
47
Types of Error
• Type I Error: reject correct H0 (false neg)
• Type 2 Error: accept wrong H0 (false pos)
p(x)
PDF of Sample Mean: H0 and H1
0.5
0.4
0.3
0.2
0.1
0
0.94
p(x)-alt
0.96
0.98
1
x
Critical Value
1.02
1.04
p(x)-null
mu's
48
In practice
• Method 1 (two tail test only)
– Compute x and confidence interval
– Accept H0 if  0 falls in the confidence interval
– Reject H0 if  0 falls out of the confidence interval
Accept
(prob=alpha)
Reject
((1-alpha)/2)
((1-alpha)/2)
x
PDF
0.5
Reject
0
-3
-2.2
-1.4
-0.6
0.2
1
1.8
2.6
49
In practice
• Method 2:
x  0
t
s
– Compute t-statistics
N
Degree of freedom
– Compute critical value
=(+/-)tinv(1-alpha, N-1) (if two-tail)
=(+/-)tinv((1-alpha)*2, N-1) (if one-tail)
– Accept (reject) H0 if t falls in (out of) the critical value
50
Two-tail critical values
Accept
(prob=alpha)
Reject
((1-alpha)/2)
Reject
((1-alpha)/2)
t
PDF
0.5
0
-3
-2.2
-1.4
-0.6
0.2
1
1.8
2.6
51
H1:
  0
One-tail critical value
Reject
Accept
(prob=alpha)
(1-alpha)
t
PDF
0.5
0
-3
-2.2
-1.4
-0.6
0.2
1
1.8
2.6
52
In practice
x  0
• Method 3:
t
s
– Compute t-statistics
N
– Compute p-value = prob(t-stat>=|t|)
=tdist(|t|, N-1,2) (if two-tail)
=tdist(|t|,N-1,1) (if one-tail)
– Reject H0 if
p-value<(1-alpha)
Accept H0 if
p-value>(1-alpha)
53
Exercise
•
•
•
•
What is H0? What is H1?
Two-tail or one-tail?
Choose a method
E.stat problems:
– two-tail test: e.stat 13.13 and 13.17
– One-tail test: e.stat 13.20 and 13.E10
54
Two-tail vs. one-tail
• Two-tail test does not indicate which direction to
go if we reject H0:   0 , so the alternative is
H1:   0 .
• One-tail test has a strong view of one direction.
For example, a saleman wants to know whether
sales have increased from the past, in which
case the alternative is H1:   0 . If he is
worried if the sales have decreased, the
alternative will point to another direction where
H1 is   0 .
55
In Excel (alpha=95%):
Two-tail
• H0:  
• H1:  
• T-stat:
0
One-tail
• H0:  
0
One-tail
• H0:  
0
• H1:  
0
• H1:
x  0
s/ N
• T-stat:
x  0
s/ N
0
  0
• T-stat:
x  0
s/ N
• Crit.Val:
(+/-) tinv(0.05,N-1)
• Crit. Val
• Crit. Val:
= + tinv(0.10,N-1)
= -tinv(0.10,N-1)
• Reject if
t > + crit. val.
or t< - crit. val
• Reject if
t> crit. val.
• Reject if
t<crit. val.
56
Three methods, same result
Two-tail
• H0:  
• H1:  
0
Two-tail
• H0:  
0
Two-tail
• H0:  
0
• H1:  
0
• H1:
• Conf. Interval
[ x  ts / N ,
x  ts / N ]
• T-stat:
x  0
s/ N
0
  0
• T-stat: x  0
s/ N
• Crit.Val:
(+/-) tinv(0.05,N-1)
• P-value:
tdist(|t-stat|,N-1,2)
• Reject if
t > + crit. val.
or t< - crit. val
• Reject if
p<0.05
tinv(0.05,N-1)
• Reject if
0 is out of
conf. interval
57
Class 11: two sample testing
• One sample test H0:   0
• What if we don’t know 0 but have two
samples
• Can we compare x1 and x 2 ?
• Yes, must account for errors in both
58
Two independent samples
• H0: 1  2
• H1: 1  2
(two-tail)
1  2 (one-tail)
• Independent  errors in the two samples are
independent
( x1  x 2 )  ( 1  2 )
t
s12
s22

N1 N 2
• Degree of freedom [min(N1,N2)-1]
• Exercise: e.stat problems 14.5, 14.6
59
Two matched samples
(same subjects, N1=N2)
• H0:
• H1:
•
•
•
•
•
•
1  2
1  2 (two-tail)
1  2 (one-tail)
Generate a new variable dx=x1-x2
Transform H0: dx  1  2
Now is a one-sample test
Degree of freedom N1-1
Example: e.stat figure 14.8
Exercise: e.stat problem 14.7
60
How to tell if two samples are
matched or not?
• N1=N2 for matched pairs
• Same subjects?
• If resorting one sample does not affect the
comparison, they are independent
61
Class 12: regression
• “Regress Y on X” means:
Error term
y  x
Independent variable(s)
Dependent variable
coefficients
62
Ordinary least squares (OLS)
Goal: Find a linear line that best fits the data
( yi  y i ) 2   ( yi      xi ) 2
Best: min

 ,
i
i
Scatter Plot: Consumption on
Income
Slope
(random!)
 ( x  x )( y  y )
 
 (x  x)
i
i
i
2
i
i
  y    x
Intercept (random!)
Average point
the line
( x , y)
is always on
Y: Consumption
Solution:
60.00
50.00
40.00
30.00
20.00
10.00
0.00
0.00
20.00
40.00
60.00
X: Disposable Income
63
Test coefficients
• Estimated coefficients are random numbers! – depend
on data
• The point estimates should be judged together with their
standard errors
• Hypothesis test H0:   0
• T-statistics
  0
t
stderr ( )
• Critical values (assuming conf. level=95%)
=tinv(0.05,N-2) for two-tail
Two-tail or one-tail depends
on H1
=tinv(0.10,N-2) for one-tail
• P-values =tdist(|t|,N-2, 2 or 1)
64
Measure the fit of OLS
• Total sum of squared deviations (TSS):
2
(
y

y
)

i
• Decompose TSS:
2

(
y

y
)
– Explained by the model: 
– Unexplained residuals:
i
2

 ( y  y)
i
• R-square: (explained)/(total)
65
F-test
k=number of coefficients
N=number of observations
ExplainedS S /( k  1)
F (k  1, N  k )  - - - - - - - - - - - - - - - - - - - - - - 
Unexplaine dSS /( N  k )
R 2 /( k  1)
--------------------(1  R 2) /( N  k )
H0: all the coefficients except the constant
term are zero.
H1: some of the non-constant coefficients
are not zero.
66
• Correlation coefficient:

 (x
i
 x )( yi  y )
i
2
2
(
x

x
)
(
y

y
)
 i
 i
i
i
Note:
1. Correlation coefficient is between -1 and 1. What does it mean if
correlation coefficient is equal to -1, 0, or 1?
2. correlation coefficient is symmetric, i.e corr(x,y)=corr(y,x), but OLS
coefficients aren’t. This means regress y on x is not equivalent to
regress x on y.
3. Correlation coefficient is always of the same sign as the OLS slope
coefficient.
4. R-square = (correlation coefficient)^2
67
Regression in Excel
• Method 1:
=linest(data of y, data of x, include const?, other statistics?)
No output labels, must be familiar with the output layout
• Method 2: Tools - data analysis – regression
• Note: you could have multiple x, but they must be
adjacent to each other.
• Example: e.stat Figure 19.7
• Exercise: e.stat problems 19.E1, 19.E2, 19.E3
68
Assumptions and Caveats in OLS
(e.stat section 22-04)
•
•
•
•
Errors have mean zero.
Errors have a constant standard deviation.
Errors are drawn independently.
Errors are uncorrelated with x. All the
important x are included in the regression.
(omitted variable bias, see e.stat Figure 22.4.)
• Errors are distributed normally.
69
Class 14: Midterm Review
– open book
– understand the concepts
– use them in real examples
– 9:30-10:45am, Plant Sciences 1129
– If you cannot attend the midterm for reasons
that are consistent with University Policy,
please let me know AT LEAST 12 hours
BEFORE the midterm time, otherwise your
midterm grade will be counted as zero.
70
Concepts to grasp (1)
• Population / sample
• Population
– Cdf (prob(var<x))
– Pdf (first derivative of cdf)
– population mean, population std. dev.
• Sample
– Histogram, frequency polygon, quartiles, percentiles, sample
mean, sample std. dev., skewness, kurtosis
• Population  sample
– Central limit theorem xbar~N(µ, σ/sqrt(n))
• Sample  Population
– Xbar is a proxy of µ with noise
71
Concepts to grasp (2)
• Inference
– Type I error, Type II error
– Confidence level α
– Confidence interval
– Hypothesis testing
•
•
•
•
H0
H1
Accept/reject?
One-tail, two-tail test
72
Summary of Excel (1)
• Basic excel
– open, save and close files
– cut, paste and paste special
– change format for cell, row or columns
– sort data by one or two variables
– chart wizard
– freeze panes
– drag cells
– use excel functions
73
Summary of Excel (2)
• Data description
– mean, median, trimmed mean
– standard deviation, variance
– quartiles
– mode, skewness, kurtosis
– histogram (absolute frequency)
– relative frequency polygon
74
Summary of Excel (3)
• Probability theory
– PDF, CDF
– mean and standard deviation
– bernoulli, binomial
– uniform, normal
– how to simulate them in Excel?
– Central limit theorem
– how to see central limit theorem in excel?
75
Summary of Excel (4)
• Estimation and Hypothesis testing
–
–
–
–
–
–
–
–
–
use sample mean to estimate population mean
confidence interval
type I error and type II error
null hypothesis (H0) and alternative hypothesis (H1)
one-tail vs. two-tail
t-statistics, critical value, p-value
one-sample test
two-sample test (independent)
two-sample test (matched pair)
76
Summary of Excel (5)
• Linear regression
– model
• one variable on the right hand side
• more than one variables on the right hand side
• create and use binary variables
– fit of the model
•
•
•
•
R square
F test
scatter plot
correlation coefficient
– coefficient estimates
• point estimate
• hypothesis testing
• omitted variable bias
77
Class 15: Midterm Grades
78