{"id":220,"date":"2011-01-19T11:31:22","date_gmt":"2011-01-19T11:31:22","guid":{"rendered":"http:\/\/larica.uniurb.it\/sigsna\/?p=220"},"modified":"2011-01-19T11:31:22","modified_gmt":"2011-01-19T11:31:22","slug":"building-your-twitter-replynet-with-sqlite","status":"publish","type":"post","link":"https:\/\/sigsna.uniurb.it\/?p=220","title":{"rendered":"Building your Twitter replynet with SQLite"},"content":{"rendered":"

Data preparation is one of the most important and interesting part of every research activity. Recently I’ve really enjoyed Axel’s posts <\/a>about how to extract conversation networks out of Twapperkeeper archives. Axel uses awk which is a very powerful and flexible tool to manipulate csv files. Obviously there are several ways to manipulate a csv (or any kind of data) in order to extract a network file readable by a SNA software such as Gephi<\/a>. We at SIGSNA have always used a database approach. This is probably due to the high expertise of Matteo with databases but I must admit that it seems to have quite a few advantages.
\nWith this post – the first of a more methodological oriented series – I’ll show how to extract a conversation network (aka Reply Network) out of a Twapperkeeper<\/a> archive using SQLite. Please note that this will be a non technical post and it i intended for a non technical audience.
\nSQLite is a cross platform embedded relational database management system.
\n(On osx you can start it simply typing sqlite3 from your command shell).
\nFirst we have to create a table suitable for importing a twapperkeeper archive:
\n.create table tablename (text varchar,
\nto_user_id int,
\nfrom_user varchar,
\nid int,
\nfrom_user_id int,
\niso_language_code varchar,
\nsource varchar,
\nprofile_image_url varchar,
\ngeo_type float,
\ngeo_coordinates_0 float,
\ngeo_coordinates_1 float,
\ncreated_at timestamp,
\ntime long);<\/code>
\nonce we’ve created the table we just have to import the .csv file into our table
\n.separator \",\"
\n.import filename.csv tablename<\/code>
\nOnce we’ve imported our data we’ve got the full advantages of a powerful database and creating our conversation network it’s really simple.
\nLet’s create a csv file with a line for every @reply in our data with the user Id of the sender and the user id of the receiver.
\nFirst we have to set a file as the output of our query:
\n.output nameoutyournetwork.csv<\/code>
\nthen we can run the query:
\nselect from_user_id,to_user_id from yourtable where text like '@%' and to_user_id <> ' ';<\/code>
\nafter that we can set the output back to normal (on screen) mode:
\n.output stdout<\/code>
\nAnd that’s all: you’ll find a csv file of the conversation network in your working directory.
\nOf course once you’ve imported your data into a database you can perform much more complex queries e.g.\u00a0filter them according to the declared language:
\nselect from_user_id,to_user_id from yourtable where text like '@%' and iso_language_code ='it';<\/code>
\nObviously this approach will provide a Reply network with userID instead on UserNames and this can be good during the research phases but we could prefer to have readable usernames for our final visualisations.
\nWe’re going into this problem and we’ll see how to solve it with some awk scripting in our next post.[articolo disponibile solo in inglese]<\/p>\n","protected":false},"excerpt":{"rendered":"

Data preparation is one of the most important and interesting part of every research activity. Recently I’ve really enjoyed Axel’s posts about how to extract conversation networks out of Twapperkeeper archives. Axel uses awk which is a very powerful and flexible tool to manipulate csv files. Obviously there are several ways to manipulate a csv … Continua a leggere Building your Twitter replynet with SQLite<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,1],"tags":[],"class_list":["post-220","post","type-post","status-publish","format-standard","hentry","category-methodology","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sigsna.uniurb.it\/index.php?rest_route=\/wp\/v2\/posts\/220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sigsna.uniurb.it\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sigsna.uniurb.it\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sigsna.uniurb.it\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sigsna.uniurb.it\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=220"}],"version-history":[{"count":0,"href":"https:\/\/sigsna.uniurb.it\/index.php?rest_route=\/wp\/v2\/posts\/220\/revisions"}],"wp:attachment":[{"href":"https:\/\/sigsna.uniurb.it\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sigsna.uniurb.it\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sigsna.uniurb.it\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}