اولین قدم برای شروع کار روی یک مجموعه داده، پیدا کردن اطلاعات غلط و بی‌ارزش در آن و حذف آنهاست. در این مطلب ابتدا با فرمت‌های رایج ذخیره‌سازی داده آشنا می‌شویم. سپس یاد می‌گیریم که چطور از چند فرمول پرکاربرد اکسل در عملیات پاکسازی داده استفاده کنیم. بعد از آن نکات مهم فرایند وارد کردن فایل‌های CSV به اکسل را مرور می‌کنیم. در آخر نیز مراحل اصلی عملیات پاکسازی و کاربرد چندین فرمول مهم در این زمینه را فرا می‌گیریم. اکثر روش‌ها و فرمول‌هایی که در این مطلب به آنها اشاره می‌شود علاوه بر اکسل در نر‌م‌افزارها و اپلیکیشن‌های مشابهی مانند Google Spreadsheets هم قابل استفاده هستند.
فرمت‌های رایج فایل داده
دولت‌ها و سایر منابع اطلاعاتی دادههای خود را به فرمت‌های متنوعی منتشر می‌کنند.
در اینجا به چند مورد از رایج‌ترین آنها اشاره می‌کنیم:
DBF: Database format
database format غالبا برای ذخیره کردن مجموعه‌های بزرگ داده و سازماندهی کردن آنها استفاده می‌شود. تعداد زیادی اپلیکیشن فایل‌های dbf را تولید می‌کنند یا امکان خواندن آنها را دارند. بعضی اوقات سازمان‌ها دادههای خام را به این شکل تهیه می‌کنند. دو اپلیکیشن رایجی که برای خواندن فایل‌های dbf استفاده می‌شوند Microsoft Access و OpenOffice هستند. Excel و Google Docs از این فایل‌ها پشتیبانی نمی‌کنند.
…
CSV: Comma separated value
فایل‌های CSV شاید بیشتر از همهی انواع دیگر فایل‌های داده از سوی دولت‌ها منتشر شده‌اند و تقریبا بوسیله‌ی هر اپلیکیشن صفحه گسترده‌ای قابل خواندن هستند. داده به صورت یک فایل متنی(text file) است که هر سلول آن با یک ویرگول از هم جدا می‌شوند و هر پاراگراف(یک یا چند خطی که با یک enter از خط یا خطوط قبلی متمایز می‌شود) یک ردیف را شکل می‌دهد.
TSV: Tab separated values
فرمت TSV یک فایل متنی دیگر است که در آن سلول‌ها به جای ویرگول با tab از یکدیگر جدا می‌شوند.
Fixed width
Fixed width هم یک نوع فایل متنی است، اما با CSV و TSV تفاوت دارد. در این نوع فایل‌ها هر ورودی با یک یا چند space جدا می‌شود تا یک شبکه‌‌ای که به خوبی هم‌تراز شده به وجود بیاید. در تصویر زیر space با کاراکترهای دیگر جایگزین شده تا شکل فایل بهتر دیده شود. Excel و OpenOffice می‌توانند این فایل‌ها را باز کنند.
XML: Extensible markup language
در XML داده‌ها به طور سلسله مراتبی و شبیه به روشی که HTML کار می‌کند مرتب می‌شود. هر ردیف داده بوسیله‌ی باز و بسته شدن تگ‌ها(tags) شرح داده می‌شود. داده‌ی موجود در هر ردیف هم توسط مجموعه‌ی تگ‌های دیگری که از عنوان ستون‌ها برداشت شده تعریف می‌شود. این فرمت برای استخراج داده برای استفاده در صفحات وب کاربرد دارد. اکسل فایل‌های XML را تا حدودی با موفقیت باز می‌کند.
JSON: JavsScript Object Notation
مانند XML، کاربرد JSON هم عمدتا در استخراج داده به منظور استفاده در صفحات وب و اپلیکیشن‌ها (بوسیله JavaScript) است. Google Refine بعضی از فایل‌های JSON را می‌تواند باز کند.
چند فرمول پرکاربرد در پاکسازی داده
CONCATENATE
وقتی داده‌ها را پاکسازی می‌کنید بهترین حالت اینست که ستون‌های داده را به وسیله یک ردیف که به آن ردیف‌عنوان(header row) گفته می‌شود برچسب‌گذاری(عنوان‌گذاری) کنید. بعضی منابع، داده‌هایی را منتشر می‌کنند که ۲، ۳، ۴ و یا ۵ ردیف‌عنوان دارند. اگر دادههای شما تعداد کمی ستون داشته باشد خیلی آسان می‌توانید با cut و paste کردن، آنها را یکی کنید اما وقتی تعداد ستون‌ها زیاد باشد باید به دنبال راه دیگری بروید.
فایل data-cleaning.xls را دانلود و باز کنید. در این فایل سه عنوان‌ردیف و به دنبال آنها سه ردیف خالی وجود دارد.
در سلول D4 تایپ کنید: (CONCATENATE(D1,D2,D3=
این تابع همهی متن‌‌ها را بر می‌دارد و به این صورت در یک سلول قرار می‌دهد: Violentcrimerate
ما نیاز داریم تا بین کلمه‌ها فضای خالی ایجاد کنیم تا متن، قابل خواندن بشود. برای این کار ” “& (یک space بین دو دبل کوتیشن) قرار می‌دهیم. فرمول به این صورت تغییر پیدا می‌کند: (CONCATENATE(D1&” ” ,D2&” “,D3= تا بین کلمه‌ها فضای خالی به وجود بیاید.
دو راه برای اعمال فرمول در سلول‌های دیگر یک ردیف وجود دارد. راه اول اینست که روی سلولی که فرمول در آن نوشته شده کلیک کنید و ماوس را به روی گوشه‌ی پایین سمت چپ ببرید تا نشانگر به یک علامت جمع سیاه‌رنگ تبدیل شود.
حالا کلیک کنید و علامت جمع را به سمت چپ(یا راست) بکشید.
وقتی ماوس را رها می‌کنید، فرمول برای هر ستون اعمال می‌شود. به هشدارهای خطا توجه نکنید. در ادامه به آنها خواهیم پرداخت.
برای اینکه فرمول را برای کل ردیف اعمال کنید، ابتدا سلول A4 را انتخاب کنید، سپس کلید شیفت(Shift) را نگه دارید و T4 را انتخاب کنید. با این کار کل ردیف انتخاب می‌شود. حالا کلید کنترل(Ctrl) را همراه با R نگه دارید. با این کار فرمول برای تمام سلول‌های انتخاب شده تا اولین سلولی که فرمول دارد لحاظ می‌شود.
نکته: اگر می‌خواهید همین کار را برای یک ستون انجام دهید کلید کنترل(Ctrl) را همراه با D نگه دارید. در این حالت بالاترین سلول باید فرمول داشته باشد.
CONCATENATE کاربردهای زیادی دارد از جمله ترکیب ستون‌هایی از داده به یک ستون واحد. به عنوان مثال می‌توان آدرس‌هایی که هر یک از قسمت‌های آن مثل استان، شهر و خیابان در ستون‌های جداگانه قرار گرفته‌اند را در یک ستون نوشت.
TRIM
به سلول A1 نگاه کنید، خالی است. خالی بودن بعضی از سلول‌ها می‌تواند پیامدهایی داشته باشد. به سلول A4 توجه کنید، ما بوسیله‌ی فرمول قبلی دو space به دو سلول خالی اضافه کردیم و حالا قبل از عنوان Year دو space اضافی وجود دارد. اکسل این امکان را به ما می‌دهد که با استفاده از تابع TRIM آنها را از بین ببریم. TRIM به دنبال فضاهای خالی موجود در آغاز و پایان هر سلول می‌گردد و آنها را حذف می‌کند. در سلول A5 تایپ کنید: (TRIM(A4=
وقتی مشاهده کردید که فضاهای خالی از بین رفته‌اند، سلول A5 را انتخاب کنید و سپس برای انتخاب ردیف مورد نظر، کلیدهای Ctrl ،Shift و فلش راست را نگه دارید. (یا همزمان روی سلول T5 کلیک کنید و کلید Shift را نگه دارید). برای اعمال فرمول کلیدهای R و Ctrl را فشار دهید و نتیجه را ملاحظه کنید.
در اکسل شما این امکان را دارید که چند فرمول را با هم ترکیب کنید. در این مورد می‌توانید TRIM و CONCATENATE را با هم ترکیب کنید تا در زمان هم صرفه‌جویی شود:
((TRIM(CONCATENATE(A1&” “,A2&” “,A3=
Paste Special
در حال حاضر ما یک عنوان‌ردیف با قالب درست داریم که نیاز است تا ردیف‌های اضافه‌ی آن از بین برود. اگر به صورتی که در تصویر می‌بینید آنها را حذف کنیم با پیام خطا روبرو می‌شویم. این پیام به این دلیل است که عنوان‌ردیف ما از توابع استفاده می‌کند و این توابع هم به دنبال سلول‌هایی می‌گردند که دیگر وجود خارجی ندارند.
ما این مشکل را با یک جادوی کوچک به نام Paste Special دور می‌زنیم.
برای انتخاب کل ردیف ۵ روی برچسب ردیف ۵ کلیک کنید. آن ردیف را copy کرده، سپس روی برچسب ردیف ۶ کلیک کنید و آن را انتخاب کنید. حالا به منوی Edit بروید و Paste Special را انتخاب کنید. یک پنجره با دسته‌ای از گزینه‌های مختلف باز می‌شود. در بخش Paste گزینه‌ی Values را انتخاب کنید.
روی OK کلیک کنید، مقادیر(values) در سلول‌ها paste می‌شوند. Paste Special معادل فرمان Paste Values در Google Spreadsheets است. حالا توابع، دیگر وجود ندارند و می‌توانید ردیف‌های ۱ تا ۵ را حذف کنید. پنج ردیف اول را انتخاب کنید و به منوی Edit بروید و Delete را انتخاب کنید.
وارد کردن فایل‌های CSV به اکسل
روند یکسانی برای وارد کردن هر نوع از فایل‌های متنی محدود شده یا با عرض ثابت به اکسل یا OpenOffice وجود دارد. در اینجا قصد داریم تا با فایل CSV کار کنیم.
فایل data-import.csv را دانلود کنید.
یک صفحه گسترده‌ی جدید باز کنید و منوی File بروید و Import را انتخاب کنید. یک پنجره‌ی جدید باز خواهد شد. CSV را انتخاب کنید و روی Import کلیک کنید.
فایلی که دانلود کرده‌اید را پیدا کنید و آن را انتخاب کنید. پنجره‌ی import wizard باز می‌شود. مطئمن شوید که Delimited انتخاب شده است و بر روی Next کلیک کنید.
اطمینان پیدا کنید که فقط Comma در بخش Delimiters انتخاب شده است و سپس بر روی Next کلیک کنید.
حالا پنجره‌ی جدیدی باز می‌شود که در آن عملیات مهمی صورت می‌گیرد. روی دکمه‌ی Advanced کلیک کنید و خاطر جمع شوید که Decimal separator روی ممیز(.) و بخش Thousands separator خالی باشد. اگر thousands separator را تغییر ندهید اکسل ویرگول اضافه می‌کند و در صورتیکه بخواهید یک فایل خروجی کامل به فرمت CSV داشته باشید، مجبور خواهید شد که ویرگول‌ها را حذف کنید.
حالا به گزینه‌های Column data format نگاه کنید. General به طور پیش فرض انتخاب شده و برای همهی ستون‌ها اعمال شده است. شما می‌توانید نتیجه‌ی آن را در Data preview ببینید.
حالت general در تشخیص متن و عدد خیلی خوب عمل می‌کند. استثنای مهم وقتی است که ستون‌های متنی از اعداد تشکیل شده باشند. صفحه‌ی پیش‌نمایش را بالا و پایین کنید تا County FIPS را ببینید. “۰۰۱” کد فدرالی استاندارد برای شناسایی Baker County در Oregon است. وقتی که اعداد به اکسل وارد می‌شوند، صفرهای اضافی آنها حذف خواهند شد. در نتیجه‌ی تنظیمات General در ستون ایالت(State) صفرها از قلم افتاده و “.۱” نشان داده می‌شود. ستون County بدون صفرها، دیگر نمی‌تواند با فایل نقشهی Census GIS متصل(join) شود. این موضوع حفظ صفرهای ابتدایی را بسیار بااهمیت می‌کند، بنابراین روی ستون County کلیک کنید و Column data format را به Text تغییر دهید. حالا اکسل، صفرها را هنگام وارد کردن فایل حفط می‌کند.
شما همچنین می‌توانید یک ستون را انتخاب و آن را Skip کنید(کنار بگذارید). این امکان برای مجموعه دادههای بسیار بزرگ ارزشمند است ولی بهتر است هر چیزی که امکان دارد را وارد(Import) کنید. روی Finish کلیک کرده و فایل را ذخیره کنید.
نکات مهم در پاکسازی داده
مهمترین نکته در پاکسازی داده اینست که همیشه روی نسخه‌ی کپی کار کنید. پس ابتدا یک فایل کپی ذخیره کنید. در آغاز با مراحلی شروع می‌کنیم که نیاری به توابع ندارند.
ردیف‌های خالی را حذف کنید
برای انتخاب یک ردیف روی برچسب(شماره)آن کلیک کیند. حالا کلید shift یا کلیدهای کنترل را برای انتخاب چند ردیف نگه دارید. سپس کلیک راست کرده و Delete را انتخاب کنید.
سلول‌هایی که حاوی توضیحات و … هستند را پاک کنید
کاراکترهای ویژه را حذف کنید
کاراکترهای ویژه شامل &،!،~ و تگ‌های html می‌شوند. با استفاده از دستور Find آنها را پیدا کنید و بوسیله‌ی فرمان Replace با کارکترهای دیگر جایگزین کنید. اگر شک دارید، آنها را با خط ربط(-) یا زیرخط(_) جایگزین کنید. شما همچنین می‌توانید بخش Replace را خالی بگذارید تا کاراکترهای مورد نظرتان حذف شوند.
املای کلمه‌ها را بررسی کنید
املای کلمات اهمیت خاص خود را دارد بخصوص زمانی که می‌خواهید داده‌هایتان را با یک فایل نقشهی GIS مرتبط کنید. یک اشتباه می‌تواند همه چیز را دچار اشکال کند. به حروف بزرگ(در الفبای انگلیسی و …) و مخفف‌ها هم توجه ویژه‌ای داشته باشید تا در کار شما خللی ایجاد نشود.
در سلول‌های عددی Format را تعیین کنید
اگر می‌خواهید داده‌ی پاکسازی شده‌ی خود را به صورت یک فایل CSV، استخراج(export) کنید باید تمام ویرگول‌ها را از سلول‌هایی که شامل عدد می‌شوند پاک کنید. اگر این کار را انجام ندهید ویرگول‌ها باعث می‌شوند که در داده‌ی شما سلول‌های اضافی ایجاد شود.
سلول‌هایی که نیاز به تغییر دارند را انتخاب کنید. در منوی Format بروید و Cells را انتخاب کنید تا پنجره‌ای جدید باز شود. روی تب Numbers کلیک کنید و در بخش Number ،Category را انتخاب کنید. تعداد اعداد اعشاری را تعیین کنید. مطمئن شوید گزینه‌ی “Use 1000 separator” انتخاب نشده است و سپس دکمه‌ی OK را فشار دهید.
کل داده را copy و در یک صفحه‌ی جدید paste کنید
ستون‌ها و ردیف‌های بی‌اهمیت را حذف کنید تا مجموعه داده کوچکتر شود
دادههای خود را با کمک توابع دستکاری کنید
این مرحله را برای آخر کار نگه دارید. با این روش شما فقط روی داده‌هایی که لازم دارید عملیات مورد نیاز را انجام می‌دهید. داده‌ی حاصل را به یک صفحه‌ی جدید منتقل کنید. با استفاده از دستور Paste Special مقادیر را باقی نگه دارید و فرمول‌ها را حذف کنید.
از صفحه‌ی داده‌ها به صورت یک فایل CSV خروجی بگیرید
مراحل کاری پیشنهادی
- اگر فایل CSV را وارد می‌کنید، صفرهای پیش از اعداد را حفظ کنید
- مطمئن بشوید که داده به درستی وارد(import) شده است و ستون‌های داده با عنوان‌هایشان تطابق دارد
- فورا یک نسخه کپی از داده‌هایتان ذخیره کنید
- ردیف‌های خالی را حذف کنید
- عنوان ستون‌ها را در یک ردیف قرار دهید
- تمام ستون‌های عددی را طوری format کنید که ویرگول‌ها حذف شوند
- کاراکترهای ویژه(&, ! , ~ و …) را پیدا و جایگزین کنید. اگر شک دارید به جای آنها space، زیر خط(_) یا خط پیوند(-) قرار دهید
- املای کلمات را بررسی کنید.(بخصوص در ستون‌هایی که قصد دارید با فایل‌های دیگر مرتبط کنید)
- حروف بزرگ(در الفبای انگلیسی و …) را بررسی کنید(بخصوص در ستون‌هایی که قصد دارید با فایل‌های دیگر مرتبط کنید)
- کلمات اختصاری(مخفف) را بررسی کنید(بخصوص در ستون‌هایی که قصد دارید با فایل‌های دیگر مرتبط کنید)
- حاصل‌جمع‌ها را از دادههای اصلی(حداقل به اندازه‌ی یک ردیف یا ستون) جدا نگه دارید تا بتوانید صحت آنها را مجددا بررسی کنید. این کار باعث می‌شود تا داده‌هایتان دست‌نخورده باقی بماند.
- در آخر دستکاری در ستون‌ها را انجام دهید.
- آنچه نیاز ندارید را حذف کنید.
فرمول‌های دم‌دستی
تابع |
عملکرد |
فرمول |
| گرفتن داده یا اعمال تابع روی داده‌ای در صفحه‌ی متفاوت | Sheet1!A1= | |
| اعمال یک تابع بر روی یک ستون(نگه داشتن Shift و همزمان انتخاب سلول‌ها – تابع باید در بالاترین سلول قرار گرفته باشد) | Control-d | |
| AVERAGE | محاسبه‌ی میانگین | (AVERAGE(A2:A10= |
| MEDIAN | محاسبه‌ی میانه | (MEDIAN(A2:A10= |
| محاسبه‌ی درصد(A2 عدد کوچکتر است) | A2/B2*100= | |
| محاسبه‌ی تغییر درصدی(B2 عدد جدید و A2 عدد قدیمی است) | B2/A2)-1)*100))= | |
| EXACT | بررسی یکسان بودن مقادیر سلول‌ها | (EXACT(A2:A10= |
| COUNTIF | شمارش تعداد سلول‌هایی که مطابق شرایط داده شده باشند(برابر بودن با یک عدد یا متن، در یک محدوده قرار گرفتن) | (COUNTIF(A2:A10, 705= |
| TRIM | حذف space از ابتدا و انتهای متن | (TRIM(A2:A10= |
| CLEAN | حذف تمامی کاراکترهای غیرقابل‌چاپ از متن | (CLEAN(A2:A10= |
| CONCATENATE | اتصال دو سلول متنی به هم و تبدیل آنها به یک سلول متنی (با قرار دادن ” “& بعد از A2 می‌توانید یک فاصله بین دو متن ایجاد می‌کند) | (CONCATENATE(A2, B2= |
| LOWER | تبدیل همهی حروف متن به حروف کوچک | (LOWER(A2:A10= |
| UPPER | تبدیل همهی حروف متن به حروف بزرگ | (UPPER(A2:A10= |
| PROPER | تبدیل حرف اول متن به حروف بزرگ | (PROPER(A2:A10= |
| FIXED | گرد کردن اعداد اعشاری(اگر پارامتر دوم منفی باشد دهگان و صدگان و … گرد می‌شود) و ارائه‌ی آن به صورت مقدار متنی | (FIXED(E2,1= |
| MID | جدا کردن یک یا چند کاراکتر(از مکان مشخص شده) از یک مقدار متنی | (MID(A2, 1,5= |
| RIGHT | جدا کردن یک یا چند کاراکتر از سمت راست یک مقدار متنی | (RIGHT(A2,5= |
| LEFT | جدا کردن یک یا چند کاراکتر از سمت چپ یک مقدار متنی | (LEFT(A2,4= |
| REPLACE | حذف قسمتی از یک متن و جایگزین یا اضافه کردن متن جدید | (“REPLACE(A2,6,5,”4= |
| SUBSTITUTE | پیدا کردن بخشی از متن سلول و جایگزین کردن آن با متن جدید | (“SUBSITU(A2,”Ore”,”OR= |
