Showing posts with label Education. Show all posts
Showing posts with label Education. Show all posts

Wednesday, June 17, 2020

Data Cleansing Tips using Excel


When you import data from different sources, you would like to do some initial assessment of the data and ensure it is cleansed. This is a preparatory step before deciding further statistical analysis. Here, I describe 6 important functions that are quite useful in data cleansing and understanding. 

Empty data

How do you remove the empty rows?  These can be a nuisance when you would like to do further operations on the data set. When you plan to apply regression and other modelling techniques, the empty cells can play truant.  

#1 – Find the empty cells of data. In the Toolbar Ribbon, go to “Find & Select” / “Go to Special”. Choose “Blanks”. It will highlight the blank rows.  

#2 – Fill or delete the empty cells. You can fill them up with any text. Once such cells are highlight (as a result of the earlier step), type the text you want and press CTRL + ENTER. All the empty cells will be filled up with whatever you have typed. If not, you may have to do this for every such empty cell. IF you want to delete, just right click and choose the option of “delete rows.” 

Duplicate data

#1 – Check for duplicate rows - Some transactions could be repeated accidentally. We need to ensure that the data set contains no duplicates.  Check for existence of such rows of data by using conditional formatting. Under that option, choose “Duplicate values” under “Highlight cell rules”. Of course, you can select any desired formatting. 

#2 – Delete the duplicate rows. For this, choose the relevant option under “Data”. You need to select which column on which this removal of duplicates will apply. 

Cleaning data

It is likely that some special characters might creep into the data when you are getting it from multiple sources and that too after some conversion. The data might come from application resulting in such special characters. 

Simply use the CLEAN function. For example, if the text is in cell B4, type in B5, CLEAN(B4). 

Remove unwanted spaces

Even after doing all these, there could be multiple spaces in between texts or at the beginning or at end. These are not removed by CLEAN function. For this, Excel gives us a beautiful function called TRIM. What does this do? This removes all multiple spaces and just keeps one space in between the words. Moreover, if there any leading spaces at the start of the data, that goes out. The same treatment is applied for the trailing spaces after the last word. 

Bring order

Especially for text fields, it may not be in a proper case. Upper and lower cases may have been used randomly. We have some beautiful functions to change the case. UPPER converts the text to full upper case; LOWER() changes to full lower case and PROPER() capitalizes the first alphabet of each word. Excel diligently applies this to texts embedded in numbers too. For example, a text in a cell “567BudGet” becomes 567Budget after PROPER() is applied. Unfortunately, in excel, we don’t have straightforward option for changing case or converting to Sentence case (that capitalizes only the first letter of the first word). For this, however, macros are available. 

Conditional Formatting 

I am sure you have tried using conditional formatting feature. I am a fan of it and use it all the time to focus on key data/rows. Few features you can apply on the data set (after cleansing it) to get a glimpse of outliers / exceptions are: 

  • Highlight cell values (that are greater or less than the average). For this, you can enter a custom formula using AVERAGE. 
  • Highlight the entire row values (meeting certain conditions). For this select the entire rows before defining your formula. 

You may want to do some selective data updates or cleansing on such rows before attempting further modelling. 

Good luck.

Tuesday, May 28, 2019

The conundrums facing my son, a 12th grade student in India


I just step into my son's room. He resembles a zombie nowadays. His hair is rumpled; He looks dazed; He wakes up constantly in the middle of the nights to murmur some gobbledygook; You guessed it right, he is a student of grade 12, having completed the board exam, trying to navigate the next steps to chart a path into the world of tertiary education.

Need of the hour is authentic information on colleges, courses, research quality, etc. available freely on the Internet. Every university proclaims #1 ranking in some aspect with 100% placement in all the courses! 

Universities abroad feel an obligation to the prospective students in terms of information sharing. In UK for instance, there is a is a clear compendium of complete university guide available on the Internet. Various rankings and the methodology behind it, student – teacher ratio, alumni information, academic calendar, courses offered, faculty details and even course and hostel fees details are published. These are validated data.

