കോളേജ് ഓഫീസുകളിൽ നിന്ന് ലഭിക്കുന്ന ഭൂരിഭാഗം data-യും “പ്രിന്റ് എടുക്കാൻ” (printing) വേണ്ടി തയ്യാറാക്കിയവയായിരിക്കും, വിശകലനത്തിന് (analysis) അനുയോജ്യമായിരിക്കില്ല: merge ചെയ്ത title rows, പേരും gender-ഉം ഒരൊറ്റ cell-ലേക്ക് ഒതുക്കിയത്, marks വരേണ്ട column-ൽ “Absent” എന്ന് ടൈപ്പ് ചെയ്തുവെച്ചത്, duplicate rows എന്നിവയൊക്കെ അതിൽ കാണാം. ഇത്തരത്തിലുള്ള ഒരു sheet-നെ formulas, filters, charts എന്നിവയ്ക്ക് പൂർണ്ണമായി വിശ്വസിക്കാൻ കഴിയുന്ന വൃത്തിയുള്ള ഒരു Excel Table ആക്കി മാറ്റുകയാണ് ഇന്ന് നാം ചെയ്യുന്നത്.
ലളിതമായി പറഞ്ഞാൽ
വിശകലനത്തിന് പൂർണ്ണമായും സജ്ജമായ (analysis-ready) ഒരു table, Topic 4-ൽ നമ്മൾ പഠിച്ച tidy-data നിയമങ്ങൾ കൃത്യമായി പാലിക്കുന്നു: ഒരു header row, ഒരു വിദ്യാർത്ഥിക്ക് ഒരു row, ഓരോ variable-നും ഓരോ column, ഒരു column-ൽ ഒരേ തരത്തിലുള്ള values മാത്രം, blank rows ഉണ്ടാകരുത്, ഒപ്പം data-യ്ക്കുള്ളിൽ ആകെത്തുക (totals) കൂട്ടിപ്പിണഞ്ഞു കിടക്കരുത്. Data ഈ രീതിയിലായിക്കഴിഞ്ഞാൽ Ctrl+T അമർത്തുക; അതോടെ Excel ഈ range-നെ ഒരൊറ്റ object (ഒരു Excel Table) ആയി പരിഗണിക്കുന്നു. പുതിയ rows ചേർക്കുമ്പോൾ ഇത് തനിയെ വലുതാവുകയും, formatting-ഉം filters-ഉം മാറ്റമില്ലാതെ നിലനിർത്തുകയും ചെയ്യുന്നു.
നമുക്ക് ഇതിനകം പരിചയമുള്ള കുറച്ചു ലളിതമായ tools ഉപയോഗിച്ചാണ് ഈ cleaning ജോലികൾ ചെയ്യുന്നത്: rows ഡിലീറ്റ് ചെയ്യുക, Text to Columns, TRIM function, Convert to Number, Remove Duplicates, Format as Table എന്നിവയാണവ.
ക്രമീകരിക്കാത്ത Dataset
ഒരു class teacher ബി.എസ്‌സി സ്റ്റാറ്റിസ്റ്റിക്സ് (BSc Statistics) ബാച്ചിന്റെ Semester 5 ഇന്റേണൽ മാർക്കുകൾ (internal marks) അയച്ചുതരുന്നു. ആ sheet താഴെ കാണുന്നതുപോലെയാണ് (row 1-ൽ merge ചെയ്ത title, row 2 blank ആണ്, അവസാനത്തെ row നേരിട്ട് ടൈപ്പ് ചെയ്തുവെച്ച total ആണ്):
| Row | A | B | C | D |
| 1 | BSc Statistics – Sem 5 Internal Marks (merged across A1:D1) | |||
| 2 | ||||
| 3 | Roll No | Student (Name – Gender) | Theory (50) | Practical (50) |
| 4 | STA501 | Anjali K – F | 38 | 41 |
| 5 | STA502 | ␣Muhammed Rashid – M | 42 | 45 |
| 6 | STA503 | Fathima Nasrin – F | Absent | 39 |
| 7 | STA502 | ␣Muhammed Rashid – M | 42 | 45 |
| 8 | STA504 | Arjun P – M | 35 (stored as text) | 37 |
| 9 | STA505 | Sneha Joseph – F | 44 | 46 |
| 10 | Total | 201 | 253 |
(␣ = അധികമായി വന്ന leading space.) പ്രശ്നങ്ങൾ: merge ചെയ്ത ഒരു title, ഒരു blank row, ഒരൊറ്റ column-ൽ രണ്ട് variables, ആവശ്യമില്ലാത്ത spaces, സംഖ്യകൾ വരേണ്ട column-ൽ text, text രൂപത്തിൽ സൂക്ഷിച്ചിരിക്കുന്ന ഒരു സംഖ്യ, ഒരു duplicate record, ഒപ്പം data-യ്ക്കുള്ളിൽ കടന്നുകൂടിയ ഒരു total row.
ഘട്ടം ഘട്ടമായി ചെയ്യേണ്ട രീതി:
Step 0 – Raw data സുരക്ഷിതമായി സൂക്ഷിക്കുക (Protect the raw data): Sheet tab-ൽ right-click ചെയ്യുക → Move or Copy → Create a copy എന്നതിൽ tick ചെയ്യുക → OK. യഥാർത്ഥ sheet-ന് Raw എന്നും, പകർപ്പിന് Clean എന്നും പേര് നൽകുക. ഒരിക്കലും Raw എഡിറ്റ് ചെയ്യരുത്; എല്ലാ തിരുത്തലുകളും Clean-ൽ മാത്രം ചെയ്യുക. നിങ്ങളുടെ വിശകലനം ആർക്കും എപ്പോഴും പരിശോധിച്ച് ബോധ്യപ്പെടാൻ (reproducible and checkable) ഇത് സഹായിക്കുന്നു.
Step 1 – ആവശ്യമില്ലാത്ത rows നീക്കം ചെയ്യുക (Remove non-data rows): Clean sheet-ൽ, rows 1–2 (title-ഉം blank row-ഉം) select ചെയ്ത് right-click → Delete നൽകുക. ഒപ്പം “Total” row-യും delete ചെയ്യുക — ആകെത്തുകകൾ കാണിക്കേണ്ടത് ഒരു analysis sheet-ൽ formulas ഉപയോഗിച്ചാണ്, അല്ലാതെ data-യ്ക്കുള്ളിൽ നേരിട്ട് ടൈപ്പ് ചെയ്തുവെച്ചുകൊണ്ടല്ല. Cells എന്തെങ്കിലും merge ചെയ്തിട്ടുണ്ടെങ്കിൽ, അവ select ചെയ്ത് Home → Merge & Center ക്ലിക്ക് ചെയ്ത് unmerge ചെയ്യുക.
Step 2 – ഓരോ column-നും ചെറുതും വ്യക്തവുമായ headers നൽകുക: Headers യഥാക്രമം RollNo, Name, Theory, Practical എന്ന് മാറ്റുക. ഇടയിൽ spaces ഇല്ലാത്ത ചെറിയ പേരുകൾ നൽകുന്നത് പിന്നീട് formulas എഴുതുമ്പോൾ വളരെ എളുപ്പമായിരിക്കും.
Step 3 – Name, Gender എന്നിവ വേർതിരിക്കുക (Split Name and Gender): ആദ്യം Name column-ന് വലതുവശത്തായി ഒരു പുതിയ empty column ചേർക്കുക (Column C-ൽ right-click → Insert). കാരണം Text to Columns അടുത്തുള്ള cells-ലേക്ക് data മാറ്റിയെഴുതും (overwrite). ഇനി Name data (B2:B7) select ചെയ്യുക, തുടർന്ന് Data → Text to Columns → Delimited → Next → Other എന്നതിൽ tick ചെയ്ത് – എന്ന് ടൈപ്പ് ചെയ്യുക → Next → Finish. ഈ പുതിയ column-ന് Gender എന്ന് പേര് നൽകുക.
Step 4 – TRIM ഉപയോഗിച്ച് അനാവശ്യ spaces ഒഴിവാക്കുക (Remove stray spaces with TRIM): വേർതിരിച്ചപ്പോൾ “Anjali K␣”, “␣F” എന്നിങ്ങനെ spaces അവശേഷിച്ചിട്ടുണ്ടാകും. മറ്റൊരു ഒഴിഞ്ഞ column-ൽ =TRIM(B2) എന്ന് ടൈപ്പ് ചെയ്ത് താഴേക്ക് fill ചെയ്യുക; Gender-ന് വേണ്ടി =TRIM(C2) എന്നും നൽകുക. വാക്കുകളുടെ തുടക്കത്തിലും ഒടുവിലുമുള്ള അനാവശ്യ spaces നീക്കം ചെയ്യാനും ഇടയിലുള്ള ഒന്നിലധികം spaces ഒരു space മാത്രമായി ചുരുക്കാനും TRIM സഹായിക്കും. ശേഷം ഈ helper results കോപ്പി ചെയ്ത്, യഥാർത്ഥ columns-ന് മുകളിൽ Paste Special → Values നൽകി മാറ്റിയെഴുതുക, തുടർന്ന് helper columns delete ചെയ്യുക.
Step 5 – സംഖ്യാ columns പൂർണ്ണമായും numeric ആക്കുക: Arjun-ന്റെ 35-ൽ ഒരു ചെറിയ പച്ച ത്രികോണം (green triangle) കാണാം (അതായത് number stored as text). അത് select ചെയ്ത് warning icon-ൽ ക്ലിക്ക് ചെയ്ത് Convert to Number തിരഞ്ഞെടുക്കുക. Fathima-യുടെ കാര്യത്തിൽ, “Absent” എന്നത് delete ചെയ്ത് Theory cell ബ്ലാങ്ക് (blank) ആക്കി വെക്കുക; ഒപ്പം വിവരങ്ങൾ രേഖപ്പെടുത്താൻ TheoryStatus (Present/Absent) എന്ന പേരിൽ ഒരു പുതിയ column ചേർക്കുക. ഒരു marks column-ൽ സംഖ്യകൾ മാത്രമേ ഉണ്ടാകാവൂ; മാർക്ക് ഇല്ലാതിരിക്കാനുള്ള കാരണം പ്രത്യേക column-ലാണ് വരേണ്ടത്.
Step 6 – Duplicate records നീക്കം ചെയ്യുക (Remove duplicate records): Data-യ്ക്കുള്ളിൽ എവിടെയെങ്കിലും ക്ലിക്ക് ചെയ്യുക → Data → Remove Duplicates → My data has headers എന്നത് tick ചെയ്തിട്ടുണ്ടെന്ന് ഉറപ്പുവരുത്തുക, RollNo മാത്രം തിരഞ്ഞെടുക്കുക → OK. എത്ര duplicates നീക്കം ചെയ്തു എന്ന് Excel കാണിക്കും (ഇവിടെ: 1). ഈ പ്രക്രിയ rows പൂർണ്ണമായും delete ചെയ്യുന്നതിനാൽ, Raw sheet മാറ്റമില്ലാതെ സൂക്ഷിച്ചത് എത്ര നന്നായി എന്ന് ഇപ്പോൾ മനസ്സിലാക്കാം.
Step 7 – ഒരു Excel Table ആക്കി മാറ്റുക (Convert to an Excel Table): Data-യിലെ ഏതെങ്കിലും cell-ൽ ക്ലിക്ക് ചെയ്ത് Ctrl+T പ്രസ്സ് ചെയ്യുക (അല്ലെങ്കിൽ Home → Format as Table, അല്ലെങ്കിൽ Insert → Table). Range പരിശോധിക്കുകയും My table has headers ടിക്ക് ചെയ്തിട്ടുണ്ടെന്ന് ഉറപ്പുവരുത്തുകയും ചെയ്യുക → OK. തുടർന്ന് Table Design tab-ൽ, Table Name ബോക്സിൽ അനുയോജ്യമായ ഒരു പേര് ടൈപ്പ് ചെയ്യുക (ഉദാഹരണത്തിന്, tblSem5).
Mac and older versions: Mac-ൽ shortcut എന്നത് Cmd+T ആണ്; ബാക്കി ഘട്ടങ്ങളെല്ലാം ഒന്നുതന്നെയാണ്. Excel 2010–2019 പതിപ്പുകളിൽ Table Design tab എന്നത് Table Tools-ന് കീഴിൽ Design എന്ന ലേബലിലാണ് കാണപ്പെടുന്നത്.
ശുദ്ധീകരിച്ച അന്തിമ രൂപം
| RollNo | Name | Gender | Theory | Practical | TheoryStatus |
| STA501 | Anjali K | F | 38 | 41 | Present |
| STA502 | Muhammed Rashid | M | 42 | 45 | Present |
| STA503 | Fathima Nasrin | F | 39 | Absent | |
| STA504 | Arjun P | M | 35 | 37 | Present |
| STA505 | Sneha Joseph | F | 44 | 46 | Present |
അഞ്ച് വിദ്യാർത്ഥികൾ, ആറ് variables, ഓരോരുത്തർക്കും ഓരോ row വീതം — ഇനി വരുന്ന പാഠങ്ങളിൽ sorting, filtering, totals, grades എന്നിവ തയ്യാറാക്കാൻ ഈ table തികച്ചും സജ്ജമാണ്.
നിങ്ങളുടെ ഫലം എങ്ങനെ പരിശോധിക്കാം
ഇവ table-നുള്ളിൽ നൽകുന്നതിന് പകരം മറ്റൊരു Analysis sheet-ൽ ചേർക്കുക:
| പരിശോധന (Check) | Formula | പ്രതീക്ഷിക്കുന്ന ഫലം (Expected) |
| വിദ്യാർത്ഥികളുടെ എണ്ണം | =COUNTA(Clean!A2:A6) | 5 |
| യഥാർത്ഥ സംഖ്യകളായ Theory marks | =COUNT(Clean!D2:D6) | 4 |
| യഥാർത്ഥ സംഖ്യകളായ Practical marks | =COUNT(Clean!E2:E6) | 5 |
| രേഖപ്പെടുത്തിയ Absentees എണ്ണം | =COUNTIF(Clean!F2:F6,”Absent”) | 1 |
COUNT സംഖ്യകൾ അടങ്ങിയ cells മാത്രമേ എണ്ണുകയുള്ളൂ. Theory-യിൽ 4-ന് പകരം 3 എന്നാണ് ലഭിക്കുന്നതെങ്കിൽ, ഏതെങ്കിലും മാർക്ക് ഇപ്പോഴും text രൂപത്തിൽ കിടക്കുന്നുണ്ടെന്ന് മനസ്സിലാക്കാം. വിദ്യാർത്ഥികളുടെ എണ്ണം 6 ആണെങ്കിൽ duplicate ഇനിയും നീക്കം ചെയ്തിട്ടില്ല. Raw ഷീറ്റുമായി ഒത്തുനോക്കുക: Raw-ൽ ഉണ്ടായിരുന്ന 7 data rows − 1 duplicate − 1 total row = 5 വിദ്യാർത്ഥികൾ.
ഇതിൽ നിന്ന് എന്ത് മനസ്സിലാക്കാം?
വിദ്യാർത്ഥികളുടെ പഠനനിലവാരത്തെക്കുറിച്ച് നമ്മൾ ഇതുവരെ കണക്കുകൂട്ടലുകളൊന്നും നടത്തിയിട്ടില്ലെങ്കിലും, ഈ dataset ഇപ്പോൾ വസ്തുതകൾ കൃത്യമായി വെളിപ്പെടുത്തുന്നു: ഇവിടെ 6 പേരല്ല, 5 വിദ്യാർത്ഥികളേ ഉള്ളൂ; ഒരു theory mark വിട്ടുപോയത് ടൈപ്പിംഗ് തെറ്റുകൊണ്ടല്ല, കൃത്യമായ കാരണത്തോടെയാണ് (Absent); കൂടാതെ ഓരോ മാർക്കും Excel-ന് ശരാശരി കാണാൻ സാധിക്കുന്ന കൃത്യമായ സംഖ്യകളാണ്. Raw രൂപത്തിലുള്ള Theory column ആയിരുന്നു നമ്മൾ average ചെയ്യാൻ ഉപയോഗിച്ചിരുന്നതെങ്കിൽ, duplicate കാരണം റാഷിദിന്റെ മാർക്ക് രണ്ടുതവണ കൂട്ടപ്പെടുകയും text രൂപത്തിലുള്ള “35” എന്നത് യാതൊരു മുന്നറിയിപ്പുമില്ലാതെ വിട്ടുപോവുകയും ചെയ്യുമായിരുന്നു — ഒരു മുന്നറിയിപ്പുമില്ലാതെ ക്ലാസ്സിന്റെ തെറ്റായ ശരാശരി മാത്രമാകും അപ്പോൾ ലഭിക്കുക.
വരുത്താൻ സാധ്യതയുള്ള തെറ്റുകൾ
1. ഏക കോപ്പിയിൽ നേരിട്ട് cleaning നടത്തുന്നത്: എപ്പോഴും മാറ്റം വരുത്താത്ത ഒരു Raw sheet സൂക്ഷിക്കുക; അല്ലാത്തപക്ഷം Remove Duplicates-ഉം ഡിലീഷനുകളും പിന്നീട് ഒത്തുനോക്കാൻ കഴിയില്ല.
2. Data-യ്ക്കുള്ളിൽ totals അല്ലെങ്കിൽ കുറിപ്പുകൾ അവശേഷിപ്പിക്കുന്നത്: നിങ്ങൾ sort, filter, average എന്നിവ ചെയ്യുമ്പോൾ ഈ “Total” row മറ്റൊരു വിദ്യാർത്ഥിയായി കണക്കാക്കപ്പെടും.
3. സംഖ്യകളുടെ column-ൽ “Absent”, “NA” തുടങ്ങിയ വാക്കുകൾ ടൈപ്പ് ചെയ്യുന്നത്: ആ cell ബ്ലാങ്ക് ആയി ഇടുക, കാരണം മറ്റൊരു status column-ൽ വ്യക്തമാക്കുക.
4. അടുത്ത് ഒരു ഒഴിഞ്ഞ column ഇടാതെ Text to Columns പ്രവർത്തിപ്പിക്കുന്നത്: ഇത് തൊട്ടടുത്തുള്ള data-യ്ക്ക് മുകളിലേക്ക് overwrite ചെയ്യപ്പെടാൻ ഇടയാക്കും.
5. തെറ്റായ columns അടിസ്ഥാനമാക്കി duplicates നീക്കം ചെയ്യുന്നത്: ഐഡന്റിഫയർ മാത്രം തിരഞ്ഞെടുക്കുക (RollNo); എല്ലാ columns-ഉം tick ചെയ്താൽ ഒരു space വ്യത്യാസം കാരണം duplicates ഒഴിവാക്കപ്പെടാതെ പോയേക്കാം.
പരിശീലന വ്യായാമം
ഒരു health-centre രജിസ്റ്ററിൽ: Date | Patient (Name – Ward) | Age | Visits എന്നിങ്ങനെ നൽകിയിരിക്കുന്നു. ഒപ്പം merge ചെയ്ത ഒരു heading, ആവർത്തിച്ചുവന്ന ഒരു രോഗിയുടെ row, Age cell-ൽ “unknown” എന്ന് രേഖപ്പെടുത്തിയത് എന്നിവയുമുണ്ട്. ഇതിനെ tblVisits എന്ന പേരിലുള്ള ഒരു Excel Table ആക്കി മാറ്റാനുള്ള ഘട്ടങ്ങൾ വിവരിക്കുക.
ഉത്തരം: Sheet കോപ്പി ചെയ്യുക (Raw മാറ്റമില്ലാതെ സൂക്ഷിക്കുക) → heading/blank/total rows ഡിലീറ്റ് ചെയ്യുക → ഒഴിഞ്ഞ column ചേർത്ത ശേഷം “Patient” എന്നത് – ഉപയോഗിച്ച് Text to Columns വഴി വേർതിരിക്കുക → രണ്ട് പുതിയ columns-ഉം TRIM ചെയ്ത് values ആയി paste ചെയ്യുക → Age-ലെ “unknown” നീക്കം ചെയ്ത് ഒരു AgeStatus column ചേർക്കുക → patient ID അടിസ്ഥാനമാക്കി Remove Duplicates നൽകുക (ID ഇല്ലെങ്കിൽ Date + Name) → Ctrl+T, My table has headers നൽകുക → Table Design-ൽ Table Name tblVisits എന്ന് നൽകുക → Analysis sheet-ൽ COUNTA, COUNT എന്നിവ ഉപയോഗിച്ച് പരിശോധിച്ച് ഉറപ്പുവരുത്തുക.
പ്രധാന ആശയങ്ങൾ
- Analysis-ready എന്നാൽ: ഒരു header row, ഓരോ row-വിലും ഓരോ record, ഓരോ column-ലും ഓരോ variable, ഓരോ column-ലും ഒരേ തരത്തിലുള്ള data.
- Raw സുരക്ഷിതമായി വെക്കുക: Raw മാറ്റമില്ലാതെ സൂക്ഷിക്കുക; കോപ്പി ചെയ്ത sheet-ൽ cleaning നടത്തുക; മറ്റൊരു Analysis sheet-ൽ പരിശോധനകൾ ചെയ്യുക.
- വേർതിരിക്കാനും space ഒഴിവാക്കാനും: കൂടിച്ചേർന്ന വിവരങ്ങൾ വേർതിരിക്കാൻ Text to Columns; അദൃശ്യമായ അധിക spaces ഒഴിവാക്കാൻ TRIM.
- നിശബ്ദമായി ഫലങ്ങൾ തെറ്റിക്കുന്നത്: Text രൂപത്തിലുള്ള നമ്പറുകളും സംഖ്യാ columns-ലെ വാക്കുകളും അറിയാതെ ഫലങ്ങൾ തെറ്റിക്കും — COUNT ഇവയെ കൃത്യമായി പുറത്തുകൊണ്ടുവരും.
- പൂർത്തിയാക്കൽ: Ctrl+T ഉപയോഗിച്ച് പൂർത്തിയാക്കി table-ന് അർത്ഥവത്തായ ഒരു പേര് നൽകുക.