nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Help needed: Extracting "likely home country" information in ArcGIS Pro

Anonymous User

Hi everyone, I want to extract "likely home country" information from the colomns I demostrated in the screenshot below.

This table has approximately 7k unique users, 4.8M rows. Each user has more than one post on different dates and times.

How can I do this using only ArcGIS Pro? What if I can't do this using just ArcGIS Pro?

I would really appreciate if you can help.

Screenshot 2023-03-14 004804.png

Find more posts tagged with

Analysis

Accepted answers

JohannesLindner

Use Summary Statistics to get counts of each combination of author/country.
- use anything for the Statistics Fields parameter, we don't care about that
- In the Case Fields parameter, input your two fields (author_name & country_en)
- The tool will generate a table. We're interested in the fields author_name, country_en (from your input table) and FREQUENCY, which counts how often the combination appeared in your input table.
Use Calculate Field to create a new text field in the statistics table. Switch the language to Arcade and use this expression:

// get all rows of the current user
var user = $feature.TextField1
var fs_user = Filter($featureset, "author_name = <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/591126">@user</a>")

// get the total post count
var total = Sum(fs_user, "FREQUENCY")

// order by frequency and country name
var fs_user_ordered = OrderBy(fs_user, "FREQUENCY DESC, country_en")

// if you're only interested in the most likely country, uncomment this line and delete everything after
//return First(fs_user_ordered).country_en

// get the percentages of posts from each country
var percentages = []
for(var f in fs_user_ordered) {
    var p = Round(f.FREQUENCY / total * 100)
    Push(percentages, `${f.country_en} (${p}%)`)
}
return Concatenate(percentages, " / ")

Run Summary Statistics on the statistics table to get one row for each user (use author_name & the new field as case fields)

All comments

AndreasHall

Are you looking at using the coordinates or the country_en attribute to extract the likely home country?

JohannesLindner

Use Summary Statistics to get counts of each combination of author/country.
- use anything for the Statistics Fields parameter, we don't care about that
- In the Case Fields parameter, input your two fields (author_name & country_en)
- The tool will generate a table. We're interested in the fields author_name, country_en (from your input table) and FREQUENCY, which counts how often the combination appeared in your input table.
Use Calculate Field to create a new text field in the statistics table. Switch the language to Arcade and use this expression:

// get all rows of the current user
var user = $feature.TextField1
var fs_user = Filter($featureset, "author_name = <a href="https://community.esri.com/t5/user/viewprofilepage/user-id/591126">@user</a>")

// get the total post count
var total = Sum(fs_user, "FREQUENCY")

// order by frequency and country name
var fs_user_ordered = OrderBy(fs_user, "FREQUENCY DESC, country_en")

// if you're only interested in the most likely country, uncomment this line and delete everything after
//return First(fs_user_ordered).country_en

// get the percentages of posts from each country
var percentages = []
for(var f in fs_user_ordered) {
    var p = Round(f.FREQUENCY / total * 100)
    Push(percentages, `${f.country_en} (${p}%)`)
}
return Concatenate(percentages, " / ")

Run Summary Statistics on the statistics table to get one row for each user (use author_name & the new field as case fields)

Anonymous User

Thank you @JohannesLindner, I tried your solution and it worked great. I appreciate your help. I got this result table below.

Screenshot 2023-03-14 232535.png

Anonymous User

Thanks for responding @AndreasHall

I am looking at "country_en" attribute to identify likely home country. I've assigned the post's coordinates to the country polygons.

Now, I am curious how to detect if a user posts in a different country than likely home country more than a few months or a year using post date field. Probably I need to do moving-window analysis of time-series models. I should do more research how to achive this approach. I didn't do time series analysis before.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.