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.

[AndreasHall]

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

[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. 

[JohannesLindner · Accepted solution]

1. 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.
2. 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 = @user")

// 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.