Validate Countries

[joaoftrodrigues]

Description

Check if countries are well written, and correct wrong ones.

Process

1. Get countries' dataset
2. Compare original values with database values (needs split of locations)
3. Assign correct values to a new field 'country' | Check wrong values to correct

Countries from countries' dataset might have to be edited.

Dataset of countries

Dataset provided by teacher: https://datahub.io/core/country-list#data

Useful commands

Imports

Import countries to a collection (option file customizable) mongoimport --db atp --collection countries --drop --file D:\MCD\1\BDDA\TP\data\countries_code.csv --type=csv --headerline

mongoimport --db atp --collection countryCodes3L --drop --file D:\MCD\1\BDDA\TP\data\wikipedia-iso-country-codes.csv --type=csv --headerline

Others for data checking

Merge atp players' info with countries that match *can be explained better: db.atpplayers.aggregate([{ "$lookup": { from: "countries", localField: "Country", foreignField: "Name", as: "CountryMatch" }}, {$out: {db: "atp", coll:"matchesVerification"}}])

Separate unique values: db.matchesVerification.aggregate([ {$match: {CountryMatch: {$size: 0}}}, {$group: {"_id": null, "CountryEmpty": {$addToSet: "$Country"}}}])

[joaoftrodrigues]

Special Case

From #9 , appeared a player with Born field only having city.

The city is located in Italy, so the country was changed from null to "Italy":

db.atpplayers.updateMany({PlayerName: 'Flavio Cobolli'}, {$set:{BornCountry: "Italy"}})

[joaoftrodrigues]

Changes to countries dataset

Changed:

• Plurinational State of Bolivia => Bolivia
• Brunei Darussalam => Brunei
• "Congo, the Democratic Republic of the" => Democratic Republic of the Congo
• Islamic Republic of Iran => Iran
• Federal States of Micronesia => Micronesia
• Republic of Moldova => Moldova
• Democratic People's Republic of Korea => North Korea
• State of Palistine => Palistine
• Russian Federation => Russia
• Republic of Korea => South Korea
• Syrian Arab Republic => Syria
• Taiwan, Province of China => Taiwan
• United Republic of Tanzania => Tanzania
• Bolivarian Republic of Venezuela => Venezuela
• Viet Nam => Vietnam

Added:

• Czechoslovakia, CS
• United Kingdom's countries
  • England, GB-ENG
  • Northern Ireland, GB-NIR
  • Scotland, GB-SCT
  • Wales, GB-WLS
• Yugoslavia, YU

[joaoftrodrigues]

Values' change (Multi)

Format db.atpplayers.updateMany({Country: {$regex: "REGEX"}},{$set: {Country: "CORRECT COUNTRY"}})

Template db.atpplayers.updateMany({Country: {$regex: " "}},{$set: {Country: " "}})

Argentina

db.atpplayers.updateMany({Country: {$regex: "Arg"}},{$set: {Country: "Argentina"}})

Values affected

• Arg.
• Argent
• Argentina*

* keeps its value

Arg. and Argent have Argentina has value more close to. Nonetheless, checking "Arg." 's location, it was spotted on Buenos Aires, confirming Argentina.

db.atpplayers.updateMany({Country: "Buenos Aires"}, {$set: {Country: "Argentina"}})

Australia

db.atpplayers.updateMany({Country: {$in: ["Tasmania", "Victoria", "Devonport", "West Perth"]}}, {$set: {Country: "Australia"}})

Values affected

• Devonport
• Tasmania
• Victoria
• West Perth

Austria

db.atpplayers.updateMany({Country: {$in: ["Aut.", "Portschach"]}}, {$set: {Country: "Austria"}})

Values affected

• Aut.
• Portschach

Bosnia and Herzegovina

db.atpplayers.updateMany({Country: {$regex: "Bosnia"}},{$set: {Country: "Bosnia and Herzegovina"}})

Values affected

• Bosnia
• Bosnia + space

Brazil

db.atpplayers.updateMany({Country: {$in: ["Braz", "Bahia","Brasilia","Brazi","Florianapolis","Guarulhos","Rio de Janeiro"]}}, {$set: {Country: "Brazil"}})

Values affected

• Bahia
• Brasilia
• Braz
• Brazi
• Florianapolis
• Guarulhos
• Rio de Janeiro

Canada

db.atpplayers.updateMany({Country: {$in: ["Alberta", "Canda", "Ontaria", "Calgary"]}}, {$set: {Country: "Canada"}})

Values affected

• Alberta
• Calgary
• Canda
• Ontaria

China

db.atpplayers.updateMany({Country: {$in: ["Nanjing", "Wuhan"]}}, {$set: {Country: "China"}})