This aside, the whole process of applying to the colleges itself is cumbersome and could well do with some fine tuning. It has become a source of revenue for many institutions. Filling up application forms with same information in different formats and making multiple photocopies of all required certificates is the next task looming ahead of me.  Colleges have designed application forms to suit their admission process. They verify all the information and documents collected. Instead the education boards can be authorized to capture and authenticate the details in one place and share with the universities. Many countries have switched over to a common application for the prospective undergraduate students. This service is provided by a government body that sends out the information to the universities. At one stroke, it saves lot of time and removes duplication of work.  This, in no way, takes the right of the institutes to ask for more specific information.

Then comes the innumerable entrance tests. A common aptitude test can be the basis for admission to a large number of UG programs. Streams like medical, law, engineering, architecture require their own tests like JEE, NEET etc. We have government as well as private institutions administering tests. Why can't the government arrive at a master list of 4 or 5 tests for admission to the UG programs in India? That way, a student will be required to take only the appropriate tests depending on the course of his desire. If it is engineering, there should be one test that is used by every university in India. Some colleges administer tests and interviews at their campuses only, thereby including a travel agenda for the outstation student and parents.  Instead, all tests can be administered by a central body like NTA (National Testing Agency) in a flexible manner over a longer duration. Imagine if this is offered online multiple times. For example, NEET can be administered online in all district headquarters from April to June 3 times. The students can appear multiple times or once as per his/her will. The maximum distance for travel will be to the nearest district office. On top of it, if the scores can be made available to the institutes directly, think of the benefits to our student community. The institutions also stand to benefit as they get validated scores from authorized bodies of government. My imagination is running riot here!!!

Let me step back into reality. My calendar and his are full for the next few weeks with filling forms, making photocopies, arranging for demand drafts, appointments at colleges for application submission and booking train tickets for tests in different cities. This is like a full-fledged project work! But that alone is not enough.

Success is when tests and interviews are cracked and admission secured! Who slogs and actually faces the acid tests? Oh! In the entire melee, I have forgotten that. Pardon me, I have to rush to motivate my son!

Tuesday, April 16, 2019

Should we certify the quality at "exit"?


In today’s India, not a day goes by without a comment on NEET (the entrance exam for medical courses) or any such entrance exam. If some student meets with an unfortunate end citing pressures, blame it on the entrance exam. Going by that logic, we should scrap all exams!  Education belongs to the centre – state area and is used or misused by both. Some states think their board education system is the best and they have the right to decide how they would admit their students. Other states follow an entrance examination. In a big country like ours and given our landscape and rural population, it is only right that all these factors are considered.

The problem arises when these factors are used to label or certify a person for his/her skills. Our focus should be on helping less fortunate amongst us and bringing them up to a nationally or globally recognizable standard. The second challenge is that we have guaranteed the right for any Indian citizen to live anywhere in the country. When a qualified person moves from one domicile to another, he or she carries the qualifications, skills and capabilities too. These should be transferable across India whether they refer to plumbing or medicine or engineering.

The first step is to define the associated capabilities/skills both technical and non-technical clearly.


This is not dissimilar to the learning outcomes. By the word “doctor”, we assume the person has certain capabilities. These do not arise because the person has passed 50 courses in medicine related subjects or has studied from a top medical college. It comes with a certain level of mastery of specific subject areas and/or associated experience. These areas can be studied as two subjects in certain universities or 4 subjects. That doesn’t matter. By listing all the capabilities, we have many advantages. The subject areas can be modified/updated depending on the way the profession evolves. The people, pursuing this profession, have clear ideas on the outcome. It also brings in consistent nomenclature to be used throughout the country. We have colleges, in India, that award “Metallurgical engineering”; “Metallurgical engineering and material science”; “Material science and metallurgical engineering”. No one knows how the capabilities of each differ. This is, certainly, not a diversity to be celebrated!

