2

I have 200000 rows of twitter data having the location attribute. I want to map the city names to their respective countries for producing demographic result based on countries. How can I do that using some formula in excel or in other efficient way. For example: new delhi mapped to India

Data Sample: location attribute

Improve this question
4
  • 1
    If accuracy is important to you, it will be impossible if you only have the city name. For example consider Paris, France vs. Paris, Texas. If you just have "Paris" as a location information, it will be impossible to say with certainty if the user is located in France or in the US. There are many cities sharing the same name.
    – J-J-J
    Commented Mar 30, 2023 at 20:06
  • Smriti, could you please post 10 sample rows?
    – Nicolas Raoul ♦
    Commented Mar 31, 2023 at 8:33
  • @Nicolas Raoul, Following is the sample of data: Tampa, Florida St George, UT Utah Springfield OR Hamilton, New Zealand West Michigan Haidian,Beijing,China India
    – Smriti
    Commented Mar 31, 2023 at 9:40
  • Thanks for the sample data, it is very valuable to know that the input data is not uniform and even contains some invalid entries.
    – Nicolas Raoul ♦
    Commented Apr 3, 2023 at 2:28

1 Answer 1

Reset to default
1

You will probably have to first geocode into latitude/longitude, then reverse geocode to get the country.

Step 1: geocode

This step is the hardest as human-entered data can take many shapes. I would suggest this API:

https://developers.google.com/maps/documentation/geocoding/overview

Be aware that it costs money.

Example when searching for Springfield OR:

enter image description here

A few alternative options are listed at https://wiki.openstreetmap.org/wiki/Geocoding

Step 2: reverse geocode into a country

Good news, this one does not cost money and it is super-fast using this Java library coordinates2country:

https://github.com/coordinates2country/coordinates2country

Disclaimer: I made it, because I needed such a feature in one of my apps.

It includes a run.sh script runnable from Linux or Mac terminal. Example:

Enter ./run.sh 44.046236 -123.022029 on the terminal, press enter, this result gets displayed: United States of America

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.