Summary Statistics
When you get a new dataset, one of the first things you want to do is find your summary statistics. These essential numbers tell you the basics about your data so you start off your analysis on an informed foot.
The Basics
The main summary statistics are:
select * from PUBLIC.LONDON_WEATHER- Inter-quartile Range
- Standard Deviation
| DATE | TEMP | WEATHER | PRECIPITATION | VISIBILITY | WINDSPEED |
|---|---|---|---|---|---|
| 2018-04-11 | 48.5 | fog | 0.2 | 2 | 5.7 |
| 2016-10-31 | 54.7 | fog | 0.01 | 2.5 | 2.1 |
| 2018-11-23 | 44.2 | rain | 0.01 | 2.5 | 5.5 |
| 2018-01-09 | 41.6 | rain | null | 3 | 5.5 |
| 2017-01-26 | 31 | sunny | 0 | 3 | 7.7 |
| 2017-02-13 | 42.8 | sunny | 0 | 3 | 11.9 |
| 2016-12-31 | 42.8 | fog | null | 3.5 | 5.7 |
| 2018-05-31 | 65.7 | fog | 0 | 3.5 | 5.1 |
| 2017-01-27 | 37.8 | sunny | 0 | 3.5 | 6.1 |
| 2018-03-02 | 30.6 | rain | 0 | 3.5 | 14.2 |
| 2019-02-16 | 47.4 | sunny | 0.01 | 3.5 | 6.3 |
| 2018-02-19 | 47.7 | rain | 0.02 | 3.5 | 5.4 |
| 2017-01-31 | 45 | rain | 0.03 | 3.5 | 7.1 |
| 2019-12-04 | 38.9 | fog | 0 | 4 | 2.3 |
| 2017-11-02 | 47.5 | fog | 0 | 4 | 2.7 |
| 2017-03-11 | 54.2 | sunny | 0 | 4 | 5.6 |
| 2018-05-29 | 59.5 | fog | 0 | 4 | 6.4 |
| 2017-03-16 | 48.7 | fog | 0 | 4 | 7.8 |
| 2018-03-24 | 46.2 | rain | 0.09 | 4 | 4.1 |
| 2016-12-28 | 36 | fog | 0.01 | 4 | 2.9 |
| 2019-11-09 | 40.4 | fog | 0.01 | 4 | 6 |
| 2018-05-30 | 60 | rain | 0.42 | 4 | 3.3 |
| 2018-04-14 | 55.2 | sunny | 0 | 4.5 | 4.5 |
| 2019-12-28 | 46.4 | fog | 0 | 4.5 | 5.7 |
| 2017-02-06 | 39.2 | fog | 0 | 4.5 | 5.1 |
| 2019-04-02 | 42.6 | rain | 0 | 4.5 | 5.8 |
| 2019-02-28 | 48.8 | rain | 0 | 4.5 | 8.6 |
| 2018-03-04 | 43 | rain | 0.19 | 4.5 | 7 |
| 2017-10-18 | 56.1 | rain | 0.01 | 4.5 | 4.3 |
| 2016-12-14 | 48.8 | fog | 0.01 | 4.5 | 4.9 |
| 2018-11-01 | 48 | fog | 0.16 | 4.5 | 5.3 |
| 2016-11-21 | 47.8 | rain | 0.4 | 4.5 | 8.8 |
| 2017-08-21 | 62.1 | rain | 0.26 | 4.5 | 4.9 |
| 2017-12-10 | 34.9 | rain | 0.21 | 4.5 | 6.1 |
| 2016-06-04 | 59.9 | sunny | 0 | 5 | 4.1 |
| 2019-11-08 | 40.7 | fog | 0 | 5 | 3.7 |
| 2018-10-05 | 60.5 | sunny | 0 | 5 | 4.3 |
| 2017-02-14 | 44.6 | sunny | 0 | 5 | 6.9 |
| 2019-04-30 | 53.5 | sunny | 0 | 5 | 4.3 |
| 2018-10-06 | 50.1 | fog | 0 | 5 | 6.8 |
| 2019-02-17 | 49 | fog | 0 | 5 | 8 |
| 2018-11-28 | 53.3 | rain | 0.27 | 5 | 11.7 |
| 2016-04-11 | 52.5 | rain | 0.05 | 5 | 6 |
| 2016-12-10 | 52.8 | rain | 0.03 | 5 | 8.4 |
| 2018-02-20 | 47.3 | rain | 0.06 | 5 | 7.8 |
| 2016-01-17 | 36.3 | rain | 0.07 | 5 | 6.4 |
| 2017-12-02 | 40.2 | rain | null | 5.5 | 4.8 |
| 2016-09-22 | 61.4 | rain | null | 5.5 | 6.6 |
| 2018-10-20 | 53.1 | sunny | 0 | 5.5 | 4 |
| 2019-01-11 | 45.5 | rain | 0 | 5.5 | 4.4 |
| 2017-05-07 | 54.5 | sunny | 0 | 5.5 | 5.4 |
| 2018-03-09 | 43.6 | rain | 0 | 5.5 | 5.8 |
| 2019-04-06 | 47.4 | fog | 0 | 5.5 | 7.6 |
| 2018-02-28 | 24.6 | snow | 0 | 5.5 | 8.9 |
| 2018-03-10 | 51.5 | rain | 0.25 | 5.5 | 8.1 |
| 2018-05-25 | 59.5 | rain | 0.09 | 5.5 | 3.3 |
| 2019-06-25 | 67.9 | rain | 0.11 | 5.5 | 4.1 |
| 2019-06-13 | 54.8 | rain | 0.57 | 5.5 | 10.5 |
| 2018-04-02 | 46.9 | rain | 0.46 | 5.5 | 9.3 |
| 2018-12-25 | 41.5 | sunny | 0.01 | 5.5 | 3.4 |
| 2017-04-04 | 53.1 | rain | 0.01 | 5.5 | 4.8 |
| 2018-07-29 | 64.6 | rain | 0.01 | 5.5 | 13 |
| 2019-02-08 | 48.6 | rain | 0.01 | 5.5 | 15.5 |
| 2019-01-23 | 34.8 | fog | 0.16 | 5.5 | 3.9 |
| 2018-05-27 | 69.3 | rain | 0.43 | 5.5 | 5.5 |
| 2018-04-22 | 61.7 | rain | 0.43 | 5.5 | 9.1 |
| 2019-12-20 | 46.8 | rain | 0.43 | 5.5 | 8.2 |
| 2016-06-09 | 62.7 | sunny | 0.05 | 5.5 | 4.9 |
| 2016-04-15 | 50 | rain | 0.1 | 5.5 | 4.2 |
| 2018-09-12 | 56.4 | rain | 0.13 | 5.5 | 3.8 |
| 2017-12-07 | 48 | rain | 0.12 | 5.5 | 12.7 |
| 2019-11-16 | 43.1 | rain | 0.07 | 5.5 | 4 |
| 2017-09-19 | 53.7 | rain | 0.14 | 5.5 | 4.3 |
| 2016-11-03 | 43.5 | rain | null | 6 | 5 |
| 2016-04-02 | 50.3 | rain | null | 6 | 7.6 |
| 2016-05-17 | 55.3 | rain | null | 6 | 7.7 |
| 2019-02-26 | 50.7 | sunny | 0 | 6 | 3 |
| 2018-11-26 | 43.1 | rain | 0 | 6 | 4.8 |
| 2016-12-01 | 35 | sunny | 0 | 6 | 4.1 |
| 2019-11-18 | 43.6 | rain | 0 | 6 | 4.6 |
| 2019-04-23 | 60.4 | sunny | 0 | 6 | 4.8 |
| 2019-08-25 | 76.3 | fog | 0 | 6 | 5.1 |
| 2017-03-10 | 49.1 | rain | 0 | 6 | 6.1 |
| 2018-06-02 | 65.4 | sunny | 0 | 6 | 7.8 |
| 2019-01-29 | 35.4 | rain | 0 | 6 | 5 |
| 2018-10-25 | 49.6 | sunny | 0 | 6 | 5.4 |
| 2018-11-14 | 52.1 | rain | 0 | 6 | 7.2 |
| 2018-02-22 | 37.7 | rain | 0 | 6 | 7.5 |
| 2017-04-03 | 51.5 | fog | 0 | 6 | 7 |
| 2018-05-09 | 57.1 | sunny | 0 | 6 | 7.2 |
| 2018-03-21 | 41.5 | sunny | 0 | 6 | 7.3 |
| 2016-04-01 | 45.9 | sunny | 0 | 6 | 7.7 |
| 2016-04-21 | 51.1 | sunny | 0 | 6 | 9.5 |
| 2019-01-16 | 45.6 | rain | 0 | 6 | 9.8 |
| 2018-09-11 | 66.6 | rain | 0 | 6 | 11.6 |
| 2018-11-09 | 50.9 | rain | 0 | 6 | 11.3 |
| 2016-09-05 | 64.6 | rain | 0.09 | 6 | 6 |
| 2017-10-13 | 61.4 | sunny | 0.09 | 6 | 10.5 |
| 2018-04-03 | 51.8 | rain | 0.18 | 6 | 10.3 |
| 2019-06-08 | 58.4 | rain | 0.33 | 6 | 15.3 |
| COL | MIN | 25th PERCENTILE | MEDIAN | 75th PERCENTILE | MAX | AVG | MODE | STD_DEV |
|---|---|---|---|---|---|---|---|---|
| TEMP | 24.6 | 45.675 | 52.75 | 61.8 | 83.4 | 53.512741047 | 57.7 | 10.211334572 |
| PRECIPITATION | 0 | 0 | 0 | 0.07 | 1.44 | 0.06960468521 | 0 | 0.1466155086 |
| VISIBILITY | 0.2 | 5.8 | 6.4 | 6.7 | 14.8 | 6.105027548 | 6.7 | 1.080844202 |
| WINDSPEED | 1.8 | 5.2 | 7.2 | 9.5 | 22.1 | 7.690013774 | 4.8 | 3.228622125 |
| COL | MIN | MAX | MODE | UNIQUE_VALUES |
|---|---|---|---|---|
| DATE | 2016-01-01 | 2019-12-31 | 2018-04-11 | 1452 |
| COL | MIN | MAX | MODE | UNIQUE_VALUES |
|---|---|---|---|---|
| WEATHER | fog | thunderstorm | rain | 5 |
Correlation Matrix
Correlation matrices let you quickly see the correlations between all the different variables.
To create them, we will make use of the CORR
| COL_1 | COL_2 | CORR |
|---|---|---|
| TEMP | PRECIPITATION | -0.05288072854 |
| TEMP | VISIBILITY | 0.3310294722 |
| TEMP | WINDSPEED | 0.03470707836 |
| PRECIPITATION | VISIBILITY | -0.08879966527 |
| PRECIPITATION | WINDSPEED | 0.131898554 |
| VISIBILITY | WINDSPEED | 0.2166210513 |
| PRECIPITATION | TEMP | -0.05288072854 |
| VISIBILITY | PRECIPITATION | -0.08879966527 |
| WINDSPEED | TEMP | 0.03470707836 |
| VISIBILITY | TEMP | 0.3310294722 |
| WINDSPEED | PRECIPITATION | 0.131898554 |
| WINDSPEED | VISIBILITY | 0.2166210513 |
We can see the highest correlations are between temperature and visibility
Identifying Outliers
Identifying outliers, or points that lie at the most extreme ends of your dataset, is a crucial to the rest of your analysis. Early identification, and potential removal of outliers makes for more accurate predictions and a better understanding of your data.
There are many ways to define outliers, but for this example, we'll use Tukey's fences:
A point is an outlier if it lies outside the range [Q1 - k(Q3-Q1) , Q3 + k(Q3-Q1)] where k is either 1.5 or 3 for identifying particularly 'far out' data.
Step 1: Identfy our Quartiles
We can use PERCENTILE_CONT to find our 25th and 75th percentiles:
| Q1 | Q3 |
|---|---|
| 5.2 | 9.5 |
This means we can define an outlier that is outside of the following upper and lower bounds:
| LOWER_BOUND | UPPER_BOUND |
|---|---|
| -1.25 | 15.95 |
Step 2: Identify points outside our range:
| WINDSPEED |
|---|
| 17.6 |
| 16.2 |
| 16 |
| 16.1 |
| 17.3 |
| 18 |
| 17 |
| 18.4 |
| 18 |
| 16.1 |
| 20.2 |
| 17.8 |
| 19.5 |
| 18.5 |
| 19.5 |
| 22.1 |
| 16.3 |
| 20.3 |
| 16.8 |
| 21.6 |
| 19.2 |
| 17.7 |
| 19.7 |
| 17.7 |
| 16 |
| 18.3 |
| 17.6 |
How We Built This
This page was built using Count. It combines the best features of a SQL IDE, Data Visualization Tool, and Computational Notebooks. In the Count notebook, each cell acts like a CTE, meaning you can reference any other cell in your queries.
This makes not only for far more readable reports (like this one) but also a much faster and more powerful way to do your analysis, essentially turning your analysis into a connected graph of data frames rather than one-off convoluted queries and CSV files. And with a built-in visualization framework, you won't have to export your data to make your charts. Go from raw data to interactive report in one document.