Once we do this, what is next? How do we screen the candidates for undergoing a course that will help them to acquire the said capabilities? Who gets to decide a 4-year course of university is better suited to this than the 5-year course of another university?  We don’t have standards for comparing universities by each degree or course rigorously. Where do the different state boards of education stand as compared to CBSE? To top it further, can a 100% marks secured in Mathematics of TN state board be considered equivalent to a 100% in West Bengal board? No, we will need far more mechanisms to do that.

We should aspire to set up validation mechanisms that compare different boards, courses and educational institutes against certain specific outcomes based on transparent parameters. The government owes this to the entire student community. The NIRF ranking is a good start. Consistent and verifiable display of information from each university is to be made mandatory. This is akin to display of credentials of every contestant in an election thanks to the electoral reforms. Today, if we look at the newspaper, the only parameter advertised by the institutes is the placement record. The student community knows not whether these claims are true. Every school boasts of its infrastructure and faculty. Look at the plight of students. How will they gather right information and make decisions? This is a subject that can be discussed on its own merits. But, right now, instead of getting into such level of details, why don’t we start with validating the quality at the exit gate? An example is a professional qualification like CA or CWA. They define the body of knowledge, type of exams to be written, quantum of practice or experience before someone can be said to have acquired this qualification. A doctor has multiple skills/capabilities to achieve before getting the official qualification and associated perks. That testing of the skill can come in the form of exams, interview, internship, practice at government hospitals etc. No matter which universities they study, they should sit for this prescribed procedure for acquiring qualification. The best thing is that these validation mechanisms are and should be only based on merit and be fool proof to tamper. Another useful option that can be evaluated is whether to make a minimum period of service in a rural area mandatory as part of the validation or immediately post-validation. This, if conceived and implemented well, will be equivalent to “Matching” that happens in the US and remove the disparities amongst the aspiring students when applying for post graduate courses.

In India, we have reservations in place to help disadvantaged people. It is very likely that these will be perpetual for no party dares to tweak it. Some states have high level and some others have medium level. Whatever they are, the respective state governments are free to decide on their applicability and choosing of students for various courses in their state. But, this will not come in when sitting for validation procedure. There is no reservation or nor a rank list. It is simply a formal and complete evaluation of the person as per the definition for acquiring the title or capability.  A doctor with a 90% marks in his final exams and another one with 75% marks, can practise medicine so long as they have successfully cleared the validation procedure. Imagine next time you visit a doctor, you don’t need to dissect his pedigree before deciding to trust yourself with him or her. Or we won’t have the problem of producing engineers who don’t know how to operate a machine or handle important tool sets!

Such a hands-off approach on the study and admission procedures to a clear well -articulated tight exit validation mechanisms are the need of the hour. We have the bar council, medical council and others who are more interested in the duration of course, facilities etc. There is nothing wrong in doing so. These can be guidelines issued and proper inspection agencies can verify the claims of universities periodically. But they should safe-guard the exit! Such a validation agency should be set up by the centre as an independent body. This body should have access to experts in various fields to set the standards of validation of knowledge and make it relevant and up-to-date.

Such a clean and professional validation body can benefit the nation as well as the aspirants in many ways. Firstly, it presents a uniform, consistent and comparable qualification mechanism. The would-be consumers can avail the services reasonably confident of the quality. Secondly, over a period of time, these skills can be made highly comparable to the western standards. Even if not, the gaps or differences can be understood clearly. For example, an Indian doctor needs to pass certain exams before getting approval to practise in the UK or US. What are these set of exams? What is that gap in the body of knowledge which these countries regard important? Answers to these will help our validation body to come out with recommendations, hold parleys with other agencies in the west and provide a clear direction in terms of upgrade or a country specific additional part as the case may be. Thirdly, the validated credentials serve as a reference/verification when the aspirants want to study abroad further. The foreign educational institutions need to be concerned only with the validation body to understand all the details. They will have less to worry about the type of semester, duration, quality of subjects studied etc. Finally, such a rigorous validation will have a domino effect in terms of raising the quality of education, infrastructure, faculty, labs etc. Students will automatically move to schools or colleges that offer them best chance of passing the validation. This will consequently put pressure on the institutes to own up and deliver quality education.


