Introduction to Data Science — CS1231-1 Unit-I (Animated)

01Types of Data Analysis

The same dataset can be read five different ways — depending on the question you ask.

Data mining

Find hidden patterns you didn't know to look for. E.g. market-basket: customers who buy diapers often buy beer.

Business intelligence

Dashboards & KPIs — what happened? Sales by region, live dashboards.

Statistical analysis

Describe and infer with statistics: mean, variance, hypothesis tests, confidence.

Predictive analysis

Forecast the future: regression, time-series, churn prediction from history.

Text analysis

Mine unstructured text: sentiment of tweets, topic extraction, spam filters.

Prescriptive*

What should we do? Optimization + simulation — "give me the best price to set".

✦ Exam point The four syllabus types are data mining, business intelligence, statistical, predictive, and text analysis — know one example application of each. The classic flow: BI looks back (what happened), predictive looks forward (what will happen).

02The Data-Analysis Process

Six stages, in order. Skimp on cleaning and every later step inherits the mess.

The six-stage pipeline — with a placement-analysis story↻ replay

Scenario: "which students get the highest placement packages?" → requirements → collect forms → clean missing CGPA → analyse (CGPA vs package) → visualise (scatter) → communicate (report to placement cell).

Stage	What you do	Placement example
1 Requirements	Define the question	"Does CGPA predict package?"
2 Collection	Gather data	Survey forms from 300 students
3 Processing	Organise, structure	One row per student, typed columns
4 Cleaning	Fix errors & gaps	Blank CGPAs, "8.5+" text, duplicates
5 Analysis	Explore & model	Correlation CGPA↔package
6 Communication	Report insights	Chart + 1-page summary to management

⚠ Common mistake "Data cleaning" is not optional decoration — it is stage 4 and it's the stage where real analysts spend most of their time. Dirty data (mixed text, blank cells, inconsistent dates) silently corrupts every analysis after it.

03Cleaning Text with Excel Functions

Real spreadsheets are full of messy text. These functions are the cleaning kit — type messy text, then fire a function and see the formula that Excel would run.

Text-function lab — edit the cell, then click a function

Live cells: A2 is your input, the next rows show the formula Excel would use (=TRIM(A2), =PROPER(A2)…) and the computed result. Watch how TRIM collapses the double spaces first — order matters when stacking functions.

Cheat sheet

Function	What it does
TRIM(text)	Removes extra spaces (keeps single spaces)
PROPER(text)	Capitalises the first letter of each word
UPPER / LOWER	All caps / all lowercase
SUBSTITUTE(text, old, new)	Replaces occurrences (e.g. "," → "")
LEFT(text,n) / RIGHT / MID(text,start,n)	Slice out substrings
FIND(find, text)	Position of a substring (returns number)
LEN(text)	Length in characters
CONCAT / &	Join text

04Dates & Times in Excel

Excel stores dates as serial numbers (days since 1900-01-01) — that's why you can subtract dates. Times are fractions of a day: 6:00 AM = 0.25.

Date lab — pick a birthday and watch every date function react

Date of birth

Start time

End time

The overnight shift 23:00 → 02:30 spans midnight: =MOD(end − start, 1) × 24 gives 3.5 hours — MOD is the trick that wraps the negative time.

✓ Worked — exact age For a DOB of 15-Jun-2005: =DATEDIF(B2,TODAY(),"y") gives full years, "ym" months since the last birthday, "md" days since the last month boundary. Date 2023-01-01 has Excel serial 44927 (try it in the lab).

Function	What it gives
TODAY() / NOW()	Today's date / date + time
DATEDIF(start, end, "y"/"ym"/"md")	Full years / months / days between
WEEKDAY(date, 1)	1=Sunday … 7=Saturday
EOMONTH(date, 0)	Last day of that month
MOD(time, 1)	Wraps negative times (overnight hours)

05Conditional Formatting

Formatting rules that react to the data: highlights, bars, color scales, top-n. Data patterns pop instantly.

One grid, four rules — toggle them on

These are exactly Excel's "Highlight Cell Rules", "Data Bars", "Color Scales" and "Top/Bottom Rules". Rules are formula-based: e.g. a rule =$B2>70 with mixed references applies per row.

✓ Worked — formula-based rule with mixed references To highlight the entire row when the score in column B exceeds 70, use rule =$B2>70 applied to range A2:F10. The $B locks the column, the 2 varies per row — that mix is what makes the rule "travel" correctly.

06Sorting & Filtering

Sorting rearranges the whole table by a key; filtering hides rows that fail a condition (it never deletes data).

Employee table — click headers to sort, pick a department to filter

Click "Name / Dept / Salary" to toggle ascending/descending (▲▼). Filtering hides rows — the status line shows how many of 8 rows are visible.

⚠ Common mistake Filtering does not delete rows — they're hidden. And if you sort only a selected column (not the whole range), you scramble the table. Always select the full table before sorting.

07Subtotals — and why SUM ≠ SUBTOTAL

Subtotals group data and insert summary rows. The twist: =SUBTOTAL(9,…) skips hidden rows, while =SUM doesn't.

Subtotal lab — group, then filter, and watch SUM vs SUBTOTAL(9) disagree

Dept	Item	Qty	Sales ₹

✓ The money question With the filter hiding rows: =SUM(C2:C9) still counts hidden rows → . =SUBTOTAL(9,C2:C9) ignores hidden rows → . Same-looking totals, different answers — that's the classic exam trap.

08Formula Sheet & 5 Near-Certain Questions

Task	Formula
Clean spaces	=TRIM(A2)
Title case	=PROPER(A2)
Strip commas	=SUBSTITUTE(A2,",","")
First word	=LEFT(A2, FIND(" ",A2)-1)
Email domain	=MID(A2, FIND("@",A2)+1, LEN(A2))
Exact age	=DATEDIF(B2,TODAY(),"y") & "y " & DATEDIF(B2,TODAY(),"ym") & "m"
Overnight hours	=MOD(B2-A2,1)*24
Visible-only total	=SUBTOTAL(9, C2:C9)

Q1 · List the five types of data analysis in the syllabus with an example each.

Data mining (market baskets), BI (sales dashboard), statistical (hypothesis test), predictive (churn forecast), text (sentiment analysis).

Q2 · Write the six stages of the data-analysis process in order.

Requirements → Collection → Processing → Cleaning → Analysis → Communication.

Q3 · Cell A1 = " rAhUl kUmAr ". Give the result of =TRIM(PROPER(A1)).

Inner first: PROPER → " Rahul Kumar " (double spaces kept), then TRIM → "Rahul Kumar". Order matters!

Q4 · Why does =SUM(C2:C9) differ from =SUBTOTAL(9,C2:C9) after filtering?

SUM includes hidden rows; SUBTOTAL(9) — the "9" means SUM of visible rows only — recalculates over what's on screen.

Q5 · What is the Excel serial number of 2023-01-01 and what does it mean?

44927 — days elapsed since 1-Jan-1900 (Excel counts 1900 as a leap year, hence the +2 offset).