OpenRe ine
Cleaning and T ans o ming Messy Da a
Da a Days Niede sachsen 2025 - Vi ual Theme Day 26.11.2025
Ins uc o : Hoang Phuong Tu
eResea ch Alliance
Geo g-Augus Uni e si y o Gö ingen
Ma e ial a ailable a : h ps://da aca pen y.gi hub.io/open e ine-socialsci/
Teaching ma e ials based on Da a Ca pen y - OpenRe ine Lesson, DOI: h ps:/doi.o g/10.5281/zenodo.7916232
P esen a ion c ea ed by he eResea ch Alliance, Geo g-Augus Uni e si y o Gö ingen
Wha you need oday:
•OpenRe ine ins alled (we ecommend using he la es s able e sion 3.8.7)
h ps://open e ine.o g/download.h ml
•A Web b owse (Fi e ox o Ch ome ecommended –NOT In e ne Explo e )
•The Da ase SAFI_open e ine.cs
•h ps://ndownloade . igsha e.com/ iles/11502815
S a ing OpenRe ine:
•Windows: double-click on open e ine.exe
•MacOS: launch OpenRe ine om Applica ions olde
•Linux: un ./ e ine in he OpenRe ine di ec o y
I you a e using a di e en b owse , o OpenRe ine does no au oma ically open o you, poin you
b owse a h p://127.0.0.1:3333/ o h p://localhos :3333 o launch he p og am.
Da a Cleaning wi h OpenRe ine
Da ase
•A ailable a : h ps://ndownloade . igsha e.com/ iles/11502815
•Subse o da a o he S udying A ican Fa me -Led I iga ion (SAFI) da abase
•Da ase om in e iews o a me s in Mozambique and Tanzania
•In e iews conduc ed be ween No embe 2016 and June 2017
•Da a con ains in o ma ion on household ea u es, ag icul u al p ac ices and
asse s
•In en ionally messed up da a
Da a Cleaning wi h OpenRe ine
Why OpenRe ine?
•Tools o iden i y and amend messy da a
•Documen a ion o da a cleaning s eps
•Lea e aw da a un ouched
•Undo/ edo o s eps, also on o he iles
•Easy use o complex algo i hms
•Open sou ce
•La ge communi y
•Wo ks wi h da ase s up o 100.000 ows, ex endable
Da a Cleaning wi h OpenRe ine
Ge ing s a ed wi h Open Re ine
Da a Cleaning wi h OpenRe ine
1. Launch OpenRe ine (see Ge ing S a ed wi h OpenRe ine).
2. Click C ea e P ojec and selec Ge da a om This Compu e .
3. Click Choose Files and selec he ile SAFI_open e ine.cs . Click Open o double-click on
he ilename.
4. Click Nex >> unde he b owse bu on o upload he da a in o OpenRe ine.
5. OpenRe ine gi es you a p e iew –check i . I his is he w ong ile, click <<S a O e (uppe
le ).
6. Please uncheck he op ion T im leading & ailing whi espace om s ings
7. I all looks well, click C ea e P ojec >> (uppe igh ).
Face ing wi h Open Re ine
Da a Cleaning wi h OpenRe ine
• o ge an o e iew o he da a in a p ojec
• o b ing mo e consis ency o he da a
A ‘Face ’ g oups he alues ha appea in a column acco ding o some c i e ion, hen allows you o
il e he g oups and edi alues ac oss many eco ds a he same ime.
Exe cise 1:
1. Sc oll o e o he illage column.
2. Click he down a ow and choose Face > Tex ace .
3. T y so ing his ace by name and by coun . Do you no ice any p oblems wi h he da a? Wha
a e hey?
4. Ho e he mouse o e one o he names in he Face lis . You should see ha you ha e an
edi unc ion a ailable.
Face ing wi h Open Re ine
Da a Cleaning wi h OpenRe ine
Exe cise 2:
1. Using ace ing, ind ou how many di e en in e iew_da e alues a e ep esen ed in he
su ey esul s.
2. Is he column o ma ed as Numbe , Da e, o Tex ? How does changing he o ma change he
ace ing display?
3. Use ace ing o p oduce a imeline display o in e iew_da e. You will need o use Edi
cells > Common ans o ms > To da e o con e his column o da es.
4. Du ing wha pe iod we e mos o he in e iews collec ed?
• o ge an o e iew o he da a in a p ojec
• o b ing mo e consis ency o he da a
A ‘Face ’ g oups he alues ha appea in a column acco ding o some c i e ion, and hen allows
you o il e he g oups and edi alues ac oss many eco ds a he same ime.
Clus e ing da a wi h Open Re ine
Da a Cleaning wi h OpenRe ine
• “ inding g oups o di e en alues ha migh be al e na i e ep esen a ions o he same hing”
• e y powe ul ool o cleaning da ase s which con ain misspelled o mis yped en ies
Exe cise 3:
1. In he illage Tex Face we c ea ed in he s ep abo e, click he Clus e bu on.
2. In he esul ing pop-up window, you can change he Me hod and he Keying Func ion. T y di e en
combina ions o see wha di e en me ge s o alues a e sugges ed.
3. Selec he key collision me hod and me aphone3 keying unc ion. I should iden i y h ee clus e s.
4. Click he Me ge? box beside each, hen click Me ge Selec ed and Reclus e o apply he co ec ions o he
da ase .
5. T y selec ing di e en Me hods and Keying Func ions again, o see wha new me ges a e sugges ed. You
may ind he e a e s ill imp o emen s ha can be made, bu don’ Me ge again; jus Close when you’ e done.
We’ll now see o he ope a ions ha will help us de ec and co ec he emaining p oblems, and ha ha e
o he , mo e gene al uses.
6. You should ind ha using he de aul se ings, no mo e clus e s a e ound, o example o me ge Ruaca-
Nhamuenda wi h Ruaca o Chi dozo wi h Chi odzo. (No e ha he nea es neighbo me hod wi h ppm
dis ance, adius ≥ 4, and block cha s ≤ 4 will ind hese clus e s, as well as o he se ings wi h le ensh ein
dis ance)
7. To me ge hese alues we will ho e o e hem in he illage ex ace , selec edi , and manually change he
names. Change Chi dozo o Chi odzo and Ruaca-Nhamuenda o Ruaca. You should now ha e ou clus e s:
Chi odzo, God, Ruaca and 49.
T ans o ming da a
Da a Cleaning wi h OpenRe ine
•Da a in he i ems_owned column is a se o i ems in a lis
•Be o e spli ing he lis in o indi idual i ems, emo e he b acke s and he quo es
Exe cise 4:
1. Click he down a ow a he op o he i ems_owned column. Choose Edi Cells >
T ans o m…
2. In he pop-up, i s emo e all le squa e b acke s ( [ ). In he Exp ession box ype
alue. eplace(“[“, “”) and click OK.
3. Wha he exp ession means is his: Take he alue In each cell in he selec ed column and
eplace all o he “[“ wi h “” (i.e. no hing – dele e).
4. Click OK. You’ll ge see in he i ems_owned column ha he e a e no longe any le squa e
b acke s..
5. Use he same s a egy o emo e he single quo e ma ks ( ‘), he igh squa e b acke s( ] ),
and spaces om he i ems_owned column.
So ing da a wi h Open Re ine (2)
Da a Cleaning wi h OpenRe ine
•so by mul iple columns by pe o ming so on addi ional columns
• o es a he so ing p ocess wi h he cu en column, check he So by his column alone box
Exe cise 9:
We disco e ed in an ea lie lesson ha he alue o one o he illage en ies was gi en as 49. This is
clea ly w ong. By looking a he GPS coo dina es o he en ies o he o he illages can we decide wha
illage he da a in ha column was collec ed om.
1. So on gps_La i ude as a numbe wi h he smalles i s ?
2. Add a so on gps_Longii ude as a numbe wi h he smalles i s .
3. Using he d op down a ow on he illage column, selec Edi column > Mo e column o end. This will
allow you o compa e cillage names wi h GPS coo dina es.
4. Sc oll h ough he en ies un ill you ind illage 49. Can you ell om i ‘s GPS coo dina es which illage i
belongs o?
5. Now so only by in e iew_da e as da e. Mo e he illage column o he s a o he able. Does
he ow whe e illage is 49 g oup wi h one a icula illage? Is i he same illage as when compa ing
GPS coo dina es?
6. Pe o m a ex ace on he illage column and change 49 o he illage name ha was de e mined in
he p e ious exe cise.
Examining numbe s in Open Re ine (1)
Da a Cleaning wi h OpenRe ine
•using da a ypes can ease iden i ying e o s in da a o cleaning up
•Click Edi cells > Common ans o ms o change he da a ype o one column
Exe cise 10:
1. Remo e any ex il e ace s om he le panel.
2. T ans o m he cells in he column yea s_ a m o numbe s by using Edi cells > Common
ans o ms > To numbe . Wha can you obse e in he display o ha column?
3. T ans o m h ee mo e columns o numbe s: no_membe s,yea s_li and
buildings_in_compound. Can all columns be ans o med o numbe s?
Examining numbe s in Open Re ine (2)
Da a Cleaning wi h OpenRe ine
•non-numbe alues o blanks in a nume ical column may ep esen e o s in da a
•we can ind hem wi h a Nume ic ace
Exe cise 11:
1. Fo a column you ans o med o numbe s, edi one o wo cells, eplacing he numbe s wi h
ex (such as abc) o blank (no numbe o ex ). Change he Da a ype o Tex .
2. Use he pulldown menu o apply a nume ic ace o he column you edi ed. The ace will
appea in he le panel.
3. No ice ha he e a e se e al checkboxes in his ace : Nume ic, Non-nume ic, Blank, and
E o . Below hese a e coun s o he numbe o cells in each ca ego y. You should see checks
o Non-nume ic and Blank i you changed some alues.
4. Expe imen wi h checking o unchecking hese boxes o selec subse s o you da a.
5. When you a e done, undo you changes and emo e he ace .
Sc ip s om Open Re ine
Da a Cleaning wi h OpenRe ine
•OpenRe ine sa es e e y change you make o he da ase
•changes a e sa ed in a sc ip ile o ma known as JSON (Ja aSc ip Objec No a ion)
•you can expo his sc ip and apply i o o he da a iles
➢quick way o clean all o you ela ed da a, o many iles wi h equal s uc u e and simila e o s
Exe cise 14:
1. In he Undo / Redo sec ion, click Ex ac ..., and selec he s eps ha you wan o apply o o he
da ase s by clicking he checkboxes.
2. Copy he code om he igh hand panel and pas e i in o a ex edi o . Make su e i sa es as
a plain ex ile.
3. S a a new p ojec in OpenRe ine wi h he o iginal aw da a ile and name i some hing
di e en om you exis ing p ojec .
4. Click he Undo / Redo ab > Apply and pas e in he con en s o x ile wi h he JSON code
5. Click Pe o m ope a ions. The da ase should now be he same as you o he cleaned da ase .
Expo ing and Sa ing Da a om Open Re ine
Da a Cleaning wi h OpenRe ine
•OpenRe ine au oma ically sa es you p ojec wi h all da a and all pe o med ope a ions
•expo ing p ojec s can be use ul o exchange no only da a, bu also supplemen a y in o ma ion
•expo ed p ojec s can also be impo ed o OpenRe ine (e.g. o con inue wo k on ano he
compu e )
Exe cise 15:
1. Click he Expo bu on in he op igh and selec OpenRe ine p ojec a chi e o ile.
2. A a .gz ile will download o you de aul Download di ec o y. The a .gz ex ension ells you
ha his is a comp essed ile, which means ha his ile con ains mul iple iles.
1. On Mac and Linux, you can double-click on he a .gz ile and i will expand in o a di ec o y.
A olde icon will now appea .
2. On Windows, opening a .gz iles equi es addi ional so wa e such as 7-zip o WinZip.
3. Look a he iles ha appea in his olde . Wha iles a e he e? Wha in o ma ion do you hink
hese iles con ain?
4. Expo only you cleaned da a by clicking Expo in he op igh , and hen selec ing a ile
o ma . Which ile o ma s would be a good choice?
O he esou ces in OpenRe ine
Da a Cleaning wi h OpenRe ine
•You can ind ou a lo mo e abou OpenRe ine a h p://open e ine.o g and check ou some
g ea in oduc o y ideos o he manual .
•The e is a Google G oup ha can answe a lo o beginne ques ions and p oblems.
•OpenRe ine ecipes, sc ip s, p ojec s, and ex ensions a e a ailable on gi hub, whe e you can
ind and copy hem in o you OpenRe ine ins ance o un on you da ase .
•The OpenRe ine Gi Hub wiki page has a e e ence o he Gene al Re ine Exp ession
Language (GREL).
•G a e ul Da a is a un si e wi h many esou ces de o ed o OpenRe ine, including a nice
u o ial.
•Ma ga e Helle shows how she uses OpenRe ine o Measu ing and Coun ing Impac in
Reposi o ies.
•In e sec Cou se Resou ces has Ja ed Be ghold’s Cleaning & Explo ing you da a wi h Open
Re ine.
