# Preparing Data

Of course we cannot talk about sources of data without defining what data collection and analysis are: So, what are data collection and data analysis?

Data collection: This refers to the gathering of relevant data from variety of useful sources.

Data analysis: This refers to the uncovering of useful insights and trends from data.

Business value is created when data is collected, managed, and analyzed to solve business problems.

1. ## Where to source data
    

Data can either be readily available or collected from other sources.

Readily available data can include:

* Data a company collect and store in storage locations with limited access. e.g., sales data, customer data, and financial statements.
    
* Public records by governments and Non-governmental organizations. e.g., medical data, census, and weather data.
    

Data collected from others may include:

* First hand data collected from source through primary research. e.g., interviews, simulations, and surveys.
    
* Second hand data collected from other people or platforms like secondary research such as technical journals, public records, e.t.c.
    

2. ## Evaluation data sources
    

One should always access the strength, limitations, biases associated with the data source. This can be done using the **C.R.A.A.P test** tool. C.R.A.A.P is an acronym for;

* C (Currency): This describes how timely the data is. Question like “Is the data current with regular updates or its outdated?“ needs to be asked.
    
* R (Relevance): How applicable is the data to our/your needs?
    
* A (Authority): Who is the source of the data and what credentials do they hold?
    
* A (Accuracy): How correct is the data? Can one verify the data from other sources or knowledge?
    
* P (Purpose): What is the data intended for? Are there possible biases?
    

Since data is the link between the problem you are trying to solve and the intended solution, always ask the right questions by employing the **5W2H** (What, Why, When, Who, How, How Much) method to define the problem and understand the data you need.

Real world data is always messy and difficult to work with. Therefore, data needs to be cleaned to be converted to useful structured data that can be used for analysis.

3. ## Data Validation
    

Data validation can be defined as the checking of the accuracy and quality of data before using it. Validation checks;

* Data type: Is the data used of the correct type? e.g., numeric.
    
* Format: Has the data followed a predefined format? e.g., date format.
    
* Length: Is the data of appropriate length? e.g., Phone number.
    
* Consistent: Does the data follow a consistent logical order? e.g., check-in and check-out dates.
    
* Range: The data falls within specified range. e.g., date within a range.
    
* Uniqueness: The data should have no duplicates.
    
* Presence: There should be no entries left blank especially mandatory fields.
    
* Look up: The data should conform to set of acceptable values. e.g., days of the week.
    

# Introduction to spreadsheet

A spreadsheet is a file with data arranged in rows and columns. The data can be in form of text, numbers, functions, e.t.c., and can be captured, viewed, or manipulated using spreadsheet app.

Examples of spreadsheets are: Google sheet, Excel, and Libre Office.

1. ## Spreadsheet terminologies
    

1. Cells: These are boxes on the grid of a worksheet representing a single data point.
    
2. Cell reference: This is used to identify a cell. It is made up of column letter and row number which the cell is located e.g., A **column A** on **row 1** becomes cell **A1**.
    
3. Column: These are vertical series of cells in a spreadsheet each being identified by specific column letter at the top of the worksheet.
    
4. Row: These are horizontal series of cells in a spreadsheet identified by specific row number located at far left of the worksheet.
    
5. Range: Range is a collection of two or more selected cells running across a column, row, or a combination of both.
    
6. Formula: Formula is a mathematical equation designed by a user to perform calculations on a set of data and return the result in a given cell.
    
7. Function: A function is a predefined formula already available in a spreadsheet app, making it easier to perform calculations.
    
8. Argument: Arguments are values required by functions to perform calculations and return results.
    
9. Worksheet: This is a single page of rows and columns.
    
10. Workbook: Workbook is an entire spreadsheet file containing a collection of one or more worksheets.
    

Some of the most commonly used text file formats in spreadsheet are:

1. Comma separated values (.csv): Here, the common delimeter is a comma, however, it can have semi colon, tab, space, etc.
    
2. Tab separated values (.tsv): Here, the common delimeter is a tab and it’s suitable for text-heavy files as it is less likely to have tab characters within texts.
    

2. ## Spreadsheet file formats
    

Some of the common spreadsheet formats include:

1. .xls - Excel binary format
    
2. .xlsx - This is the current default file format for Microsoft excel. The additional x makes it be based on XML standard.
    
3. xlsm - This is an XML-based and macro enabled file format. The additional m shows that the file contains macros.
    

Since spreadsheet are not suitable for storing data long-term due to them being software specific and may become obsolete, text files are more ideal for storing and preserving original raw data because

* They are simple and lightweight.
    
