untitled

Size: px

Start display at page:

Download "untitled"

민경 한
6 years ago
Views:

1 Data cleaning & Exploratory data analysis Seungho Ryu, MD, PhD Kanguk Samsung Hospital, Sungkyunkwan University

2 Data Cleaning Definition: A process used to determining inaccurate, incomplete, or unreasonable data and then improving the quality through correction of detected errors and omission. Error prevention is far superior to error detection and cleaning. No matter how efficient the process of data entry, errors will still occur and therefore data validation and correction cannot be ignored. One important product of data cleaning is the identification of the basic errors detected and using that information to improvement the data entry process to prevent those errors from reoccurring.

3 Need for Data Cleaning Centre around improving the quality of data to make them fit for use by users through reducing errors in the data. They are bad, but a good understanding of errors and error propagation can lead to active quality control and managed improvement in the overall data quality. It is important that errors not just be deleted, but corrections documented and changes traced. It is best to add corrections to the database while retaining the original data in a separate field or fields so that there is always the chance of going back to the original information.

4 Principles of Data Cleaning Planning is essential Organizing data improves efficiency Prevention is better than cure Responsibility belongs to everyone Partnerships improves efficiency Minimize duplication Documentation of validation procedures Feedback is a two-way street Education and training

5 Overview of data cleaning process Data request & download without original unique identifier/ with a new assigned study-id Read data files with STATA by types of variables Examine individual variable data files check potential errors including duplicates define labels check inconsistency Edit commands Integrate labels Check incorrect id Merge (combine) individual variables comprehensive visits versus other visits define index visit/ define incorrect id Create STATA data set for analysis apply labels & possible ranges impute values for limits of detection create derived variables