Values affected

• Nanjing
• Wuhan

Colombia

db.atpplayers.updateMany({Country: {$regex: "(Columbia)|(Col$)", $options:"m"}},{$set: {Country: "Colombia"}})

Values affected

• Col
• Columbia

db.atpplayers.updateMany({Country: "Villavicencio"}, {$set: {Country: "Colombia"}})

Côte d'Ivoire

db.atpplayers.updateMany({Country: {$in: ["Ivory Coast", "Abidjan"]}}, {$set: {Country: "Côte d'Ivoire"}})

Values affected

• Abidjan
• Ivory Coast

Cuba

db.atpplayers.updateMany({Country: {$in:["Ciudad de Habana", "Havana"]}}, {$set: {Country: "Cuba"}})

Values affected

• Ciudade de Habana
• Havana

Czech Republic

db.atpplayers.updateMany({Country: {$regex: "Czech Rep"}},{$set: {Country: "Czech Republic"}})

Values affected

• Czech. Rep.
• Czech Repub
• Czech Republic*

* keeps its value

Dominican Republic

db.atpplayers.updateMany({Country:{$in:["D.R.", "Domincan Republic", "Santo Domingo"]}}, {$set: {Country: "Dominican Republic"}})

Values affected

• D.R.
• Domincan Republic
• Santo Domingo

Ecuador

db.atpplayers.updateMany({Country: {$in: ["Guayaquil", "Salinas"]}}, {$set: {Country: "Ecuador"}})

Values affected

• Guyaquil
• Salinas

Egypt

db.atpplayers.updateMany({Country: {$regex: "(Egpyt)|(Eygpt)"}},{$set: {Country: "Egypt"}})

Values affected

• Egpyt
• Epgyt

db.atpplayers.updateMany({Country: "Sharm El Sheikh"}, {$set: {Country: "Egypt"}})

El Salvador

db.atpplayers.updateMany({Country: {$in: ["Salvador", "San Salvador"]}}, {$set: {Country: "El Salvador"}})

Values affected

• Salvador
• San Salvador

France

db.atpplayers.updateMany({Country: {$in: ["Bourg", "Forbach", "Lesser Antilles", "New Caledoni", "Reunion Island", "Uriage", "Angleur - Liege", "Pau","Toulouse"]}}, {$set: {Country: "France"}})

Values affected

• Angleur - Liege
• Bourg
• Forbach
• Lesser Antilles
• Pau
• Reunion Island
• Toulouse
• Uriage

Germany

db.atpplayers.updateMany({Country: {$in:["Wetzlar", "Braunschweig"]}},{$set: {Country: "Germany"}})

Values affected

• Braunschweig
• Wetzlar

Guatemala

db.atpplayers.updateMany({Country: {$in:["GUA","Guatemala City"]}}, {$set: {Country: "Guatemala"}})

Values affected

• GUA
• Guatemala City

Hungary

db.atpplayers.updateMany({Country: {$in: ["Hu", "Hunary"]}}, {$set: {Country: "Hungary"}})

Values affected

• Hu
• Hunary

Indonesia

db.atpplayers.updateMany({Country: {$in:["INA","Jakarta", "Surabaya"]}}, {$set: {Country: "Indonesia"}})

Values affected

• INA
• Jakarta
• Surabaya

Israel

db.atpplayers.updateMany({Country: {$in:["Hasharon", "Isra", "Ramat Hasharon"]}}, {$set: {Country: "Israel"}})

Values affected

• Hasharon
• Isra
• Ramat Hasharon

Italy

db.atpplayers.updateMany({Country: {$in:["Ita", "Sardinia", "Reggio Calabria"]}}, {$set: {Country: "Italy"}})

Values affected

• Ita
• Reggio Calabria
• Sardinia

Luxembourg

db.atpplayers.updateMany({Country:{$in: ["Esch", "Mondorf-Les-Bains", "Esch/Alzette"]}},{$set: {Country: "Luxembourg"}})

Values affected

• Esch
• Esch/Alzette
• Mondorf-Les-Bains

Malaysia

db.atpplayers.updateMany({Country:{$in:["MAS","Kuala Lampur"]}}, {$set: {Country: "Malaysia"}})

Values affected

• MAS
• Kuala Lampur

Mexico

db.atpplayers.updateMany({Country: {$in: ["Meixco","Mexica", "Mexico City", "Toluca"]}}, {$set: {Country: "Mexico"}})

Values affected

• Meixco
• Mexica
• Mexico City
• Toluca

Netherlands

Regular expression (close to Netherlands)

