Finding the game that your friend also play for network analysis

[Xu Ji]

Hello, statalist community!

Currently I am trying to manipulate my dataset so that I can use it to analyse the network effect/peer effect between steam(a gaming platform) users. In order to do that, I have to find out the games that one's friends(peer effect) or generally others(direct network effect) also own. Unfortunately I don't know how to do that since my stata knowledge/skill is still very limited.

Here is a sample from my dataset, which has over 32 million observations:

* Example generated by -dataex-. For more info, type help dataex
clear
input str17(steamid steamid_b) strL title long(appid playtime_forever)
"76561197960265729" "76561197967144365" "Ricochet"                                             60    0
"76561197960265730" "76561197960265733" "Half-Life 2: Lost Coast"                             340    0
"76561197960265730" "76561197960265733" "Kerbal Space Program"                             220200    0
"76561197960265730" "76561197960265733" "Small World 2"                                    235620   45
"76561197960265733" "76561197964770089" "Insaniquarium Deluxe"                               3320    0
"76561197960265733" "76561197964770089" "Darwinia"                                           1500   30
"76561197960265733" "76561197964770089" "Gear Up"                                          214420    7
"76561197960265733" "76561197964770089" "Half-Life"                                            70 1614
"76561197960265733" "76561197964770089" "Day of Defeat"                                        30   59
"76561197960265738" "76561198010062752" "Half-Life: Opposing Force"                            50    0
"76561197960265738" "76561198010062752" "Half-Life 2"                                         220    0
"76561197960265738" "76561198010062752" "Kentucky Route Zero"                              231200   11
"76561197960265742" "76561197960268662" "Team Fortress Classic"                                20    0
"76561197960265742" "76561197960268662" "Battlefield: Bad Companyâ„¢ 2"                 24960  884
"76561197960265742" "76561197960268662" "Borderlands 2"                                     49520 2221
"76561197960265743" "76561197992910264" "AstroPop Deluxe"                                    3340    0
"76561197960265743" "76561197992910264" "Amazing Adventures Around the World"                3530    0
"76561197960265743" "76561197992910264" "Insurgency"                                       222880    0
"76561197960265743" "76561197992910264" "Mirror's Edgeâ„¢"                              17410    0
"76561197960265743" "76561197992910264" "Deathmatch Classic"                                   40    0
"76561197960265743" "76561197992910264" "Venice Deluxe"                                      3490    0
"76561197960265744" "76561197968452293" "Ricochet"                                             60    0
"76561197960265745" "76561197962688722" "Counter-Strike"                                       10    0
"76561197960265747" "76561197960563532" "Feeding Frenzy 2 Deluxe"                            3390    0
"76561197960265747" "76561197960563532" "Bejeweled Twist"                                    3560    0
"76561197960265747" "76561197960563532" "Tomb Raider: Underworld"                            8140    0
"76561197960265747" "76561197960563532" "Assassin’s Creed® IV Black Flag™" 242050 2015
"76561197960265747" "76561197960563532" "Dark Messiah of Might & Magic"                      2130    0
"76561197960265749" "76561197960649750" "Call of Duty: World at War"                        10090    0
"76561197960265749" "76561197960649750" "Call of Duty®: Black Ops II"                   202990    0
"76561197960265749" "76561197960649750" "Call of Duty®: Modern Warfare® 3"            42680    0
"76561197960265749" "76561197960649750" "Saints Row IV"                                    206420    0
"76561197960265749" "76561197960649750" "RAGE"                                               9200    0
"76561197960265749" "76561197960649750" "Deus Ex: Human Revolution - Director's Cut"       238010    0
"76561197960265749" "76561197960649750" "Serious Sam Classics: Revolution"                 227780    0
"76561197960265755" "76561197970094732" "Half-Life"                                            70    0
"76561197960265755" "76561197970094732" "Deathmatch Classic"                                   40    0
"76561197960265763" "76561198000554239" "Insecticide Part 1"                                16710    0
"76561197960265763" "76561198000554239" "Mass Effect"                                       17460    0
"76561197960265763" "76561198000554239" "Frozen Hearth"                                    257890    0
"76561197960265763" "76561198000554239" "Death Track®: Resurrection"                      7840    0
"76561197960265763" "76561198000554239" "Noitu Love 2: Devolution"                         207530    0
"76561197960265763" "76561198000554239" "Assassin’s Creed® Brotherhood"           48190    0
"76561197960265763" "76561198000554239" "Sacraboar"                                         40500    0
"76561197960265763" "76561198000554239" "Coniclysm"                                         39900    0
"76561197960265763" "76561198000554239" "Warhammer 40,000: Dawn of War II: Retribution"     56400    0
"76561197960265763" "76561198000554239" "Humanity Asset"                                   271640    0
"76561197960265763" "76561198000554239" "The First Templar - Steam Special Edition"         57680    0
"76561197960265763" "76561198000554239" "God Mode"                                         227480    0
"76561197960265763" "76561198000554239" "Slam Bolt Scrappers"                               96900    0
end

so the "steamid" is an individual user and "steamid_b" is one of their friends. We can see that "76561197960265733" not only has the friend "76561197964770089" but also has the friend "76561197960265730" since it also appears as "steamid_b" to "76561197960265730".
The "appid" should be unique to every "title", however I have manually found one mismatch(I don't know which code to use to find the mismatch between the "title" and "appid" since there are some messy codes in the string variable).

Thank you in advance for your help!

[Xu Ji]

Cross-posted on https://stackoverflow.com/questions/...twork-analysis

[Ali Atia]

tempfile temp
save `temp'
keep steamid appid
rename steamid steamid_b
merge 1:1 steamid_b appid using `temp',keep(2 3)
rename _merge friendplays
replace friendplays = friendplays==3
label values friendplays

The above code generates a variable "friendplays" which is 1 if the user listed in "steamid_b" has their own observation in "steamid" with the same "appid".

The above code relies on the appid as the game identifier, so avoids the problem of inconsistent strings. Below is a solution for the inconsistent problem:

end
tempfile temp
save `temp'
collapse (first) appid, by(title)
gen count = strlen(title) - strlen(ustrregexra(title,"[^A-Za-z\d :]",""))
bysort appid (count): keep if _n==1
rename title newtitle
drop count
merge 1:m appid using `temp', nogen

The above code checks if multiple titles exist per appid, and selects the title with the fewest problematic characters (e.g., "’"). These titles are placed in a variable called "newtitle".

[Xu Ji]

Thank you very much Ali! I have tried the second code and sequetially the first one. However, this error came up as I was trying the second one.

merge 1:1 steamid_b appid using `temp',keep(2 3)
variables steamid_b appid do not uniquely identify observations in the using data

How can I solve that?
Thank you again in advance!