Wednesday, April 3, 2019

Technology that redefines education sector

% of Knowledge Retention

When talking about technology innovation, automatically we tend to talk of industry or service. What about education? Technology is becoming more and more ubiquitous in all educational campuses. It has become integral to the process of learning. We have online learning; interactive digital classroom. This impacts students, professionals undergoing skills enhancement or new skills acquisition as well as those imparting education. Like any other organizations, higher education institutions too must adopt technology to remain innovative.

Here is a set of technology trends relevant in this context:

  1. Smart Campus – What is a smart campus? This is a digital or physical space in which smart devices and humans interact freely. Some are in the preliminary stages. But this will become a de facto phenomenon in the coming years.
  2. Personalization in delivery – Like insurance or banks that personalize a product specifically for an individual based on a lot of factors, more and more personalization will happen in the way education is delivered. Take for example “Nudging.” This is about using data for driving certain behaviour in order to make learning fun and help the students succeed. The data can be used to make time for swimming or sport in between classes or prescribe good habits with examples to a lab session interspersed with studies.

  3.  Credentialing tech - Eliminating fraud in education space is quite critical. Technology can help in creating and securing digital credentials. This will be quite useful for the educational institutions in admission process as well as recruitment.
  4. Life cycle and career Management - Students should continue to have a centralized outlook into their educational path. This should be the case as the students move from one phase of their educational journey into another. More integration of cloud based business applications with learning management solutions will become the norm. Institutions will have more hybrid platforms to deliver, assess, measure the entire life cycle of learning and administration. This will also help in career aspirations of the students across different streams.
  5. BYOD (Bring Your Own Device) – Imagine the various wireless presentation technologies that allows users as well as consumers to project materials from a device onto a screen via wi-fi. More and more institutions are adopting BYOD policies and seamless integration of the existing systems should follow. 

Indian higher educational institutions like universities, centre of excellences, research labs have shown appetite for technology. A lot more needs to be done by others in the next level. But to truly succeed, we must inject technology starting from primary schooling in order to expand the base at the bottom of the pyramid. For that, both the environment, infrastructure and reskilling of current teachers’ eco-system are required to be done.


Friday, November 30, 2018

No takers for philosophy?


Have you ever wondered when dictators arise, the first thing they tend to do is to silence the “Philosophers”! Why? For it is they who seem to bubble with ideas and ensure the thirst for continuous inquiry continues. Yes, the dictators and autocrats fear the philosophers and their influence on the thinking of common men and women.

What do you think studying philosophy means? This is one area where there is no set of defined answers to issues. The act of thinking deeply and critically, inquiring constantly and arriving at one’s ideas i.e. the sheer journey itself is so special. This is one subject you can be sure that those who take it up are at least interested in that subject with no other expectations.

LSE (London School of Economics) has a department – “Philosophy, Logic and Scientific Method”. According to LSE, “Studying philosophy is an excellent opportunity to think deeply whilst developing important transferable skills. Choosing Philosophy as your subject will prepare you for the kind of careful thinking, critical analysis and persuasive writing that is critical in many different areas of work.”

Philosophy has multiple dimensions and aspects of inquiry. It covers ethics, politics, economics, law, metaphysics etc. Challenging questions are constantly debated and explored in philosophic inquiries.

A few examples are:

  • What is good?
  • Is it ok to sacrifice a few for the greater good of the society?
  • Is there something called “Free will”?
  • Does God exist?
  • What is an opinion, a belief? What are the types of knowledge?
  • How should the society be grouped?
  • What can we know?
  • Why should we obey the law?
  • Can we every state anything with absolute certainty?