db.atpplayers.updateMany({Country: {$regex: "Nethe", $options: "m"}},{$set: {Country: "Netherlands"}})

Values affected

• Netherlands*
• The Nethe
• The Netherlands * keeps its value

Variances

db.atpplayers.updateMany({Country: {$in: ["Curacao", "Dutch Anti", "Dutch Antil","NED","The Hague", "'s-Hertogenbosch","Elndhoven", "Willemstad"]}},{$set: {Country: "Netherlands"}})

Values affected

• 's-Hertogenbosch
• Curacao
• Dutch Anti
• Dutch Antil
• Elndhoven
• NED
• The Hague
• Willemstad

New Zealand

db.atpplayers.updateMany({Country: {$in: ["New Zealan", "Wellington"]}}, {$set: {Country: "New Zealand"}})

Values affected

• New Zealan
• Wellington

North Macedonia

Although 'Macedonia' is a shorter version, it's used to name a region which is not entirely from North Macedonia country.

db.atpplayers.updateMany({Country: {$regex:"Mace"}}, {$set: {Country: "North Macedonia"}})

Values affected

• Macedona
• Macedonia

Philippines

db.atpplayers.updateMany({Country: {$in: ["PHI", "Phillipines", "Manila", "Manilla"]}}, {$set: {Country: "Philippines"}})

Values affected

• Manila
• Manilla
• PHI
• Phillipines

Portugal

db.atpplayers.updateMany({Country: {$in: ["Estoril", "Faro"]}}, {$set: {Country: "Portugal"}})

Values affected

• Estoril
• Faro

Romania

db.atpplayers.updateMany({Country: {$in:["Bucharest","Constanta"]}}, {$set: {Country: "Romania"}})

Values affected

• Bucharest
• Constanta

Russia

db.atpplayers.updateMany({Country: {$in: ["Korolev", "Moscow"]}}, {$set: {Country: "Russia"}})

Values affected

• Korolev
• Moscow

Serbia

db.atpplayers.updateMany({Country: {$in: ["Belgrade", "SCG", "SErgia "]}}, {$set: {Country: "Serbia"}})

Values affected

• Belgrade
• SCG
• SErgia

Slovakia

db.atpplayers.updateMany({Country: {$regex: "^Slov[akia]+", $options: "m"}},{$set: {Country: "Slovakia"}})

Values affected

• Slovak
• Slovak R
• Slovak Republic
• Slovakia*
• Slovkia

* keeps its value

db.atpplayers.updateMany({Country: {$in: ["Bratislava", "Presov"]}}, {$set: {Country: "Slovakia"}})

• Bratislava
• Presov

Slovenia

db.atpplayers.updateMany({Country: {$in: ["Solvenia", "Portoroz"]}}, {$set: {Country: "Slovenia"}})

Values affected

• Solvenia
• Portoroz

South Africa

db.atpplayers.updateMany({Country: {$regex: "(South$)|South Africa", $options: "m"}},{$set: {Country: "South Africa"}})

Values affected

• South*
• South Africa

* This value is justified to be substituted to "South Africa", some comments below.

db.atpplayers.updateMany({Country: {$in: ["Johannesburg", "Nelspruit", "Polokwane", "Pretoria"]}}, {$set: {Country: "South Africa"}})

Values affected

• Johannesburg
• Nelspruit
• Polokwane
• Pretoria

Spain

db.atpplayers.updateMany({Country: {$in:["Bakio","Melilla", "Valldoreix","Barcelona", "Palma de Mallorca"]}}, {$set: {Country: "Spain"}})

Values affected

• Bakio
• Barcelona
• Melilla
• Palma de Mallorca
• Valldoreix

Switzerland

db.atpplayers.updateMany({Country: {$in:["Switz.", "Neuchatel"]}}, {$set: {Country: "Switzerland"}})

Values affected

• Switz
• Neuchatel

Taiwan

Taipei variances

db.atpplayers.updateMany({Country: {$regex: "(Chinese Ta[ipei]*)|(Taipei)"}},{$set: {Country: "Taiwan"}})

Values affected

• Chinese Ta
• Chinese Taip
• Chinese Taipei
• Taipei

Others

db.atpplayers.updateMany({Country: {$in:["TPE", "Kaohsiung"]}}, {$set: {Country: "Taiwan"}})

• Kaohsiung
• TPE

Tunisia

db.atpplayers.updateMany({Country: {$in: ["Monastir", "Tunis"]}}, {$set: {Country: "Tunisia"}})

Values affected

• Monastir
• Tunis

United Kingdom

db.atpplayers.updateMany({Country: {$in: ["Britain","Great Britain", "UK", "Devon", "London"]}}, {$set: {Country: "United Kingdom"}})

