डेटा क्लीनिंग डेटा विश्लेषण और गणना के लिए महत्वपूर्ण है और यह डेटा तैयारी और फॉर्मेटिंग का पहला चरण है। डेटा क्लीनिंग का अर्थ है असंगतियों, त्रुटियों और अवांछित फॉर्मेटिंग को हटाना। Microsoft Excel का Power Query और Google Sheets के REGEX फंक्शन दोनों ही एडवांस्ड क्लीनिंग के लिए शक्तिशाली टूल हैं। आइए जानते हैं कि Power Query और Google Sheets के REGEX फंक्शन का उपयोग एडवांस्ड डेटा क्लीनिंग के लिए कैसे करें और इनकी तुलना करें।
Excel का Power Query
Power Query Excel का एक अंतर्निहित फीचर है जो डेटा ट्रांसफॉर्मेशन और क्लीनिंग के लिए यूजर-फ्रेंडली इंटरफेस प्रदान करता है। यह बिना किसी जटिल फॉर्मूला के डेटा को क्लीन और फॉर्मेट करता है। Power Query विभिन्न स्रोतों से डेटा इम्पोर्ट करता है और मजबूत डेटा कनेक्टिविटी प्रदान करता है। इसमें जटिल डेटा-क्लीनिंग कार्यों को करने के लिए लचीले फीचर्स हैं।
Google Sheets के REGEX फंक्शन
Google Sheets के REGEX फंक्शन टेक्स्ट डेटा में पैटर्न खोजने के लिए रेगुलर एक्सप्रेशन का उपयोग करते हैं, जो टेक्स्ट एंट्री को पार्स करने, फॉर्मेट करने या मान्य करने के लिए आदर्श है।
- REGEXREPLACE: निर्दिष्ट टेक्स्ट के साथ रेगुलर एक्सप्रेशन से मेल खाने वाले टेक्स्ट को बदलता है।
- =REGEXREPLACE(text, regular_expression, replacement)
- REGEXMATCH: जांचता है कि क्या टेक्स्ट स्ट्रिंग निर्दिष्ट रेगुलर एक्सप्रेशन से मेल खाती है।
- =REGEXMATCH(text, regular_expression)
- REGEXEXTRACT: रेगुलर एक्सप्रेशन के आधार पर टेक्स्ट से मिलान करने वाले सबस्ट्रिंग निकालता है।
- =REGEXEXTRACT(text, regular_expression)
ये फंक्शन डेटा क्लीनिंग, पैटर्न रिकग्निशन और डायनामिक टेक्स्ट मैनिपुलेशन के लिए उपयोगी हैं।
आइए एक रॉ डेटासेट पर विचार करें जिसमें गलत फॉर्मेटिंग, अवांछित वर्ण, त्रुटियां, ब्लैंक स्पेस आदि हैं। हम Excel के Power Query और Google Sheets के REGEX फंक्शन का उपयोग करके एडवांस्ड डेटा क्लीनिंग दिखाएंगे और उनकी तुलना करेंगे।
अवांछित वर्णों को हटाना
पैरेंथेसिस, डैश या स्पेस जैसे अवांछित वर्णों वाले फोन नंबरों के डेटासेट को क्लीन करें।
Power Query
- डेटा रेंज चुनें।
- Data टैब पर जाएं >> From Table/Range चुनें।
- Power Query एडिटर खुलेगा। कॉलम चुनें। हमने Phone Number कॉलम चुना।
- Transform टैब पर जाएं >> Replace Values चुनें।
- Replace Values डायलॉग बॉक्स खुलेगा:
- Value to Find बॉक्स में: अवांछित वर्ण ((, ), -, ) व्यक्तिगत रूप से दर्ज करें।
- Replace with बॉक्स में: इसे खाली रखें या यदि आपके पास बदलने के लिए कोई मान है तो उसे डालें।
- OK पर क्लिक करें।
- क्लीन डेटा को Excel में वापस लाने के लिए Close & Load चुनें।
Google Sheets का REGEXREPLACE फंक्शन
Google Sheets में REGEXREPLACE फंक्शन का उपयोग करके डेटा को फॉर्मेटिंग के साथ क्लीन करें। सेल G2 में निम्न फॉर्मूला डालें:
=ARRAYFORMULA(IF(LEN(REGEXREPLACE(D2:D6, "[^0-9]", ""))=10, "(" & MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 1, 3) & ") " & MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 4, 3) & "-" & MID(REGEXREPLACE(D2:D6, "[^0-9]", ""), 7, 4), "Invalid"))
यह फॉर्मूला सभी नॉन-न्यूमेरिक वर्णों को हटा देगा। फिर यह जांचेगा कि क्लीन नंबर में ठीक 10 अंक हैं या नहीं। यदि वैध है, तो इसे (XXX) XXX-XXXX के रूप में फॉर्मेट करेगा; अन्यथा "Invalid" लौटाएगा।
Google Sheets का REGEXREPLACE फंक्शन (सामान्य क्लीनिंग)
अनावश्यक विशेष वर्णों को क्लीन करने के लिए निम्न फॉर्मूला उपयोग करें:
फॉर्मूला:
=REGEXREPLACE(D2, "[^a-zA-Z0-9]", "")
यह [^a-zA-Z0-9] पैटर्न किसी भी ऐसे वर्ण को हटा देता है जो अक्षर या अंक नहीं है।
यदि आप रेगुलर एक्सप्रेशन से परिचित हैं, तो आप REGEXREPLACE फंक्शन का उपयोग कर सकते हैं, यह एक ही फॉर्मूला में अक्षरों या अंकों को छोड़कर किसी भी वर्ण को हटा देता है।
टेक्स्ट केस को मानकीकृत करना
डेटासेट में मिश्रित टेक्स्ट हो सकता है; आप सभी टेक्स्ट एंट्री को लोअरकेस या टाइटल केस में बदल सकते हैं।
Power Query
डेटा रेंज चुनकर और Data टैब >> From Table/Range पर जाकर Power Query खोलें।
- ट्रांसफॉर्म करने के लिए टेक्स्ट वाला कॉलम चुनें।
- Transform टैब पर जाएं >> Capitalize Each Word चुनें।
Google Sheets PROPER फंक्शन
Google Sheets में REGEX का उपयोग करके प्रत्येक शब्द को कैपिटलाइज़ करने का कोई सीधा तरीका नहीं है, क्योंकि Google Sheets का REGEXREPLACE रेगुलर एक्सप्रेशन पैटर्न या रिप्लेसमेंट में सीधे UPPER और LOWER फंक्शन को सपोर्ट नहीं करता। आप PROPER, UPPER, या LOWER जैसे बाहरी फंक्शन का उपयोग कर सकते हैं।
यह फॉर्मूला सुनिश्चित करता है कि प्रत्येक शब्द कैपिटलाइज़ हो, और गैर-पहले अक्षर लोअरकेस में बदल जाएं।
डिलीमीटर द्वारा डेटा विभाजित करना
आप किसी भी मर्ज किए गए टेक्स्ट या नामों को डिलीमीटर द्वारा अलग-अलग कॉलम में विभाजित कर सकते हैं। आइए Power Query और Google Sheets के REGEX फंक्शन का उपयोग करके डेटा को क्लीन करें।
Power Query
डेटा रेंज चुनकर और Data टैब >> From Table/Range पर जाकर Power Query खोलें।
- नाम वाले कॉलम को चुनें।
- Home टैब पर जाएं >> Split Column से By Delimiter चुनें।
- Split Column by Delimiter डायलॉग बॉक्स में:
- Select and enter delimiter: में Space( ) चुनें।
- OK पर क्लिक करें।
- परिणामी कॉलम का नाम बदलकर "First Name" और "Last Name" करें।
Google Sheets REGEXEXTRACT फंक्शन
डेटा को डिलीमीटर द्वारा विभाजित करने के लिए REGEXEXTRACT फंक्शन का उपयोग करें। अपने चुने हुए सेल में निम्न फॉर्मूला डालें:
=REGEXEXTRACT(E9, "^([^ ]+) (.+)$")
यह फॉर्मूला अंतिम नाम और पहले नाम को दो सेल में अलग करता है। आवश्यकतानुसार अतिरिक्त कॉलम का उपयोग करता है।
Power Query बनाम Google Sheets REGEX: कब उपयोग करें
Power Query बेसिक से एडवांस्ड डेटा क्लीनिंग के लिए सबसे उपयोगी है। यह यूजर-फ्रेंडली इंटरफेस के साथ व्यापक, संरचित डेटा ट्रांसफॉर्मेशन के लिए आदर्श है। यह जटिल डेटासेट, संरचित जॉइन और डेटा मर्जिंग के साथ काम करते समय कुशल है।
Google Sheets REGEX फंक्शन त्वरित टेक्स्ट-आधारित ऑपरेशन और व्यक्तिगत सेल में विशिष्ट टेक्स्ट निष्कर्षण के लिए उत्तम हैं। यदि आप रेगुलर एक्सप्रेशन सिंटैक्स से सहज हैं तो REGEX फंक्शन विशेष रूप से सहायक हैं।
निष्कर्ष
Power Query और Google Sheets के REGEX फंक्शन डेटा क्लीनिंग के लिए पूरक टूल प्रदान करते हैं। Power Query बड़े डेटासेट पर बैच ट्रांसफॉर्मेशन चाहने वाले Excel यूजर्स के लिए आदर्श है, जबकि Google Sheets REGEX फंक्शन छोटे डेटासेट में टेक्स्ट-आधारित ऑपरेशन के लिए लचीलापन प्रदान करते हैं। आप दोनों का प्रभावी ढंग से उपयोग शक्तिशाली, सुव्यवस्थित डेटा क्लीनिंग के लिए कर सकते हैं! दोनों टूल को समझकर, आप Excel या Google Sheets में अधिकांश क्लीनिंग कार्यों को संभाल सकते हैं!
निःशुल्क एडवांस्ड Excel अभ्यास समाधान के साथ प्राप्त करें!