* They are easily readable even in future.
    

***N/B:*** *A delimeter is a character used to split plain text into separate data units. They organize text data into rows and columns, specifying the exact location each data unit within text string starts and ends.*

### Sheet interface

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1751552847375/b5d8e31c-4093-4217-ae5e-fa7bef8d1a2f.png align="left")

3. ## Importing and Exporting data in sheets
    

Importing can be defined as the process of transferring data from an existing file into google sheet to view data, inspect data, manipulate, or analyze data.

Exporting on the other hand is the process of transferring data from google sheet into a different format.

Data can be imported from formats such as Microsoft Excel (`.xls`, `.xlsx`, `.xlsm`, `.xlt`), `.csv`, `.tsv`, `.txt`, `.ods`, e.t.c.

### Importing dataset

a. From a local machine:

* On the Google sheet interface, click **File > Import**
    
* Click on **Upload > Drag and drop** the file
    
* Or Browse for file on **Device > Select File > Click Open**
    

b. From Google drive:

* Click **File > Import**
    
* Click **Drive > Search** for file in the drive.
    
* Select **File > Click Insert**.
    
* Select preferred import location option.
    
* Pick suitable separator type.
    
* Click on Import data.
    

### Exporting dataset

* Click the worksheet tab with data to be exported as csv.
    
* Go to **File > Download**.
    
* Select (.csv).
    
* The file will be downloaded to the local machine.
    

# Data Governance, Legislation, and Ethics

Data governance, legislation, and ethics are important to ensure organizations are transparent and accountable for how they manage their own and others’ data. They moreover help organizations make data-driven decisions based on relevant, accurate, quality, and usable data.

1. *Data Governance*
    

Data governance are the people, processes, and technologies used to manage, protect, and use data to ensure consistent and trustworthy data that organizations can leverage as organization’s assets.

Data governance can also be referred to the collection of data management principles and practices that help organizations manage internal and external data flows. i.e., data architecture, data quality, data development, and data security.

Data governance framework ensures that data is actionable by directing how data will be managed.

2. *Data Legislation*
    

Data legislation mostly about data and information privacy.

Data Legislation in this case protects individuals and organizations from misuse of data and ensure that personal data are handled responsibly and transparently.

Some special terms under data legislation include;

* Data privacy: This refers to protecting personal information of individual from being collected, used, and shared with others without consent.
    
* Data security: This refers to protecting availability, integrity, and confidentiality of organizational data from unauthorized access, use, modification, disclosure, and destruction.
    
* Sensitive data: These are data requiring additional protection due to their sensitive nature.
    

3. *Data Ethics*
    

Data ethics are moral obligations of collecting, storing, and using data.

# Introduction to using Data

Before delving deep into data, we have to understand data types.

Data types are attributes associated with collection of data that inform the computer on how to interpret it’s value.

Data types can be categorized into two categories.

* Qualitative data
    
* Quantitative data
    

## Quantitative Data

Quantitative data measure values expressed as numbers. they answer questions such as; how much?, how many?, how often?, how long? and can be evaluated using mathematical formulas.

Quantitative data can further be divided into; Discrete and Continuous data.

Discrete measure numbers that are countable within finite amount of time (integers).

Continuous on the other hand take numbers that are of an infinite number of values in some defined range. They can be floating point numbers, date time, e.t.c.

### Qualitative Data

Qualitative data measure qualities or characteristics of variables, answering questions such as; what?, why?, how?, which?

They can further be divided into; Nominal, ordinal, and binary data.

Nominal are used to group observations f the same characteristics with no quantitative value or order.

Ordinal are classifications used to outline the order of variables.

Binary only accept two values i.e., True and False or 1 and 0.

## Spreadsheet formulas

Before diving deep into spreadsheet formulas, let’s first understand references.

**Reference** is a cell address that identifies a cell or range of cells by referring to the column letter and row number of the cell or cells.

Ranges are referenced using two cell references separated by a colon and can span multiple rows and columns.

**Relative reference:** changes when copied across multiple cells based on the relative position of rows and columns. e.g., a formula **\=A1+B1** copied from row 1 to row 2, the formula in row two becomes **=A1+B1.** All cells are relative referenced by default.

**Absolute reference:** keeps a row and/or column constant by using a dollar sign ($) before the row number and/or column letter. e.g., If formula **\=$A$1+B1** is copied from row 1 to 2, the formula in row two becomes **\=$A$1+B2.** In order to use Absolute reference, one has to understand the following;

* $A$1 = The column and row do not change.
    
* $A1 = The column does not change.
    
* A$1 = The row does not change.