## 1. Project Overview & Quickstart (microsoft/Data-Science-For-Beginners) ## File: README.md # Data Science for Beginners - A Curriculum [](https://github.com/codespaces/new?hide_repo_select=true&ref=main&repo=344191198) [](https://github.com/microsoft/Data-Science-For-Beginners/blob/master/LICENSE) [](https://GitHub.com/microsoft/Data-Science-For-Beginners/graphs/contributors/) [](https://GitHub.com/microsoft/Data-Science-For-Beginners/issues/) [](https://GitHub.com/microsoft/Data-Science-For-Beginners/pulls/) [](http://makeapullrequest.com) [](https://GitHub.com/microsoft/Data-Science-For-Beginners/watchers/) [](https://GitHub.com/microsoft/Data-Science-For-Beginners/network/) [](https://GitHub.com/microsoft/Data-Science-For-Beginners/stargazers/) [](https://discord.gg/nTYy5BXMWG) [](https://aka.ms/foundry/forum) Azure Cloud Advocates at Microsoft are pleased to offer a 10-week, 20-lesson curriculum all about Data Science. Each lesson includes pre-lesson and post-lesson quizzes, written instructions to complete the lesson, a solution, and an assignment. Our project-based pedagogy allows you to learn while building, a proven way for new skills to 'stick'. **Hearty thanks to our authors:** [Jasmine Greenaway](https://www.twitter.com/paladique), [Dmitry Soshnikov](http://soshnikov.com), [Nitya Narasimhan](https://twitter.com/nitya), [Jalen McGee](https://twitter.com/JalenMcG), [Jen Looper](https://twitter.com/jenlooper), [Maud Levy](https://twitter.com/maudstweets), [Tiffany Souterre](https://twitter.com/TiffanySouterre), [Christopher Harrison](https://www.twitter.com/geektrainer). **πŸ™ Special thanks πŸ™ to our [Microsoft Student Ambassador](https://studentambassadors.microsoft.com/) authors, reviewers and content contributors,** notably Aaryan Arora, [Aditya Garg](https://github.com/AdityaGarg00), [Alondra Sanchez](https://www.linkedin.com/in/alondra-sanchez-molina/), [Ankita Singh](https://www.linkedin.com/in/ankitasingh007), [Anupam Mishra](https://www.linkedin.com/in/anupam--mishra/), [Arpita Das](https://www.linkedin.com/in/arpitadas01/), ChhailBihari Dubey, [Dibri Nsofor](https://www.linkedin.com/in/dibrinsofor), [Dishita Bhasin](https://www.linkedin.com/in/dishita-bhasin-7065281bb), [Majd Safi](https://www.linkedin.com/in/majd-s/), [Max Blum](https://www.linkedin.com/in/max-blum-6036a1186/), [Miguel Correa](https://www.linkedin.com/in/miguelmque/), [Mohamma Iftekher (Iftu) Ebne Jalal](https://twitter.com/iftu119), [Nawrin Tabassum](https://www.linkedin.com/in/nawrin-tabassum), [Raymond Wangsa Putra](https://www.linkedin.com/in/raymond-wp/), [Rohit Yadav](https://www.linkedin.com/in/rty2423), Samridhi Sharma, [Sanya Sinha](https://www.linkedin.com/mwlite/in/sanya-sinha-13aab1200), [Sheena Narula](https://www.linkedin.com/in/sheena-narua-n/), [Tauqeer Ahmad](https://www.linkedin.com/in/tauqeerahmad5201/), Yogendrasingh Pawar , [Vidushi Gupta](https://www.linkedin.com/in/vidushi-gupta07/), [Jasleen Sondhi](https://www.linkedin.com/in/jasleen-sondhi/) || |:---:| | Data Science For Beginners - _Sketchnote by [@nitya](https://twitter.com/nitya)_ | ### 🌐 Multi-Language Support #### Supported via GitHub Action (Automated & Always Up-to-Date) [Arabic](./translations/ar/README.md) | [Bengali](./translations/bn/README.md) | [Bulgarian](./translations/bg/README.md) | [Burmese (Myanmar)](./translations/my/README.md) | [Chinese (Simplified)](./translations/zh-CN/README.md) | [Chinese (Traditional, Hong Kong)](./translations/zh-HK/README.md) | [Chinese (Traditional, Macau)](./translations/zh-MO/README.md) | [Chinese (Traditional, Taiwan)](./translations/zh-TW/README.md) | [Croatian](./translations/hr/README.md) | [Czech](./translations/cs/README.md) | [Danish](./translations/da/README.md) | [Dutch](./translations/nl/README.md) | [Estonian](./translations/et/README.md) | [Finnish](./translations/fi/README.md) | [French](./translations/fr/README.md) | [German](./translations/de/README.md) | [Greek](./translations/el/README.md) | [Hebrew](./translations/he/README.md) | [Hindi](./translations/hi/README.md) | [Hungarian](./translations/hu/README.md) | [Indonesian](./translations/id/README.md) | [Italian](./translations/it/README.md) | [Japanese](./translations/ja/README.md) | [Kannada](./translations/kn/README.md) | [Khmer](./translations/km/README.md) | [Korean](./translations/ko/README.md) | [Lithuanian](./translations/lt/README.md) | [Malay](./translations/ms/README.md) | [Malayalam](./translations/ml/README.md) | [Marathi](./translations/mr/README.md) | [Nepali](./translations/ne/README.md) | [Nigerian Pidgin](./translations/pcm/README.md) | [Norwegian](./translations/no/README.md) | [Persian (Farsi)](./translations/fa/README.md) | [Polish](./translations/pl/README.md) | [Portuguese (Brazil)](./translations/pt-BR/README.md) | [Portuguese (Portugal)](./translations/pt-PT/README.md) | [Punjabi (Gurmukhi)](./translations/pa/README.md) | [Romanian](./translations/ro/README.md) | [Russian](./translations/ru/README.md) | [Serbian (Cyrillic)](./translations/sr/README.md) | [Slovak](./translations/sk/README.md) | [Slovenian](./translations/sl/README.md) | [Spanish](./translations/es/README.md) | [Swahili](./translations/sw/README.md) | [Swedish](./translations/sv/README.md) | [Tagalog (Filipino)](./translations/tl/README.md) | [Tamil](./translations/ta/README.md) | [Telugu](./translations/te/README.md) | [Thai](./translations/th/README.md) | [Turkish](./translations/tr/README.md) | [Ukrainian](./translations/uk/README.md) | [Urdu](./translations/ur/README.md) | [Vietnamese](./translations/vi/README.md) > **Prefer to Clone Locally?** > > This repository includes 50+ language translations which significantly increases the download size. To clone without translations, use sparse checkout: > > **Bash / macOS / Linux:** > ```bash > git clone --filter=blob:none --sparse https://github.com/microsoft/Data-Science-For-Beginners.git > cd Data-Science-For-Beginners > git sparse-checkout set --no-cone '/*' '!translations' '!translated_images' > ``` > > **CMD (Windows):** > ```cmd > git clone --filter=blob:none --sparse https://github.com/microsoft/Data-Science-For-Beginners.git > cd Data-Science-For-Beginners > git sparse-checkout set --no-cone "/*" "!translations" "!translated_images" > ``` > > This gives you everything you need to complete the course with a much faster download. **If you wish to have additional translations languages supported are listed [here](https://github.com/Azure/co-op-translator/blob/main/getting_started/supported-languages.md)** #### Join Our Community [](https://discord.gg/nTYy5BXMWG) We have a Discord learn with AI series ongoing, learn more and join us at [Learn with AI Series](https://aka.ms/learnwithai/discord) from 18 - 30 September, 2025. You will get tips and tricks of using GitHub Copilot for Data Science. # Are you a student? Get started with the following resources: - [Student Hub page](https://docs.microsoft.com/en-gb/learn/student-hub?WT.mc_id=academic-77958-bethanycheum) In this page, you will find beginner resources, Student packs and even ways to get a free cert voucher. This is one page you want to bookmark and check from time to time as we switch out content at least monthly. - [Microsoft Learn Student Ambassadors](https://studentambassadors.microsoft.com?WT.mc_id=academic-77958-bethanycheum) Join a global community of student ambassadors, this could be your way into Microsoft. # Getting Started ## πŸ“š Documentation - **[Installation Guide](INSTALLATION.md)** - Step-by-step setup instructions for beginners - **[Usage Guide](USAGE.md)** - Examples and common workflows - **[Troubleshooting](TROUBLESHOOTING.md)** - Solutions to common issues - **[Contributing Guide](CONTRIBUTING.md)** - How to contribute to this project - **[For Teachers](for-teachers.md)** - Teaching guidance and classroom resources ## πŸ‘¨β€πŸŽ“ For Students > **Complete Beginners**: New to data science? Start with our [beginner-friendly examples](examples/README.md)! These simple, well-commented examples will help you understand the basics before diving into the full curriculum. > **[Students](https://aka.ms/student-page)**: to use this curriculum on your own, fork the entire repo and complete the exercises on your own, starting with a pre-lecture quiz. Then read the lecture and complete the rest of the activities. Try to create the projects by comprehending the lessons rather than copying the solution code; however, that code is available in the /solutions folders in each project-oriented lesson. Another idea would be to form a study group with friends and go through the content together. For further study, we recommend [Microsoft Learn](https://docs.microsoft.com/en-us/users/jenlooper-2911/collections/qprpajyoy3x0g7?WT.mc_id=academic-77958-bethanycheum). **Quick Start:** 1. Check the [Installation Guide](INSTALLATION.md) to set up your environment 2. Review the [Usage Guide](USAGE.md) to learn how to work with the curriculum 3. Start with Lesson 1 and work through sequentially 4. Join our [Discord community](https://aka.ms/ds4beginners/discord) for support ## πŸ‘©β€πŸ« For Teachers > **Teachers**: we have [included some suggestions](for-teachers.md) on how to use this curriculum. We'd love your feedback [in our discussion forum](https://github.com/microsoft/Data-Science-For-Beginners/discussions)! ## Meet the Team [](https://youtu.be/8mzavjQSMM4 "Promo video") **Gif by** [Mohit Jaisal](https://www.linkedin.com/in/mohitjaisal) > πŸŽ₯ Click the image above for a video about the project the folks who created it! ## Pedagogy We have chosen two pedagogical tenets while building this curriculum: ensuring that it is project-based and that it includes frequent quizzes. By the end of this series, students will have learned basic principles of data science, including ethical concepts, data preparation, different ways of working with data, data visualization, data analysis, real-world use cases of data science, and more. In addition, a low-stakes quiz before a class sets the intention of the student towards learning a topic, while a second quiz after class ensures further retention. This curriculum was designed to be flexible and fun and can be taken in whole or in part. The projects start small and become increasingly complex by the end of the 10 week cycle. > Find our [Code of Conduct](CODE_OF_CONDUCT.md), [Contributing](CONTRIBUTING.md), [Translation](TRANSLATIONS.md) guidelines. We welcome your constructive feedback! ## Each lesson includes: - Optional sketchnote - Optional supplemental video - Pre-lesson warmup quiz - Written lesson - For project-based lessons, step-by-step guides on how to build the project - Knowledge checks - A challenge - Supplemental reading - Assignment - [Post-lesson quiz](https://ff-quizzes.netlify.app/en/) > **A note about quizzes**: All quizzes are contained in the Quiz-App folder, for 40 total quizzes of three questions each. They are linked from within the lessons, but the quiz app can be run locally or deployed to Azure; follow the instruction in the `quiz-app` folder. They are gradually being localized. ## πŸŽ“ Beginner-Friendly Examples **New to Data Science?** We've created a special [examples directory](examples/README.md) with simple, well-commented code to help you get started: - 🌟 **Hello World** - Your first data science program - πŸ“‚ **Loading Data** - Learn to read and explore datasets - πŸ“Š **Simple Analysis** - Calculate statistics and find patterns - πŸ“ˆ **Basic Visualization** - Create charts and graphs - πŸ”¬ **Real-World Project** - Complete workflow from start to finish Each example includes detailed comments explaining every step, making it perfect for absolute beginners! πŸ‘‰ **[Start with the examples](examples/README.md)** πŸ‘ˆ ## Lessons || |:---:| | Data Science For Beginners: Roadmap - _Sketchnote by [@nitya](https://twitter.com/nitya)_ | | Lesson Number | Topic | Lesson Grouping | Learning Objectives | Linked Lesson | Author | | :-----------: | :----------------------------------------: | :--------------------------------------------------: | :-----------------------------------------------------------------------------------------------------------------------------------------------------------------------: | :---------------------------------------------------------------------: | :----: | | 01 | Defining Data Science | [Introduction](1-Introduction/README.md) | Learn the basic concepts behind data science and how it’s related to artificial intelligence, machine learning, and big data. | [lesson](1-Introduction/01-defining-data-science/README.md) [video](https://youtu.be/beZ7Mb_oz9I) | [Dmitry](http://soshnikov.com) | | 02 | Data Science Ethics | [Introduction](1-Introduction/README.md) | Data Ethics Concepts, Challenges & Frameworks. | [lesson](1-Introduction/02-ethics/README.md) | [Nitya](https://twitter.com/nitya) | | 03 | Defining Data | [Introduction](1-Introduction/README.md) | How data is classified and its common sources. | [lesson](1-Introduction/03-defining-data/README.md) | [Jasmine](https://www.twitter.com/paladique) | | 04 | Introduction to Statistics & Probability | [Introduction](1-Introduction/README.md) | The mathematical techniques of probability and statistics to understand data. | [lesson](1-Introduction/04-stats-and-probability/README.md) [video](https://youtu.be/Z5Zy85g4Yjw) | [Dmitry](http://soshnikov.com) | | 05 | Working with Relational Data | [Working With Data](2-Working-With-Data/README.md) | Introduction to relational data and the basics of exploring and analyzing relational data with the Structured Query Language, also known as SQL (pronounced β€œsee-quell”). | [lesson](2-Working-With-Data/05-relational-databases/README.md) | [Christopher](https://www.twitter.com/geektrainer) | | | | 06 | Working with NoSQL Data | [Working With Data](2-Working-With-Data/README.md) | Introduction to non-relational data, its various types and the basics of exploring and analyzing document databases. | [lesson](2-Working-With-Data/06-non-relational/README.md) | [Jasmine](https://twitter.com/paladique)| | 07 | Working with Python | [Working With Data](2-Working-With-Data/README.md) | Basics of using Python for data exploration with libraries such as Pandas. Foundational understanding of Python programming is recommended. | [lesson](2-Working-With-Data/07-python/README.md) [video](https://youtu.be/dZjWOGbsN4Y) | [Dmitry](http://soshnikov.com) | | 08 | Data Preparation | [Working With Data](2-Working-With-Data/README.md) | Topics on data techniques for cleaning and transforming the data to handle challenges of missing, inaccurate, or incomplete data. | [lesson](2-Working-With-Data/08-data-preparation/README.md) | [Jasmine](https://www.twitter.com/paladique) | | 09 | Visualizing Quantities | [Data Visualization](3-Data-Visualization/README.md) | Learn how to use Matplotlib to visualize bird data πŸ¦† | [lesson](3-Data-Visualization/09-visualization-quantities/README.md) | [Jen](https://twitter.com/jenlooper) | | 10 | Visualizing Distributions of Data | [Data Visualization](3-Data-Visualization/README.md) | Visualizing observations and trends within an interval. | [lesson](3-Data-Visualization/10-visualization-distributions/README.md) | [Jen](https://twitter.com/jenlooper) | | 11 | Visualizing Proportions | [Data Visualization](3-Data-Visualization/README.md) | Visualizing discrete and grouped percentages. | [lesson](3-Data-Visualization/11-visualization-proportions/README.md) | [Jen](https://twitter.com/jenlooper) | | 12 | Visualizing Relationships | [Data Visualization](3-Data-Visualization/README.md) | Visualizing connections and correlations between sets of data and their variables. | [lesson](3-Data-Visualization/12-visualization-relationships/README.md) | [Jen](https://twitter.com/jenlooper) | | 13 | Meaningful Visualizations | [Data Visualization](3-Data-Visualization/README.md) | Techniques and guidance for making your visualizations valuable for effective problem solving and insights. | [lesson](3-Data-Visualization/13-meaningful-visualizations/README.md) | [Jen](https://twitter.com/jenlooper) | | 14 | Introduction to the Data Science lifecycle | [Lifecycle](4-Data-Science-Lifecycle/README.md) | Introduction to the data science lifecycle and its first step of acquiring and extracting data. | [lesson](4-Data-Science-Lifecycle/14-Introduction/README.md) | [Jasmine](https://twitter.com/paladique) | | 15 | Analyzing | [Lifecycle](4-Data-Science-Lifecycle/README.md) | This phase of the data science lifecycle focuses on techniques to analyze data. | [lesson](4-Data-Science-Lifecycle/15-analyzing/README.md) | [Jasmine](https://twitter.com/paladique) | | | | 16 | Communication | [Lifecycle](4-Data-Science-Lifecycle/README.md) | This phase of the data science lifecycle focuses on presenting the insights from the data in a way that makes it easier for decision makers to understand. | [lesson](4-Data-Science-Lifecycle/16-communication/README.md) | [Jalen](https://twitter.com/JalenMcG) | | | | 17 | Data Science in the Cloud | [Cloud Data](5-Data-Science-In-Cloud/README.md) | This series of lessons introduces data science in the cloud and its benefits. | [lesson](5-Data-Science-In-Cloud/17-Introduction/README.md) | [Tiffany](https://twitter.com/TiffanySouterre) and [Maud](https://twitter.com/maudstweets) | | 18 | Data Science in the Cloud | [Cloud Data](5-Data-Science-In-Cloud/README.md) | Training models using Low Code tools. |[lesson](5-Data-Science-In-Cloud/18-Low-Code/README.md) | [Tiffany](https://twitter.com/TiffanySouterre) and [Maud](https://twitter.com/maudstweets) | | 19 | Data Science in the Cloud | [Cloud Data](5-Data-Science-In-Cloud/README.md) | Deploying models with Azure Machine Learning Studio. | [lesson](5-Data-Science-In-Cloud/19-Azure/README.md)| [Tiffany](https://twitter.com/TiffanySouterre) and [Maud](https://twitter.com/maudstweets) | | 20 | Data Science in the Wild | [In the Wild](6-Data-Science-In-Wild/README.md) | Data science driven projects in the real world. | [lesson](6-Data-Science-In-Wild/20-Real-World-Examples/README.md) | [Nitya](https://twitter.com/nitya) | ## GitHub Codespaces Follow these steps to open this sample in a Codespace: 1. Click the Code drop-down menu and select the Open with Codespaces option. 2. Select + New codespace at the bottom on the pane. For more info, check out the [GitHub documentation](https://docs.github.com/en/codespaces/developing-in-codespaces/creating-a-codespace-for-a-repository#creating-a-codespace). ## VSCode Remote - Containers Follow these steps to open this repo in a container using your local machine and VSCode using the VS Code Remote - Containers extension: 1. If this is your first time using a development container, please ensure your system meets the pre-reqs (i.e. have Docker installed) in [the getting started documentation](https://code.visualstudio.com/docs/devcontainers/containers#_getting-started). To use this repository, you can either open the repository in an isolated Docker volume: **Note**: Under the hood, this will use the Remote-Containers: **Clone Repository in Container Volume...** command to clone the source code in a Docker volume instead of the local filesystem. [Volumes](https://docs.docker.com/storage/volumes/) are the preferred mechanism for persisting container data. Or open a locally cloned or downloaded version of the repository: - Clone this repository to your local filesystem. - Press F1 and select the **Remote-Containers: Open Folder in Container...** command. - Select the cloned copy of this folder, wait for the container to start, and try things out. ## Offline access You can run this documentation offline by using [Docsify](https://docsify.js.org/#/). Fork this repo, [install Docsify](https://docsify.js.org/#/quickstart) on your local machine, then in the root folder of this repo, type `docsify serve`. The website will be served on port 3000 on your localhost: `localhost:3000`. > Note, notebooks will not be rendered via Docsify, so when you need to run a notebook, do that separately in VS Code running a Python kernel. ## Other Curricula Our team produces other curricula! Check out: ### LangChain [](https://aka.ms/langchain4j-for-beginners) [](https://aka.ms/langchainjs-for-beginners?WT.mc_id=m365-94501-dwahlin) [](https://github.com/microsoft/langchain-for-beginners?WT.mc_id=m365-94501-dwahlin) --- ### Azure / Edge / MCP / Agents [](https://github.com/microsoft/AZD-for-beginners?WT.mc_id=academic-105485-koreyst) [](https://github.com/microsoft/edgeai-for-beginners?WT.mc_id=academic-105485-koreyst) [](https://github.com/microsoft/mcp-for-beginners?WT.mc_id=academic-105485-koreyst) [](https://github.com/microsoft/ai-agents-for-beginners?WT.mc_id=academic-105485-koreyst) --- ### Generative AI Series [](https://github.com/microsoft/generative-ai-for-beginners?WT.mc_id=academic-105485-koreyst) [-9333EA?style=for-the-badge&labelColor=E5E7EB&color=9333EA)](https://github.com/microsoft/Generative-AI-for-beginners-dotnet?WT.mc_id=academic-105485-koreyst) [-C084FC?style=for-the-badge&labelColor=E5E7EB&color=C084FC)](https://github.com/microsoft/generative-ai-for-beginners-java?WT.mc_id=academic-105485-koreyst) [-E879F9?style=for-the-badge&labelColor=E5E7EB&color=E879F9)](https://github.com/microsoft/generative-ai-with-javascript?WT.mc_id=academic-105485-koreyst) --- ### Core Learning [](https://aka.ms/ml-beginners?WT.mc_id=academic-105485-koreyst) [](https://aka.ms/datascience-beginners?WT.mc_id=academic-105485-koreyst) [](https://aka.ms/ai-beginners?WT.mc_id=academic-105485-koreyst) [](https://github.com/microsoft/Security-101?WT.mc_id=academic-96948-sayoung) [](https://aka.ms/webdev-beginners?WT.mc_id=academic-105485-koreyst) [](https://aka.ms/iot-beginners?WT.mc_id=academic-105485-koreyst) [](https://github.com/microsoft/xr-development-for-beginners?WT.mc_id=academic-105485-koreyst) --- ### Copilot Series [](https://aka.ms/GitHubCopilotAI?WT.mc_id=academic-105485-koreyst) [](https://github.com/microsoft/mastering-github-copilot-for-dotnet-csharp-developers?WT.mc_id=academic-105485-koreyst) [](https://github.com/microsoft/CopilotAdventures?WT.mc_id=academic-105485-koreyst) ## Getting Help **Encountering issues?** Check our [Troubleshooting Guide](TROUBLESHOOTING.md) for solutions to common problems. If you get stuck or have any questions about building AI apps. Join fellow learners and experienced developers in discussions about MCP. It's a supportive community where questions are welcome and knowledge is shared freely. [](https://discord.gg/nTYy5BXMWG) If you have product feedback or errors while building visit: [](https://aka.ms/foundry/forum) --- ## File: 1-Introduction/04-stats-and-probability/README.md # A Brief Introduction to Statistics and Probability | ](../../sketchnotes/04-Statistics-Probability.png)| |:---:| | Statistics and Probability - _Sketchnote by [@nitya](https://twitter.com/nitya)_ | Statistics and Probability Theory are two highly related areas of Mathematics that are highly relevant to Data Science. It is possible to operate with data without deep knowledge of mathematics, but it is still better to know at least some basic concepts. Here we will present a short introduction that will help you get started. [](https://youtu.be/Z5Zy85g4Yjw) ## [Pre-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/6) ## Probability and Random Variables **Probability** is a number between 0 and 1 that expresses how probable an **event** is. It is defined as a number of positive outcomes (that lead to the event), divided by total number of outcomes, given that all outcomes are equally probable. For example, when we roll a dice, the probability that we get an even number is 3/6 = 0.5. When we talk about events, we use **random variables**. For example, the random variable that represents a number obtained when rolling a dice would take values from 1 to 6. Set of numbers from 1 to 6 is called **sample space**. We can talk about the probability of a random variable taking a certain value, for example P(X=3)=1/6. The random variable in previous example is called **discrete**, because it has a countable sample space, i.e. there are separate values that can be enumerated. There are cases when sample space is a range of real numbers, or the whole set of real numbers. Such variables are called **continuous**. A good example is the time when the bus arrives. ## Probability Distribution In the case of discrete random variables, it is easy to describe the probability of each event by a function P(X). For each value *s* from sample space *S* it will give a number from 0 to 1, such that the sum of all values of P(X=s) for all events would be 1. The most well-known discrete distribution is **uniform distribution**, in which there is a sample space of N elements, with equal probability of 1/N for each of them. It is more difficult to describe the probability distribution of a continuous variable, with values drawn from some interval [a,b], or the whole set of real numbers ℝ. Consider the case of bus arrival time. In fact, for each exact arrival time *t*, the probability of a bus arriving at exactly that time is 0! > Now you know that events with 0 probability happen, and very often! At least each time when the bus arrives! We can only talk about the probability of a variable falling in a given interval of values, eg. P(t1≤X<t2). In this case, probability distribution is described by a **probability density function** p(x), such that A continuous analog of uniform distribution is called **continuous uniform**, which is defined on a finite interval. A probability that the value X falls into an interval of length l is proportional to l, and rises up to 1. Another important distribution is **normal distribution**, which we will talk about in more detail below. ## Mean, Variance and Standard Deviation Suppose we draw a sequence of n samples of a random variable X: x1, x2, ..., xn. We can define **mean** (or **arithmetic average**) value of the sequence in the traditional way as (x1+x2+xn)/n. As we grow the size of the sample (i.e. take the limit with n→∞), we will obtain the mean (also called **expectation**) of the distribution. We will denote expectation by **E**(x). > It can be demonstrated that for any discrete distribution with values {x1, x2, ..., xN} and corresponding probabilities p1, p2, ..., pN, the expectation would equal to E(X)=x1p1+x2p2+...+xNpN. To identify how far the values are spread, we can compute the variance σ2 = ∑(xi - μ)2/n, where μ is the mean of the sequence. The value σ is called **standard deviation**, and σ2 is called a **variance**. ## Mode, Median and Quartiles Sometimes, mean does not adequately represent the "typical" value for data. For example, when there are a few extreme values that are completely out of range, they can affect the mean. Another good indication is a **median**, a value such that half of data points are lower than it, and another half - higher. To help us understand the distribution of data, it is helpful to talk about **quartiles**: * First quartile, or Q1, is a value, such that 25% of the data fall below it * Third quartile, or Q3, is a value that 75% of the data fall below it Graphically we can represent relationship between median and quartiles in a diagram called the **box plot**: Here we also compute **inter-quartile range** IQR=Q3-Q1, and so-called **outliers** - values, that lie outside the boundaries [Q1-1.5*IQR,Q3+1.5*IQR]. For finite distribution that contains a small number of possible values, a good "typical" value is the one that appears the most frequently, which is called **mode**. It is often applied to categorical data, such as colors. Consider a situation when we have two groups of people - some that strongly prefer red, and others who prefer blue. If we code colors by numbers, the mean value for a favorite color would be somewhere in the orange-green spectrum, which does not indicate the actual preference on neither group. However, the mode would be either one of the colors, or both colors, if the number of people voting for them is equal (in this case we call the sample **multimodal**). ## Real-world Data When we analyze data from real life, they often are not random variables as such, in a sense that we do not perform experiments with unknown result. For example, consider a team of baseball players, and their body data, such as height, weight and age. Those numbers are not exactly random, but we can still apply the same mathematical concepts. For example, a sequence of people's weights can be considered to be a sequence of values drawn from some random variable. Below is the sequence of weights of actual baseball players from [Major League Baseball](http://mlb.mlb.com/index.jsp), taken from [this dataset](http://wiki.stat.ucla.edu/socr/index.php/SOCR_Data_MLB_HeightsWeights) (for your convenience, only first 20 values are shown): ``` [180.0, 215.0, 210.0, 210.0, 188.0, 176.0, 209.0, 200.0, 231.0, 180.0, 188.0, 180.0, 185.0, 160.0, 180.0, 185.0, 197.0, 189.0, 185.0, 219.0] ``` > **Note**: To see the example of working with this dataset, have a look at the [accompanying notebook](notebook.ipynb). There are also a number of challenges throughout this lesson, and you may complete them by adding some code to that notebook. If you are not sure how to operate on data, do not worry - we will come back to working with data using Python at a later time. If you do not know how to run code in Jupyter Notebook, have a look at [this article](https://soshnikov.com/education/how-to-execute-notebooks-from-github/). Here is the box plot showing mean, median and quartiles for our data: Since our data contains information about different player **roles**, we can also do the box plot by role - it will allow us to get the idea on how parameters values differ across roles. This time we will consider height: This diagram suggests that, on average, height of first basemen is higher that height of second basemen. Later in this lesson we will learn how we can test this hypothesis more formally, and how to demonstrate that our data is statistically significant to show that. > When working with real-world data, we assume that all data points are samples drawn from some probability distribution. This assumption allows us to apply machine learning techniques and build working predictive models. To see what the distribution of our data is, we can plot a graph called a **histogram**. X-axis would contain a number of different weight intervals (so-called **bins**), and the vertical axis would show the number of times our random variable sample was inside a given interval. From this histogram you can see that all values are centered around certain mean weight, and the further we go from that weight - the fewer weights of that value are encountered. I.e., it is very improbable that the weight of a baseball player would be very different from the mean weight. Variance of weights show the extent to which weights are likely to differ from the mean. > If we take weights of other people, not from the baseball league, the distribution is likely to be different. However, the shape of the distribution will be the same, but mean and variance would change. So, if we train our model on baseball players, it is likely to give wrong results when applied to students of a university, because the underlying distribution is different. ## Normal Distribution The distribution of weights that we have seen above is very typical, and many measurements from real world follow the same type of distribution, but with different mean and variance. This distribution is called **normal distribution**, and it plays a very important role in statistics. Using normal distribution is a correct way to generate random weights of potential baseball players. Once we know mean weight `mean` and standard deviation `std`, we can generate 1000 weight samples in the following way: ```python samples = np.random.normal(mean,std,1000) ``` If we plot the histogram of the generated samples we will see the picture very similar to the one shown above. And if we increase the number of samples and the number of bins, we can generate a picture of a normal distribution that is more close to ideal: *Normal Distribution with mean=0 and std.dev=1* ## Confidence Intervals When we talk about weights of baseball players, we assume that there is certain **random variable W** that corresponds to ideal probability distribution of weights of all baseball players (so-called **population**). Our sequence of weights corresponds to a subset of all baseball players that we call **sample**. An interesting question is, can we know the parameters of distribution of W, i.e. mean and variance of the population? The easiest answer would be to calculate mean and variance of our sample. However, it could happen that our random sample does not accurately represent complete population. Thus it makes sense to talk about **confidence interval**. > **Confidence interval** is the estimation of true mean of the population given our sample, which is accurate is a certain probability (or **level of confidence**). Suppose we have a sample X1, ..., Xn from our distribution. Each time we draw a sample from our distribution, we would end up with different mean value μ. Thus μ can be considered to be a random variable. A **confidence interval** with confidence p is a pair of values (Lp,Rp), such that **P**(Lp≤μ≤Rp) = p, i.e. a probability of measured mean value falling within the interval equals to p. It does beyond our short intro to discuss in detail how those confidence intervals are calculated. Some more details can be found [on Wikipedia](https://en.wikipedia.org/wiki/Confidence_interval). In short, we define the distribution of computed sample mean relative to the true mean of the population, which is called **student distribution**. > **Interesting fact**: Student distribution is named after mathematician William Sealy Gosset, who published his paper under the pseudonym "Student". He worked in the Guinness brewery, and, according to one of the versions, his employer did not want general public to know that they were using statistical tests to determine the quality of raw materials. If we want to estimate the mean μ of our population with confidence p, we need to take *(1-p)/2-th percentile* of a Student distribution A, which can either be taken from tables, or computer using some built-in functions of statistical software (eg. Python, R, etc.). Then the interval for μ would be given by X±A*D/√n, where X is the obtained mean of the sample, D is the standard deviation. > **Note**: We also omit the discussion of an important concept of [degrees of freedom](https://en.wikipedia.org/wiki/Degrees_of_freedom_(statistics)), which is important in relation to Student distribution. You can refer to more complete books on statistics to understand this concept deeper. An example of calculating confidence interval for weights and heights is given in the [accompanying notebooks](notebook.ipynb). | p | Weight mean | |-----|-----------| | 0.85 | 201.73Β±0.94 | | 0.90 | 201.73Β±1.08 | | 0.95 | 201.73Β±1.28 | Notice that the higher is the confidence probability, the wider is the confidence interval. ## Hypothesis Testing In our baseball players dataset, there are different player roles, that can be summarized below (look at the [accompanying notebook](notebook.ipynb) to see how this table can be calculated): | Role | Height | Weight | Count | |------|--------|--------|-------| | Catcher | 72.723684 | 204.328947 | 76 | | Designated_Hitter | 74.222222 | 220.888889 | 18 | | First_Baseman | 74.000000 | 213.109091 | 55 | | Outfielder | 73.010309 | 199.113402 | 194 | | Relief_Pitcher | 74.374603 | 203.517460 | 315 | | Second_Baseman | 71.362069 | 184.344828 | 58 | | Shortstop | 71.903846 | 182.923077 | 52 | | Starting_Pitcher | 74.719457 | 205.163636 | 221 | | Third_Baseman | 73.044444 | 200.955556 | 45 | We can notice that the mean heights of first basemen is higher than that of second basemen. Thus, we may be tempted to conclude that **first basemen are higher than second basemen**. > This statement is called **a hypothesis**, because we do not know whether the fact is actually true or not. However, it is not always obvious whether we can make this conclusion. From the discussion above we know that each mean has an associated confidence interval, and thus this difference can just be a statistical error. We need some more formal way to test our hypothesis. Let's compute confidence intervals separately for heights of first and second basemen: | Confidence | First Basemen | Second Basemen | |------------|---------------|----------------| | 0.85 | 73.62..74.38 | 71.04..71.69 | | 0.90 | 73.56..74.44 | 70.99..71.73 | | 0.95 | 73.47..74.53 | 70.92..71.81 | We can see that under no confidence the intervals overlap. That proves our hypothesis that first basemen are higher than second basemen. More formally, the problem we are solving is to see if **two probability distributions are the same**, or at least have the same parameters. Depending on the distribution, we need to use different tests for that. If we know that our distributions are normal, we can apply **[Student t-test](https://en.wikipedia.org/wiki/Student%27s_t-test)**. In Student t-test, we compute so-called **t-value**, which indicates the difference between means, taking into account the variance. It is demonstrated that t-value follows **student distribution**, which allows us to get the threshold value for a given confidence level **p** (this can be computed, or looked up in the numerical tables). We then compare t-value to this threshold to approve or reject the hypothesis. In Python, we can use the **SciPy** package, which includes `ttest_ind` function (in addition to many other useful statistical functions!). It computes the t-value for us, and also does the reverse lookup of confidence p-value, so that we can just look at the confidence to draw the conclusion. For example, our comparison between heights of first and second basemen give us the following results: ```python from scipy.stats import ttest_ind tval, pval = ttest_ind(df.loc[df['Role']=='First_Baseman',['Height']], df.loc[df['Role']=='Designated_Hitter',['Height']],equal_var=False) print(f"T-value = {tval[0]:.2f}\nP-value: {pval[0]}") ``` ``` T-value = 7.65 P-value: 9.137321189738925e-12 ``` In our case, p-value is very low, meaning that there is strong evidence supporting that first basemen are taller. There are also different other types of hypothesis that we might want to test, for example: * To prove that a given sample follows some distribution. In our case we have assumed that heights are normally distributed, but that needs formal statistical verification. * To prove that a mean value of a sample corresponds to some predefined value * To compare means of a number of samples (eg. what is the difference in happiness levels among different age groups) ## Law of Large Numbers and Central Limit Theorem One of the reasons why normal distribution is so important is so-called **central limit theorem**. Suppose we have a large sample of independent N values X1, ..., XN, sampled from any distribution with mean μ and variance σ2. Then, for sufficiently large N (in other words, when N→∞), the mean ΣiXi would be normally distributed, with mean μ and variance σ2/N. > Another way to interpret the central limit theorem is to say that regardless of distribution, when you compute the mean of a sum of any random variable values you end up with normal distribution. From the central limit theorem it also follows that, when N→∞, the probability of the sample mean to be equal to μ becomes 1. This is known as **the law of large numbers**. ## Covariance and Correlation One of the things Data Science does is finding relations between data. We say that two sequences **correlate** when they exhibit the similar behavior at the same time, i.e. they either rise/fall simultaneously, or one sequence rises when another one falls and vice versa. In other words, there seems to be some relation between two sequences. > Correlation does not necessarily indicate causal relationship between two sequences; sometimes both variables can depend on some external cause, or it can be purely by chance the two sequences correlate. However, strong mathematical correlation is a good indication that two variables are somehow connected. Mathematically, the main concept that shows the relation between two random variables is **covariance**, that is computed like this: Cov(X,Y) = **E**\[(X-**E**(X))(Y-**E**(Y))\]. We compute the deviation of both variables from their mean values, and then product of those deviations. If both variables deviate together, the product would always be a positive value, that would add up to positive covariance. If both variables deviate out-of-sync (i.e. one falls below average when another one rises above average), we will always get negative numbers, that will add up to negative covariance. If the deviations are not dependent, they will add up to roughly zero. The absolute value of covariance does not tell us much on how large the correlation is, because it depends on the magnitude of actual values. To normalize it, we can divide covariance by standard deviation of both variables, to get **correlation**. The good thing is that correlation is always in the range of [-1,1], where 1 indicates strong positive correlation between values, -1 - strong negative correlation, and 0 - no correlation at all (variables are independent). **Example**: We can compute correlation between weights and heights of baseball players from the dataset mentioned above: ```python print(np.corrcoef(weights,heights)) ``` As a result, we get **correlation matrix** like this one: ``` array([[1. , 0.52959196], [0.52959196, 1. ]]) ``` > Correlation matrix C can be computed for any number of input sequences S1, ..., Sn. The value of Cij is the correlation between Si and Sj, and diagonal elements are always 1 (which is also self-correlation of Si). In our case, the value 0.53 indicates that there is some correlation between weight and height of a person. We can also make the scatter plot of one value against the other to see the relationship visually: > More examples of correlation and covariance can be found in [accompanying notebook](notebook.ipynb). ## Conclusion In this section, we have learnt: * basic statistical properties of data, such as mean, variance, mode and quartiles * different distributions of random variables, including normal distribution * how to find correlation between different properties * how to use sound apparatus of math and statistics in order to prove some hypotheses, * how to compute confidence intervals for random variable given data sample While this is definitely not exhaustive list of topics that exist within probability and statistics, it should be enough to give you a good start into this course. ## πŸš€ Challenge Use the sample code in the notebook to test other hypothesis that: 1. First basemen are older than second basemen 2. First basemen are taller than third basemen 3. Shortstops are taller than second basemen ## [Post-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/7) ## Review & Self Study Probability and statistics is such a broad topic that it deserves its own course. If you are interested to go deeper into theory, you may want to continue reading some of the following books: 1. [Carlos Fernandez-Granda](https://cims.nyu.edu/~cfgranda/) from New York University has great lecture notes [Probability and Statistics for Data Science](https://cims.nyu.edu/~cfgranda/pages/stuff/probability_stats_for_DS.pdf) (available online) 1. [Peter and Andrew Bruce. Practical Statistics for Data Scientists.](https://www.oreilly.com/library/view/practical-statistics-for/9781491952955/) [[sample code in R](https://github.com/andrewgbruce/statistics-for-data-scientists)]. 1. [James D. Miller. Statistics for Data Science](https://www.packtpub.com/product/statistics-for-data-science/9781788290678) [[sample code in R](https://github.com/PacktPublishing/Statistics-for-Data-Science)] ## Assignment [Small Diabetes Study](assignment.md) ## Credits This lesson has been authored with β™₯️ by [Dmitry Soshnikov](http://soshnikov.com) --- ## File: 1-Introduction/03-defining-data/README.md # Defining Data | ](../../sketchnotes/03-DefiningData.png)| |:---:| |Defining Data - _Sketchnote by [@nitya](https://twitter.com/nitya)_ | Data is facts, information, observations and measurements that are used to make discoveries and to support informed decisions. A data point is a single unit of data with in a dataset, which is collection of data points. Datasets may come in different formats and structures, and will usually be based on its source, or where the data came from. For example, a company's monthly earnings might be in a spreadsheet but hourly heart rate data from a smartwatch may be in [JSON](https://stackoverflow.com/a/383699) format. It's common for data scientists to work with different types of data within a dataset. This lesson focuses on identifying and classifying data by its characteristics and its sources. ## [Pre-Lecture Quiz](https://ff-quizzes.netlify.app/en/ds/quiz/4) ## How Data is Described ### Raw Data Raw data is data that has come from its source in its initial state and has not been analyzed or organized. In order to make sense of what is happening with a dataset, it needs to be organized into a format that can be understood by humans as well as the technology they may use to analyze it further. The structure of a dataset describes how it's organized and can be classified at structured, unstructured and semi-structured. These types of structure will vary, depending on the source but will ultimately fit in these three categories. ### Quantitative Data Quantitative data is numerical observations within a dataset and can typically be analyzed, measured and used mathematically. Some examples of quantitative data are: a country's population, a person's height or a company's quarterly earnings. With some additional analysis, quantitative data could be used to discover seasonal trends of the Air Quality Index (AQI) or estimate the probability of rush hour traffic on a typical work day. ### Qualitative Data Qualitative data, also known as categorical data is data that cannot be measured objectively like observations of quantitative data. It's generally various formats of subjective data that captures the quality of something, such as a product or process. Sometimes, qualitative data is numerical and wouldn't be typically used mathematically, like phone numbers or timestamps. Some examples of qualitative data are: video comments, the make and model of a car or your closest friends' favorite color. Qualitative data could be used to understand which products consumers like best or identifying popular keywords in job application resumes. ### Structured Data Structured data is data that is organized into rows and columns, where each row will have the same set of columns. Columns represent a value of a particular type and will be identified with a name describing what the value represents, while rows contain the actual values. Columns will often have a specific set of rules or restrictions on the values, to ensure that the values accurately represent the column. For example imagine a spreadsheet of customers where each row must have a phone number and the phone numbers never contain alphabetical characters. There may be rules applied on the phone number column to make sure it's never empty and only contains numbers. A benefit of structured data is that it can be organized in such a way that it can be related to other structured data. However, because the data is designed to be organized in a specific way, making changes to its overall structure can take a lot of effort to do. For example, adding an email column to the customer spreadsheet that cannot be empty means you'll need figure out how you'll add these values to the existing rows of customers in the dataset. Examples of structured data: spreadsheets, relational databases, phone numbers, bank statements ### Unstructured Data Unstructured data typically cannot be categorized into rows or columns and doesn't contain a format or set of rules to follow. Because unstructured data has less restrictions on its structure it's easier to add new information in comparison to a structured dataset. If a sensor capturing data on barometric pressure every 2 minutes has received an update that now allows it to measure and record temperature, it doesn't require altering the existing data if it's unstructured. However, this may make analyzing or investigating this type of data take longer. For example, a scientist who wants to find the average temperature of the previous month from the sensors data, but discovers that the sensor recorded an "e" in some of its recorded data to note that it was broken instead of a typical number, which means the data is incomplete. Examples of unstructured data: text files, text messages, video files ### Semi-structured Semi-structured data has features that make it a combination of structured and unstructured data. It doesn't typically conform to a format of rows and columns but is organized in a way that is considered structured and may follow a fixed format or set of rules. The structure will vary between sources, such as a well defined hierarchy to something more flexible that allows for easy integration of new information. Metadata are indicators that help decide how the data is organized and stored and will have various names, based on the type of data. Some common names for metadata are tags, elements, entities and attributes. For example, a typical email message will have a subject, body and a set of recipients and can be organized by whom or when it was sent. Examples of semi-structured data: HTML, CSV files, JavaScript Object Notation (JSON) ## Sources of Data A data source is the initial location of where the data was generated, or where it "lives" and will vary based on how and when it was collected. Data generated by its user(s) are known as primary data while secondary data comes from a source that has collected data for general use. For example, a group of scientists collecting observations in a rainforest would be considered primary and if they decide to share it with other scientists it would be considered secondary to those that use it. Databases are a common source and rely on a database management system to host and maintain the data where users use commands called queries to explore the data. Files as data sources can be audio, image, and video files as well as spreadsheets like Excel. Internet sources are a common location for hosting data, where databases as well as files can be found. Application programming interfaces, also known as APIs allow programmers to create ways to share data with external users through the internet, while the process of web scraping extracts data from a web page. The [lessons in Working with Data](/2-Working-With-Data) focus on how to use various data sources. ## Conclusion In this lesson we have learned: - What data is - How data is described - How data is classified and categorized - Where data can be found ## πŸš€ Challenge Kaggle is an excellent source of open datasets. Use the [dataset search tool](https://www.kaggle.com/datasets) to find some interesting datasets and classify 3-5 datasets with this criteria: - Is the data quantitative or qualitative? - Is the data structured, unstructured, or semi-structured? ## [Post-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/5) ## Review & Self Study - This Microsoft Learn unit, titled [Identify data formats](https://learn.microsoft.com/en-us/training/modules/explore-core-data-concepts/2-data-formats?pivots=text) has a detailed breakdown of structured, semi-structured, and unstructured data. ## Assignment [Classifying Datasets](assignment.md) --- ## File: 1-Introduction/02-ethics/README.md # Introduction to Data Ethics | ](../../sketchnotes/02-Ethics.png)| |:---:| | Data Science Ethics - _Sketchnote by [@nitya](https://twitter.com/nitya)_ | --- We are all data citizens living in a datafied world. Market trends tell us that by 2022, 1-in-3 large organizations will buy and sell their data through online [Marketplaces and Exchanges](https://www.gartner.com/smarterwithgartner/gartner-top-10-trends-in-data-and-analytics-for-2020/). As **App Developers**, we'll find it easier and cheaper to integrate data-driven insights and algorithm-driven automation into daily user experiences. But as AI becomes pervasive, we'll also need to understand the potential harms caused by the [weaponization](https://www.youtube.com/watch?v=TQHs8SA1qpk) of such algorithms at scale. Trends suggest that by 2025, we will generate and consume over [180 zettabytes](https://www.statista.com/statistics/871513/worldwide-data-created/) of data. For **Data Scientists**, this explosion of information provides unprecedented access to personal and behavioral data. With it comes the power to build detailed user profiles and subtly influence decision-makingβ€”often in ways that foster an [illusion of free choice](https://www.datasciencecentral.com/the-pareto-set-and-the-paradox-of-choice/). While this can be used to nudge users toward preferred outcomes, it also raises critical questions about data privacy, autonomy, and the ethical boundaries of algorithmic influence. Data ethics are now _necessary guardrails_ for data science and engineering, helping us minimize potential harms and unintended consequences from our data-driven actions. The [Gartner Hype Cycle for AI](https://www.gartner.com/smarterwithgartner/2-megatrends-dominate-the-gartner-hype-cycle-for-artificial-intelligence-2020/) identifies relevant trends in digital ethics, responsible AI, and AI governance as key drivers for larger megatrends around _democratization_ and _industrialization_ of AI. In this lesson, we'll explore the fascinating area of data ethics - from core concepts and challenges, to case studies and applied AI concepts like governance - that help establish an ethics culture in teams and organizations that work with data and AI. ## [Pre-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/2) 🎯 ## Basic Definitions Let's start by understanding the basic terminology. The word "ethics" comes from the [Greek word "ethikos"](https://en.wikipedia.org/wiki/Ethics) (and its root "ethos") meaning _character or moral nature_. **Ethics** is about the shared values and moral principles that govern our behavior in society. Ethics is based not on laws but on widely accepted norms of what is "right vs. wrong". However, ethical considerations can influence corporate governance initiatives and government regulations that create more incentives for compliance. **Data Ethics** is a [new branch of ethics](https://royalsocietypublishing.org/doi/full/10.1098/rsta.2016.0360#sec-1) that "studies and evaluates moral problems related to _data, algorithms and corresponding practices_". Here, **"data"** focuses on actions related to generation, recording, curation, processing, dissemination, sharing, and usage, **"algorithms"** focuses on AI, agents, machine learning, and robots, and **"practices"** focuses on topics like responsible innovation, programming, hacking, and ethics codes. **Applied Ethics** is the [practical application of moral considerations](https://en.wikipedia.org/wiki/Applied_ethics). It's the process of actively investigating ethical issues in the context of _real-world actions, products and processes_, and taking corrective measures to make that these remain aligned with our defined ethical values. **Ethics Culture** is about [_operationalizing_ applied ethics](https://hbr.org/2019/05/how-to-design-an-ethical-organization) to make sure that our ethical principles and practices are adopted in a consistent and scalable manner across the entire organization. Successful ethics cultures define organization-wide ethical principles, provide meaningful incentives for compliance, and reinforce ethics norms by encouraging and amplifying desired behaviors at every level of the organization. ## Ethics Concepts In this section, we'll discuss concepts like **shared values** (principles) and **ethical challenges** (problems) for data ethics - and explore **case studies** that help you understand these concepts in real-world contexts. ### 1. Ethics Principles Every data ethics strategy begins by defining _ethical principles_ - the "shared values" that describe acceptable behaviors, and guide compliant actions, in our data & AI projects. You can define these at an individual or team level. However, most large organizations outline these in an _ethical AI_ mission statement or framework that is defined at corporate levels and enforced consistently across all teams. **Example:** Microsoft's [Responsible AI](https://www.microsoft.com/en-us/ai/responsible-ai) mission statement reads: _"We are committed to the advancement of AI-driven by ethical principles that put people first"_ - identifying 6 ethical principles in the framework below: Let's briefly explore these principles. _Transparency_ and _accountability_ are foundational values that other principles built upon - so let's begin there: * [**Accountability**](https://www.microsoft.com/en-us/ai/responsible-ai?activetab=pivot1:primaryr6) makes practitioners _responsible_ for their data & AI operations, and compliance with these ethical principles. * [**Transparency**](https://www.microsoft.com/en-us/ai/responsible-ai?activetab=pivot1:primaryr6) ensures that data and AI actions are _understandable_ (interpretable) to users, explaining the what and why behind decisions. * [**Fairness**](https://www.microsoft.com/en-us/ai/responsible-ai?activetab=pivot1%3aprimaryr6) - focuses on ensuring AI treats _all people_ fairly, addressing any systemic or implicit socio-technical biases in data and systems. * [**Reliability & Safety**](https://www.microsoft.com/en-us/ai/responsible-ai?activetab=pivot1:primaryr6) - ensures that AI behaves _consistently_ with defined values, minimizing potential harms or unintended consequences. * [**Privacy & Security**](https://www.microsoft.com/en-us/ai/responsible-ai?activetab=pivot1:primaryr6) - is about understanding data lineage, and providing _data privacy and related protections_ to users. * [**Inclusiveness**](https://www.microsoft.com/en-us/ai/responsible-ai?activetab=pivot1:primaryr6) - is about designing AI solutions with intention, adapting them to meet a _broad range of human needs_ & capabilities. > 🚨 Think about what your data ethics mission statement could be. Explore ethical AI frameworks from other organizations - here are examples from [IBM](https://www.ibm.com/cloud/learn/ai-ethics), [Google](https://ai.google/principles), and [Facebook](https://ai.facebook.com/blog/facebooks-five-pillars-of-responsible-ai/). What shared values do they have in common? How do these principles relate to the AI product or industry they operate in? ### 2. Ethics Challenges Once we have ethical principles defined, the next step is to evaluate our data and AI actions to see if they align with those shared values. Think about your actions in two categories: _data collection_ and _algorithm design_. With data collection, actions will likely involve **personal data** or personally identifiable information (PII) for identifiable living individuals. This includes [diverse items of non-personal data](https://ec.europa.eu/info/law/law-topic/data-protection/reform/what-personal-data_en) that _collectively_ identify an individual. Ethical challenges can relate to _data privacy_, _data ownership_, and related topics like _informed consent_ and _intellectual property rights_ for users. With algorithm design, actions will involve collecting & curating **datasets**, then using them to train & deploy **data models** that predict outcomes or automate decisions in real-world contexts. Ethical challenges can arise from _dataset bias_, _data quality_ issues, _unfairness_ ,and _misrepresentation_ in algorithms - including some issues that are systemic in nature. In both cases, ethics challenges highlight areas where our actions may encounter conflict with our shared values. To detect, mitigate, minimize, or eliminate, these concerns - we need to ask moral "yes/no" questions related to our actions, then take corrective actions as needed. Let's take a look at some ethical challenges and the moral questions they raise: #### 2.1 Data Ownership Data collection often involves personal data that can identify the data subjects. [Data ownership](https://permission.io/blog/data-ownership) is about _control_ and [_user rights_](https://permission.io/blog/data-ownership) related to the creation, processing ,and dissemination of data. The moral questions we need to ask are: * Who owns the data? (user or organization) * What rights do data subjects have? (ex: access, erasure, portability) * What rights do organizations have? (ex: rectify malicious user reviews) #### 2.2 Informed Consent [Informed consent](https://legaldictionary.net/informed-consent/) defines the act of users agreeing to an action (like data collection) with a _full understanding_ of relevant facts including the purpose, potential risks, and alternatives. Questions to explore here are: * Did the user (data subject) give permission for data capture and usage? * Did the user understand the purpose for which that data was captured? * Did the user understand the potential risks from their participation? #### 2.3 Intellectual Property [Intellectual property](https://en.wikipedia.org/wiki/Intellectual_property) refers to intangible creations resulting from the human initiative, that may _have economic value_ to individuals or businesses. Questions to explore here are: * Did the collected data have economic value to a user or business? * Does the **user** have intellectual property here? * Does the **organization** have intellectual property here? * If these rights exist, how are we protecting them? #### 2.4 Data Privacy [Data privacy](https://www.northeastern.edu/graduate/blog/what-is-data-privacy/) or information privacy refers to the preservation of user privacy and protection of user identity with respect to personally identifiable information. Questions to explore here are: * Is users' (personal) data secured against hacks and leaks? * Is users' data accessible only to authorized users and contexts? * Is users' anonymity preserved when data is shared or disseminated? * Can a user be de-identified from anonymized datasets? #### 2.5 Right To Be Forgotten The [Right To Be Forgotten](https://en.wikipedia.org/wiki/Right_to_be_forgotten) or [Right to Erasure](https://www.gdpreu.org/right-to-be-forgotten/) provides additional personal data protection to users. Specifically, it gives users the right to request deletion or removal of personal data from Internet searches and other locations, _under specific circumstances_ - allowing them a fresh start online without past actions being held against them. Questions to explore here are: * Does the system allow data subjects to request erasure? * Should the withdrawal of user consent trigger automated erasure? * Was data collected without consent or by unlawful means? * Are we compliant with government regulations for data privacy? #### 2.6 Dataset Bias Dataset or [Collection Bias](http://researcharticles.com/index.php/bias-in-data-collection-in-research/) is about selecting a _non-representative_ subset of data for algorithm development, creating potential unfairness in result outcomes for diverse groups. Types of bias include selection or sampling bias, volunteer bias, and instrument bias. Questions to explore here are: * Did we recruit a representative set of data subjects? * Did we test our collected or curated dataset for various biases? * Can we mitigate or remove any discovered biases? #### 2.7 Data Quality [Data Quality](https://lakefs.io/data-quality-testing/) looks at the validity of the curated dataset used to develop our algorithms, checking to see if features and records meet requirements for the level of accuracy and consistency needed for our AI purpose. Questions to explore here are: * Did we capture valid _features_ for our use case? * Was data captured _consistently_ across diverse data sources? * Is the dataset _complete_ for diverse conditions or scenarios? * Is information captured _accurately_ in reflecting reality? #### 2.8 Algorithm Fairness [Algorithm Fairness](https://towardsdatascience.com/what-is-algorithm-fairness-3182e161cf9f) checks to see if the algorithm design systematically discriminates against specific subgroups of data subjects leading to [potential harms](https://docs.microsoft.com/en-us/azure/machine-learning/concept-fairness-ml) in _allocation_ (where resources are denied or withheld from that group) and _quality of service_ (where AI is not as accurate for some subgroups as it is for others). Questions to explore here are: * Did we evaluate model accuracy for diverse subgroups and conditions? * Did we scrutinize the system for potential harms (e.g., stereotyping)? * Can we revise data or retrain models to mitigate identified harms? Explore resources like [AI Fairness checklists](https://query.prod.cms.rt.microsoft.com/cms/api/am/binary/RE4t6dA) to learn more. #### 2.9 Misrepresentation [Data Misrepresentation](https://www.sciencedirect.com/topics/computer-science/misrepresentation) is about asking whether we are communicating insights from honestly reported data in a deceptive manner to support a desired narrative. Questions to explore here are: * Are we reporting incomplete or inaccurate data? * Are we visualizing data in a manner that drives misleading conclusions? * Are we using selective statistical techniques to manipulate outcomes? * Are there alternative explanations that may offer a different conclusion? #### 2.10 Free Choice The [Illusion of Free Choice](https://www.datasciencecentral.com/profiles/blogs/the-illusion-of-choice) occurs when system "choice architectures" use decision-making algorithms to nudge people towards taking a preferred outcome while seeming to give them options and control. These [dark patterns](https://www.darkpatterns.org/) can cause social and economic harm to users. Because user decisions impact behavior profiles, these actions potentially drive future choices that can amplify or extend the impact of these harms. Questions to explore here are: * Did the user understand the implications of making that choice? * Was the user aware of (alternative) choices and the pros & cons of each? * Can the user reverse an automated or influenced choice later? ### 3. Case Studies To put these ethical challenges in real-world contexts, it helps to look at case studies that highlight the potential harms and consequences to individuals and society, when such ethics violations are overlooked. Here are a few examples: | Ethics Challenge | Case Study | |--- |--- | | **Informed Consent** | 1972 - [Tuskegee Syphilis Study](https://en.wikipedia.org/wiki/Tuskegee_Syphilis_Study) - African American men who participated in the study were promised free medical care _but deceived_ by researchers who failed to inform subjects of their diagnosis or about availability of treatment. Many subjects died & partners or children were affected; the study lasted 40 years. | | **Data Privacy** | 2007 - The [Netflix data prize](https://www.wired.com/2007/12/why-anonymous-data-sometimes-isnt/) provided researchers with _10M anonymized movie rankings from 50K customers_ to help improve recommendation algorithms. However, researchers were able to correlate anonymized data with personally-identifiable data in _external datasets_ (e.g., IMDb comments) - effectively "de-anonymizing" some Netflix subscribers.| | **Collection Bias** | 2013 - The City of Boston [developed Street Bump](https://www.boston.gov/transportation/street-bump), an app that let citizens report potholes, giving the city better roadway data to find and fix issues. However, [people in lower income groups had less access to cars and phones](https://hbr.org/2013/04/the-hidden-biases-in-big-data), making their roadway issues invisible in this app. Developers worked with academics to _equitable access and digital divides_ issues for fairness. | | **Algorithmic Fairness** | 2018 - The MIT [Gender Shades Study](http://gendershades.org/overview.html) evaluated the accuracy of gender classification AI products, exposing gaps in accuracy for women and persons of color. A [2019 Apple Card](https://www.wired.com/story/the-apple-card-didnt-see-genderand-thats-the-problem/) seemed to offer less credit to women than men. Both illustrated issues in algorithmic bias leading to socio-economic harms.| | **Data Misrepresentation** | 2020 - The [Georgia Department of Public Health released COVID-19 charts](https://www.vox.com/covid-19-coronavirus-us-response-trump/2020/5/18/21262265/georgia-covid-19-cases-declining-reopening) that appeared to mislead citizens about trends in confirmed cases with non-chronological ordering on the x-axis. This illustrates misrepresentation through visualization tricks. | | **Illusion of free choice** | 2020 - Learning app [ABCmouse paid $10M to settle an FTC complaint](https://www.washingtonpost.com/business/2020/09/04/abcmouse-10-million-ftc-settlement/) where parents were trapped into paying for subscriptions they couldn't cancel. This illustrates dark patterns in choice architectures, where users were nudged towards potentially harmful choices. | | **Data Privacy & User Rights** | 2021 - Facebook [Data Breach](https://www.npr.org/2021/04/09/986005820/after-data-breach-exposes-530-million-facebook-says-it-will-not-notify-users) exposed data from 530M users, resulting in a $5B settlement to the FTC. It however refused to notify users of the breach violating user rights around data transparency and access. | Want to explore more case studies? Check out these resources: * [Ethics Unwrapped](https://ethicsunwrapped.utexas.edu/case-studies) - ethics dilemmas across diverse industries. * [Data Science Ethics course](https://www.coursera.org/learn/data-science-ethics#syllabus) - landmark case studies explored. * [Where things have gone wrong](https://deon.drivendata.org/examples/) - deon checklist with examples > 🚨 Think about the case studies you've seen - have you experienced, or been affected by, a similar ethical challenge in your life? Can you think of at least one other case study that illustrates one of the ethical challenges we've discussed in this section? ## Applied Ethics We've talked about ethics concepts, challenges ,and case studies in real-world contexts. But how do we get started _applying_ ethical principles and practices in our projects? And how do we _operationalize_ these practices for better governance? Let's explore some real-world solutions: ### 1. Professional Codes Professional Codes offer one option for organizations to "incentivize" members to support their ethical principles and mission statement. Codes are _moral guidelines_ for professional behavior, helping employees or members make decisions that align with their organization's principles. They are only as good as the voluntary compliance from members; however, many organizations offer additional rewards and penalties to motivate compliance from members. Examples include: * [Oxford Munich](http://www.code-of-ethics.org/code-of-conduct/) Code of Ethics * [Data Science Association](http://datascienceassn.org/code-of-conduct.html) Code of Conduct (created 2013) * [ACM Code of Ethics and Professional Conduct](https://www.acm.org/code-of-ethics) (since 1993) > 🚨 Do you belong to a professional engineering or data science organization? Explore their site to see if they define a professional code of ethics. What does this say about their ethical principles? How are they "incentivizing" members to follow the code? ### 2. Ethics Checklists While professional codes define required _ethical behavior_ from practitioners, they [have known limitations](https://resources.oreilly.com/examples/0636920203964/blob/master/of_oaths_and_checklists.md) in enforcement, particularly in large-scale projects. Instead, many data Science experts [advocate for checklists](https://resources.oreilly.com/examples/0636920203964/blob/master/of_oaths_and_checklists.md), that can **connect principles to practices** in more deterministic and actionable ways. Checklists convert questions into "yes/no" tasks that can be operationalized, allowing them to be tracked as part of standard product release workflows. Examples include: * [Deon](https://deon.drivendata.org/) - a general-purpose data ethics checklist created from [industry recommendations](https://deon.drivendata.org/#checklist-citations) with a command-line tool for easy integration. * [Privacy Audit Checklist](https://cyber.harvard.edu/ecommerce/privacyaudit.html) - provides general guidance for information handling practices from legal and social exposure perspectives. * [AI Fairness Checklist](https://www.microsoft.com/en-us/research/project/ai-fairness-checklist/) - created by AI practitioners to support the adoption and integration of fairness checks into AI development cycles. * [22 questions for ethics in data and AI](https://medium.com/the-organization/22-questions-for-ethics-in-data-and-ai-efb68fd19429) - more open-ended framework, structured for initial exploration of ethical issues in design, implementation, and organizational, contexts. ### 3. Ethics Regulations Ethics is about defining shared values and doing the right thing _voluntarily_. **Compliance** is about _following the law_ if and where defined. **Governance** broadly covers all the ways in which organizations operate to enforce ethical principles and comply with established laws. Today, governance takes two forms within organizations. First, it's about defining **ethical AI** principles and establishing practices to operationalize adoption across all AI-related projects in the organization. Second, it's about complying with all government-mandated **data protection regulations** for regions it operates in. Examples of data protection and privacy regulations: * `1974`, [US Privacy Act](https://www.justice.gov/opcl/privacy-act-1974) - regulates _federal govt._ collection, use ,and disclosure of personal information. * `1996`, [US Health Insurance Portability & Accountability Act (HIPAA)](https://www.cdc.gov/phlp/publications/topic/hipaa.html) - protects personal health data. * `1998`, [US Children's Online Privacy Protection Act (COPPA)](https://www.ftc.gov/enforcement/rules/rulemaking-regulatory-reform-proceedings/childrens-online-privacy-protection-rule) - protects data privacy of children under 13. * `2018`, [General Data Protection Regulation (GDPR)](https://gdpr-info.eu/) - provides user rights, data protection ,and privacy. * `2018`, [California Consumer Privacy Act (CCPA)](https://www.oag.ca.gov/privacy/ccpa) gives consumers more _rights_ over their (personal) data. * `2021`, China's [Personal Information Protection Law](https://www.reuters.com/world/china/china-passes-new-personal-data-privacy-law-take-effect-nov-1-2021-08-20/) just passed, creating one of the strongest online data privacy regulations worldwide. > 🚨 The European Union defined GDPR (General Data Protection Regulation) remains one of the most influential data privacy regulations today. Did you know it also defines [8 user rights](https://www.freeprivacypolicy.com/blog/8-user-rights-gdpr) to protect citizens' digital privacy and personal data? Learn about what these are, and why they matter. ### 4. Ethics Culture Note that there remains an intangible gap between _compliance_ (doing enough to meet "the letter of the law") and addressing [systemic issues](https://www.coursera.org/learn/data-science-ethics/home/week/4) (like ossification, information asymmetry, and distributional unfairness) that can speed up the weaponization of AI. The latter requires [collaborative approaches to defining ethics cultures](https://towardsdatascience.com/why-ai-ethics-requires-a-culture-driven-approach-26f451afa29f) that build emotional connections and consistent shared values _across organizations_ in the industry. This calls for more [formalized data ethics cultures](https://www.codeforamerica.org/news/formalizing-an-ethical-data-culture/) in organizations - allowing _anyone_ to [pull the Andon cord](https://en.wikipedia.org/wiki/Andon_(manufacturing)) (to raise ethics concerns early in the process) and making _ethical assessments_ (e.g., in hiring) a core criteria team formation in AI projects. --- ## [Post-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/3) 🎯 ## Review & Self Study Courses and books help with understanding core ethics concepts and challenges, while case studies and tools help with applied ethics practices in real-world contexts. Here are a few resources to start with. * [Machine Learning For Beginners](https://github.com/microsoft/ML-For-Beginners/blob/main/1-Introduction/3-fairness/README.md) - lesson on Fairness, from Microsoft. * [Principles of Responsible AI](https://docs.microsoft.com/en-us/learn/modules/responsible-ai-principles/) - free learning path from Microsoft Learn. * [Ethics and Data Science](https://resources.oreilly.com/examples/0636920203964) - O'Reilly EBook (M. Loukides, H. Mason et. al) * [Data Science Ethics](https://www.coursera.org/learn/data-science-ethics#syllabus) - online course from the University of Michigan. * [Ethics Unwrapped](https://ethicsunwrapped.utexas.edu/case-studies) - case studies from the University of Texas. # Assignment [Write A Data Ethics Case Study](assignment.md) --- ## File: 1-Introduction/01-defining-data-science/README.md # Defining Data Science | ](../../sketchnotes/01-Definitions.png) | | :----------------------------------------------------------------------------------------------------: | | Defining Data Science - _Sketchnote by [@nitya](https://twitter.com/nitya)_ | --- [](https://youtu.be/beZ7Mb_oz9I) ## [Pre-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/0) ## What is Data? In our everyday life, we are constantly surrounded by data. The text you are reading now is data. The list of phone numbers of your friends in your smartphone is data, as well as the current time displayed on your watch. As human beings, we naturally operate with data by counting the money we have or by writing letters to our friends. However, data became much more critical with the creation of computers. The primary role of computers is to perform computations, but they need data to operate on. Thus, we need to understand how computers store and process data. With the emergence of the Internet, the role of computers as data handling devices increased. If you think about it, we now use computers more and more for data processing and communication, rather than actual computations. When we write an e-mail to a friend or search for some information on the Internet - we are essentially creating, storing, transmitting, and manipulating data. > Can you remember the last time you have used computers to actually compute something? ## What is Data Science? In [Wikipedia](https://en.wikipedia.org/wiki/Data_science), **Data Science** is defined as *a scientific field that uses scientific methods to extract knowledge and insights from structured and unstructured data, and apply knowledge and actionable insights from data across a broad range of application domains*. This definition highlights the following important aspects of data science: * The main goal of data science is to **extract knowledge** from data, in other words - to **understand** data, find some hidden relationships and build a **model**. * Data science uses **scientific methods**, such as probability and statistics. In fact, when the term *data science* was first introduced, some people argued that data science was just a new fancy name for statistics. Nowadays it has become evident that the field is much broader. * Obtained knowledge should be applied to produce some **actionable insights**, i.e. practical insights that you can apply to real business situations. * We should be able to operate on both **structured** and **unstructured** data. We will come back to discuss different types of data later in the course. * **Application domain** is an important concept, and data scientists often need at least some degree of expertise in the problem domain, for example: finance, medicine, marketing, etc. > Another important aspect of Data Science is that it studies how data can be gathered, stored and operated upon using computers. While statistics gives us mathematical foundations, data science applies mathematical concepts to actually draw insights from data. One of the ways (attributed to [Jim Gray](https://en.wikipedia.org/wiki/Jim_Gray_(computer_scientist))) to look at the data science is to consider it to be a separate paradigm of science: * **Empirical**, in which we rely mostly on observations and results of experiments * **Theoretical**, where new concepts emerge from existing scientific knowledge * **Computational**, where we discover new principles based on some computational experiments * **Data-Driven**, based on discovering relationships and patterns in the data ## Other Related Fields Since data is pervasive, data science itself is also a broad field, touching many other disciplines.
Databases
A critical consideration is **how to store** the data, i.e. how to structure it in a way that allows faster processing. There are different types of databases that store structured and unstructured data, which [we will consider in our course](../../2-Working-With-Data/README.md).
Big Data
Often we need to store and process very large quantities of data with a relatively simple structure. There are special approaches and tools to store that data in a distributed manner on a computer cluster, and process it efficiently.
Machine Learning
One way to understand data is to **build a model** that will be able to predict a desired outcome. Developing models from data is called **machine learning**. You may want to have a look at our [Machine Learning for Beginners](https://aka.ms/ml-beginners) Curriculum to learn more about it.
Artificial Intelligence
An area of machine learning known as artificial intelligence (AI) also relies on data, and it involves building high complexity models that mimic human thought processes. AI methods often allow us to turn unstructured data (e.g. natural language) into structured insights.
Visualization
Vast amounts of data are incomprehensible for a human being, but once we create useful visualizations using that data, we can make more sense of the data, and draw some conclusions. Thus, it is important to know many ways to visualize information - something that we will cover in [Section 3](../../3-Data-Visualization/README.md) of our course. Related fields also include **Infographics**, and **Human-Computer Interaction** in general.
## Types of Data As we have already mentioned, data is everywhere. We just need to capture it in the right way! It is useful to distinguish between **structured** and **unstructured** data. The former is typically represented in some well-structured form, often as a table or number of tables, while the latter is just a collection of files. Sometimes we can also talk about **semi-structured** data, that have some sort of a structure that may vary greatly. | Structured | Semi-structured | Unstructured | | ---------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------- | --------------------------------------- | | List of people with their phone numbers | Wikipedia pages with links | Text of Encyclopedia Britannica | | Temperature in all rooms of a building at every minute for the last 20 years | Collection of scientific papers in JSON format with authors, data of publication, and abstract | File share with corporate documents | | Data for age and gender of all people entering the building | Internet pages | Raw video feed from surveillance camera | ## Where to get Data There are many possible sources of data, and it will be impossible to list all of them! However, let's mention some of the typical places where you can get data: * **Structured** - **Internet of Things** (IoT), including data from different sensors, such as temperature or pressure sensors, provides a lot of useful data. For example, if an office building is equipped with IoT sensors, we can automatically control heating and lighting in order to minimize costs. - **Surveys** that we ask users to complete after a purchase, or after visiting a web site. - **Analysis of behavior** can, for example, help us understand how deeply a user goes into a site, and what is the typical reason for leaving the site. * **Unstructured** - **Texts** can be a rich source of insights, such as an overall **sentiment score**, or extracting keywords and semantic meaning. - **Images** or **Video**. A video from a surveillance camera can be used to estimate traffic on the road, and inform people about potential traffic jams. - Web server **Logs** can be used to understand which pages of our site are most often visited, and for how long. * Semi-structured - **Social Network** graphs can be great sources of data about user personalities and potential effectiveness in spreading information around. - When we have a bunch of photographs from a party, we can try to extract **Group Dynamics** data by building a graph of people taking pictures with each other. By knowing different possible sources of data, you can try to think about different scenarios where data science techniques can be applied to know the situation better, and to improve business processes. ## What you can do with Data In Data Science, we focus on the following steps of data journey:
1) Data Acquisition
The first step is to collect the data. While in many cases it can be a straightforward process, like data coming to a database from a web application, sometimes we need to use special techniques. For example, data from IoT sensors can be overwhelming, and it is a good practice to use buffering endpoints such as IoT Hub to collect all the data before further processing.
2) Data Storage
Storing data can be challenging, especially if we are talking about big data. When deciding how to store data, it makes sense to anticipate the way you would like to query the data in the future. There are several ways data can be stored:
  • A relational database stores a collection of tables, and uses a special language called SQL to query them. Typically, tables are organized into different groups called schemas. In many cases we need to convert the data from original form to fit the schema.
  • [A NoSQL](https://en.wikipedia.org/wiki/NoSQL) database, such as [CosmosDB](https://azure.microsoft.com/services/cosmos-db/?WT.mc_id=academic-77958-bethanycheum), does not enforce schemas on data, and allows storing more complex data, for example, hierarchical JSON documents or graphs. However, NoSQL databases do not have the rich querying capabilities of SQL, and cannot enforce referential integrity, i.e. rules on how the data is structured in tables and governing the relationships between tables.
  • [Data Lake](https://en.wikipedia.org/wiki/Data_lake) storage is used for large collections of data in raw, unstructured form. Data lakes are often used with big data, where all data cannot fit on one machine, and has to be stored and processed by a cluster of servers. [Parquet](https://en.wikipedia.org/wiki/Apache_Parquet) is the data format that is often used in conjunction with big data.
    • 3) Data Processing
    This is the most exciting part of the data journey, which involves converting the data from its original form into a form that can be used for visualization/model training. When dealing with unstructured data such as text or images, we may need to use some AI techniques to extract **features** from the data, thus converting it to structured form.
    4) Visualization / Human Insights
    Oftentimes, in order to understand the data, we need to visualize it. Having many different visualization techniques in our toolbox, we can find the right view to make an insight. Often, a data scientist needs to "play with data", visualizing it many times and looking for some relationships. Also, we may use statistical techniques to test a hypotheses or prove a correlation between different pieces of data.
    5) Training a predictive model
    Because the ultimate goal of data science is to be able to make decisions based on data, we may want to use the techniques of [Machine Learning](http://github.com/microsoft/ml-for-beginners) to build a predictive model. We can then use this to make predictions using new data sets with similar structures.
    Of course, depending on the actual data, some steps might be missing (e.g., when we already have the data in the database, or when we do not need model training), or some steps might be repeated several times (such as data processing). ## Digitalization and Digital Transformation In the last decade, many businesses started to understand the importance of data when making business decisions. To apply data science principles to running a business, one first needs to collect some data, i.e. translate business processes into digital form. This is known as **digitalization**. Applying data science techniques to this data to guide decisions can lead to significant increases in productivity (or even business pivot), called **digital transformation**. Let's consider an example. Suppose we have a data science course (like this one) which we deliver online to students, and we want to use data science to improve it. How can we do it? We can start by asking "What can be digitized?" The simplest way would be to measure the time it takes each student to complete each module, and to measure the obtained knowledge by giving a multiple-choice test at the end of each module. By averaging time-to-complete across all students, we can find out which modules cause the most difficulties for students, and work on simplifying them. > You may argue that this approach is not ideal, because modules can be of different lengths. It is probably more fair to divide the time by the length of the module (in number of characters), and compare those values instead. When we start analyzing results of multiple-choice tests, we can try to determine which concepts that students have difficulty understanding, and use that information to improve the content. To do that, we need to design tests in such a way that each question maps to a certain concept or chunk of knowledge. If we want to get even more complicated, we can plot the time taken for each module against the age category of students. We might find out that for some age categories it takes an inappropriately long time to complete the module, or that students drop out before completing it. This can help us provide age recommendations for the module, and minimize people's dissatisfaction from wrong expectations. ## πŸš€ Challenge In this challenge, we will try to find concepts relevant to the field of Data Science by looking at texts. We will take a Wikipedia article on Data Science, download and process the text, and then build a word cloud like this one: Visit [`notebook.ipynb`](./notebook.ipynb ':ignore') to read through the code. You can also run the code, and see how it performs all data transformations in real time. > If you do not know how to run code in a Jupyter Notebook, have a look at [this article](https://soshnikov.com/education/how-to-execute-notebooks-from-github/). ## [Post-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/1) ## Assignments * **Task 1**: Modify the code above to find out related concepts for the fields of **Big Data** and **Machine Learning** * **Task 2**: [Think About Data Science Scenarios](assignment.md) ## Credits This lesson has been authored with β™₯️ by [Dmitry Soshnikov](http://soshnikov.com) --- ## File: 1-Introduction/README.md # Introduction to Data Science > Photo by [Stephen Dawson](https://unsplash.com/@dawson2406?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText) on [Unsplash](https://unsplash.com/s/photos/data?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText) In these lessons, you will discover how Data Science is defined and learn about ethical considerations that must be considered by a data scientist. You will also learn how data is defined and learn a bit about statistics and probability, the core academic domains of Data Science. ### Topics 1. [Defining Data Science](01-defining-data-science/README.md) 2. [Data Science Ethics](02-ethics/README.md) 3. [Defining Data](03-defining-data/README.md) 4. [Introduction to Statistics and Probability](04-stats-and-probability/README.md) ### Credits These lessons were written with ❀️ by [Nitya Narasimhan](https://twitter.com/nitya) and [Dmitry Soshnikov](https://twitter.com/shwars). --- ## File: 2-Working-With-Data/08-data-preparation/README.md # Working with Data: Data Preparation | ](../../sketchnotes/08-DataPreparation.png)| |:---:| |Data Preparation - _Sketchnote by [@nitya](https://twitter.com/nitya)_ | ## [Pre-Lecture Quiz](https://ff-quizzes.netlify.app/en/ds/quiz/14) Depending on its source, raw data may contain some inconsistencies that will cause challenges in analysis and modeling. In other words, this data can be categorized as β€œdirty” and will need to be cleaned up. This lesson focuses on techniques for cleaning and transforming the data to handle challenges of missing, inaccurate, or incomplete data. Topics covered in this lesson will utilize Python and the Pandas library and will be [demonstrated in the notebook](notebook.ipynb) within this directory. ## The importance of cleaning data - **Ease of use and reuse**: When data is properly organized and normalized it’s easier to search, use, and share with others. - **Consistency**: Data science often requires working with more than one dataset, where datasets from different sources need to be joined together. Making sure that each individual data set has common standardization will ensure that the data is still useful when they are all merged into one dataset. - **Model accuracy**: Data that has been cleaned improves the accuracy of models that rely on it. ## Common cleaning goals and strategies - **Exploring a dataset**: Data exploration, which is covered in a [later lesson](https://github.com/microsoft/Data-Science-For-Beginners/tree/main/4-Data-Science-Lifecycle/15-analyzing) can help you discover data that needs to be cleaned up. Visually observing values within a dataset can set expectations of what that rest of it will look like, or provide an idea of the problems that can be resolved. Exploration can involve basic querying, visualizations, and sampling. - **Formatting**: Depending on the source, data can have inconsistencies in how it’s presented. This can cause problems in searching for and representing the value, where it’s seen within the dataset but is not properly represented in visualizations or query results. Common formatting problems involve resolving whitespace, dates, and data types. Resolving formatting issues is typically up to the people who are using the data. For example, standards on how dates and numbers are presented can differ by country. - **Duplications**: Data that has more than one occurrence can produce inaccurate results and usually should be removed. This can be a common occurrence when joining two or more datasets together. However, there are instances where duplication in joined datasets contain pieces that can provide additional information and may need to be preserved. - **Missing Data**: Missing data can cause inaccuracies as well as weak or biased results. Sometimes these can be resolved by a "reload" of the data, filling in the missing values with computation and code like Python, or simply just removing the value and corresponding data. There are numerous reasons for why data may be missing and the actions that are taken to resolve these missing values can be dependent on how and why they went missing in the first place. ## Exploring DataFrame information > **Learning goal:** By the end of this subsection, you should be comfortable finding general information about the data stored in pandas DataFrames. Once you have loaded your data into pandas, it will more likely than not be in a DataFrame(refer to the previous [lesson](https://github.com/microsoft/Data-Science-For-Beginners/tree/main/2-Working-With-Data/07-python#dataframe) for detailed overview). However, if the data set in your DataFrame has 60,000 rows and 400 columns, how do you even begin to get a sense of what you're working with? Fortunately, [pandas](https://pandas.pydata.org/) provides some convenient tools to quickly look at overall information about a DataFrame in addition to the first few and last few rows. In order to explore this functionality, we will import the Python scikit-learn library and use an iconic dataset: the **Iris data set**. ```python import pandas as pd from sklearn.datasets import load_iris iris = load_iris() iris_df = pd.DataFrame(data=iris['data'], columns=iris['feature_names']) ``` | |sepal length (cm)|sepal width (cm)|petal length (cm)|petal width (cm)| |----------------------------------------|-----------------|----------------|-----------------|----------------| |0 |5.1 |3.5 |1.4 |0.2 | |1 |4.9 |3.0 |1.4 |0.2 | |2 |4.7 |3.2 |1.3 |0.2 | |3 |4.6 |3.1 |1.5 |0.2 | |4 |5.0 |3.6 |1.4 |0.2 | - **DataFrame.info**: To start off, the `info()` method is used to print a summary of the content present in a `DataFrame`. Let's take a look at this dataset to see what we have: ```python iris_df.info() ``` ``` RangeIndex: 150 entries, 0 to 149 Data columns (total 4 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 sepal length (cm) 150 non-null float64 1 sepal width (cm) 150 non-null float64 2 petal length (cm) 150 non-null float64 3 petal width (cm) 150 non-null float64 dtypes: float64(4) memory usage: 4.8 KB ``` From this, we know that the *Iris* dataset has 150 entries in four columns with no null entries. All of the data is stored as 64-bit floating-point numbers. - **DataFrame.head()**: Next, to check the actual content of the `DataFrame`, we use the `head()` method. Let's see what the first few rows of our `iris_df` look like: ```python iris_df.head() ``` ``` sepal length (cm) sepal width (cm) petal length (cm) petal width (cm) 0 5.1 3.5 1.4 0.2 1 4.9 3.0 1.4 0.2 2 4.7 3.2 1.3 0.2 3 4.6 3.1 1.5 0.2 4 5.0 3.6 1.4 0.2 ``` - **DataFrame.tail()**: Conversely, to check the last few rows of the `DataFrame`, we use the `tail()` method: ```python iris_df.tail() ``` ``` sepal length (cm) sepal width (cm) petal length (cm) petal width (cm) 145 6.7 3.0 5.2 2.3 146 6.3 2.5 5.0 1.9 147 6.5 3.0 5.2 2.0 148 6.2 3.4 5.4 2.3 149 5.9 3.0 5.1 1.8 ``` > **Takeaway:** Even just by looking at the metadata about the information in a DataFrame or the first and last few values in one, you can get an immediate idea about the size, shape, and content of the data you are dealing with. ## Dealing with Missing Data > **Learning goal:** By the end of this subsection, you should know how to replace or remove null values from DataFrames. Most of the time the datasets you want to use (of have to use) have missing values in them. How missing data is handled carries with it subtle tradeoffs that can affect your final analysis and real-world outcomes. Pandas handles missing values in two ways. The first you've seen before in previous sections: `NaN`, or Not a Number. This is a actually a special value that is part of the IEEE floating-point specification and it is only used to indicate missing floating-point values. For missing values apart from floats, pandas uses the Python `None` object. While it might seem confusing that you will encounter two different kinds of values that say essentially the same thing, there are sound programmatic reasons for this design choice and, in practice, going this route enables pandas to deliver a good compromise for the vast majority of cases. Notwithstanding this, both `None` and `NaN` carry restrictions that you need to be mindful of with regards to how they can be used. Check out more about `NaN` and `None` from the [notebook](https://github.com/microsoft/Data-Science-For-Beginners/blob/main/4-Data-Science-Lifecycle/15-analyzing/notebook.ipynb)! - **Detecting null values**: In `pandas`, the `isnull()` and `notnull()` methods are your primary methods for detecting null data. Both return Boolean masks over your data. We will be using `numpy` for `NaN` values: ```python import numpy as np example1 = pd.Series([0, np.nan, '', None]) example1.isnull() ``` ``` 0 False 1 True 2 False 3 True dtype: bool ``` Look closely at the output. Does any of it surprise you? While `0` is an arithmetic null, it's nevertheless a perfectly good integer and pandas treats it as such. `''` is a little more subtle. While we used it in Section 1 to represent an empty string value, it is nevertheless a string object and not a representation of null as far as pandas is concerned. Now, let's turn this around and use these methods in a manner more like you will use them in practice. You can use Boolean masks directly as a ``Series`` or ``DataFrame`` index, which can be useful when trying to work with isolated missing (or present) values. > **Takeaway**: Both the `isnull()` and `notnull()` methods produce similar results when you use them in `DataFrame`s: they show the results and the index of those results, which will help you enormously as you wrestle with your data. - **Dropping null values**: Beyond identifying missing values, pandas provides a convenient means to remove null values from `Series` and `DataFrame`s. (Particularly on large data sets, it is often more advisable to simply remove missing [NA] values from your analysis than deal with them in other ways.) To see this in action, let's return to `example1`: ```python example1 = example1.dropna() example1 ``` ``` 0 0 2 dtype: object ``` Note that this should look like your output from `example3[example3.notnull()]`. The difference here is that, rather than just indexing on the masked values, `dropna` has removed those missing values from the `Series` `example1`. Because `DataFrame`s have two dimensions, they afford more options for dropping data. ```python example2 = pd.DataFrame([[1, np.nan, 7], [2, 5, 8], [np.nan, 6, 9]]) example2 ``` | | 0 | 1 | 2 | |------|---|---|---| |0 |1.0|NaN|7 | |1 |2.0|5.0|8 | |2 |NaN|6.0|9 | (Did you notice that pandas upcast two of the columns to floats to accommodate the `NaN`s?) You cannot drop a single value from a `DataFrame`, so you have to drop full rows or columns. Depending on what you are doing, you might want to do one or the other, and so pandas gives you options for both. Because in data science, columns generally represent variables and rows represent observations, you are more likely to drop rows of data; the default setting for `dropna()` is to drop all rows that contain any null values: ```python example2.dropna() ``` ``` 0 1 2 1 2.0 5.0 8 ``` If necessary, you can drop NA values from columns. Use `axis=1` to do so: ```python example2.dropna(axis='columns') ``` ``` 2 0 7 1 8 2 9 ``` Notice that this can drop a lot of data that you might want to keep, particularly in smaller datasets. What if you just want to drop rows or columns that contain several or even just all null values? You specify those setting in `dropna` with the `how` and `thresh` parameters. By default, `how='any'` (if you would like to check for yourself or see what other parameters the method has, run `example4.dropna?` in a code cell). You could alternatively specify `how='all'` so as to drop only rows or columns that contain all null values. Let's expand our example `DataFrame` to see this in action. ```python example2[3] = np.nan example2 ``` | |0 |1 |2 |3 | |------|---|---|---|---| |0 |1.0|NaN|7 |NaN| |1 |2.0|5.0|8 |NaN| |2 |NaN|6.0|9 |NaN| The `thresh` parameter gives you finer-grained control: you set the number of *non-null* values that a row or column needs to have in order to be kept: ```python example2.dropna(axis='rows', thresh=3) ``` ``` 0 1 2 3 1 2.0 5.0 8 NaN ``` Here, the first and last row have been dropped, because they contain only two non-null values. - **Filling null values**: Depending on your dataset, it can sometimes make more sense to fill null values with valid ones rather than drop them. You could use `isnull` to do this in place, but that can be laborious, particularly if you have a lot of values to fill. Because this is such a common task in data science, pandas provides `fillna`, which returns a copy of the `Series` or `DataFrame` with the missing values replaced with one of your choosing. Let's create another example `Series` to see how this works in practice. ```python example3 = pd.Series([1, np.nan, 2, None, 3], index=list('abcde')) example3 ``` ``` a 1.0 b NaN c 2.0 d NaN e 3.0 dtype: float64 ``` You can fill all of the null entries with a single value, such as `0`: ```python example3.fillna(0) ``` ``` a 1.0 b 0.0 c 2.0 d 0.0 e 3.0 dtype: float64 ``` You can **forward-fill** null values, which is to use the last valid value to fill a null: ```python example3.fillna(method='ffill') ``` ``` a 1.0 b 1.0 c 2.0 d 2.0 e 3.0 dtype: float64 ``` You can also **back-fill** to propagate the next valid value backward to fill a null: ```python example3.fillna(method='bfill') ``` ``` a 1.0 b 2.0 c 2.0 d 3.0 e 3.0 dtype: float64 ``` As you might guess, this works the same with `DataFrame`s, but you can also specify an `axis` along which to fill null values. taking the previously used `example2` again: ```python example2.fillna(method='ffill', axis=1) ``` ``` 0 1 2 3 0 1.0 1.0 7.0 7.0 1 2.0 5.0 8.0 8.0 2 NaN 6.0 9.0 9.0 ``` Notice that when a previous value is not available for forward-filling, the null value remains. > **Takeaway:** There are multiple ways to deal with missing values in your datasets. The specific strategy you use (removing them, replacing them, or even how you replace them) should be dictated by the particulars of that data. You will develop a better sense of how to deal with missing values the more you handle and interact with datasets. ## Removing duplicate data > **Learning goal:** By the end of this subsection, you should be comfortable identifying and removing duplicate values from DataFrames. In addition to missing data, you will often encounter duplicated data in real-world datasets. Fortunately, `pandas` provides an easy means of detecting and removing duplicate entries. - **Identifying duplicates: `duplicated`**: You can easily spot duplicate values using the `duplicated` method in pandas, which returns a Boolean mask indicating whether an entry in a `DataFrame` is a duplicate of an earlier one. Let's create another example `DataFrame` to see this in action. ```python example4 = pd.DataFrame({'letters': ['A','B'] * 2 + ['B'], 'numbers': [1, 2, 1, 3, 3]}) example4 ``` | |letters|numbers| |------|-------|-------| |0 |A |1 | |1 |B |2 | |2 |A |1 | |3 |B |3 | |4 |B |3 | ```python example4.duplicated() ``` ``` 0 False 1 False 2 True 3 False 4 True dtype: bool ``` - **Dropping duplicates: `drop_duplicates`:** simply returns a copy of the data for which all of the `duplicated` values are `False`: ```python example4.drop_duplicates() ``` ``` letters numbers 0 A 1 1 B 2 3 B 3 ``` Both `duplicated` and `drop_duplicates` default to consider all columns but you can specify that they examine only a subset of columns in your `DataFrame`: ```python example4.drop_duplicates(['letters']) ``` ``` letters numbers 0 A 1 1 B 2 ``` > **Takeaway:** Removing duplicate data is an essential part of almost every data-science project. Duplicate data can change the results of your analyses and give you inaccurate results! ## πŸš€ Challenge All of the discussed materials are provided as a [Jupyter Notebook](https://github.com/microsoft/Data-Science-For-Beginners/blob/main/2-Working-With-Data/08-data-preparation/notebook.ipynb). Additionally, there are exercises present after each section, give them a try! ## [Post-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/15) ## Review & Self Study There are many ways to discover and approach preparing your data for analysis and modeling and cleaning the data is an important step that is a "hands on" experience. Try these challenges from Kaggle to explore techniques that this lesson didn't cover. - [Data Cleaning Challenge: Parsing Dates](https://www.kaggle.com/rtatman/data-cleaning-challenge-parsing-dates/) - [Data Cleaning Challenge: Scale and Normalize Data](https://www.kaggle.com/rtatman/data-cleaning-challenge-scale-and-normalize-data) ## Assignment [Evaluating Data from a Form](assignment.md) --- ## File: 2-Working-With-Data/07-python/README.md # Working with Data: Python and the Pandas Library | ](../../sketchnotes/07-WorkWithPython.png) | | :-------------------------------------------------------------------------------------------------------: | | Working With Python - _Sketchnote by [@nitya](https://twitter.com/nitya)_ | [](https://youtu.be/dZjWOGbsN4Y) While databases offer very efficient ways to store data and query them using query languages, the most flexible way of data processing is writing your own program to manipulate data. In many cases, doing a database query would be a more effective way. However in some cases when more complex data processing is needed, it cannot be done easily using SQL. Data processing can be programmed in any programming language, but there are certain languages that are higher level with respect to working with data. Data scientists typically prefer one of the following languages: * **[Python](https://www.python.org/)**, a general-purpose programming language, which is often considered one of the best options for beginners due to its simplicity. Python has a lot of additional libraries that can help you solve many practical problems, such as extracting your data from ZIP archive, or converting picture to grayscale. In addition to data science, Python is also often used for web development. * **[R](https://www.r-project.org/)** is a traditional toolbox developed with statistical data processing in mind. It also contains large repository of libraries (CRAN), making it a good choice for data processing. However, R is not a general-purpose programming language, and is rarely used outside of data science domain. * **[Julia](https://julialang.org/)** is another language developed specifically for data science. It is intended to give better performance than Python, making it a great tool for scientific experimentation. In this lesson, we will focus on using Python for simple data processing. We will assume basic familiarity with the language. If you want a deeper tour of Python, you can refer to one of the following resources: * [Learn Python in a Fun Way with Turtle Graphics and Fractals](https://github.com/shwars/pycourse) - GitHub-based quick intro course into Python Programming * [Take your First Steps with Python](https://docs.microsoft.com/en-us/learn/paths/python-first-steps/?WT.mc_id=academic-77958-bethanycheum) Learning Path on [Microsoft Learn](http://learn.microsoft.com/?WT.mc_id=academic-77958-bethanycheum) Data can come in many forms. In this lesson, we will consider three forms of data - **tabular data**, **text** and **images**. We will focus on a few examples of data processing, instead of giving you full overview of all related libraries. This would allow you to get the main idea of what's possible, and leave you with understanding on where to find solutions to your problems when you need them. > **Most useful advice**. When you need to perform certain operation on data that you do not know how to do, try searching for it in the internet. [Stackoverflow](https://stackoverflow.com/) usually contains a lot of useful code sample in Python for many typical tasks. ## [Pre-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/12) ## Tabular Data and Dataframes You have already met tabular data when we talked about relational databases. When you have a lot of data, and it is contained in many different linked tables, it definitely makes sense to use SQL for working with it. However, there are many cases when we have a table of data, and we need to gain some **understanding** or **insights** about this data, such as the distribution, correlation between values, etc. In data science, there are a lot of cases when we need to perform some transformations of the original data, followed by visualization. Both those steps can be easily done using Python. There are two most useful libraries in Python that can help you deal with tabular data: * **[Pandas](https://pandas.pydata.org/)** allows you to manipulate so-called **Dataframes**, which are analogous to relational tables. You can have named columns, and perform different operations on row, columns and dataframes in general. * **[Numpy](https://numpy.org/)** is a library for working with **tensors**, i.e. multi-dimensional **arrays**. Array has values of the same underlying type, and it is simpler than dataframe, but it offers more mathematical operations, and creates less overhead. There are also a couple of other libraries you should know about: * **[Matplotlib](https://matplotlib.org/)** is a library used for data visualization and plotting graphs * **[SciPy](https://www.scipy.org/)** is a library with some additional scientific functions. We have already come across this library when talking about probability and statistics Here is a piece of code that you would typically use to import those libraries in the beginning of your Python program: ```python import numpy as np import pandas as pd import matplotlib.pyplot as plt from scipy import ... # you need to specify exact sub-packages that you need ``` Pandas is centered around a few basic concepts. ### Series **Series** is a sequence of values, similar to a list or numpy array. The main difference is that series also has an **index**, and when we operate on series (eg., add them), the index is taken into account. Index can be as simple as integer row number (it is the index used by default when creating a series from list or array), or it can have a complex structure, such as date interval. > **Note**: There is some introductory Pandas code in the accompanying notebook [`notebook.ipynb`](notebook.ipynb). We only outline some the examples here, and you are definitely welcome to check out the full notebook. Consider an example: we want to analyze sales of our ice-cream spot. Let's generate a series of sales numbers (number of items sold each day) for some time period: ```python start_date = "Jan 1, 2020" end_date = "Mar 31, 2020" idx = pd.date_range(start_date,end_date) print(f"Length of index is {len(idx)}") items_sold = pd.Series(np.random.randint(25,50,size=len(idx)),index=idx) items_sold.plot() ``` Now suppose that each week we are organizing a party for friends, and we take additional 10 packs of ice-cream for a party. We can create another series, indexed by week, to demonstrate that: ```python additional_items = pd.Series(10,index=pd.date_range(start_date,end_date,freq="W")) ``` When we add two series together, we get total number: ```python total_items = items_sold.add(additional_items,fill_value=0) total_items.plot() ``` > **Note** that we are not using simple syntax `total_items+additional_items`. If we did, we would have received a lot of `NaN` (*Not a Number*) values in the resulting series. This is because there are missing values for some of the index point in the `additional_items` series, and adding `Nan` to anything results in `NaN`. Thus we need to specify `fill_value` parameter during addition. With time series, we can also **resample** the series with different time intervals. For example, suppose we want to compute mean sales volume monthly. We can use the following code: ```python monthly = total_items.resample("1M").mean() ax = monthly.plot(kind='bar') ``` ### DataFrame A DataFrame is essentially a collection of series with the same index. We can combine several series together into a DataFrame: ```python a = pd.Series(range(1,10)) b = pd.Series(["I","like","to","play","games","and","will","not","change"],index=range(0,9)) df = pd.DataFrame([a,b]) ``` This will create a horizontal table like this: | | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | | --- | --- | ---- | --- | --- | ------ | --- | ------ | ---- | ---- | | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | | 1 | I | like | to | use | Python | and | Pandas | very | much | We can also use Series as columns, and specify column names using dictionary: ```python df = pd.DataFrame({ 'A' : a, 'B' : b }) ``` This will give us a table like this: | | A | B | | --- | --- | ------ | | 0 | 1 | I | | 1 | 2 | like | | 2 | 3 | to | | 3 | 4 | use | | 4 | 5 | Python | | 5 | 6 | and | | 6 | 7 | Pandas | | 7 | 8 | very | | 8 | 9 | much | **Note** that we can also get this table layout by transposing the previous table, eg. by writing ```python df = pd.DataFrame([a,b]).T.rename(columns={ 0 : 'A', 1 : 'B' }) ``` Here `.T` means the operation of transposing the DataFrame, i.e. changing rows and columns, and `rename` operation allows us to rename columns to match the previous example. Here are a few most important operations we can perform on DataFrames: **Column selection**. We can select individual columns by writing `df['A']` - this operation returns a Series. We can also select a subset of columns into another DataFrame by writing `df[['B','A']]` - this return another DataFrame. **Filtering** only certain rows by criteria. For example, to leave only rows with column `A` greater than 5, we can write `df[df['A']>5]`. > **Note**: The way filtering works is the following. The expression `df['A']<5` returns a boolean series, which indicates whether expression is `True` or `False` for each element of the original series `df['A']`. When boolean series is used as an index, it returns subset of rows in the DataFrame. Thus it is not possible to use arbitrary Python boolean expression, for example, writing `df[df['A']>5 and df['A']<7]` would be wrong. Instead, you should use special `&` operation on boolean series, writing `df[(df['A']>5) & (df['A']<7)]` (*brackets are important here*). **Creating new computable columns**. We can easily create new computable columns for our DataFrame by using intuitive expression like this: ```python df['DivA'] = df['A']-df['A'].mean() ``` This example calculates divergence of A from its mean value. What actually happens here is we are computing a series, and then assigning this series to the left-hand-side, creating another column. Thus, we cannot use any operations that are not compatible with series, for example, the code below is wrong: ```python # Wrong code -> df['ADescr'] = "Low" if df['A'] < 5 else "Hi" df['LenB'] = len(df['B']) # <- Wrong result ``` The latter example, while being syntactically correct, gives us wrong result, because it assigns the length of series `B` to all values in the column, and not the length of individual elements as we intended. If we need to compute complex expressions like this, we can use `apply` function. The last example can be written as follows: ```python df['LenB'] = df['B'].apply(lambda x : len(x)) # or df['LenB'] = df['B'].apply(len) ``` After operations above, we will end up with the following DataFrame: | | A | B | DivA | LenB | | --- | --- | ------ | ---- | ---- | | 0 | 1 | I | -4.0 | 1 | | 1 | 2 | like | -3.0 | 4 | | 2 | 3 | to | -2.0 | 2 | | 3 | 4 | use | -1.0 | 3 | | 4 | 5 | Python | 0.0 | 6 | | 5 | 6 | and | 1.0 | 3 | | 6 | 7 | Pandas | 2.0 | 6 | | 7 | 8 | very | 3.0 | 4 | | 8 | 9 | much | 4.0 | 4 | **Selecting rows based on numbers** can be done using `iloc` construct. For example, to select first 5 rows from the DataFrame: ```python df.iloc[:5] ``` **Grouping** is often used to get a result similar to *pivot tables* in Excel. Suppose that we want to compute mean value of column `A` for each given number of `LenB`. Then we can group our DataFrame by `LenB`, and call `mean`: ```python df.groupby(by='LenB')[['A','DivA']].mean() ``` If we need to compute mean and the number of elements in the group, then we can use more complex `aggregate` function: ```python df.groupby(by='LenB') \ .aggregate({ 'DivA' : len, 'A' : lambda x: x.mean() }) \ .rename(columns={ 'DivA' : 'Count', 'A' : 'Mean'}) ``` This gives us the following table: | LenB | Count | Mean | | ---- | ----- | -------- | | 1 | 1 | 1.000000 | | 2 | 1 | 3.000000 | | 3 | 2 | 5.000000 | | 4 | 3 | 6.333333 | | 6 | 2 | 6.000000 | ### Getting Data We have seen how easy it is to construct Series and DataFrames from Python objects. However, data usually comes in the form of a text file, or an Excel table. Luckily, Pandas offers us a simple way to load data from disk. For example, reading CSV file is as simple as this: ```python df = pd.read_csv('file.csv') ``` We will see more examples of loading data, including fetching it from external web sites, in the "Challenge" section ### Printing and Plotting A Data Scientist often has to explore the data, thus it is important to be able to visualize it. When DataFrame is big, many times we want just to make sure we are doing everything correctly by printing out the first few rows. This can be done by calling `df.head()`. If you are running it from Jupyter Notebook, it will print out the DataFrame in a nice tabular form. We have also seen the usage of `plot` function to visualize some columns. While `plot` is very useful for many tasks, and supports many different graph types via `kind=` parameter, you can always use raw `matplotlib` library to plot something more complex. We will cover data visualization in detail in separate course lessons. This overview covers most important concepts of Pandas, however, the library is very rich, and there is no limit to what you can do with it! Let's now apply this knowledge for solving specific problem. ## πŸš€ Challenge 1: Analyzing COVID Spread First problem we will focus on is modelling of epidemic spread of COVID-19. In order to do that, we will use the data on the number of infected individuals in different countries, provided by the [Center for Systems Science and Engineering](https://systems.jhu.edu/) (CSSE) at [Johns Hopkins University](https://jhu.edu/). Dataset is available in [this GitHub Repository](https://github.com/CSSEGISandData/COVID-19). Since we want to demonstrate how to deal with data, we invite you to open [`notebook-covidspread.ipynb`](notebook-covidspread.ipynb) and read it from top to bottom. You can also execute cells, and do some challenges that we have left for you at the end. > If you do not know how to run code in Jupyter Notebook, have a look at [this article](https://soshnikov.com/education/how-to-execute-notebooks-from-github/). ## Working with Unstructured Data While data very often comes in tabular form, in some cases we need to deal with less structured data, for example, text or images. In this case, to apply data processing techniques we have seen above, we need to somehow **extract** structured data. Here are a few examples: * Extracting keywords from text, and seeing how often those keywords appear * Using neural networks to extract information about objects on the picture * Getting information on emotions of people on video camera feed ## πŸš€ Challenge 2: Analyzing COVID Papers In this challenge, we will continue with the topic of COVID pandemic, and focus on processing scientific papers on the subject. There is [CORD-19 Dataset](https://www.kaggle.com/allen-institute-for-ai/CORD-19-research-challenge) with more than 7000 (at the time of writing) papers on COVID, available with metadata and abstracts (and for about half of them there is also full text provided). A full example of analyzing this dataset using [Text Analytics for Health](https://docs.microsoft.com/azure/cognitive-services/text-analytics/how-tos/text-analytics-for-health/?WT.mc_id=academic-77958-bethanycheum) cognitive service is described [in this blog post](https://soshnikov.com/science/analyzing-medical-papers-with-azure-and-text-analytics-for-health/). We will discuss simplified version of this analysis. > **NOTE**: We do not provide a copy of the dataset as part of this repository. You may first need to download the [`metadata.csv`](https://www.kaggle.com/allen-institute-for-ai/CORD-19-research-challenge?select=metadata.csv) file from [this dataset on Kaggle](https://www.kaggle.com/allen-institute-for-ai/CORD-19-research-challenge). Registration with Kaggle may be required. You may also download the dataset without registration [from here](https://ai2-semanticscholar-cord-19.s3-us-west-2.amazonaws.com/historical_releases.html), but it will include all full texts in addition to metadata file. Open [`notebook-papers.ipynb`](notebook-papers.ipynb) and read it from top to bottom. You can also execute cells, and do some challenges that we have left for you at the end. ## Processing Image Data Recently, very powerful AI models have been developed that allow us to understand images. There are many tasks that can be solved using pre-trained neural networks, or cloud services. Some examples include: * **Image Classification**, which can help you categorize the image into one of the pre-defined classes. You can easily train your own image classifiers using services such as [Custom Vision](https://azure.microsoft.com/services/cognitive-services/custom-vision-service/?WT.mc_id=academic-77958-bethanycheum) * **Object Detection** to detect different objects in the image. Services such as [computer vision](https://azure.microsoft.com/services/cognitive-services/computer-vision/?WT.mc_id=academic-77958-bethanycheum) can detect a number of common objects, and you can train [Custom Vision](https://azure.microsoft.com/services/cognitive-services/custom-vision-service/?WT.mc_id=academic-77958-bethanycheum) model to detect some specific objects of interest. * **Face Detection**, including Age, Gender and Emotion detection. This can be done via [Face API](https://azure.microsoft.com/services/cognitive-services/face/?WT.mc_id=academic-77958-bethanycheum). All those cloud services can be called using [Python SDKs](https://docs.microsoft.com/samples/azure-samples/cognitive-services-python-sdk-samples/cognitive-services-python-sdk-samples/?WT.mc_id=academic-77958-bethanycheum), and thus can be easily incorporated into your data exploration workflow. Here are some examples of exploring data from Image data sources: * In the blog post [How to Learn Data Science without Coding](https://soshnikov.com/azure/how-to-learn-data-science-without-coding/) we explore Instagram photos, trying to understand what makes people give more likes to a photo. We first extract as much information from pictures as possible using [computer vision](https://azure.microsoft.com/services/cognitive-services/computer-vision/?WT.mc_id=academic-77958-bethanycheum), and then use [Azure Machine Learning AutoML](https://docs.microsoft.com/azure/machine-learning/concept-automated-ml/?WT.mc_id=academic-77958-bethanycheum) to build interpretable model. * In [Facial Studies Workshop](https://github.com/CloudAdvocacy/FaceStudies) we use [Face API](https://azure.microsoft.com/services/cognitive-services/face/?WT.mc_id=academic-77958-bethanycheum) to extract emotions on people on photographs from events, in order to try to understand what makes people happy. ## Conclusion Whether you already have structured or unstructured data, using Python you can perform all steps related to data processing and understanding. It is probably the most flexible way of data processing, and that is the reason the majority of data scientists use Python as their primary tool. Learning Python in depth is probably a good idea if you are serious about your data science journey! ## [Post-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/13) ## Review & Self Study **Books** * [Wes McKinney. Python for Data Analysis: Data Wrangling with Pandas, NumPy, and IPython](https://www.amazon.com/gp/product/1491957662) **Online Resources** * Official [10 minutes to Pandas](https://pandas.pydata.org/pandas-docs/stable/user_guide/10min.html) tutorial * [Documentation on Pandas Visualization](https://pandas.pydata.org/pandas-docs/stable/user_guide/visualization.html) **Learning Python** * [Learn Python in a Fun Way with Turtle Graphics and Fractals](https://github.com/shwars/pycourse) * [Take your First Steps with Python](https://docs.microsoft.com/learn/paths/python-first-steps/?WT.mc_id=academic-77958-bethanycheum) Learning Path on [Microsoft Learn](http://learn.microsoft.com/?WT.mc_id=academic-77958-bethanycheum) ## Assignment [Perform more detailed data study for the challenges above](assignment.md) ## Credits This lesson has been authored with β™₯️ by [Dmitry Soshnikov](http://soshnikov.com) --- ## File: 2-Working-With-Data/06-non-relational/README.md # Working with Data: Non-Relational Data | ](../../sketchnotes/06-NoSQL.png)| |:---:| |Working with NoSQL Data - _Sketchnote by [@nitya](https://twitter.com/nitya)_ | ## [Pre-Lecture Quiz](https://ff-quizzes.netlify.app/en/ds/quiz/10) Data is not limited to relational databases. This lesson focuses on non-relational data and will cover the basics of spreadsheets and NoSQL. ## Spreadsheets Spreadsheets are a popular way to store and explore data because it requires less work to setup and get started. In this lesson you'll learn the basic components of a spreadsheet, as well as formulas and functions. The examples will be illustrated with Microsoft Excel, but most of the parts and topics will have similar names and steps in comparison to other spreadsheet software. A spreadsheet is a file and will be accessible in the file system of a computer, device, or cloud based file system. The software itself may be browser based or an application that must be installed on a computer or downloaded as an app. In Excel these files are also defined as **workbooks** and this terminology will be used the remainder of this lesson. A workbook contains one or more **worksheets**, where each worksheet are labeled by tabs. Within a worksheet are rectangles called **cells**, which will contain the actual data. A cell is the intersection of a row and column, where the columns are labeled with alphabetical characters and rows labeled numerically. Some spreadsheets will contain headers in the first few rows to describe the data in a cell. With these basic elements of an Excel workbook, we'll use and an example from [Microsoft Templates](https://templates.office.com/) focused on an inventory to walk through some additional parts of a spreadsheet. ### Managing an Inventory The spreadsheet file named "InventoryExample" is a formatted spreadsheet of items within an inventory that contains three worksheets, where the tabs are labeled "Inventory List", "Inventory Pick List" and "Bin Lookup". Row 4 of the Inventory List worksheet is the header, which describes the value of each cell in the header column. There are instances where a cell is dependent on the values of other cells to generate its value. The Inventory List spreadsheet keeps track of the cost of every item in its inventory, but what if we need to know the value of everything in the inventory? [**Formulas**](https://support.microsoft.com/en-us/office/overview-of-formulas-34519a4e-1e8d-4f4b-84d4-d642c4f63263) perform actions on cell data and is used to calculate the cost of the inventory in this example. This spreadsheet used a formula in the Inventory Value column to calculate the value of each item by multiplying the quantity under the QTY header and its costs by the cells under the COST header. Double clicking or highlighting a cell will show the formula. You'll notice that formulas start with an equals sign, followed by the calculation or operation. We can use another formula to add all the values of Inventory Value together to get its total value. This could be calculated by adding each cell to generate the sum, but that can be a tedious task. Excel has [**functions**](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89), or predefined formulas to perform calculations on cell values. Functions require arguments, which are the required values used to perform these calculations. When functions require more than one argument, they will need to be listed in a particular order or the function may not calculate the correct value. This example uses the SUM function, and uses the values of on Inventory Value as the argument to add generate the total listed under row 3, column B (also referred to as B3). ## NoSQL NoSQL is an umbrella term for the different ways to store non-relational data and can be interpreted as "non-SQL", "non-relational" or "not only SQL". These type of database systems can be categorized into 4 types. > Source from [MichaΕ‚ BiaΕ‚ecki Blog](https://www.michalbialecki.com/2018/03/18/azure-cosmos-db-key-value-database-cloud/) [Key-value](https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data#keyvalue-data-stores) databases pair unique keys, which are a unique identifier associated with a value. These pairs are stored using a [hash table](https://www.hackerearth.com/practice/data-structures/hash-tables/basics-of-hash-tables/tutorial/) with an appropriate hashing function. > Source from [Microsoft](https://docs.microsoft.com/en-us/azure/cosmos-db/graph/graph-introduction#graph-database-by-example) [Graph](https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data#graph-data-stores) databases describe relationships in data and are represented as a collection of nodes and edges. A node represents an entity, something that exists in the real world such as a student or bank statement. Edges represent the relationship between two entities Each node and edge have properties that provides additional information about each node and edges. [Columnar](https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data#columnar-data-stores) data stores organizes data into columns and rows like a relational data structure but each column is divided into groups called a column family, where all the data under one column is related and can be retrieved and changed in one unit. ### Document Data Stores with the Azure Cosmos DB [Document](https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data#document-data-stores) data stores build on the concept of a key-value data store and is made up of a series of fields and objects. This section will explore document databases with the Cosmos DB emulator. A Cosmos DB database fits the definition of "Not Only SQL", where Cosmos DB's document database relies on SQL to query the data. The [previous lesson](../05-relational-databases/README.md) on SQL covers the basics of the language, and we'll be able to apply some of the same queries to a document database here. We'll be using the Cosmos DB Emulator, which allows us to create and explore a document database locally on a computer. Read more about the Emulator [here](https://docs.microsoft.com/en-us/azure/cosmos-db/local-emulator?tabs=ssl-netstd21). A document is a collection of fields and object values, where the fields describe what the object value represents. Below is an example of a document. ```json { "firstname": "Eva", "age": 44, "id": "8c74a315-aebf-4a16-bb38-2430a9896ce5", "_rid": "bHwDAPQz8s0BAAAAAAAAAA==", "_self": "dbs/bHwDAA==/colls/bHwDAPQz8s0=/docs/bHwDAPQz8s0BAAAAAAAAAA==/", "_etag": "\"00000000-0000-0000-9f95-010a691e01d7\"", "_attachments": "attachments/", "_ts": 1630544034 } ``` The fields of interest in this document are: `firstname`, `id`, and `age`. The rest of the fields with the underscores were generated by Cosmos DB. #### Exploring Data with the Cosmos DB Emulator You can download and install the emulator [for Windows here](https://aka.ms/cosmosdb-emulator). Refer to this [documentation](https://docs.microsoft.com/en-us/azure/cosmos-db/local-emulator?tabs=ssl-netstd21#run-on-linux-macos) for options on how to run the Emulator for macOS and Linux. The Emulator launches a browser window, where the Explorer view allows you to explore documents. If you're following along, click on "Start with Sample" to generate a sample database called SampleDB. If you expand Sample DB by clicking on the arrow you'll find a container called `Persons`, a container holds a collection of items, which are the documents within the container. You can explore the four individual documents under `Items`. #### Querying Document Data with the Cosmos DB Emulator We can also query the sample data by clicking on the new SQL Query button (second button from the left). `SELECT * FROM c` returns all the documents in the container. Let's add a where clause and find everyone younger than 40. `SELECT * FROM c where c.age < 40` The query returns two documents, notice the age value for each document is less than 40. #### JSON and Documents If you're familiar with JavaScript Object Notation (JSON) you'll notice that documents look similar to JSON. There is a `PersonsData.json` file in this directory with more data that you may upload to the Persons container in the Emulator via the `Upload Item` button. In most instances, APIs that return JSON data can be directly transferred and stored in document databases. Below is another document, it represents tweets from the Microsoft Twitter account that was retrieved using the Twitter API, then inserted into Cosmos DB. ```json { "created_at": "2021-08-31T19:03:01.000Z", "id": "1432780985872142341", "text": "Blank slate. Like this tweet if you’ve ever painted in Microsoft Paint before. https://t.co/cFeEs8eOPK", "_rid": "dhAmAIUsA4oHAAAAAAAAAA==", "_self": "dbs/dhAmAA==/colls/dhAmAIUsA4o=/docs/dhAmAIUsA4oHAAAAAAAAAA==/", "_etag": "\"00000000-0000-0000-9f84-a0958ad901d7\"", "_attachments": "attachments/", "_ts": 1630537000 ``` The fields of interest in this document are: `created_at`, `id`, and `text`. ## πŸš€ Challenge There is a `TwitterData.json` file that you can upload to the SampleDB database. It's recommended that you add it to a separate container. This can be done by: 1. Clicking the new container button in the top right 1. Selecting the existing database (SampleDB) creating a container id for the container 1. Setting the partition key to `/id` 1. Clicking OK (you can ignore rest of the information in this view as this is a small dataset running locally on your machine) 1. Open your new container and upload the Twitter Data file with `Upload Item` button Try to run a few select queries to find the documents that have Microsoft in the text field. Hint: try to use the [LIKE keyword](https://docs.microsoft.com/en-us/azure/cosmos-db/sql/sql-query-keywords#using-like-with-the--wildcard-character) ## [Post-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/11) ## Review & Self Study - There are some additional formatting and features added to this spreadsheet that this lesson does not cover. Microsoft has a [large library of documentation and videos](https://support.microsoft.com/excel) on Excel if you're interested in learning more. - This architectural documentation details the characteristics in the different types of non-relational data: [Non-relational Data and NoSQL](https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data) - Cosmos DB is a cloud based non-relational database that can also store the different NoSQL types mentioned in this lesson. Learn more about these types in this [Cosmos DB Microsoft Learn Module](https://docs.microsoft.com/en-us/learn/paths/work-with-nosql-data-in-azure-cosmos-db/) ## Assignment [Soda Profits](assignment.md) --- ## File: 2-Working-With-Data/05-relational-databases/README.md # Working with Data: Relational Databases | ](../../sketchnotes/05-RelationalData.png)| |:---:| | Working With Data: Relational Databases - _Sketchnote by [@nitya](https://twitter.com/nitya)_ | Chances are you have used a spreadsheet in the past to store information. You had a set of rows and columns, where the rows contained the information (or data), and the columns described the information (sometimes called metadata). A relational database is built upon this core principle of columns and rows in tables, allowing you to have information spread across multiple tables. This allows you to work with more complex data, avoid duplication, and have flexibility in the way you explore the data. Let's explore the concepts of a relational database. ## [Pre-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/8) ## It all starts with tables A relational database has at its core tables. Just as with the spreadsheet, a table is a collection of columns and rows. The row contains the data or information we wish to work with, such as the name of a city or the amount of rainfall. The columns describe the data they store. Let's begin our exploration by starting a table to store information about cities. We might start with their name and country. You could store this in a table as follows: | City | Country | | -------- | ------------- | | Tokyo | Japan | | Atlanta | United States | | Auckland | New Zealand | Notice the column names of **city**, **country** and **population** describe the data being stored, and each row has information about one city. ## The shortcomings of a single table approach Chances are, the table above seems relatively familiar to you. Let's start to add some additional data to our burgeoning database - annual rainfall (in millimeters). We'll focus on the years 2018, 2019 and 2020. If we were to add it for Tokyo, it might look something like this: | City | Country | Year | Amount | | ----- | ------- | ---- | ------ | | Tokyo | Japan | 2020 | 1690 | | Tokyo | Japan | 2019 | 1874 | | Tokyo | Japan | 2018 | 1445 | What do you notice about our table? You might notice we're duplicating the name and country of the city over and over. That could take up quite a bit of storage, and is largely unnecessary to have multiple copies of. After all, Tokyo has just the one name we're interested in. OK, let's try something else. Let's add new columns for each year: | City | Country | 2018 | 2019 | 2020 | | -------- | ------------- | ---- | ---- | ---- | | Tokyo | Japan | 1445 | 1874 | 1690 | | Atlanta | United States | 1779 | 1111 | 1683 | | Auckland | New Zealand | 1386 | 942 | 1176 | While this avoids the row duplication, it adds a couple of other challenges. We would need to modify the structure of our table each time there's a new year. Additionally, as our data grows having our years as columns will make it trickier to retrieve and calculate values. This is why we need multiple tables and relationships. By breaking apart our data we can avoid duplication and have more flexibility in how we work with our data. ## The concepts of relationships Let's return to our data and determine how we want to split things up. We know we want to store the name and country for our cities, so this will probably work best in one table. | City | Country | | -------- | ------------- | | Tokyo | Japan | | Atlanta | United States | | Auckland | New Zealand | But before we create the next table, we need to figure out how to reference each city. We need some form of an identifier, ID or (in technical database terms) a primary key. A primary key is a value used to identify one specific row in a table. While this could be based on a value itself (we could use the name of the city, for example), it should almost always be a number or other identifier. We don't want the id to ever change as it would break the relationship. You will find in most cases the primary key or id will be an auto-generated number. > βœ… Primary key is frequently abbreviated as PK ### cities | city_id | City | Country | | ------- | -------- | ------------- | | 1 | Tokyo | Japan | | 2 | Atlanta | United States | | 3 | Auckland | New Zealand | > βœ… You will notice we use the terms "id" and "primary key" interchangeably during this lesson. The concepts here apply to DataFrames, which you will explore later. DataFrames don't use the terminology of "primary key", however you will notice they behave much in the same way. With our cities table created, let's store the rainfall. Rather than duplicating the full information about the city, we can use the id. We should also ensure the newly created table has an *id* column as well, as all tables should have an id or primary key. ### rainfall | rainfall_id | city_id | Year | Amount | | ----------- | ------- | ---- | ------ | | 1 | 1 | 2018 | 1445 | | 2 | 1 | 2019 | 1874 | | 3 | 1 | 2020 | 1690 | | 4 | 2 | 2018 | 1779 | | 5 | 2 | 2019 | 1111 | | 6 | 2 | 2020 | 1683 | | 7 | 3 | 2018 | 1386 | | 8 | 3 | 2019 | 942 | | 9 | 3 | 2020 | 1176 | Notice the **city_id** column inside the newly created **rainfall** table. This column contains values which reference the IDs in the **cities** table. In technical relational data terms, this is called a **foreign key**; it's a primary key from another table. You can just think of it as a reference or a pointer. **city_id** 1 references Tokyo. > [!NOTE] > Foreign key is frequently abbreviated as FK ## Retrieving the data With our data separated into two tables, you may be wondering how we retrieve it. If we are using a relational database such as MySQL, SQL Server or Oracle, we can use a language called Structured Query Language or SQL. SQL (sometimes pronounced sequel) is a standard language used to retrieve and modify data in a relational database. To retrieve data you use the command `SELECT`. At its core, you **select** the columns you want to see **from** the table they're contained in. If you wanted to display just the names of the cities, you could use the following: ```sql SELECT city FROM cities; -- Output: -- Tokyo -- Atlanta -- Auckland ``` `SELECT` is where you list the columns, and `FROM` is where you list the tables. > [!NOTE] > SQL syntax is case-insensitive, meaning `select` and `SELECT` mean the same thing. However, depending on the type of database you are using the columns and tables might be case sensitive. As a result, it's a best practice to always treat everything in programming like it's case sensitive. When writing SQL queries common convention is to put the keywords in all upper-case letters. The query above will display all cities. Let's imagine we only wanted to display cities in New Zealand. We need some form of a filter. The SQL keyword for this is `WHERE`, or "where something is true". ```sql SELECT city FROM cities WHERE country = 'New Zealand'; -- Output: -- Auckland ``` ## Joining data Until now we've retrieved data from a single table. Now we want to bring the data together from both **cities** and **rainfall**. This is done by *joining* them together. You will effectively create a seam between the two tables, and match up the values from a column from each table. In our example, we will match the **city_id** column in **rainfall** with the **city_id** column in **cities**. This will match the rainfall value with its respective city. The type of join we will perform is what's called an *inner* join, meaning if any rows don't match with anything from the other table they won't be displayed. In our case every city has rainfall, so everything will be displayed. Let's retrieve the rainfall for 2019 for all our cities. We're going to do this in steps. The first step is to join the data together by indicating the columns for the seam - **city_id** as highlighted before. ```sql SELECT cities.city rainfall.amount FROM cities INNER JOIN rainfall ON cities.city_id = rainfall.city_id ``` We have highlighted the two columns we want, and the fact we want to join the tables together by the **city_id**. Now we can add the `WHERE` statement to filter out only year 2019. ```sql SELECT cities.city rainfall.amount FROM cities INNER JOIN rainfall ON cities.city_id = rainfall.city_id WHERE rainfall.year = 2019 -- Output -- city | amount -- -------- | ------ -- Tokyo | 1874 -- Atlanta | 1111 -- Auckland | 942 ``` ## Summary Relational databases are centered around dividing information between multiple tables which is then brought back together for display and analysis. This provides a high degree of flexibility to perform calculations and otherwise manipulate data. You have seen the core concepts of a relational database, and how to perform a join between two tables. ## πŸš€ Challenge There are numerous relational databases available on the internet. You can explore the data by using the skills you've learned above. ## Post-Lecture Quiz ## [Post-lecture quiz](https://ff-quizzes.netlify.app/en/ds/quiz/9) ## Review & Self Study There are several resources available on [Microsoft Learn](https://docs.microsoft.com/learn?WT.mc_id=academic-77958-bethanycheum) for you to continue your exploration of SQL and relational database concepts - [Describe concepts of relational data](https://docs.microsoft.com//learn/modules/describe-concepts-of-relational-data?WT.mc_id=academic-77958-bethanycheum) - [Get Started Querying with Transact-SQL](https://docs.microsoft.com//learn/paths/get-started-querying-with-transact-sql?WT.mc_id=academic-77958-bethanycheum) (Transact-SQL is a version of SQL) - [SQL content on Microsoft Learn](https://docs.microsoft.com/learn/browse/?products=azure-sql-database%2Csql-server&expanded=azure&WT.mc_id=academic-77958-bethanycheum) ## Assignment [Displaying airport data](assignment.md) ## 2. Official Technical Reference & Guides (microsoft/microsoft.github.io) [microsoft.github.io](https://microsoft.github.io) =================== Many open source projects released by Microsoft products, services and teams also have deployments powered by GitHub Pages on this domain. To discover Microsoft-released open source projects, learn about Microsoft's open source approach and other information, check us out online at [opensource.microsoft.com](https://opensource.microsoft.com). If we can help answer questions about the open source program at Microsoft, you can contact `opensource at microsoft com` - although we are not able to provide support for projects through this mechanism. ## Microsoft GitHub resources - Default repo templates including `SECURITY.MD` and code of conduct files: [microsoft/repo-templates](https://github.com/microsoft/repo-templates) - [Code of Conduct](https://opensource.microsoft.com/codeofconduct) ([FAQ](https://opensource.microsoft.com/codeofconduct/faq/)) ## Microsoft open source resources - [Open Source Blog](https://cloudblogs.microsoft.com/opensource/) - [The Open Source Show on Channel 9](https://channel9.msdn.com/Shows/The-Open-Source-Show) - [https://opensource.microsoft.com/](https://opensource.microsoft.com/) ## Resources for Microsoft employees - [docs.opensource.microsoft.com](https://docs.opensource.microsoft.com) - [Link your GitHub Account and join GitHub organizations automatically](https://repos.opensource.microsoft.com) ## How to identify an official Microsoft organization on GitHub - Official Microsoft GitHub orgs have a professional avatar and include a web address at Microsoft.com or an associated official domain - Official Microsoft orgs are [Verified on GitHub](https://github.blog/changelog/2018-08-07-domain-verification/) - look for the domain verification indicator on GitHub