If you are curious and has an open mind, philosophy is the subject to turn into.

Let’s go to the next topic. What can I do with a philosophy undergraduate degree? In other words, “Does it really pay?”. Very pertinent question that cannot be ignored in today’s world of survival.
Many studies have pointed to the fact that the philosophy graduates work in various departments starting from finance, science, NGOs, Government, Law and business and consistently earn well above the median average.

The students tend to do well in tests that require thinking, writing and critical judgement. Take the GRE or GMAT exam. Philosophy majors seem to get to the essence and ace the exams as entry to graduate studies.

One of the most sought-after skills in the 21st century is critical thinking, working in a team and analytical/logical bent of mind. Studying philosophy prepares one for such a rigour and it can play a role in the general life too.

For this, it would be better to see what can one be able to do after studying philosophy major:
  • To understand and articulate issues – public or otherwise clearly drawing upon philosophical learning
  • To develop an inquiring and investigative mindset
  • To attain research skills, approach any issue logically and reason it out
  • To recognize fundamental questions on life, knowledge, rights, living, society etc.
  • To hone skills in analysis, interpretation and proof
  • To apply formal techniques of logic
  • To identify central issues/messages/concerns and get to the core of the debate/text
John Campbell, a renowned philosopher, says “Philosophy is thinking in slow motion. It breaks down, describes, and assesses moves we ordinarily make at great speed.... It then becomes evident that alternatives are possible.

Starting from Socrates, Plato, Aristotle to early modern and latest era of Machiavelli, Sir Thomas More, John Calvin, Michael de Montaigne, Francis Bacon, Descartes, Boyle, Spinoza, Locke, Voltaire, Hume, KantBertrand Russell, Gödel and Camus have contributed to a better understanding of the fundamental questions, thought process and knowledge.

India has its own philosophers who have written treatises on metaphysics, materialism, justice, law, soul and body etc. Yajnavalkya was one of the earliest in the Vedic age. Many others followed including Kapila, Gautama Buddha, Mahavira, Chanakya, Patanjali, Bodhi dharma, Adi Shankara, Ramanuja, Basaveshwara, Madhvacharya, Vallabhacharya. In the modern times, Rabindranath Tagore, Vivekananda, Sri Aurobindo, Sarvapalli Radhakrishnan, Jiddu Kirshnamurti are notable ones propounding and exploring in various aspects of the discipline. In addition, the Upanishads and other treatises serve as philosophical and scientific inquiries.

If you are keen to enjoy such a rigorous subject, go out, fall in love and find joy in the learning process itself.

Bertrand Russell said “"Philosophy is to be studied, not for the sake of any definite answers to its questions since no definite answers can, as a rule, be known to be true, but rather for the sake of the questions themselves; because these questions enlarge our conception of what is possible, enrich our intellectual imagination and diminish the dogmatic assurance which closes the mind against speculation; but above all because, through the greatness of the universe which philosophy contemplates, the mind also is rendered great, and becomes capable of that union with the universe which constitutes its highest good."

15th November is celebrated as “World Philosophy Day” all over the world. UNESCO introduced it in 2002 with the following objectives:

  • to renew the national, sub regional, regional and international commitment to philosophy;
  • to foster philosophical analysis, research and studies on major contemporary issues, so as to respond more effectively to the challenges that are confronting humanity today;
  • to raise public awareness of the importance of philosophy and its critical use in the choices arising for many societies from the effects of globalization or entry into modernity;
  • to appraise the state of philosophy teaching throughout the world, with special emphasis on unequal access;
  • to underline the importance of the universalization of philosophy teaching for future generations. In establishing World Philosophy Day UNESCO strives to promote an international culture of philosophical debate that respects human dignity and diversity. The Day encourages academic exchange and highlights the contribution of philosophical knowledge in addressing global issues.
References