Values affected

• Britain
• Devon
• Great Britain
• London
• UK

Uruguay

db.atpplayers.updateMany({Country: {$in: ["Uraguay", "URU", "Urugay", "Montevideo"]}},{$set: {Country: "Uruguay"}})

Values affected

• Montevideo
• Uraguay
• URU
• Urugay

USA (United States of America)

db.atpplayers.updateMany({Country: {$regex: "^U[SA.]+$", $options: "m"}},{$set: {Country: "United States"}})

Values affected

• USA
• U.S.A
• U.S.A.

db.atpplayers.updateMany({Country: {$in:["OK","Texas", "United S", "United States of America", "Delray Beach"]}}, {$set: {Country: "United States"}})

Values affected

• Delray Beach
• OK
• Texas
• United S
• United States Of America

Uzbekistan

db.atpplayers.updateMany({Country: {$in: ["Uzb.", "Tashkent"]}}, {$set: {Country: "Uzbekistan"}})

Values affected

• Uzb.
• Tashkent

Venezuela

db.atpplayers.updateMany({Country: {$in:["Venezeuela", "Lara"]}}, {$set: {Country: "Venezuela"}})

Values affected

• Venezeuela
• Lara

[joaoftrodrigues]

South Case

It was spotted countries having the value "South" only. After a match of distinct locations, containing it, it only shown locations on Doornfontein, a city from South Africa.

This way, the only locations having "South" as country, are "South Africa".

[joaoftrodrigues]

The Nethe Case

Spotted a Country written only "The Nethe", as it could be "The Netherlands". Nonetheless, it was verified, and based on the city, its is, indeed.

[joaoftrodrigues]

Values' change of individual cases

This is separated from regular expressions, for size purposes.

db.atpplayers.updateMany({Country: " "}, {$set: {Country: " "}})

Countries

Austria

db.atpplayers.updateMany({Country: "Aut."}, {$set: {Country: "Austria"}})

Belarus

db.atpplayers.updateMany({Country: "Minsk"}, {$set: {Country: "Belarus"}})

Belgium

db.atpplayers.updateMany({Country: "Liege"}, {$set: {Country: "Belgium"}})

Bolivia

db.atpplayers.updateMany({Country: "Santa Cruz de la Sie"}, {$set: {Country: "Bolivia"}})

Botswana

db.atpplayers.updateMany({Country: "Botwana"}, {$set: {Country: "Botswana"}})

Chile

db.atpplayers.updateMany({Country: "Santiago"}, {$set: {Country: "Chile"}})

China

db.atpplayers.updateMany({Country: "Nanjing"}, {$set: {Country: "China"}})

Croatia

db.atpplayers.updateMany({Country: "Crotia"}, {$set: {Country: "Croatia"}})

Denmark

db.atpplayers.updateMany({Country: "DEN."}, {$set: {Country: "Denmark"}})

India

db.atpplayers.updateMany({Country: "Kolkata"}, {$set: {Country: "India"}})

Iran

db.atpplayers.updateMany({Country: "Tehran"}, {$set: {Country: "Iran"}})

Ireland

db.atpplayers.updateMany({Country: "Dublin"}, {$set: {Country: "Ireland"}})

Fiji

db.atpplayers.updateMany({Country: "Fiji Island"}, {$set: {Country: "Fiji"}})

Moldova

db.atpplayers.updateMany({Country: "Moldovia"}, {$set: {Country: "Moldova"}})

Morocco

db.atpplayers.updateMany({Country: "Casablanca"}, {$set: {Country: "Morocco"}})

Pakistan

db.atpplayers.updateMany({Country: "Lahore"}, {$set: {Country: "Pakistan"}})

Poland

db.atpplayers.updateMany({Country: "Wrocklaw"},{$set: {Country: "Poland"}})

Qatar

db.atpplayers.updateMany({Country: "Doha"}, {$set: {Country: "Qatar"}})

Serbia and Montenegro

db.atpplayers.updateMany({Country: "Serbia & Montenegro"}, {$set: {Country: "Serbia and Montenegro"}})

South Korea

db.atpplayers.updateMany({Country: "Korea"}, {$set: {Country: "South Korea"}})

Switzerland

db.atpplayers.updateMany({Country: "Switz."}, {$set: {Country: "Switzerland"}})

Thailand

db.atpplayers.updateMany({Country: "Bangkok"}, {$set: {Country: "Thailand"}})

Trinidad and Tobago

db.atpplayers.updateMany({Country: "TRI"}, {$set: {Country: "Trinidad and Tobago"}})

Turkey