health check-up in the center sampling time- proxy of blood sampling time (the time for printing out

6 Download format To define a proper format for each variable Lab/anthropometry/exam/image or procedures To define different time variables request time- the time a participant registered a health check-up in the center sampling time- proxy of blood sampling time (the time for printing out a lab label test time the time an analyzer starts to measure report time the time a value is confirmed and saved

7 Simplified data sources Local equipment or server Laboratory Raw exam values -directly from analyzer machine Procedures (PFT, PWV) PACS Server server

8 Data sources Raw exam values Raw values variable data files (*.csv) Reported values Without personal unique identifiers

9 Data cleaning files

10 General principles Preserving all original data files received

11 General principles STATA data files self-explanatory

12 Stata do files Do files - Include all data cleaning processes - Self explanatory - All reproducible - Apply feedback - Record tasks - Date and version

14 Self-explanatory titles & tasks Define tasks Record version & date

15 kshc_data_dictionary_v01_2012_02_29 Provide summary documentation of all data changes and help investigators to use the data efficiently (avoid same problems of data)

16 Checking data quality & correction Checking erroneous values Range and consistency checks Developing boundaries for out-of range values required a collaborative effort between data cleaning team and clinicians

17 Data cleaning examples Hearing High sensitivity C-reactive protein Blood pressure Direct bilirubin Waist Duplicates Decision process

18 Simple example - hearing

19 Assign a different code for possible a different meaning value Assign a same code for same meaning values

21 High sensitivity C-reactive protein Impossible value of zero (0) limits of detection varied over time in terms of magnitude

22 Inconsistent between raw values and reported values. tab year if raw_values=="<0.30" raw_values=="<0.31" raw_values=="< > 0.32" raw_values=="<0.34" year Freq. Percent Cum Total tab year if raw_values=="<0.03" year Freq. Percent Cum Total

23 High sensitivity C-reactive protein

24 Example- SBP (systolic blood pressure)

Density 0.02.04.06.08 50 100 150 200 250 systolic blood pressure, mmhg Density 0.02.04.06.08.1 Year 2002 Year 2003 Density 0.

25 Density systolic blood pressure, mmhg Density Year 2002 Year 2003 Density systolic blood pressure, mmhg Year systolic blood pressure, mmhg

27 Direct bilirubin

28 waist

29 Duplicates issue All variable duplicates were removed

30 Inconsistent identifier Decide to remove

31 Creating data sets for analysis To help investigators use data efficiently and properly remove duplicates and unreliable partial duplicates set erroneous values of each variable to missing set out-of range to missing check for consistency and correct inconsistency Impute a certain value for limit of detection

32 Difficulties Data -Not designed for study No current available documentation for range check (e.g., limit of detection over time) Required a collaborative effort between data cleaning team and clinicians for developing out of range in terms of both laboratory and clinical aspects Raw exam values- Not available before 2003

33 Data cleaning KSHC 288,419 subjects; 619,763 visits; 1,238 deaths Clean, internally consistent data sets for analysis Maximize the existing database Help investigators perform studies efficiently Promote better quality products Improve hospital image with reliable data and proper data usage

34 Kangbuk Samsung Cohort Study Data entry, timely review and correction will continue throughout the data cleaning possible recovery of erroneous data Feedback throughout retrospective data cleaning improvement of reporting consistency Data collection through standardized examination best quality of cohort study data

35 Exploratory Data Analysis

36 Main Reasons of EDA Detection of mistakes Checking of assumptions Preliminary selection of appropriate models Determining relationships among the explanatory variables Assessing the direction and rough size of relationships between explanatory and outcome variables

38 ( ) 1

40 Data

51 Univariate Analysis

52 Mean Median Measures of central location Geometric mean

53 Mean (arithmetic mean) If there are n observations, x 1, x 2,, x n, the sample mean is x = x 1 + x n x Properties Intuitive Nice statistical properties Sensitive to outliers Appropriate for continuous and discrete variables n = n i= 1 n x i

54 Computing the mean in a random sample of 10 SBP measurements Random sample of 10 SBP measurements in NHANES III 111, 134, 105, 138, 110, 122, 196, 126, 177, 117 x = n i= 1 n x i = = mmhg

55 Median (50 th percentile) Middle value of the sample (50 th percentile) Order the sample (from lowest to highest) If n is odd, the median is the middle value If n is even, the median is the average of the two middle values Properties: The mean may not be a good measure of the middle value of a distribution Insensitive to outliers

56 Computing the median in a random sample of 10 SBP measurements Random sample of 10 SBP measurements in NHANES III 111, 134, 105, 138, 110, 122, 196, 126, 177, 117 Order the sample values 105, 110, 111, 117, 122, 126, 134, 138, 177, 196 The median is the average of the 5 th and 6 th values Median = ( ) / 2 = 124

57 Geometric mean If there are n observations, x1, x2,, xn, the geometric mean is x geom = = n x 1 x 2... x n logx1 + logx logx exp n n

58 Serum β-carotene in NHANES III adults ( ) Frequency Geom. mean: 14.3 µg/dl Min: 0.48 µg/dl P 25: 8.0 µg/dl P 50: 14.0 µg/dl P 75: 24.0 µg/dl Max: µg/dl Serum β-carotene (µg/dl) Based on unweighted analysis of NHANES III data. N = 16,629. N missing = 2,989

59 Measures of spread Standard deviation Interquartile range Range Coefficient of variation

60 Standard deviation If there are n observations, x 1, x 2,, x n, the sample standard deviation is s = n i= 1 ( ) Note: s 2 is the sample variance Properties x n i 1 x 2

61 s Computing the SD in a random sample of 10 SBP measurements Random sample of 10 SBP measurements in NHANES III 111, 134, 105, 138, 110, 122, 196, 126, 177, 117 Mean: mmhg = = = n i= 1 ( x x) i n ( ) + ( ) ( ) mmhg

62 Other measures of spread Interquartile range 75 th percentile 25 th percentile Range Highest lowest observation Coefficient of variation s CV = 100% x

63 Skewness of distributions Pagano and Gauvreau, page 42

64 Univariate non-graphical EDA: Continuous variable Summary statistics

65 Univariate non-graphical EDA: Categorical variable A simple tabulation of the frequency of each category is the best univariate non-graphical EDA for categorical data

66 Graphical methods for continuous data (univariate) Bar chart Histogram Box plot Stem-and-leaf plot

67 SBP in NHANES III adults ( ) Properties of a distribution Location Probability 0.01 Spread Shape Systolic blood pressure (mm Hg) Based on unweighted analysis of NHANES III data. N = 19,256. N missing = 362

68 SBP in NHANES III adults ( ) Probability Mean: mm Hg SD: 20.6 mm Hg Min: 69.0 mm Hg P 25: mm Hg P 50: mm Hg P 75: mm Hg Max: mm Hg IQR: 27.0 mm Hg CV: 16.3 % Systolic blood pressure (mm Hg) Based on unweighted analysis of NHANES III data. N = 19,256. N missing = 362

69 Histogram The key with a histogram is to use a sufficient number of intervals to define the shape of the distribution clearly and not lose much information. A rough rule of thumb is to choose the number of bins to be about 1+3.3log 10 (n) where no is the sample size.

70 Boxplots

71 Boxplot Location, as measured by the median Spead, as measured by the height of the box (this is called the interquartile range for IQR) Range of the observations Presence of outliers Some information about shape

72 SBP in NHANES III adults ( ) Stem and leaf plot N = 500 Median = 123 Quartiles = 112, 140 Decimal point is 1 place to the right of the colon 8 : 1 8 : 9 : : : : : : : : : : : : : : : : : : : : 8 19 : : 6 High: Based on a random sample of 500 adult NHANES III participants

73 Quantile-normal plots

74 KSHS fasting triglycerides

75 Transformation of Data

76 bivariate Analysis

77 Scatterplots

78 Correlation Analysis. pwcorr bmi gluc ldl hdl tchol, sig

80 Side-by-side boxplots

81 Side-by-side boxplots Age non-smoker ex-smoker current smoker

82 * ttest(mean-comparison tests) Group mean-comparison test that varname has the different mean(unequal variance) within the two groups defined by group variable ttest variable name, by(group variable) option. ttest age, by(diabetes)unequal Two-sample t test with unequal variances Group Obs Mean Std. Err. Std. Dev. [95% Conf. Interval] no yes combined diff diff = mean(0 no) - mean(1 yes) t = Ho: diff = 0 Satterthwaite's degrees of freedom = Ha: diff < 0 Ha: diff!= 0 Ha: diff > 0 Pr(T < t) = Pr( T > t ) = Pr(T > t) =

83 * Chi-square test.. tab2 dm1 agegr, col chi2 -> tabulation of dm1 by agegr Key frequency column percentage Two-way tab2 1 2, col chi2 3 2, 1 Diabetes mellitus RECODE of age (Age) in Total no 9,902 4, , yes Total 10,016 5, , Pearson chi2(2) = Pr = 0.000

84 . tab2 dm1 overweight, col exact chi2 -> tabulation of dm1 by overweight Key frequency column percentage Diabetes RECODE of bmi1 (Body mellitus mass index in 2002) in 2002 BMI<25 BMI>=25 Total no 9,320 5,748 15, yes Total 9,435 5,912 15, Pearson chi2(1) = Pr = Fisher's exact = sided Fisher's exact = 0.000

85 . tab2 dm1 smoke02, col chi2 -> tabulation of dm1 by smoke Key frequency column percentage Diabetes mellitus Smoking habit in 2002 in 2002 non-smoke ex-smoker current s Total no 4,383 3,432 6,755 14, yes Total 4,449 3,502 6,886 14, Pearson chi2(2) = Pr = 0.156

전립선암발생률추정과관련요인분석 : The Korean Cancer Prevention Study-II (KCPS-II)

전립선암발생률추정과관련요인분석 : The Korean Cancer Prevention Study-II (KCPS-II) 전립선암발생률추정과관련요인분석 : The Korean Cancer Prevention Study-II (KCPS-II) - i - - ii - - iii - - iv - - v - - vi - - vii - - viii - - ix - -