Lesson 3
Data Analysis
Computations
Statistics
Basic analysis
Statistics
Basic analysis
Data Analysis
After you have gathered and cleaned your data, we get to the third step — to the data analysis.
Even though it may sound perplexing and terrifying, in fact many projects are built on basic statistical analysis and mathematical operations.
You can see the basic rules for writing formulas in Excel in the next column →
In Python, there are other rules!
Even though it may sound perplexing and terrifying, in fact many projects are built on basic statistical analysis and mathematical operations.
You can see the basic rules for writing formulas in Excel in the next column →
In Python, there are other rules!
- "equals" in conditions ==
- doesn't equal — != or is not
- and — & or and
- or — | or or
Excel generalities:
- formulas start with =
- to close a cell, hit Enter
- to spread a formula to the neighbouring cells, click on little blue dot in the right bottom corner and drag it down or double click it
- to fix a range, add dollar signs before the letter and the number both for the first and for the last cell in the range: $A$1:$D$12
- equals =
- less than <
- more than >
- doesn't equal <>
- wrong format =....(..)=FALSE — formulas ISTEXT, ISNUMBER, ISBLANK etc.
- numbers — without quotation marks 2023
- text — in quotation marks "2023"
- condition "and" is a formula =AND(condition_1;condition_2)
- condition "or" is a formula =OR(condition_1;condition_2)
- parentheses () turn the number inside of them to a negative number, so (60) = -60
Wrong decimal separator
If the data has the wrong separator for integers and floats (point instead of comma, comma as a separator), there are two ways to fix it.
First way — using "Find and replace":
- Change commas (which separate thousands from hundreds) to nothing.
- Change points to commas.
Second way — changing the locale settings:
- In Google Spreadsheets: File → Settings → Select Locale → Click Save → Reload page.
- В Excel: File → Options → Advanced → clear (uncheck) the Use system separators check box → change the separators.
- In Russian Excel: Файл → Параметры → Дополнительно → Отщёлкнуть Использовать системные разделители → Заменить запятую на точку и пустоту на запятую.
KEY POINT
Your actions come from the questions you have to the data
Statistical Analysis
Question №1: How many entities do I have in general?
Question №2: What is the total of my values?
Question №3: What is the average of my values?
Question №4: What is the middle value of my range?
- =COUNT(range_of_values) — counts only the numeric values in a range
- =COUNTA(range_of_values) — counts all values in a range
- =COUNTIF(range_of_values;condition) — counts values if they meet the condition
- if you want to compare the range to a particular cell, use ">"&Α1 or "<"&Α1 as condition, where A1 stands for the cell number
- =COUNTIF(range_of_values;"") — all the blank values
- =COUNTIF(range_of_values;"*") — all the text values
Question №2: What is the total of my values?
- =SUM(range_of_values) — sums up the values
- =SUMIF(range_of_values;condition) — sums up the values that meet the condition
Question №3: What is the average of my values?
- =AVERAGE(range_of_values) — computes the average of a range
Question №4: What is the middle value of my range?
- =MEDIAN(range_of_values) — returns the middle value of your sorted range
Question №5: Which value is the most frequent on in my range?
- =MODE(range_of_values) — returns one or several most frequent values
Question №6: Are my values from two datasets interconnected? Do they influence each other?
- =CORREL(first_range;second_range) — computates the Pearson correlation coefficient for two ranges
Question №7: What are the minimum and the maximum values of my range?
- =MIN(range_of_values) — minimum
- =MAX(range_of_values) — maximum
Question №8: Which values are “lagging behind”, which ones are the “leaders”, and which ones are somewhere in the middle? How can I group them?
- =PERCENTILE(range_of_values; percentage) — find the value boundary which separates the given percentage of all values from the rest of the values
- quantile — 0,20 or 20 %
- quartile — 0,25 or 25 %
Question 9:
By what percentage is one number greater than another?
For example, by what percentage has this year's figure changed compared to last year's?
If you want to better understand percentages,
check out this website
(YouTube video tutorials require a VPN)
By what percentage is one number greater than another?
For example, by what percentage has this year's figure changed compared to last year's?
If you want to better understand percentages,
check out this website
(YouTube video tutorials require a VPN)
Average VS Median
Imagine we have measured the temperatures in our apartment:
If we want to find the average temperature of our apartment, we should:
Thus, the average will be (24+18-18+9+180)/5 = 42,6 °C
It is not plausible that we live by such a temperature. The value "+180°C" is the statistical outlier — it is far away from other values and significantly influences the result without any significant meaning.
- +24°C — in the rooms
- +18°C — in a room with an open window
- -18°C — in the freezer
- +9°C — in the main section of the refrigerator
- +180°C — in the oven.
If we want to find the average temperature of our apartment, we should:
- Sum the values up.
- Divide the sum by the amount of values.
Thus, the average will be (24+18-18+9+180)/5 = 42,6 °C
It is not plausible that we live by such a temperature. The value "+180°C" is the statistical outlier — it is far away from other values and significantly influences the result without any significant meaning.
Ответ: вагадане
—
If we want to avoid the statistical outliers and to find the "true" middle value, we should calculate the median.
- Sort the values out from the minimum to the maximum: -18 +9 +18 +24 +180.
- Count the amount of values.
- If the amount of values is an odd number (1, 3, 5 etc.), choose the middle value in the sorted range: -18 +9 +18 +24 +180.
- Here, the median is +18 °C.
- If the amount of values is an even number (2, 4, 6 etc.), choose two middle values and calculate the average of them: -18 +9 +18 +20 +24 +180
Here, the median is +19 °C.
CALCULATE AVERAGE, median, and mode
50 12 55 118
5 39 0 5 5
5 39 0 5 5
Correlation
The Pearson correlation coefficient shows, whether the values are interconnected — whether their "flow" goes with a similar pattern. It is a linear coefficient with returned values between -1 and +1.
Examples of correlations:
Examples of correlations:
- Number of crimes and population by region — 0.9
- Cost of housing and walking distance to the metro — 0.5
Ответ: вагадане
—
The strength of the correlation is measured by its absolute value. In ideal conditions, a correlation is considered very strong if its absolute value exceeds 0.8. In reality, such a correlation is rare, and values around 0.5 already show a fairly significant relationship between two variables.
The correlation coefficient
must be confirmed
by external evidence!
See the Spurious correlations project
must be confirmed
by external evidence!
See the Spurious correlations project
Statistical outliers
in a scatter plot and a histogram
What is correlation
Correlation does not imply causation
Confounding variables (confounders) are the factors that influence both the dependent and independent variables
For example, regions with more children attending kindergarten have higher crime rates. Does this mean kindergartens are producing criminals? No! Most likely, the region has a larger population—and therefore more kindergarteners and criminals.
What are quartiles, quantiles, and percentiles
Pivot tables in Excel
Pivot tables in Google Sheets
How to Use AI Chatbots
for Data Analysis
for Data Analysis
Rule 1: Don't upload huge datasets directly to your chatbot, especially if you have a free version.
Why? Each chatbot has a context window — the maximum amount of data the neural network can process and consider at one time. A large dataset will fill the context window, leaving no room for further maneuvering.
What to do instead? It's best to describe the dataset: write down its name, format, essence, column names. For example: "The dataset in CSV format is dedicated to banana sales at the market and contains the following columns: Banana_variety, Weight_of_bananas_sold, Price_per_banana, Total_cost".
Why? Each chatbot has a context window — the maximum amount of data the neural network can process and consider at one time. A large dataset will fill the context window, leaving no room for further maneuvering.
What to do instead? It's best to describe the dataset: write down its name, format, essence, column names. For example: "The dataset in CSV format is dedicated to banana sales at the market and contains the following columns: Banana_variety, Weight_of_bananas_sold, Price_per_banana, Total_cost".
Rule 2: You can ask the chatbot to supplement existing data, but you need to double-check it.
Example task. Given: a dataset with addresses. Task: to find districts. Request to the AI: for each address, find the district in which it is located.
What can go wrong:
Rule 3: It's a good idea to ask the chatbot about non-obvious hypotheses, potential influencing factors, and possible pitfalls.
Example task. Given: a dataset with addresses. Task: to find districts. Request to the AI: for each address, find the district in which it is located.
What can go wrong:
- The AI may write random districts.
- The AI may fail to find districts for certain addresses and leave cells blank.
- The query context window in the free version may become full, and it may not process all the required addresses.
Rule 3: It's a good idea to ask the chatbot about non-obvious hypotheses, potential influencing factors, and possible pitfalls.
A case study using an AI chatbot for data analysis
Task. The editors are asking you to analyze how November weather affects accident rates. It seems like everyone is more distracted in November than in summer. The news seemingly more often reports accidents caused by driver or pedestrian inattention.
Difficulty. The absolute number of accidents in June was higher than in November. So, does this mean the perceptions aren't confirmed?
DeepSeek: What if there are more accidents in the summer simply because there are more vehicles on the streets? Cars, motorcycles, bicycles, scooters... But on average, fewer people die in traffic accidents in June than in November?
Result. The hypothesis was confirmed. Although there are more road accidents in June than in November, fewer people die in them. Accidents in November are more often fatal.
Final post
Difficulty. The absolute number of accidents in June was higher than in November. So, does this mean the perceptions aren't confirmed?
DeepSeek: What if there are more accidents in the summer simply because there are more vehicles on the streets? Cars, motorcycles, bicycles, scooters... But on average, fewer people die in traffic accidents in June than in November?
Result. The hypothesis was confirmed. Although there are more road accidents in June than in November, fewer people die in them. Accidents in November are more often fatal.
Final post
Home Assignment
Deadline — 19 February, Thursday, 18:00
Grading criteria
Grading criteria
This task is an individual one, you have two weeks to complete it.
1. Create a new text document in your folder with a prefix "Surname_Analysis"
2. Select one of your project datasets or the dataset you've scraped using Instant Data Scraper.
3. Calculate the following values for all the important characteristics and write them down in your "Analysis" document:
4. Research your results, try to interpret them, bring human sense to your data.
Describe your foundings in the "Analysis" document — as if it were a part of a media article.
5. Quote
Find in the open sources a quote of the officials or experts that can illustrate your insights — for instance, a quote of the UN or corporate leaders. Put it in the file, mention
Find a picture of the speaker and paste it near the quote with a link to the original source.
6. Find a suitable image with open rights (see Unsplash, Freepik, Wikimedia Commons etc.) with a good quality (1920x1080 or more) to illustrate your future article. It doesn't matter, whether it is raster or vector. Download it, upload it to your folder as a file and then copy-paste it in the document with a link to the original source.
7. Summarize your key insight in one sentence as a push-notification — up to 120 symbols. Put it in the end of your document with a lightning ⚡ emoji.
P.S. This home assignment is sponsored by Miley Cyrus.
1. Create a new text document in your folder with a prefix "Surname_Analysis"
2. Select one of your project datasets or the dataset you've scraped using Instant Data Scraper.
3. Calculate the following values for all the important characteristics and write them down in your "Analysis" document:
- minimum
- maximum
- average
- median
4. Research your results, try to interpret them, bring human sense to your data.
- For instance, if the Google trends measure hit 100 on a particular date, what happened?
- Or if some measure in Bolivia overpasses this measure in India, what particular conditions in Bolivia fuel this process? And what conditions in India prevent it?
- 🌻 Do the average and the median differ? Is this difference significant? Why? What is better to use?
- Are there any correlations?
- Take a look at the historical aspect of this data: are the values decreasing or increasing in the course of time? Add some general historical background. You can use external sources of information to back up your reasoning.
Describe your foundings in the "Analysis" document — as if it were a part of a media article.
5. Quote
Find in the open sources a quote of the officials or experts that can illustrate your insights — for instance, a quote of the UN or corporate leaders. Put it in the file, mention
- the name,
- the duty position of this person
- and the link to the original quote sourse.
Find a picture of the speaker and paste it near the quote with a link to the original source.
6. Find a suitable image with open rights (see Unsplash, Freepik, Wikimedia Commons etc.) with a good quality (1920x1080 or more) to illustrate your future article. It doesn't matter, whether it is raster or vector. Download it, upload it to your folder as a file and then copy-paste it in the document with a link to the original source.
7. Summarize your key insight in one sentence as a push-notification — up to 120 symbols. Put it in the end of your document with a lightning ⚡ emoji.
P.S. This home assignment is sponsored by Miley Cyrus.