db.atpplayers.updateMany({Country: "Antalya"}, {$set: {Country: "Turkey"}})

United Arab Emirates

db.atpplayers.updateMany({Country: "U.A.E."}, {$set: {Country: "United Arab Emirates"}})

Ukraine

db.atpplayers.updateMany({Country: "Novaya Kakhovka"}, {$set: {Country: "Ukraine"}})

Yugoslavia

db.atpplayers.updateMany({Country: "Yug."},{$set: {Country: "Yugoslavia"}})

Zimbabwe

db.atpplayers.updateMany({Country: "Harare"}, {$set: {Country: "Zimbabwe"}})

[joaoftrodrigues]

Values as 2 letter codes

Some countries are inputted as its 2 letter nomenclature, so it was matched those values with the countries database, to substitute.

Queries

Match code values with countries' database entries db.atpplayers.aggregate([{ "$lookup": { from: "countries", localField: "Country", foreignField: "Code", as: "CountryMatch" }}, {$out: {db: "atp", coll:"atpplayers"}}])

Extract first element of array db.atpplayers.updateMany({CountryMatch: {$size: 1}}, [{$set: {Country: {$first: "$CountryMatch"}}}])

Associate respective country db.atpplayers.updateMany({CountryMatch: {$size: 1}}, [{$set: {Country: "$Country.Name"}}])

Delete variable CountryMatch db.atpplayers.updateMany({},{$unset: {CountryMatch: ""}})

Countries affected

Running a distinct of countries on the match, these were the ones found:

Values changed

Based on previous image, the follow names were changed:

• AR -> Argentina
• CA -> Canada
• NZ -> New Zealand
• SA -> Saudi Arabia

[joaoftrodrigues]

Values as 3 letter code

Same case as previous, but for 3 letters code.

Queries

Match code values with countries' database entries db.atpplayers.aggregate([{ "$lookup": { from: "countryCodes3L", localField: "Country", foreignField: "Alpha-3", as: "CountryMatch" }}, {$out: {db: "atp", coll:"atpplayers"}}])

Extract first element of array db.atpplayers.updateMany({CountryMatch: {$size: 1}}, [{$set: {Country: {$first: "$CountryMatch"}}}])

Associate respective country db.atpplayers.updateMany({CountryMatch: {$size: 1}}, [{$set: {Country: "$Country.CountryName"}}])

Countries affected

Running a distinct of countries on the match, these were the ones found:

Values changed

• BRA -> Brazil
• COL -> Colombia
• CZE -> Czech Republic
• CIV -> Côte D'Ivoire
• HKG -> Hong Kong
• ISR -> Isreael
• NZL -> New Zealand
• THA -> Thailand
• GBR -> United Kingdom

[joaoftrodrigues]

Laos is not on countries csv

[joaoftrodrigues]

TBA Treatment

Locations with TBA as location, has follow tournaments:

So the countries will be extracted from the tournament name.

Queries

db.atpplayers.updateMany({Country:"TBA", Tournament: {$regex: " "}}, {$set: {Country: " "}})

Botswana db.atpplayers.updateMany({Country:"TBA", Tournament: {$regex: "Botswana"}}, {$set: {Country: "Botswana"}})

Egypt db.atpplayers.updateMany({Country:"TBA", Tournament: {$regex: "Egypt"}}, {$set: {Country: "Egypt"}})

France db.atpplayers.updateMany({Country:"TBA", Tournament: {$regex: "France"}}, {$set: {Country: "France"}})

India db.atpplayers.updateMany({Country:"TBA", Tournament: {$regex: "India"}}, {$set: {Country: "India"}})

Italy db.atpplayers.updateMany({Country:"TBA", Tournament: {$regex: "Italy"}}, {$set: {Country: "Italy"}})

United Kingdom db.atpplayers.updateMany({Country:"TBA", Tournament: {$regex: "Great Britain"}}, {$set: {Country: "United Kingdom"}})

United States db.atpplayers.updateMany({Country:"TBA", Tournament: {$regex: "U.S.A."}}, {$set: {Country: "United States"}})

Zimbabwe db.atpplayers.updateMany({Country:"TBA", Tournament: {$regex: "Zimbabwe"}}, {$set: {Country: "Zimbabwe"}})

[joaoftrodrigues]

TBC

With location as "TBC" were found follow tournaments:

Countries will be extracted as previous case, in this particular, is just one, so will be a direct attribution.

db.atpplayers.updateMany({Country: "TBC"}, {$set: {Country: "United Kingdom"}})

TBD

Tournaments with TBD location:

Using previous methodology: db.atpplayers.updateMany({Country: "TBD"}, {$set: {Country: "United States"}})