Up until this point, we haven't been specifying a specific location of where the tweet was made. As mentioned earlier, this database is constantly updating and collecting tweets from 50 different cities across the United States. This is done by specifying a radius around cooridinates of a city through our API call. Twitter then returns tweets from that specific geographic area whenever that call is made. Now, you may have noticed that the tweet table contains a location_geo column that references the geographic coordinates of where the tweet was sent out to the world. This is one reference that the API will return from our call. You may have also noticed that this column is largely empty. That is simply because most users have this data collection feature turned off (it turns out that it is the default setting to have it turned off). So what about the other tweets, the majority that don't have a location_geo? Well, Twitter has an unspecified algorithm that can identify where a tweeter is based off of particular features.
For our purposes, this city-level location information is stored in the job_id column, specifically ids 255 and 257 through 305. These job ids refer to a job that contains the query for a specific city, therefore, when each job is run, a different city's data is being gathered.
To see how this works, we can start by querying a specific job in the tweet table to return only the data from once city. We can go ahead and query for job id 261. All we have to do is add a WHERE clause to our query and specify that we want job_id to equal 261.
Here are the "Twitter Collection Jobs" we are using in this notebook (and throughout the Twitter collection). Tweets were collected using the Twitter Search API from January 14th, 2017 through May 18, 2017. We continue to collect tweets for these jobs as part of a research project led by James Bain, Sean Goggins & Grant Scott.
| job_id | query | description |
| 255 | q=&geocode=42.5144566,-83.01465259999999,40km | Warren, Michigan |
| 256 | q=&geocode=39.0997,-94.5786,40km | Kansas City, Missouri |
| 257 | q=&geocode=36.7468422,-119.7725868,40km | Fresno, California |
| 258 | q=&geocode=39.103118200000004,-84.5120196,40km | Cincinnati, Ohio |
| 259 | q=&geocode=35.4675602,-97.5164276,40km | Oklahoma City, Oklahoma |
| 260 | q=&geocode=40.6084305,-75.4901833,40km | Allentown, Pennsylvania |
| 261 | q=&geocode=38.95170529999999,-92.33407240000001,40km | Columbia, Missouri |
| 262 | q=&geocode=41.499320000000004,-81.6943605,40km | Cleveland, Ohio |
| 263 | q=&geocode=41.600544799999994,-93.6091064,40km | Des Moines, Iowa |
| 264 | q=&geocode=42.9633599,-85.6680863,40km | Grand Rapids, Michigan |
| 265 | q=&geocode=44.0121221,-92.4801989,40km | Rochester, Minnesota |
| 266 | q=&geocode=33.5805955,-112.23737790000001,40km | Peoria, Arizona |
| 267 | q=&geocode=37.20895720000001,-93.2922989,40km | Springfield, Missouri |
| 268 | q=&geocode=30.2240897,-92.0198427,40km | Lafayette, Louisiana |
| 269 | q=&geocode=38.9822282,-94.6707917,40km | Overland Park, Kansas |
| 270 | q=&geocode=43.0389025,-87.9064736,40km | Milwaukee, Wisconsin |
| 271 | q=&geocode=36.0395247,-114.9817213,40km | Henderson, Nevada |
| 272 | q=&geocode=35.2270869,-80.8431267,40km | Charlotte, North Carolina |
| 273 | q=&geocode=41.8781136,-87.62979820000001,40km | Chicago, Illinois |
| 274 | q=&geocode=25.9017472,-97.4974838,40km | Brownsville, Texas |
| 275 | q=&geocode=42.360082500000004,-71.0588801,40km | Boston, Massachusetts |
| 276 | q=&geocode=30.458282899999997,-91.1403196,40km | Baton Rouge, Louisiana |
| 277 | q=&geocode=33.3061605,-111.8412502,40km | Chandler, Arizona |
| 278 | q=&geocode=39.529632899999996,-119.8138027,40km | Reno, Nevada |
| 279 | q=&geocode=33.7455731,-117.8678338,40km | Santa Ana, California |
| 280 | q=&geocode=41.308274,-72.9278835,40km | New Haven, Connecticut |
| 281 | q=&geocode=36.060949,-95.7974526,40km | Broken Arrow, Oklahoma |
| 282 | q=&geocode=40.5187154,-74.4120953,40km | Edison, New Jersey |
| 283 | q=&geocode=42.2711311,-89.09399520000001,40km | Rockford, Illinois |
| 284 | q=&geocode=39.9525839,-75.1652215,40km | Philadelphia, Pennsylvania |
| 285 | q=&geocode=40.6096698,-111.9391031,40km | West Jordan, Utah |
| 286 | q=&geocode=36.0998596,-80.24421600000001,40km | Winston Salem, North Carolina |
| 287 | q=&geocode=32.5251516,-93.75017890000001,40km | Shreveport, Louisiana |
| 288 | q=&geocode=31.7618778,-106.48502169999999,40km | El Paso, Texas |
| 289 | q=&geocode=33.5206608,-86.80249,40km | Birmingham, Alabama |
| 290 | q=&geocode=42.8864468,-78.8783689,40km | Buffalo, New York |
| 291 | q=&geocode=40.7127837,-74.00594129999999,40km | New York City, New York |
| 292 | q=&geocode=37.9715592,-87.5710898,40km | Evansville, Indiana |
| 293 | q=&geocode=32.776474900000004,-79.9310512,40km | Charleston, South Carolina |
| 294 | q=&geocode=44.953702899999996,-93.0899578,40km | Saint Paul, Minnesota |
| 295 | q=&geocode=45.5001357,-122.43020130000001,40km | Gresham, Oregon |
| 296 | q=&geocode=38.804835499999996,-77.0469214,40km | Alexandria, Virginia |
| 297 | q=&geocode=29.7604267,-95.36980279999999,40km | Houston, Texas |
| 298 | q=&geocode=40.6936488,-89.58898640000001,40km | Peoria, Illinois |
| 299 | q=&geocode=32.8546197,-79.9748103,40km | North Charleston, South Carolina |
| 300 | q=&geocode=40.233843799999995,-111.6585337,40km | Provo, Utah |
| 301 | q=&geocode=35.222566799999996,-97.4394777,40km | Norman, Oklahoma |
| 302 | q=&geocode=33.4734978,-82.0105148,40km | Augusta, Georgia |
| 303 | q=&geocode=47.6062095,-122.33207079999998,40km | Seattle, Washington |
| 304 | q=&geocode=35.99403289999999,-78.898619,40km | Durham, North Carolina |
| 305 | q=&geocode=39.768403,-86.158068,40km | Indianapolis, Indiana |
In [1]:
# BE SURE TO RUN THIS CELL BEFORE ANY OF THE OTHER CELLS
import psycopg2
import pandas as pd
In [3]:
# query database
statement = """
SELECT *
FROM twitter.tweet
WHERE job_id = 261
LIMIT 1000;
"""
try:
connect_str = "dbname='twitter' user='dsa_ro_user' host='dbase.dsa.missouri.edu'password='readonly'"
# use our connection values to establish a connection
conn = psycopg2.connect(connect_str)
cursor = conn.cursor()
cursor.execute(statement)
column_names = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()
except Exception as e:
print("Uh oh, can't connect. Invalid dbname, user or password?")
print(e)
# create dictionary from the rows and column names
job_261 = {}
for i in list(range(len(column_names))):
job_261['{}'.format(column_names[i])] = [x[i] for x in rows]
# turn dictionary into a data frame
pd.DataFrame(job_261)
Out[3]:
analysis_state
created_at
from_user
from_user_created_at
from_user_favorites
from_user_followers
from_user_following
from_user_fullname
from_user_id_str
from_user_name
...
job_id
location_geo
location_geo_0
location_geo_1
source
text
to_user
to_user_id_str
to_user_name
tweet_id_str
0
0
2017-02-08 02:47:37
373136218
2011-09-14 01:55:46
829
491
502
Cole Lawson
373136218
WilliamDeOro
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @HaywardKory: This is the letter if you hav...
None
None
None
829159724344082437
1
0
2017-02-08 02:47:40
241977300
2011-01-23 16:46:44
14847
629
785
Macho Man
241977300
Dean_Bertelsen
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @Dave_Matter: Avery Johnson has Bama 7-4 in...
None
None
None
829159734364295172
2
0
2017-02-08 02:47:56
233665774
2011-01-03 20:32:50
55300
1030
1657
brit dawson
233665774
britnidowsin
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
idk just kinda over it
None
None
None
829159800600719362
3
0
2017-02-08 02:47:56
241977300
2011-01-23 16:46:44
14847
629
785
Macho Man
241977300
Dean_Bertelsen
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @Dave_Matter: Don't look now, but SEC hoops...
None
None
None
829159801250836480
4
0
2017-02-08 02:47:59
2334609456
2014-02-09 05:49:12
1523
514
908
Blake Tarrants
2334609456
btsportsman
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Won't really change how I feel, tbh. Especiall...
None
None
None
829159812680314880
5
0
2017-02-08 02:48:00
2415061772
2014-03-28 00:08:13
11544
436
1274
Donell McGloson
2415061772
CoachDonellRB
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @cj_teague: Rock Bridge boys team improves ...
None
None
None
829159820737519616
6
0
2017-02-08 02:48:22
1411394016
2013-05-07 22:28:21
17505
468
1007
DCMDSports
1411394016
DCMDSports
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @Dave_Matter: Avery Johnson has Bama 7-4 in...
None
None
None
829159913008033792
7
0
2017-02-08 02:48:23
821766421923373060
2017-01-18 17:09:17
0
12
204
Benn y Mo
821766421923373060
newsinCoMo
...
261
None
None
None
<a href="http://publicize.wp.com/" rel="nofoll...
Reflection on Pancake and Sausage https://t.c...
None
None
None
829159913591025668
8
0
2017-02-08 02:48:28
2734157038
2014-08-05 13:37:18
14573
533
285
Brodieâ¢
2734157038
Marco_Hill12
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouHoops: Tigers in College Station! #M...
None
None
None
829159937536380929
9
0
2017-02-08 02:48:28
18330216
2008-12-23 09:28:11
1872
606
2439
Chad McVeigh
18330216
radiobuff
...
261
None
None
None
<a href="http://www.facebook.com/twitter" rel=...
Not bad first half. Damian Lillard was feeling...
None
None
None
829159938148667392
10
0
2017-02-08 02:48:30
752149663
2012-08-12 00:22:32
5854
1064
3013
Kwynten Gage
752149663
KwyntenGage
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Dave_Matter: Avery Johnson has Bama 7-4 in...
None
None
None
829159946323378176
11
0
2017-02-08 02:48:46
24638084
2009-03-16 02:54:29
24622
2098
756
Brandon
24638084
SabanNation_15
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Dave_Matter: Avery Johnson has Bama 7-4 in...
None
None
None
829160011502854144
12
0
2017-02-08 02:48:47
1529434586
2013-06-19 02:12:31
31301
1084
696
silly billy willie
1529434586
basedsilly_
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Dave_Matter: Avery Johnson has Bama 7-4 in...
None
None
None
829160015281917952
13
0
2017-02-08 02:49:01
86758198
2009-11-01 17:04:46
6701
2039
749
Danny Jones
86758198
daniel_m_jones
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
Crazy 4 OT thriller vs. South Carolina. https:...
None
None
None
829160075877031936
14
0
2017-02-08 02:49:01
1660994196
2013-08-10 21:46:00
2093
351
328
George Young
1660994196
GYCoMo
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @JoeVozzelli: Latest ESPN bracketology keep...
None
None
None
829160076267180032
15
0
2017-02-08 02:49:28
800196272271474689
2016-11-20 04:37:13
362
163
365
Kelli Albrecht
800196272271474689
AlbrechtKelli
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
I date the most wonderful boy in the world htt...
None
None
None
829160188108296192
16
0
2017-02-08 02:49:25
30033679
2009-04-09 18:10:47
21487
331
559
Tom Wes
30033679
MUfan17
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Dave_Matter: Don't look now, but SEC hoops...
None
None
None
829160174363475970
17
0
2017-02-08 02:49:30
3091174640
2015-03-16 21:45:06
86423
461
823
UnbreakabLily
3091174640
dylevolution
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @HaywardKory: This is the letter if you hav...
None
None
None
829160195418755072
18
0
2017-02-08 02:49:32
76483392
2009-09-22 23:11:53
5276
823
1553
Jeff Parles
76483392
jeffparles
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Oh https://t.co/zobSflLQdb
None
None
None
829160203975221248
19
0
2017-02-08 02:49:32
78491618
2009-09-30 02:38:02
19606
13522
933
Bud Lane
78491618
bud22089
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Dave_Matter: Avery Johnson has Bama 7-4 in...
None
None
None
829160205971750914
20
0
2017-02-08 02:49:33
713444644164648960
2016-03-25 19:17:13
4877
202
822
Jordan Carpenter
713444644164648960
F3_Gus
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @Dave_Matter: Avery Johnson has Bama 7-4 in...
None
None
None
829160207553019904
21
0
2017-02-08 02:49:33
1547225467
2013-06-26 03:10:37
218
51
230
Anthony Guidarini
1547225467
anthony03168015
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @xblandx: A @columbiatribune farewell colum...
None
None
None
829160210644213766
22
0
2017-02-08 02:49:40
16421755
2008-09-23 17:02:33
1910
529
389
Mitzi Clayton
16421755
Mitzi_Clayton
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @MizzouTSF: #DidYouKnow in addition to prac...
None
None
None
829160240063082496
23
0
2017-02-08 02:49:47
1660994196
2013-08-10 21:46:00
2093
351
328
George Young
1660994196
GYCoMo
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
@ahodgson92 Baylor apparently using Roundup as...
2515891722
2515891722
ahodgson92
829160268835991552
24
0
2017-02-08 02:49:50
96795379
2009-12-14 16:49:16
4550
693
2222
Josh Kranzberg
96795379
JEKranzberg
...
261
None
None
None
<a href="https://about.twitter.com/products/tw...
No words...literally no words https://t.co/EtF...
None
None
None
829160281683132416
25
0
2017-02-08 02:49:53
743339821
2012-08-07 17:24:14
447
88
422
Kevin W. Martin
743339821
KWMSocCub8
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Ultimately the point that is missed, is that i...
743339821
743339821
KWMSocCub8
829160294601601024
26
0
2017-02-08 02:49:53
1631488910
2013-07-29 23:18:09
23801
772
2205
Mistica Bell RTR
1631488910
Crimson3Pointer
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @Dave_Matter: Avery Johnson has Bama 7-4 in...
None
None
None
829160294819778562
27
0
2017-02-08 02:49:55
3593438775
2015-09-09 01:42:52
642
83
310
Parker Randolph
3593438775
parkerbrandolph
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Dave_Matter: Avery Johnson has Bama 7-4 in...
None
None
None
829160303120232449
28
0
2017-02-08 02:50:00
298842801
2011-05-15 01:43:40
12312
827
2511
Ed Lova
298842801
edlova31
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @Dave_Matter: Avery Johnson has Bama 7-4 in...
None
None
None
829160321713573888
29
0
2017-02-08 02:50:01
278285780
2011-04-07 00:12:16
1492
285
318
Vallerie Lynn
278285780
valtrk
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouWrestling: #Mizzou checks in at No. ...
None
None
None
829160324834156544
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
970
0
2017-02-08 04:56:57
847594885
2012-09-26 15:19:01
631
662
460
Brian White
847594885
Brian_E_White_
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouTSF: #DidYouKnow in addition to prac...
None
None
None
829192269345460224
971
0
2017-02-08 04:57:08
380564046
2011-09-26 21:20:58
2374
1404
786
Dan Ebner
380564046
WeatherEbner
...
261
None
None
None
<a href="https://about.twitter.com/products/tw...
TONIGHT: Mostly cloudy with lows in the upper ...
None
None
None
829192315927425024
972
0
2017-02-08 04:57:21
1411603476
2013-05-08 00:52:29
4524
760
348
Isaiah Patrick
1411603476
Youngi_Patrick
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @kyjuan_collins: Happy Gday to one of my id...
None
None
None
829192370147188739
973
0
2017-02-08 04:57:36
785318375652225025
2016-10-10 03:17:46
87
125
122
Emmalee
785318375652225025
KingSavage0210
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
This tooth ache gonna kill me
None
None
None
829192434492067842
974
0
2017-02-08 04:57:32
380564046
2011-09-26 21:20:58
2374
1404
786
Dan Ebner
380564046
WeatherEbner
...
261
None
None
None
<a href="https://about.twitter.com/products/tw...
TOMORROW: Mix of sun and clouds with highs in ...
None
None
None
829192417639272450
975
0
2017-02-08 04:57:33
4364923707
2015-12-03 20:51:38
19063
1735
4827
b hatch
4364923707
u3Y4BDE
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @Stardustspeck: Our Articles on the Attacks...
None
None
None
829192420755652609
976
0
2017-02-08 04:57:40
826865642896838666
2017-02-01 18:51:46
67
3
16
Anna Newman
826865642896838666
6vb6WsiPzvFrWy6
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @EjOKeefe: @HalleKearns told me to send mor...
None
None
None
829192451592171523
977
0
2017-02-08 04:57:46
786210891490471936
2016-10-12 14:24:18
7996
142
67
Donald Aper
786210891490471936
donald_aper
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
@Patriotic_Me @KatLaughlin1 Now............THA...
35216399
35216399
Patriotic_Me
829192476166610944
978
0
2017-02-08 04:58:11
1393756832
2013-05-01 04:11:54
2734
343
755
Davonte
1393756832
Davontekyles
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
@MELOD1P dropping 92!!!!!! Where's the highlig...
1553895072
1553895072
MELOD1P
829192580336386048
979
0
2017-02-08 04:58:14
2500482547
2014-05-17 03:13:10
6211
503
497
rylee
2500482547
BeelerRylee
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@jammiemartin201 why tf is she legit me i'm 4t...
3246561775
3246561775
jammiemartin201
829192592004890626
980
0
2017-02-08 04:58:20
38786885
2009-05-09 03:17:32
19
217
332
Maurice Tolson
38786885
Reace84
...
261
None
None
None
<a href="http://itunes.apple.com/us/app/twitte...
My feet are beyond sore after the past week. B...
None
None
None
829192618831642629
981
0
2017-02-08 04:58:22
1393756832
2013-05-01 04:11:54
2734
343
755
Davonte
1393756832
Davontekyles
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
@macpayne25
2368173948
2368173948
macpayne25
829192627056758785
982
0
2017-02-08 04:58:23
2183567150
2013-11-09 04:23:10
1631
322
424
Ryan Oberlag
2183567150
ryanobe4
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
I'm sorry what? I can't hear you over my origi...
None
None
None
829192631062261763
983
0
2017-02-08 04:59:07
2655009922
2014-06-30 01:47:33
5000
252
416
miss mo
2655009922
beckiejake
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
"Keep it sexy. If I don't want to fuck your st...
None
None
None
829192813950726144
984
0
2017-02-08 04:59:21
284352660
2011-04-19 03:56:01
2624
1057
1867
Angela Speck
284352660
Stardustspeck
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
https://t.co/qBvBpRLnKz Why Stop At Rosie? 'SN...
None
None
None
829192875279806465
985
0
2017-02-08 04:59:34
2515891722
2014-05-22 17:13:22
1312
227
572
Andrew Hodgson
2515891722
ahodgson92
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
I don't think I had 92 points total in five ye...
None
None
None
829192929646362625
986
0
2017-02-08 04:59:46
38327512
2009-05-07 01:04:51
2600
82
253
Cheryl S. Rosenfeld
38327512
baskincs
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@JuddLegum vote them out!!
15464697
15464697
JuddLegum
829192980770746368
987
0
2017-02-08 04:59:56
632655627
2012-07-11 04:21:46
2785
457
338
Erika.
632655627
ErikaLutz_
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
I LOVE YOU https://t.co/aXZRnsSGw3
None
None
None
829193019882606593
988
0
2017-02-08 04:59:56
786210891490471936
2016-10-12 14:24:18
7996
143
67
Donald Aper
786210891490471936
donald_aper
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
@FrancyFish Damn!!! Is it good genes? Good par...
21995179
21995179
FrancyFish
829193021061206018
989
0
2017-02-08 05:00:01
303852475
2011-05-23 14:40:47
666
623
477
Press Partners
303852475
AlfredFriendly
...
261
None
None
None
<a href="https://about.twitter.com/products/tw...
#media outlets have already seen a jump in sub...
None
None
None
829193040266874880
990
0
2017-02-08 05:00:09
85883846
2009-10-28 18:54:53
132
67
569
Regine Brown
85883846
Regine_Brown18
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @amandakkins: I had to go through FOUR diff...
None
None
None
829193073368453124
991
0
2017-02-08 05:00:49
822166077014679552
2017-01-19 19:37:22
2
4
101
Tess O'Brien
822166077014679552
tessobrien2150
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Mizzou speaks up on the issue of the immigrant...
None
None
None
829193243267067909
992
0
2017-02-08 05:01:05
786210891490471936
2016-10-12 14:24:18
7996
143
67
Donald Aper
786210891490471936
donald_aper
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
@RealVinnieJames Sure, even those of us with o...
707246777515245572
707246777515245572
RealVinnieJames
829193312120795139
993
0
2017-02-08 05:01:11
822298858696933376
2017-01-20 04:25:00
9
1
37
Madison Skahill
822298858696933376
Madi_Skahill
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Perpspective is important, but this article re...
None
None
None
829193335537557508
994
0
2017-02-08 05:01:23
887746046
2012-10-17 23:38:12
4523
288
508
Bailey Malone
887746046
JuneBailey95
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @baileyann_25:
None
None
None
829193384204042240
995
0
2017-02-08 05:01:33
727019325773762560
2016-05-02 06:18:09
33
70
232
Tanner Ragan
727019325773762560
tanner_ragan
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
"I love her. Well, I love PARTS of her." - Mic...
None
None
None
829193429448085506
996
0
2017-02-08 05:01:35
796395278
2012-09-01 16:15:58
710
285
387
Ashton Boatman
796395278
123AshtonCoby
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Fayette_Hoops: Good win over Atlanta 82-23...
None
None
None
829193434057605120
997
0
2017-02-08 05:01:38
786210891490471936
2016-10-12 14:24:18
7996
143
67
Donald Aper
786210891490471936
donald_aper
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
@KatLaughlin1 @Patriotic_Me You are sooooo bad...
785323004796424195
785323004796424195
KatLaughlin1
829193450453073922
998
0
2017-02-08 05:01:45
605749209
2012-06-11 19:10:55
3248
213
273
samantha
605749209
sam_beiter
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @nikkisindelar: i wonder what it feels like...
None
None
None
829193478584270848
999
0
2017-02-08 05:01:48
4107672913
2015-11-03 01:13:37
330
213
237
Tolton Managers
4107672913
toltonmanagers
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Toltonbball: Congrats to @JonnyBerndt on ...
None
None
None
829193492421279745
1000 rows × 23 columns
Wonderful! But we are missing what exactly job id 261 means, however, this information isn't stored in the tweet table. Instead, it is located in the job table. This is the table that is responsible for collecting all of the data. It also contains a description column that contains the city name for that job. Let's go ahead and JOIN this column to the query that we created above so we can see what city we are looking at.
In [5]:
# query database
statement = """
SELECT j.description, t.*
FROM twitter.tweet t, twitter.job j
WHERE t.job_id = 261 AND t.job_id = j.job_id
LIMIT 1000;
"""
try:
connect_str = "dbname='twitter' user='dsa_ro_user' host='dbase.dsa.missouri.edu'password='readonly'"
# use our connection values to establish a connection
conn = psycopg2.connect(connect_str)
cursor = conn.cursor()
cursor.execute(statement)
column_names = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()
except Exception as e:
print("Uh oh, can't connect. Invalid dbname, user or password?")
print(e)
# create dictionary from the rows and column names
job_261 = {}
for i in list(range(len(column_names))):
job_261['{}'.format(column_names[i])] = [x[i] for x in rows]
# turn dictionary into a data frame
pd.DataFrame(job_261)
Out[5]:
analysis_state
created_at
description
from_user
from_user_created_at
from_user_favorites
from_user_followers
from_user_following
from_user_fullname
from_user_id_str
...
job_id
location_geo
location_geo_0
location_geo_1
source
text
to_user
to_user_id_str
to_user_name
tweet_id_str
0
0
2017-02-08 05:01:05
Columbia, Missouri
786210891490471936
2016-10-12 14:24:18
7996
143
67
Donald Aper
786210891490471936
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
@RealVinnieJames Sure, even those of us with o...
707246777515245572
707246777515245572
RealVinnieJames
829193312120795139
1
0
2017-02-08 05:01:11
Columbia, Missouri
822298858696933376
2017-01-20 04:25:00
9
1
37
Madison Skahill
822298858696933376
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Perpspective is important, but this article re...
None
None
None
829193335537557508
2
0
2017-02-08 05:01:23
Columbia, Missouri
887746046
2012-10-17 23:38:12
4523
288
508
Bailey Malone
887746046
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @baileyann_25:
None
None
None
829193384204042240
3
0
2017-02-08 05:01:33
Columbia, Missouri
727019325773762560
2016-05-02 06:18:09
33
70
232
Tanner Ragan
727019325773762560
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
"I love her. Well, I love PARTS of her." - Mic...
None
None
None
829193429448085506
4
0
2017-02-08 05:01:35
Columbia, Missouri
796395278
2012-09-01 16:15:58
710
285
387
Ashton Boatman
796395278
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Fayette_Hoops: Good win over Atlanta 82-23...
None
None
None
829193434057605120
5
0
2017-02-08 05:01:38
Columbia, Missouri
786210891490471936
2016-10-12 14:24:18
7996
143
67
Donald Aper
786210891490471936
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
@KatLaughlin1 @Patriotic_Me You are sooooo bad...
785323004796424195
785323004796424195
KatLaughlin1
829193450453073922
6
0
2017-02-08 05:01:45
Columbia, Missouri
605749209
2012-06-11 19:10:55
3248
213
273
samantha
605749209
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @nikkisindelar: i wonder what it feels like...
None
None
None
829193478584270848
7
0
2017-02-08 05:01:48
Columbia, Missouri
4107672913
2015-11-03 01:13:37
330
213
237
Tolton Managers
4107672913
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Toltonbball: Congrats to @JonnyBerndt on ...
None
None
None
829193492421279745
8
0
2017-02-08 05:02:01
Columbia, Missouri
14846054
2008-05-20 14:55:02
1882
59
471
fuschiagirl
14846054
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@ananavarro I generally love civil debate, but...
19568591
19568591
ananavarro
829193545630232577
9
0
2017-02-08 05:02:16
Columbia, Missouri
230248552
2010-12-24 19:01:22
1846
1069
1256
SQ2
230248552
...
261
None
None
None
<a href="http://www.tweetjukebox.com" rel="nof...
You'll do more GOOD if you aim to SERVE more t...
None
None
None
829193606539988996
10
0
2017-02-08 05:02:19
Columbia, Missouri
179540697
2010-08-17 15:02:17
589
79
374
Dave Moser
179540697
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Graham Moser unofficial stat line 11 points, 8...
None
None
None
829193621115195394
11
0
2017-02-08 05:02:34
Columbia, Missouri
822166077014679552
2017-01-19 19:37:22
2
4
101
Tess O'Brien
822166077014679552
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Plans for Ted Cruz and Bernie Sanders debate o...
None
None
None
829193684851765251
12
0
2017-02-08 05:02:41
Columbia, Missouri
104565848
2010-01-13 18:33:24
172
145
133
Grace
104565848
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
@jenna_rayanne27 but in reality, we can't #sad...
446769041
446769041
jenna_rayanne27
829193714249633793
13
0
2017-02-08 05:02:44
Columbia, Missouri
401757556
2011-10-31 01:34:26
8002
591
740
Angela Kruse
401757556
...
261
None
None
None
<a href="http://instagram.com" rel="nofollow">...
Without knowing Curie, she seems relaxed here,...
None
None
None
829193725607821319
14
0
2017-02-08 05:02:49
Columbia, Missouri
76483392
2009-09-22 23:11:53
5276
823
1553
Jeff Parles
76483392
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
This seems completely ridiculous. https://t.co...
None
None
None
829193745023266816
15
0
2017-02-08 05:02:56
Columbia, Missouri
745770771478306817
2016-06-23 00:09:42
683
121
203
meeeeena.
745770771478306817
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@gabbbbby_18 @Em_ILY226 lol never hook up with...
966608916
966608916
gabbbbby_18
829193775704637440
16
0
2017-02-08 05:03:03
Columbia, Missouri
786210891490471936
2016-10-12 14:24:18
7996
143
67
Donald Aper
786210891490471936
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
@FrancyFish Take care, buddy
21995179
21995179
FrancyFish
829193805173837826
17
0
2017-02-08 05:03:12
Columbia, Missouri
1405280911
2013-05-05 14:59:33
2866
111
132
mulder
1405280911
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@RoyBlunt fuck you
21269970
21269970
RoyBlunt
829193844889645058
18
0
2017-02-08 05:03:19
Columbia, Missouri
729327563370168321
2016-05-08 15:10:16
1861
106
46
Zachary Motley
729327563370168321
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
Amount of assignments per week.\\nStatics: 3\\...
None
None
None
829193870525227008
19
0
2017-02-08 05:03:27
Columbia, Missouri
3178757012
2015-04-29 01:58:11
14153
468
260
em
3178757012
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
me, 2017 https://t.co/OGy9jiDYSl
None
None
None
829193905186947072
20
0
2017-02-08 05:03:46
Columbia, Missouri
427747319
2011-12-03 22:45:01
29232
693
502
Ashley Kistaitis
427747319
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @macpayne25: OTH is my favorite show at the...
None
None
None
829193984006295552
21
0
2017-02-08 05:03:53
Columbia, Missouri
2921842214
2014-12-07 15:49:03
1376
41
325
Bailey Young
2921842214
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MikeUrsery: Cairo defeats Glasgow 43-39
None
None
None
829194014058565632
22
0
2017-02-08 05:03:57
Columbia, Missouri
2921842214
2014-12-07 15:49:03
1376
41
329
Bailey Young
2921842214
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @bdogs4: HSGBB final \\n\\nNew Franklin 6...
None
None
None
829194030143721472
23
0
2017-02-08 05:03:59
Columbia, Missouri
2921842214
2014-12-07 15:49:03
1376
41
325
Bailey Young
2921842214
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @bdogs4: HSGBB 3rd qtr\\n\\nNew Franklin ...
None
None
None
829194038276403200
24
0
2017-02-08 05:04:00
Columbia, Missouri
76483392
2009-09-22 23:11:53
5276
823
1553
Jeff Parles
76483392
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
I mean, the Falcons defense improved immensely...
None
None
None
829194043888431104
25
0
2017-02-08 05:04:01
Columbia, Missouri
2921842214
2014-12-07 15:49:03
1376
41
329
Bailey Young
2921842214
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @bdogs4: HSGBB halftime\\n\\nNew Franklin ...
None
None
None
829194048586006529
26
0
2017-02-08 05:04:03
Columbia, Missouri
2921842214
2014-12-07 15:49:03
1376
41
325
Bailey Young
2921842214
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @bdogs4: HSGBB 1st qtr\\n\\nNew Franklin 1...
None
None
None
829194055988936704
27
0
2017-02-08 05:04:08
Columbia, Missouri
2921842214
2014-12-07 15:49:03
1376
41
329
Bailey Young
2921842214
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @bdogs4: HSBB 1st qtr\\n\\nNew Franklin ...
None
None
None
829194078969536515
28
0
2017-02-08 05:04:12
Columbia, Missouri
2921842214
2014-12-07 15:49:03
1376
41
325
Bailey Young
2921842214
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @bdogs4: HSBB halftime\\n\\nNew Franklin ...
None
None
None
829194093779701760
29
0
2017-02-08 05:04:13
Columbia, Missouri
833816388
2012-09-19 17:48:31
7209
948
396
ĸ É r É n z a
833816388
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @lakoskey31: it's the little things that ca...
None
None
None
829194098066259972
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
970
0
2017-02-08 13:34:52
Columbia, Missouri
786210891490471936
2016-10-12 14:24:18
7996
142
67
Donald Aper
786210891490471936
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
Maybe McConnell will let her on the Senate flo...
None
None
None
829322607837196289
971
0
2017-02-08 13:35:24
Columbia, Missouri
90488007
2009-11-16 21:25:24
567
89
378
Carrie Collier
90488007
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
May have heard this myself before. Proud to be...
None
None
None
829322742830874625
972
0
2017-02-08 13:36:22
Columbia, Missouri
786210891490471936
2016-10-12 14:24:18
7996
142
67
Donald Aper
786210891490471936
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
@gradeplg1 @pink_lady56 Did you let the words ...
3265591214
3265591214
gradeplg1
829322985307766785
973
0
2017-02-08 13:37:32
Columbia, Missouri
349675489
2011-08-06 14:12:36
1818
199
290
Mickey â®
349675489
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @CrookedFix: The last song from our 3 track...
None
None
None
829323277613023232
974
0
2017-02-08 13:38:24
Columbia, Missouri
321101330
2011-06-21 01:15:25
1871
500
1023
Bill Ellis
321101330
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
.@Duff805 is the man Twitter needs right now.
None
None
None
829323496476000256
975
0
2017-02-08 13:38:36
Columbia, Missouri
1861778160
2013-09-13 21:41:12
2858
229
156
TOM
1861778160
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@kcrossover2012 domenica questa?
507183179
507183179
kcrossover2012
829323549445935104
976
0
2017-02-08 13:38:47
Columbia, Missouri
4808585893
2016-01-24 17:26:50
21
26
62
MNiezing
4808585893
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @CoMissourian: Columbia K-12 educators said...
None
None
None
829323594991812609
977
0
2017-02-08 13:38:53
Columbia, Missouri
325126593
2011-06-27 19:43:37
156688
6356
6991
J. Chris Pires
325126593
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
American Universities Must Take a Stand #Highe...
None
None
None
829323619385933824
978
0
2017-02-08 13:39:42
Columbia, Missouri
1965122851
2013-10-16 16:21:13
198
546
320
CARFAX Careers
1965122851
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Want to work at CARFAX? We're #hiring in #Colu...
None
None
None
829323824386756609
979
0
2017-02-08 13:39:51
Columbia, Missouri
555708780
2012-04-17 03:06:57
1343
136
476
Kendrick Smith
555708780
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
Could really use use some of that global warmi...
None
None
None
829323861942554625
980
0
2017-02-08 13:39:51
Columbia, Missouri
2953941530
2014-12-31 18:46:12
3270
402
556
taylor renfro.
2953941530
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @BekahRenner: People: I don't sleep with a ...
None
None
None
829323863565672449
981
0
2017-02-08 13:40:18
Columbia, Missouri
201753602
2010-10-12 15:06:51
893
2048
1939
Nat.Churchill Museum
201753602
...
261
None
None
None
<a href="http://www.hootsuite.com" rel="nofoll...
"Dogs look up to you, cats look down on you. G...
None
None
None
829323975205453824
982
0
2017-02-08 13:40:21
Columbia, Missouri
2181735726
2013-11-08 08:03:17
1127
1236
511
Jeff Rouder
2181735726
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
@olivier_klein So, does Le Pen benefit?
44344053
44344053
olivier_klein
829323989893779456
983
0
2017-02-08 13:40:29
Columbia, Missouri
90488007
2009-11-16 21:25:24
567
89
378
Carrie Collier
90488007
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Find beauty where you can. https://t.co/ZPrqeH...
None
None
None
829324022479519745
984
0
2017-02-08 13:40:44
Columbia, Missouri
2728173410
2014-08-13 01:55:50
4090
421
460
Ciara Moore
2728173410
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @SoWrestling: 3 big dual wins tonight over ...
None
None
None
829324082780966912
985
0
2017-02-08 13:41:45
Columbia, Missouri
195411820
2010-09-26 16:53:49
3994
177
246
Madeline Clarke
195411820
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@realDonaldTrump @POTUS maybe if your picks we...
25073877
25073877
realDonaldTrump
829324341800206337
986
0
2017-02-08 13:41:46
Columbia, Missouri
2600371586
2014-07-02 19:45:24
452
301
414
Sarah Rausch
2600371586
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Our @lmsleopards are hard at work before schoo...
None
None
None
829324343968661504
987
0
2017-02-08 13:41:48
Columbia, Missouri
780914917
2012-08-25 19:16:05
3705
103
174
Ricky Sirois
780914917
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @robweir: An independent judiciary deciding...
None
None
None
829324353615626241
988
0
2017-02-08 13:42:03
Columbia, Missouri
4039038972
2015-10-27 20:34:16
3343
287
540
Ashley Elizabeth
4039038972
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @CornmealFryin: My bed is SO FREAKING COMFY...
None
None
None
829324415263449088
989
0
2017-02-08 13:42:23
Columbia, Missouri
240158989
2011-01-19 08:40:09
194
669
1431
Non-Flying Dutchman
240158989
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
@WengerIsFrench https://t.co/5DMvNaUQyX
347465214
347465214
WengerIsFrench
829324500550447105
990
0
2017-02-08 13:42:43
Columbia, Missouri
159175944
2010-06-24 17:32:57
16960
1485
2254
sneakily1â¢
159175944
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
oh no, just when you thought it was safe to st...
None
None
None
829324582641360896
991
0
2017-02-08 13:42:45
Columbia, Missouri
4912895466
2016-02-15 21:54:21
424
666
838
Josh Karnowski
4912895466
...
261
None
None
None
<a href="https://about.twitter.com/products/tw...
@MattNapeSPTA @kypac2 \\n1. Our MPTA talk w/ D...
2563766401
2563766401
MattNapeSPTA
829324592544100352
992
0
2017-02-08 13:42:55
Columbia, Missouri
240158989
2011-01-19 08:40:09
194
669
1431
Non-Flying Dutchman
240158989
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
@LeWookieGooner @AFCAMDEN His partnership work...
1410383011
1410383011
LeWookieGooner
829324635653210114
993
0
2017-02-08 13:43:02
Columbia, Missouri
786210891490471936
2016-10-12 14:24:18
7996
142
67
Donald Aper
786210891490471936
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
@gradeplg1 @pink_lady56 Do you honestly think ...
3265591214
3265591214
gradeplg1
829324661561446400
994
0
2017-02-08 13:43:03
Columbia, Missouri
821806223548784641
2017-01-18 19:47:26
88
95
619
CPFF 1055
821806223548784641
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @ABC17News: Grapuel (sleet) hitting the gro...
None
None
None
829324668930764801
995
0
2017-02-08 13:44:07
Columbia, Missouri
159175944
2010-06-24 17:32:57
16960
1485
2254
sneakily1â¢
159175944
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
see, not making this up: \\nhttps://t.co/U6Fw4...
None
None
None
829324934539333632
996
0
2017-02-08 13:44:29
Columbia, Missouri
2571483007
2014-06-16 19:01:28
0
89
8
Dayton Freight Jobs
2571483007
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://tweetmyjobs.com" rel="nofollow...
Can you recommend anyone for this #job in #Col...
None
None
None
829325029687042048
997
0
2017-02-08 13:45:08
Columbia, Missouri
1369452368
2013-04-21 12:28:08
26077
6336
5748
dawn
1369452368
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @BlackSheep_Mizz: Right before you leave fo...
None
None
None
829325192052801536
998
0
2017-02-08 13:45:10
Columbia, Missouri
4749847700
2016-01-12 20:43:03
2223
10737
8825
Joel N. Little
4749847700
...
261
None
None
None
<a href="http://postplanner.com" rel="nofollow...
"Start where you are. Use what you have. Do wh...
None
None
None
829325198289731584
999
0
2017-02-08 13:45:24
Columbia, Missouri
527975074
2012-03-18 00:25:16
308
140
290
Robyn Frame
527975074
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Pajama day!#schoolcounselorsweek #bedroomeyes ...
None
None
None
829325257785876480
1000 rows × 24 columns
There we have it. Take a look at the description column. 261 = Columbia, Missouri. This was achieved by joining the tweet table to the job table's description where job_id = 261. This can be done so easily because job_id in the tweet table is a foreign key, which corresponds to the job_id column in the job table.
Now that we know we are working with tweets from Columbia, MO, let's start digging into some summaries. The first thing we want to look at is whether or not the tweeters of Columbia are tweeting at relatively the same rate.
Using what we learned in the previous twitter notebook, count the number of tweets per user in Columbia when limiting it by 10,000 rows. Do users tweet at relatively the same amount?
In [6]:
# put your code here
# ------------------
# query database
statement = """
SELECT DISTINCT from_user, COUNT(*)
FROM (
SELECT from_user
FROM twitter.tweet
WHERE job_id = 261
LIMIT 10000) AS users
GROUP BY from_user
ORDER BY count;
"""
try:
connect_str = "dbname='twitter' user='dsa_ro_user' host='dbase.dsa.missouri.edu'password='readonly'"
# use our connection values to establish a connection
conn = psycopg2.connect(connect_str)
cursor = conn.cursor()
cursor.execute(statement)
column_names = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()
except Exception as e:
print("Uh oh, can't connect. Invalid dbname, user or password?")
print(e)
# create dictionary from the rows and column names
job_261 = {}
for i in list(range(len(column_names))):
job_261['{}'.format(column_names[i])] = [x[i] for x in rows]
# turn dictionary into a data frame
pd.DataFrame(job_261)
Out[6]:
count
from_user
0
1
100136060
1
1
1004872890
2
1
100532423
3
1
1007913314
4
1
1010269596
5
1
1011609667
6
1
101522107
7
1
1016434656
8
1
101823228
9
1
1018469690
10
1
101875269
11
1
102074232
12
1
102093419
13
1
1025513407
14
1
102855000
15
1
1029145808
16
1
1031005550
17
1
103399093
18
1
1035433584
19
1
1035849884
20
1
1038001897
21
1
1041025459
22
1
1043752657
23
1
1049219233
24
1
1051416174
25
1
1053866899
26
1
105683222
27
1
1060023800
28
1
106279352
29
1
106298092
...
...
...
4198
27
278176278
4199
27
54589218
4200
28
156737248
4201
28
299924991
4202
28
413641306
4203
28
557090270
4204
28
713145331
4205
29
3192691890
4206
31
16365009
4207
33
59211807
4208
34
22535712
4209
34
317135855
4210
35
78898839
4211
37
76483392
4212
38
59677067
4213
39
1879970142
4214
42
86758198
4215
43
344390257
4216
44
214505086
4217
46
2786673238
4218
47
906034194
4219
50
256203380
4220
50
30032915
4221
51
7166582
4222
53
23220667
4223
59
240158989
4224
59
420066395
4225
66
4749847700
4226
72
48932450
4227
79
786210891490471936
4228 rows × 2 columns
SPOILER ALERT! There are some people who tweet a whole lot more than others. These high volume tweeters could distort our diversity measure when we eventually get to calculating it. Since we are interested in how many speakers of a language there are, if there are several people who are tweeting a lot, this could artificially boost the count of a language. Instead, we want to limit our result to one user per city.
Let's do just that. In our query, we want to specify that we only want one row per user. In fact, we can add one more condition on top of that as bilingual/multilingual individuals might be interesting to keep track of, so we can say one row per user per language. This way we can still capture those users who tweet in different languages.
In [33]:
statement = """
SELECT
DISTINCT ON (from_user, iso_language)
*
FROM (SELECT * FROM twitter.tweet WHERE job_id = 261 LIMIT 10000) as T
ORDER BY from_user, iso_language;
"""
try:
connect_str = "dbname='twitter' user='dsa_ro_user' host='dbase.dsa.missouri.edu'password='readonly'"
# use our connection values to establish a connection
conn = psycopg2.connect(connect_str)
cursor = conn.cursor()
cursor.execute(statement)
column_names = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()
except Exception as e:
print("Uh oh, can't connect. Invalid dbname, user or password?")
print(e)
# create dictionary from the rows and column names
job_261 = {}
for i in list(range(len(column_names))):
job_261['{}'.format(column_names[i])] = [x[i] for x in rows]
# turn dictionary into a data frame
pd.DataFrame(job_261)
Out[33]:
analysis_state
created_at
from_user
from_user_created_at
from_user_favorites
from_user_followers
from_user_following
from_user_fullname
from_user_id_str
from_user_name
...
job_id
location_geo
location_geo_0
location_geo_1
source
text
to_user
to_user_id_str
to_user_name
tweet_id_str
0
0
2017-05-03 02:58:57
1000421880
2012-12-09 23:20:35
5244
606
572
Reagan
1000421880
ReaganSophia1
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @chloefischer_: Go check out my end of the ...
None
None
None
859603154207461376
1
0
2017-05-02 19:55:10
1000678674
2012-12-10 02:05:52
148424
1211
2024
1000678674
NerbieDansers
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @CS_1950: This is what happens when Black S...
None
None
None
859496506432397312
2
0
2017-05-03 02:20:42
1002757896
2012-12-10 23:01:48
7754
152
99
Comrade Ian
1002757896
IanMichaelT
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @amelioratinig_: Please enjoy these low qua...
None
None
None
859593527176646659
3
0
2017-05-03 21:29:21
1003030716
2012-12-11 01:39:50
7173
83
202
Jennifer Wyatt
1003030716
wyatjen
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Y107: #ALERT!!! Major phishing scam going...
None
None
None
859882596184059904
4
0
2017-05-02 23:19:37
1003109286
2012-12-11 02:32:56
0
8
8
Deadly Writes
1003109286
DeadlyWrites
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @SWiegenstein: Now Comes the Hard Part htt...
None
None
None
859547959293247489
5
0
2017-05-03 15:44:42
10103692
2007-11-09 17:07:55
580
725
955
Kerry Townsend
10103692
kltown
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Looking forward to learning all things #edtech...
None
None
None
859795861513142273
6
0
2017-05-03 22:01:19
101348100
2010-01-03 00:10:04
2117
600
465
Cayce Hendrickson
101348100
CaseCase11
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouAthletics: Congratulations to #Mizzo...
None
None
None
859890639877451776
7
0
2017-05-03 14:12:52
1014002233
2012-12-15 21:51:23
1004
513
459
Mike Olsen
1014002233
artfulhacker
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @CarrieJWatkins: More than 1 in 4 students ...
None
None
None
859772752496009216
8
0
2017-05-03 16:39:28
1014300108
2012-12-16 02:12:34
123
63
171
Stephen Scott
1014300108
sdsxray347
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @joshcreamer: BooneCountyFire: Below are th...
None
None
None
859809645921284096
9
0
2017-05-02 20:15:54
101691981
2010-01-04 07:31:13
906
316
0
Tommy Meza
101691981
kirbymrkc
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
RT @thelleocenttrop: Hayal 19 yasinda %100 Orj...
None
None
None
859501725647896576
10
0
2017-05-02 22:40:44
102055841
2010-01-05 13:07:24
67
1876
1195
ESPN100.5/105.1 KTGR
102055841
KTGRsports
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
It's a lovely night for #Mizzou Baseball vs. M...
None
None
None
859538174669934593
11
0
2017-05-02 20:11:37
102093419
2010-01-05 15:52:34
0
716
274
Dryer's Shoe Store
102093419
DryersShoeStore
...
261
None
None
None
<a href="http://www.facebook.com/twitter" rel=...
Great story about a Missouri company https://t...
None
None
None
859500648311779329
12
0
2017-05-03 17:13:08
1021371355
2012-12-19 05:04:30
761
1633
725
Tony Mullen
1021371355
TonyKRCG13
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @ToltonAthletics: @Toltonsoccer game v Ells...
None
None
None
859818118100791296
13
0
2017-05-03 01:35:22
1021371355
2012-12-19 05:04:30
761
1629
724
Tony Mullen
1021371355
TonyKRCG13
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @HHS___Baseball: Harrisburg 8 Cairo 1 @Harr...
None
None
None
859582122004054016
14
0
2017-05-03 19:17:58
1022957623
2012-12-19 21:44:06
2063
609
879
Quez
1022957623
marquez_scott
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: Where the faithful girls at
None
None
None
859849531004989440
15
0
2017-05-03 22:53:39
102521700
2010-01-07 00:10:10
3191
2960
1495
RivvDee
102521700
TBB_RivvDee
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DeltaTauAKA: https://t.co/htaTYNCudb
None
None
None
859903809782460416
16
0
2017-05-03 18:35:14
1026807758
2012-12-21 17:28:33
21687
1371
1709
Derrek Hardy
1026807758
Derrek_Hardy
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouLife: PSA: There are no more canoe, ...
None
None
None
859838777128173568
17
0
2017-05-03 11:19:03
102762333
2010-01-07 19:05:16
1527
215
108
Eric Quackenbush
102762333
ea_quackenbush
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @TrumansTales: No, #Mizzou isn't out of the...
None
None
None
859729008921018368
18
0
2017-05-03 18:47:45
102837179
2010-01-08 00:43:21
36135
710
574
cal
102837179
CalistaMariee12
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@marahkay524 I feel like you would do this to ...
62103615
62103615
marahkay524
859841928287977474
19
0
2017-05-03 03:38:23
102848497
2010-01-08 01:35:25
701
135
164
James Skosky
102848497
MST3James
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@_mcchris This couldn't have come at a better ...
122092164
122092164
_mcchris
859613079465852931
20
0
2017-05-02 19:58:13
1028924318
2012-12-22 17:27:44
931
447
816
Sam Newton
1028924318
SamNewton
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @J_Hancock: Discord among Republicans in th...
None
None
None
859497275990712321
21
0
2017-05-03 20:57:40
1029145808
2012-12-22 19:44:40
6847
559
340
paige perego
1029145808
_pPEREGO
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouAthletics: Congratulations to #Mizzo...
None
None
None
859874621734125568
22
0
2017-05-03 17:30:10
1031517073
2012-12-23 23:08:11
6364
250
332
scarefoot contessa
1031517073
packyloud
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MattVereen: Sports â¤ï¸
None
None
None
859822403668541442
23
0
2017-05-03 19:52:39
1033384075
2012-12-24 20:11:46
74
104
140
Amy Poffenbarger
1033384075
coachPoff
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouWBB: #Mizzou's Amber Smith has accep...
None
None
None
859858261822099456
24
0
2017-05-03 21:04:57
103354917
2010-01-09 19:26:48
9295
548
494
Susan McClintic
103354917
SusanJMcClintic
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MissouriSBA: New report shows slowing tax ...
None
None
None
859876456058540033
25
0
2017-05-02 22:36:24
103399093
2010-01-09 22:24:42
14
223
171
Buchroeder's Jeweler
103399093
Buchroeders
...
261
None
None
None
<a href="http://instagram.com" rel="nofollow">...
Our love is not once in a lifetime adrenaline ...
None
None
None
859537081705615362
26
0
2017-05-02 19:39:23
1034009695
2012-12-25 04:06:59
4117
232
606
Luc Leo
1034009695
luc_leo55
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Update, I just missed the bus by 3 minutes so ...
None
None
None
859492533780570114
27
0
2017-05-03 18:19:26
1035849884
2012-12-25 23:33:44
1042
178
303
Keith Hammons
1035849884
keithhambone
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Shelter_Ins: Ready to mow? Maybe, maybe no...
None
None
None
859834801469128704
28
0
2017-05-03 11:00:52
103586238
2010-01-10 14:52:52
186
42
205
GeorgeSoria
103586238
GSoria12
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouHoops:
None
None
None
859724432855773186
29
0
2017-05-03 16:59:03
103694505
2010-01-10 23:23:29
0
22
0
EGAshop
103694505
egastore
...
261
None
None
None
<a href="http://www.facebook.com/twitter" rel=...
One word can say a lot. These shirts are a sim...
None
None
None
859814572009631748
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
4843
0
2017-05-03 17:36:09
969689240
2012-11-25 08:58:27
63
60
200
Dakota Vanderpool
969689240
DelshadowDv
...
261
None
None
None
<a href="http://www.facebook.com/twitter" rel=...
https://t.co/VHJwwvn7tl
None
None
None
859823909482496000
4844
0
2017-05-03 00:15:46
97000497
2009-12-15 15:24:16
31
467
1496
Christopher Dohm
97000497
DohmChris
...
261
None
None
None
<a href="http://instagram.com" rel="nofollow">...
Feeling French on Mardi https://t.co/BT7nkfEiS0
None
None
None
859562088867516417
4845
0
2017-05-02 22:08:58
970789752
2012-11-25 21:13:57
46557
37643
14071
PJ Webb
970789752
PletchaPJWebb
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @Radgirl84: New Book Cover! I need your opi...
None
None
None
859530179387043841
4846
0
2017-05-03 03:55:40
970966874
2012-11-25 23:18:33
4851
9542
7357
JuJu Smiles
970966874
dswizzzlee
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Obey_Khia: @dswizzzlee This goes hella hard!
None
None
None
859617427889500160
4847
0
2017-05-03 17:45:29
97097779
2009-12-16 00:40:12
15217
4854
3635
myON
97097779
myONreader
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MissHeinsClass: .@myONreader @myONnews tha...
None
None
None
859826256916783104
4848
0
2017-05-02 21:46:14
972175584
2012-11-26 15:37:02
8379
686
575
Rob
972175584
_jack_steinbeck
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
studying sucks and making beats is fun so..\\n...
None
None
None
859524456443596800
4849
0
2017-05-03 19:34:22
97230655
2009-12-16 15:30:34
1212
171
794
Wendy Peña
97230655
WendyBPena
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
@Mizzou @TheMPJ1
23620660
23620660
Mizzou
859853660448337921
4850
0
2017-05-03 17:15:14
97260656
2009-12-16 18:18:59
1114
864
889
Ryan Famuliner
97260656
RyanFamuliner
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @KBIA: From foodies to farmers, pesticides ...
None
None
None
859818647266709505
4851
0
2017-05-02 19:01:06
97349265
2009-12-17 03:09:44
653
1192
188
MU International Ctr
97349265
muintctr
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
Travel signatures continue today and tomorrow....
None
None
None
859482901137829890
4852
0
2017-05-02 22:22:58
97362461
2009-12-17 04:24:16
13958
9831
9067
Lindy Ruff's Tie
97362461
LindyRuffsTie
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @essbeeay: https://t.co/IGqDQ7eqnx
None
None
None
859533700702375936
4853
0
2017-05-03 12:25:08
974702137
2012-11-27 20:56:05
22
78
123
Tiffany Rose Designs
974702137
TiffanyRoseGems
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
Love this shot of the King Protea Necklace in ...
None
None
None
859745639957229568
4854
0
2017-05-03 17:35:25
974796103
2012-11-27 21:46:24
18548
427
417
Andy Scherf
974796103
Scherf_N_Turf
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouLife: PSA: There are no more canoe, ...
None
None
None
859823726937952256
4855
0
2017-05-03 17:05:57
97607690
2009-12-18 05:43:27
1104
197
338
Stephen A. Spliff
97607690
The_RealTreezus
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: I hope that nigga break y...
None
None
None
859816308581560320
4856
0
2017-05-03 03:08:37
979731134
2012-11-30 04:23:03
886
3295
2520
Muditis Pottery
979731134
MuditisPottery
...
261
None
None
None
<a href="https://roundteam.co" rel="nofollow">...
RT @ericordway: Sometimes you stop and realize...
None
None
None
859605588061323268
4857
0
2017-05-02 20:42:41
98049626
2009-12-20 04:36:09
447
204
480
Madeline Stanley
98049626
madexposure
...
261
None
None
None
<a href="https://ifttt.com" rel="nofollow">IFT...
Sunsets on the Seine... I'm in heaven. https:/...
None
None
None
859508465609453568
4858
0
2017-05-03 17:18:16
981652778
2012-12-01 02:17:31
631
97
257
Jonathan Root
981652778
rootjb_root
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
Damn. This is harsh. https://t.co/fUVQqqQFaS
None
None
None
859819408470016000
4859
0
2017-05-03 02:24:30
98221246
2009-12-20 22:40:55
7276
2373
1729
Peja Stoy$HMOPavich
98221246
Dalethees
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @JontayPorter: God's timing is the perfect ...
None
None
None
859594483578437632
4860
0
2017-05-03 21:27:36
98675387
2009-12-22 17:02:15
8
75
67
Columbia Recycles
98675387
comorecycles
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
Rain barrels and compost bins on sale now thro...
None
None
None
859882156574814213
4861
0
2017-05-03 10:05:54
989116956
2012-12-04 16:45:02
22
38
258
Thomas Savio
989116956
ThomasSavio1
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @JanetGodonCPRD: Columbia trail system is s...
None
None
None
859710599856521216
4862
0
2017-05-03 14:14:23
99177362
2009-12-24 22:14:54
5182
905
2722
Rebecca Lea
99177362
gdrl
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @NewsEditors: ASNE joined more than 20 orga...
None
None
None
859773133573816320
4863
0
2017-05-03 08:09:06
992201761
2012-12-06 02:00:13
1792
35
118
Amelia
992201761
Yahngmi
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
#visitCoMo https://t.co/oiSGtnhwAI
None
None
None
859681208082542592
4864
0
2017-05-03 13:38:15
993713828
2012-12-06 19:52:09
665
18
171
Karlan Seville
993713828
KarlanSeville
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Mizzou: We're so proud of you, Megan!
None
None
None
859764041304031232
4865
0
2017-05-03 11:28:53
995651192
2012-12-07 18:53:48
130
337
82
Lee Expressive Arts
995651192
LEEexplorers
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
Wonder of the Day: What Is a Cyberbully? - htt...
None
None
None
859731483958943745
4866
0
2017-05-03 18:58:41
996586927
2012-12-08 06:48:22
3666
473
765
Sean Burgess
996586927
sean_burgess1
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouMensGolf: #Mizzou's first @SEC All-F...
None
None
None
859844678132355072
4867
0
2017-05-02 23:19:01
99721059
2009-12-27 14:52:16
10731
669
674
⥠a
99721059
sunwallflower_
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Relatable teen content https://t.co/BsRJO8WeGI
None
None
None
859547807077814273
4868
0
2017-05-02 22:22:48
997441232
2012-12-08 15:59:08
334
15
49
William Konrad
997441232
WilliamKonrad
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @CuonzoMartin: If you're trying to improve ...
None
None
None
859533657970802688
4869
0
2017-05-03 01:34:43
997730624
2012-12-08 18:35:50
503
164
233
Burks
997730624
Mrs__Burks
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MattVillasana: Would love #moedchat Ts to ...
None
None
None
859581957625327616
4870
0
2017-05-03 18:55:58
997767025
2012-12-08 18:52:10
3658
357
124
Stephanie Murray
997767025
stephanie_xoxo
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
I AM AMAZED https://t.co/q1I99lSVui
None
None
None
859843994666270720
4871
0
2017-05-03 19:09:15
997767025
2012-12-08 18:52:10
3659
357
124
Stephanie Murray
997767025
stephanie_xoxo
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@williemiller__ @jhutton21 https://t.co/0ZySH7...
311049841
311049841
williemiller__
859847339879649280
4872
0
2017-05-03 16:36:27
9998602
2007-11-06 14:09:22
1527
2282
744
MU Libraries
9998602
MULibraries
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
Use our Finals Survival Guides to conquer your...
None
None
None
859808884755767296
4873 rows × 23 columns
We can now save this data frame to an object. We will call this unique_users. Let's see if we actually have unique users.
In [34]:
unique_users = pd.DataFrame(job_261)
In [35]:
print("Number of rows: {}".format(len(unique_users)))
print("Number of unique users: {}".format(len(pd.unique(unique_users['from_user']))))
Number of rows: 4873
Number of unique users: 4555
Whoops! The number of unique users is less than the number of rows. That means there are some duplicate users. But remember, we didn't just limit it to unique user. We did it by unique language per user. Let's see if that works.
In [37]:
print("This should be equal to the number of rows of the entire data frame: {}".format(
len(pd.unique(unique_users['from_user'] + unique_users['iso_language']))))
This should be equal to the number of rows of the entire data frame: 4873
And now, after this step, we want to run this query again, but there is one more step. In the previous notebook we counted the number of speakers per iso_language. However, there is a subset of rows that don't provide any meaning to us. You will notice that one of the languages is "und". This is not actually a language. Instead it is Twitter's way of saying "we don't know how to identify the written language of this tweet." Since unidentified languages aren't actually languages, we need to remove these rows where a language isn't specified.
In [45]:
statement = """
SELECT
DISTINCT ON (from_user, iso_language)
*
FROM (SELECT * FROM twitter.tweet WHERE job_id = 261 AND iso_language != 'und' LIMIT 10000) as T
ORDER BY from_user, iso_language;
"""
try:
connect_str = "dbname='twitter' user='dsa_ro_user' host='dbase.dsa.missouri.edu'password='readonly'"
# use our connection values to establish a connection
conn = psycopg2.connect(connect_str)
cursor = conn.cursor()
cursor.execute(statement)
column_names = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()
except Exception as e:
print("Uh oh, can't connect. Invalid dbname, user or password?")
print(e)
# create dictionary from the rows and column names
job_261 = {}
for i in list(range(len(column_names))):
job_261['{}'.format(column_names[i])] = [x[i] for x in rows]
# turn dictionary into a data frame
pd.DataFrame(job_261)
Out[45]:
analysis_state
created_at
from_user
from_user_created_at
from_user_favorites
from_user_followers
from_user_following
from_user_fullname
from_user_id_str
from_user_name
...
job_id
location_geo
location_geo_0
location_geo_1
source
text
to_user
to_user_id_str
to_user_name
tweet_id_str
0
0
2017-05-06 04:34:59
100015134
2009-12-28 18:30:18
91
1159
922
Kathy McQuiggan, CPA
100015134
McQK
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @Kim_Becking: "Perfection is not attainable...
None
None
None
860714487698997249
1
0
2017-05-05 21:56:16
1003021020
2012-12-11 01:34:39
3159
603
643
Brandon A âï¸
1003021020
pfgum2
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @GotGils: When you're chillin on your farm ...
None
None
None
860614144176906243
2
0
2017-05-05 22:56:55
1004425164
2012-12-11 17:02:20
13860
6732
6160
Richard Toye
1004425164
RichardToye
...
261
None
None
None
<a href="http://www.twitter.com" rel="nofollow...
RT @ChurchillMuseum: @jmeacham Watch the 2017 ...
None
None
None
860629408150945797
3
0
2017-05-05 16:06:30
1008180102
2012-12-13 06:59:23
8566
511
410
isaiah edoho
1008180102
IEdoho171
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @TrillNickens: I need some Tequila & Tacos
None
None
None
860526122345406465
4
0
2017-05-06 04:06:07
100844105
2009-12-31 21:04:26
2803
1290
918
SUE Dem
100844105
LoveSade_Xoxo
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: Never be afraid to say wh...
None
None
None
860707221856030720
5
0
2017-05-05 15:51:01
100993821
2010-01-01 15:08:37
10910
331
91
sean sloan
100993821
seancsloan
...
261
\0\0\0\0\0\0\07âÒyC@ö7òWÀ
38.9517373900
-92.2960338500
<a href="http://foursquare.com" rel="nofollow"...
Salad days lunch (at @SamsClub in Columbia, MO...
None
None
None
860522226831020033
6
0
2017-05-06 04:45:21
1011674582
2012-12-14 18:14:56
6915
177
444
Josh Roller
1011674582
teamroller5
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @knox2theZOU: Retweet if you want Kevin Kno...
None
None
None
860717094354202628
7
0
2017-05-05 02:22:17
1013675084
2012-12-15 18:05:02
1074
740
185
Brad Boyer
1013675084
BradBoyerKRES
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @HHS___Baseball: Harrisburg 9 Higbee 4 \\n...
None
None
None
860318702167830528
8
0
2017-05-05 03:21:53
1014151904
2012-12-16 00:00:11
536
272
1089
åå
æ¯æ·å é·
1014151904
ernesto_garay
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: a SMART NIGGA ain't gone ...
None
None
None
860333704178274304
9
0
2017-05-05 22:50:04
101424819
2010-01-03 07:23:02
1005
404
278
Samantha Schott
101424819
SamSchott96
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @ADPiMizzou: We are so excited to release o...
None
None
None
860627684702990340
10
0
2017-05-06 10:29:56
101625312
2010-01-04 01:41:51
27404
1012
626
101625312
colg8smile
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @knox2theZOU: Retweet if you want Kevin Kno...
None
None
None
860803812445106177
11
0
2017-05-05 12:57:36
1018276508
2012-12-17 20:34:56
4228
358
487
Paula Cunningham
1018276508
P2Cham
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouWBB: Bench reaction game always strong
None
None
None
860478585857085441
12
0
2017-05-05 20:52:33
101852818
2010-01-04 20:17:04
3797
6182
2405
Eric Schmitt
101852818
Eric_Schmitt
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @CDTCivilWar: #Mizzou projects fall incomin...
None
None
None
860598111441494016
13
0
2017-05-05 14:04:58
102093419
2010-01-05 15:52:34
0
716
274
Dryer's Shoe Store
102093419
DryersShoeStore
...
261
None
None
None
<a href="http://www.facebook.com/twitter" rel=...
I posted a new photo to Facebook https://t.co/...
None
None
None
860495539854606337
14
0
2017-05-05 20:19:32
1021080727
2012-12-19 01:38:34
20219
627
407
hollywood
1021080727
ssaucyx2
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: I am a very private perso...
None
None
None
860589801787019264
15
0
2017-05-05 20:43:28
1021371355
2012-12-19 05:04:30
764
1633
725
Tony Mullen
1021371355
TonyKRCG13
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
RT @ToltonAthletics: Tolton Catholic baseball ...
None
None
None
860595825897906176
16
0
2017-05-05 01:57:45
102159409
2010-01-05 20:41:46
1609
153
348
Chennyn
102159409
nipplelickercow
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
@lexiiim I fucking hate our government lol
1107630338
1107630338
lexiiim
860312528550809600
17
0
2017-05-05 19:53:57
1022904060
2012-12-19 21:21:43
24902
5978
36
ã
¤
1022904060
bIahniks
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @GotGils: When you're chillin on your farm ...
None
None
None
860583363119161344
18
0
2017-05-05 17:36:07
1023372666
2012-12-20 02:59:07
23237
473
247
madein876
1023372666
brinabeear
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @GotGils: When you're chillin on your farm ...
None
None
None
860548676414038017
19
0
2017-05-06 01:07:51
1023464456
2012-12-20 03:59:11
1454
18
111
Claudia Knudten
1023464456
shihtzutoy
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
@WalshFreedom Wow, most sociopaths are a littl...
236487888
236487888
WalshFreedom
860662360113131520
20
0
2017-05-05 04:46:24
1023907356
2012-12-20 09:44:32
78
33
155
Robert Glydewell
1023907356
BobbyBangN
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @MizzouHoops: âï¸ Prepare for knockout p...
None
None
None
860354971405086720
21
0
2017-05-05 23:39:06
1025057696
2012-12-20 21:25:11
14718
215
182
alayna
1025057696
alaynatoocool
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @AmandaMLundgren: Me on @Ticketmaster at 10...
None
None
None
860640023653228545
22
0
2017-05-05 03:56:19
1025513407
2012-12-21 02:51:11
26540
343
1841
Fig Newton
1025513407
Figglypuff
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @Nanobuds: So.... which Project M fans want...
None
None
None
860342365793509376
23
0
2017-05-05 15:37:42
102762333
2010-01-07 19:05:16
1542
213
101
Eric Quackenbush
102762333
ea_quackenbush
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @TrumansTales: With Kassius Robertson trans...
None
None
None
860518878304251904
24
0
2017-05-05 16:38:08
1027790184
2012-12-22 05:12:12
3876
1546
944
jess
1027790184
jessica_renaeg
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @AmandaMLundgren: Me on @Ticketmaster at 10...
None
None
None
860534086716227585
25
0
2017-05-05 17:58:48
1028924318
2012-12-22 17:27:44
947
449
817
Sam Newton
1028924318
SamNewton
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @CDTCivilWar: .@MoDemParty chair @s_webber ...
None
None
None
860554385356709888
26
0
2017-05-06 00:13:19
1029057296
2012-12-22 18:47:18
439
44
110
shawn frank
1029057296
j72cmc
...
261
None
None
None
<a href="http://twitter.com/#!/download/ipad" ...
RT @knox2theZOU: Retweet if you want Kevin Kno...
None
None
None
860648637260582912
27
0
2017-05-05 20:46:22
1029647160
2012-12-23 02:04:19
4987
249
236
dc
1029647160
d_castaneda4
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @GotGils: When you're chillin on your farm ...
None
None
None
860596554675007488
28
0
2017-05-05 21:48:10
1030125224
2012-12-23 08:11:18
861
50
273
Corey Landreth
1030125224
cl_shocker8
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @knox2theZOU: Retweet if you want Kevin Kno...
None
None
None
860612107892658176
29
0
2017-05-05 14:20:04
1033803878
2012-12-25 01:24:16
13709
1281
666
lv
1033803878
xoxo_laa
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: I am a very private perso...
None
None
None
860499339466113024
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
5170
0
2017-05-06 03:39:50
97698722
2009-12-18 16:17:56
53
10597
10390
Druu !
97698722
F_uckYourSelf
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: Never be afraid to say wh...
None
None
None
860700608269094917
5171
0
2017-05-05 16:48:26
97966069
2009-12-19 19:28:19
827
54
86
Psyched to Write
97966069
psychedtowrite
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @CoMissourian: Did you know that two of the...
None
None
None
860536676153774080
5172
0
2017-05-06 10:31:11
98049626
2009-12-20 04:36:09
447
204
480
Madeline Stanley
98049626
madexposure
...
261
None
None
None
<a href="https://ifttt.com" rel="nofollow">IFT...
The view of Paris from the Sacre-Couer Basilic...
None
None
None
860804128745959424
5173
0
2017-05-05 15:28:57
982977024
2012-12-01 17:30:30
18044
3810
3948
982977024
SayChels_
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: yall niggas be having som...
None
None
None
860516674495275008
5174
0
2017-05-05 22:06:02
983175486
2012-12-01 19:29:29
3569
838
258
Jack Cooper
983175486
jackcooper_2
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @knox2theZOU: Retweet if you want Kevin Kno...
None
None
None
860616602248777730
5175
0
2017-05-05 02:13:36
983771977
2012-12-02 02:49:32
1241
272
139
Jo-zee
983771977
josie_bruhh
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: a SMART NIGGA ain't gone ...
None
None
None
860316517719736320
5176
0
2017-05-05 19:44:38
987742285
2012-12-04 00:46:52
2692
1046
627
Natalie Fleming
987742285
thenatfleming15
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MizzouSoftball: GAMEDAY I It's a perfect
None
None
None
860581021204525056
5177
0
2017-05-05 17:41:57
989530243
2012-12-04 20:28:01
1
10
13
???
989530243
KennadyGee
...
261
None
None
None
<a href="http://twitter.com" rel="nofollow">Tw...
I'm getting an AWESOME sample kit from Legend ...
None
None
None
860550145049587713
5178
0
2017-05-05 13:24:23
989666898
2012-12-04 22:04:47
14262
724
615
TEXASisourSTATE
989666898
TEXASisourSTATE
...
261
None
None
None
<a href="https://mobile.twitter.com" rel="nofo...
RT @ottensam: GOP leadership:\\nFlint STILL do...
None
None
None
860485325117849601
5179
0
2017-05-05 16:54:57
990096026
2012-12-05 03:25:46
2317
266
227
jessica
990096026
jessssorduna
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @AmandaMLundgren: Me on @Ticketmaster at 10...
None
None
None
860538318873706497
5180
0
2017-05-05 22:12:52
990114096
2012-12-05 03:47:00
2762
429
397
Brett Bales
990114096
brettbales1
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @knox2theZOU: Retweet if you want Kevin Kno...
None
None
None
860618324534857729
5181
0
2017-05-06 03:45:15
99134982
2009-12-24 16:50:14
1688
1155
779
Ho'Gotti
99134982
J0nchapman1
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: I don't fear commitment, ...
None
None
None
860701970306355201
5182
0
2017-05-05 21:26:58
991758721
2012-12-05 20:51:38
186
725
1354
Andy Tutin
991758721
AndyTJr4
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
LAST chance FREE Garth Brooks TICKETS...stop b...
None
None
None
860606774096920576
5183
0
2017-05-05 01:45:45
991980552
2012-12-05 23:19:01
85517
1551
864
MARS
991980552
sfergs_
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: a SMART NIGGA ain't gone ...
None
None
None
860309508211527680
5184
0
2017-05-05 19:57:54
992376289
2012-12-06 04:28:17
5484
2584
2460
Deja.
992376289
DejaaDemechaa
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: I'll drop everything for ...
None
None
None
860584357857243138
5185
0
2017-05-06 01:33:35
992397350
2012-12-06 04:53:08
4096
242
749
Michael Cottey
992397350
CotteyMcottey
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @CuonzoMartin: Thank you @18ashlie. I had a...
None
None
None
860668837582303232
5186
0
2017-05-05 21:20:52
993477932
2012-12-06 17:19:06
878
2595
616
Pizza Tree
993477932
pizzatreecomo
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Pizza Elotes and Corne Adada by the slice all ...
None
None
None
860605239375798272
5187
0
2017-05-05 21:02:03
994070832
2012-12-07 00:25:11
633
98
317
Curtis Sloan
994070832
juniorflip81
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @TheMattMoreno: @Lavarbigballer That strate...
None
None
None
860600501863239680
5188
0
2017-05-05 21:28:54
994316971
2012-12-07 03:49:37
4236
215
184
Doodle Bob Jr
994316971
Young_Simp57
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @knox2theZOU: Retweet if you want Kevin Kno...
None
None
None
860607259788939271
5189
0
2017-05-06 01:22:00
995295043
2012-12-07 15:29:07
1826
295
916
Jarred Frank
995295043
OnlyBeJarred
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @knox2theZOU: Retweet if you want Kevin Kno...
None
None
None
860665921668620288
5190
0
2017-05-06 04:42:34
99544614
2009-12-26 17:46:28
8019
326
529
Skip Bishop
99544614
wdrkb
...
261
None
None
None
<a href="http://www.tweetcaster.com" rel="nofo...
RT @cmueagles: TRACK: The CMU men's team is 4t...
None
None
None
860716395776032768
5191
0
2017-05-06 00:53:14
995651192
2012-12-07 18:53:48
130
337
86
Lee Expressive Arts
995651192
LEEexplorers
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
Celebrating our partners in education this mor...
None
None
None
860658679342813185
5192
0
2017-05-06 00:16:41
995786388
2012-12-07 20:20:54
23390
1118
512
Grant
995786388
GTFORTHREE
...
261
None
None
None
<a href="http://twitter.com/download/android" ...
RT @MizzouHoops: "Good energy. Good enthusiasm...
None
None
None
860649482890993665
5193
0
2017-05-05 17:09:38
996192176
2012-12-08 01:08:13
1732
4193
590
Ozark Mtn Biscuit Co
996192176
biscuit_truck
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
We're set up at MU North and Rusk Rehab on @Th...
None
None
None
860542013552205824
5194
0
2017-05-05 16:48:05
996334549
2012-12-08 03:05:03
5479
1138
966
bolby
996334549
bolbyjones21
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @DaOfficialTrell: Where the faithful girls at
None
None
None
860536587578462209
5195
0
2017-05-05 16:58:47
99721059
2009-12-27 14:52:16
10809
667
675
⥠a
99721059
sunwallflower_
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
@Maddie_Enright @Darcee2016 This is all I need...
1222591255
1222591255
Maddie_Enright
860539283433693185
5196
0
2017-05-05 22:40:25
997448912
2012-12-08 16:03:32
18374
314
431
Carren Rogan
997448912
carren_rogan
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @CoMoSports: Here's what Missouri's Schweiz...
None
None
None
860625255919955971
5197
0
2017-05-05 23:48:56
997730624
2012-12-08 18:35:50
505
165
233
Burks
997730624
Mrs__Burks
...
261
None
None
None
<a href="http://twitter.com/download/iphone" r...
RT @MisterDrury: @thelensthinkers @sms_wildcat...
None
None
None
860642497692930049
5198
0
2017-05-05 23:12:04
9998602
2007-11-06 14:09:22
1529
2283
744
MU Libraries
9998602
MULibraries
...
261
None
None
None
<a href="http://bufferapp.com" rel="nofollow">...
Now on display in #EllisLibrary: Visualizing A...
None
None
None
860633221142376448
5199
0
2017-05-06 02:05:10
99988580
2009-12-28 16:24:52
114
360
770
Kristen M Kalz
99988580
kristenmkalz
...
261
None
None
None
<a href="http://instagram.com" rel="nofollow">...
None
None
None
860676783795916801
5200 rows × 23 columns
..and we can check to see if any of these rows with "und" still exist.
In [51]:
df = pd.DataFrame(job_261)
df[df['iso_language'] == 'und']
Out[51]:
analysis_state
created_at
from_user
from_user_created_at
from_user_favorites
from_user_followers
from_user_following
from_user_fullname
from_user_id_str
from_user_name
...
job_id
location_geo
location_geo_0
location_geo_1
source
text
to_user
to_user_id_str
to_user_name
tweet_id_str
0 rows × 23 columns
Okay, and now the final step is to count the number of speakers of each language after all of this clean up is done.
In [53]:
statement = """
SELECT DISTINCT iso_language, COUNT(*)
FROM
(SELECT
DISTINCT ON (from_user, iso_language)
*
FROM (SELECT * FROM twitter.tweet WHERE job_id = 261 AND iso_language != 'und' LIMIT 10000) as T
ORDER BY from_user, iso_language) as UNIQ
GROUP BY iso_language;
"""
try:
connect_str = "dbname='twitter' user='dsa_ro_user' host='dbase.dsa.missouri.edu'password='readonly'"
# use our connection values to establish a connection
conn = psycopg2.connect(connect_str)
cursor = conn.cursor()
cursor.execute(statement)
column_names = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()
except Exception as e:
print("Uh oh, can't connect. Invalid dbname, user or password?")
print(e)
# create dictionary from the rows and column names
job_261 = {}
for i in list(range(len(column_names))):
job_261['{}'.format(column_names[i])] = [x[i] for x in rows]
# turn dictionary into a data frame
pd.DataFrame(job_261)
Out[53]:
count
iso_language
0
1
ar
1
1
fi
2
5
it
3
3
pt
4
20
es
5
1
lt
6
13
in
7
4
nl
8
2
pl
9
4
da
10
7
ht
11
1
cs
12
2
no
13
4
tr
14
3
hi
15
3
ro
16
9
tl
17
22
fr
18
4507
en
19
10
de
20
2
cy
21
3
sv
22
8
et
23
1
eu
Okay, one thing. Most tweets don't have location coordinates tied to it. This is quite unfortunate, but it is what it is. But you can bet that we are collecting data that does have location_geo that is not empty.
Now test your skills out and remove any row where location_geo is Null. For guidance, take a look here (https://www.postgresql.org/docs/9.1/static/functions-comparison.html). Limit by 10,000.
In [2]:
# put your code here
# ------------------
statement = """
SELECT *
FROM twitter.tweet
WHERE job_id = 261 AND location_geo IS NOT NULL
LIMIT 100;
"""
try:
connect_str = "dbname='twitter' user='dsa_ro_user' host='dbase.dsa.missouri.edu'password='readonly'"
# use our connection values to establish a connection
conn = psycopg2.connect(connect_str)
cursor = conn.cursor()
cursor.execute(statement)
column_names = [desc[0] for desc in cursor.description]
rows = cursor.fetchall()
except Exception as e:
print("Uh oh, can't connect. Invalid dbname, user or password?")
print(e)
# create dictionary from the rows and column names
job_261 = {}
for i in list(range(len(column_names))):
job_261['{}'.format(column_names[i])] = [x[i] for x in rows]
# turn dictionary into a data frame
pd.DataFrame(job_261)
Out[2]:
analysis_state
created_at
from_user
from_user_created_at
from_user_favorites
from_user_followers
from_user_following
from_user_fullname
from_user_id_str
from_user_name
...
job_id
location_geo
location_geo_0
location_geo_1
source
text
to_user
to_user_id_str
to_user_name
tweet_id_str
0
0
2017-05-17 15:31:08
17891216
2008-12-05 05:31:10
490
252
237
Michael Yetman
17891216
Lil_Yetti
...
261
\0\0\0\0\0\0\0aÃÓ+eyC@èÙ¬ú\WÀ
38.9484000000
-92.3338000000
<a href="http://instagram.com" rel="nofollow">...
Zero help with laundry\\n#waffleadventures @ C...
None
None
None
864865879984447488
1
0
2017-05-17 15:40:49
72458202
2009-09-08 03:13:30
0
167
129
MO Physician Jobs
72458202
MO_physician
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Can you recommend anyone for this #job in #Col...
None
None
None
864868317315518465
2
0
2017-05-17 15:58:49
22535712
2009-03-02 21:20:35
0
680
316
Missouri Nursing Job
22535712
tmj_MO_NURSING
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Can you recommend anyone for this #job in #Col...
None
None
None
864872846056579072
3
0
2017-05-17 15:59:51
112339233
2010-02-08 04:08:08
0
325
308
TMJ-MO Bank Jobs
112339233
tmj_MO_banking
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Interested in a #job in #Columbia, MO? This co...
None
None
None
864873105084211200
4
0
2017-05-17 16:10:03
39347802
2009-05-11 21:33:12
690
8852
10180
Stovetop Radio
39347802
StovetopMusic
...
261
\0\0\0\0\0\0\0Q. a|C@kàÝWÀ
38.9717293000
-92.3104172000
<a href="http://instagram.com" rel="nofollow">...
https://t.co/TnsNs3y0TA #itunespodcast #indepe...
None
None
None
864875673453809664
5
0
2017-05-17 16:12:19
22535712
2009-03-02 21:20:35
0
680
316
Missouri Nursing Job
22535712
tmj_MO_NURSING
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Interested in a #job in #Columbia, MO? This co...
None
None
None
864876243598036993
6
0
2017-05-17 16:23:22
149735467
2010-05-30 02:50:49
0
335
281
TMJ-MO Transport.
149735467
tmj_MO_transp
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Want to work at Dayton Freight? We're #hiring ...
None
None
None
864879022395711488
7
0
2017-05-17 16:46:55
1135620926
2013-01-31 00:47:45
932
713
1214
Muse
1135620926
MuseComo
...
261
\0\0\0\0\0\0\0îyþ´yC@7àñWÀ
38.9508360000
-92.3272630000
<a href="http://instagram.com" rel="nofollow">...
Happy Hump Day! This tiny metal choker is 20% ...
None
None
None
864884947949043712
8
0
2017-05-17 16:49:48
149735467
2010-05-30 02:50:49
0
335
281
TMJ-MO Transport.
149735467
tmj_MO_transp
...
261
\0\0\0\0\0\0\0!ezx
|C@üGðÐ.WÀ
38.9728232000
-92.7315026000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Join the Advanced Disposal team! See our lates...
None
None
None
864885676226994176
9
0
2017-05-17 16:51:12
376963779
2011-09-20 19:54:17
2047
240
735
Hannah Spaar
376963779
HannahSpaar
...
261
\0\0\0\0\0\0\0d±M*yC@èÀr WÀ
38.9461110000
-92.3288890000
<a href="http://instagram.com" rel="nofollow">...
Just two J-School alumnae a while back
None
None
None
864886028691279872
10
0
2017-05-17 16:55:06
247523403
2011-02-05 00:04:19
7202
419
445
Angie Kernâ
247523403
AngieKern94
...
261
\0\0\0\0\0\0\0NÎPÜsC@GsdåWÀ
38.9051610000
-92.3373960000
<a href="http://instagram.com" rel="nofollow">...
Laughing because it was only 14 inches
None
None
None
864887007885053952
11
0
2017-05-17 16:58:13
750755677254000640
2016-07-06 18:17:56
0
130
331
2020Jobs
750755677254000640
2020Jobs
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Join the Samsung team! See our latest #job ope...
None
None
None
864887792345563136
12
0
2017-05-17 16:58:41
770363154
2012-08-20 22:46:34
6
36
61
Trina Reifsteck
770363154
TrinaReifsteck
...
261
\0\0\0\0\0\0\0ã+Ë!óyC@Ð4§WÀ
38.9527323000
-92.3282259000
<a href="http://instagram.com" rel="nofollow">...
This picture doesn't even do the hair justice
None
None
None
864887910553845761
13
0
2017-05-17 17:17:13
2841269945
2014-10-22 17:06:44
0
932
1
Jobs at VA
2841269945
JobsatVA
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://tweetmyjobs.com" rel="nofollow...
Can you recommend anyone for this #job in #Col...
None
None
None
864892574045044736
14
0
2017-05-17 17:19:47
9906872
2007-11-03 04:07:57
1106
121
360
Mark Thrasher
9906872
thethrasher
...
261
\0\0\0\0\0\0\0³ÑZM®yC@àòýyÿWÀ
38.9506317800
-92.3280930500
<a href="http://foursquare.com" rel="nofollow"...
There's a first time for everythingâ... Can ...
None
None
None
864893220744044544
15
0
2017-05-17 17:23:31
770363154
2012-08-20 22:46:34
6
36
61
Trina Reifsteck
770363154
TrinaReifsteck
...
261
\0\0\0\0\0\0\0ã+Ë!óyC@Ð4§WÀ
38.9527323000
-92.3282259000
<a href="http://instagram.com" rel="nofollow">...
@MeeeganCrenshaw
448253372
448253372
MeeeganCrenshaw
864894161547395074
16
0
2017-05-17 17:30:43
19538069
2009-01-26 15:05:03
263
244
431
Mike Kenagy
19538069
mikekenagy
...
261
\0\0\0\0\0\0\0SþGC@ ¥ÅO,WÀ
39.1484766000
-92.6923689000
<a href="http://instagram.com" rel="nofollow">...
Proud of all the grandkids at school award cer...
None
None
None
864895971569872903
17
0
2017-05-17 18:12:41
39493107
2009-05-12 13:00:05
332
371
658
Lauren Hermann
39493107
laurenahermann
...
261
\0\0\0\0\0\0\0aÃÓ+eyC@èÙ¬ú\WÀ
38.9484000000
-92.3338000000
<a href="http://instagram.com" rel="nofollow">...
Fun on the first grade field trip!!! #hikers #...
None
None
None
864906535654354946
18
0
2017-05-17 18:41:51
867921199
2012-10-08 12:34:59
73
52
131
Josh Cobb, CPT
867921199
IronCobb
...
261
\0\0\0\0\0\0\0v7OuÈC@rPÂLùVÀ
39.0139300000
-91.8953100000
<a href="http://instagram.com" rel="nofollow">...
I had a rabbit once that died from a brain sei...
None
None
None
864913873094234112
19
0
2017-05-17 18:47:04
410805147
2011-11-12 15:59:16
384
257
299
Andy Clay
410805147
AndyHClay
...
261
\0\0\0\0\0\0\0½ 4iC@zTüß WÀ
38.8219185000
-92.5010910000
<a href="http://instagram.com" rel="nofollow">...
Do you ever have one of those days, weeks, or ...
None
None
None
864915184627982337
20
0
2017-05-17 18:51:34
242587778
2011-01-25 03:41:20
102733
815
566
Jennis
242587778
TheSilentRiver
...
261
\0\0\0\0\0\0\0aÃÓ+eyC@èÙ¬ú\WÀ
38.9484000000
-92.3338000000
<a href="http://instagram.com" rel="nofollow">...
Happy birthday to my little, Abby! Wow, where ...
None
None
None
864916320772608001
21
0
2017-05-17 19:03:28
14803134
2008-05-16 19:40:50
6521
87
181
MW
14803134
uporoff
...
261
\0\0\0\0\0\0\0¥À2|C@t&mªWÀ
38.9702940000
-92.3229020000
<a href="http://www.twitter.com" rel="nofollow...
#bikecommute
None
None
None
864919315354710017
22
0
2017-05-17 19:23:40
18386161
2008-12-26 07:13:53
22
978
1953
CynthiaLaboile
18386161
ColumbiaMoHomes
...
261
\0\0\0\0\0\0\0 ªÑ«wC@â̯æ\0WÀ
38.9349310000
-92.2969300000
<a href="http://www.circlepix.com" rel="nofoll...
I would love to show you my #listing at 2615 M...
None
None
None
864924398167314432
23
0
2017-05-17 19:30:15
72458202
2009-09-08 03:13:30
0
167
129
MO Physician Jobs
72458202
MO_physician
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Can you recommend anyone for this #job in #Col...
None
None
None
864926053185110018
24
0
2017-05-17 19:30:40
613620134
2012-06-20 17:46:18
0
53
1
Columbia, MO Deals
613620134
SB_ColumbiaMO
...
261
\0\0\0\0\0\0\0DúíëÀyC@ioð
ÉWÀ
38.9512000000
-92.3248000000
<a href="http://www.simplybestcoupons.com" rel...
Up to 52% Off from Jessica Bevans at Salon 4 a...
None
None
None
864926157099208704
25
0
2017-05-17 19:32:49
1135620926
2013-01-31 00:47:45
932
713
1214
Muse
1135620926
MuseComo
...
261
\0\0\0\0\0\0\0îyþ´yC@7àñWÀ
38.9508360000
-92.3272630000
<a href="http://instagram.com" rel="nofollow">...
Ladies make sure your check out our men's stuf...
None
None
None
864926699896553472
26
0
2017-05-17 20:01:04
367414256
2011-09-03 22:57:41
5260
580
386
rebrabinale
367414256
lanirae0422
...
261
\0\0\0\0\0\0\0aÃÓ+eyC@èÙ¬ú\WÀ
38.9484000000
-92.3338000000
<a href="http://instagram.com" rel="nofollow">...
Spin wildly in your next action âºï¸
None
None
None
864933809657073665
27
0
2017-05-17 20:07:45
80667397
2009-10-07 20:05:59
101
191
221
James Bratten
80667397
TheSkinnyD
...
261
\0\0\0\0\0\0\0aÃÓ+eyC@èÙ¬ú\WÀ
38.9484000000
-92.3338000000
<a href="http://instagram.com" rel="nofollow">...
We're not just super cool prints! For more tha...
None
None
None
864935491208085506
28
0
2017-05-17 20:11:48
838803776
2012-09-22 00:34:01
18803
513
484
Jayy Marie
838803776
JayyNelly0419
...
261
\0\0\0\0\0\0\0Õ/\tþÔxC@÷¥eAôWÀ
38.9440000100
-92.3274081700
<a href="http://instagram.com" rel="nofollow">...
So on Sunday my best friend graduated from The...
None
None
None
864936510902210564
29
0
2017-05-17 20:29:37
41670266
2009-05-21 20:31:03
0
823
528
MO Non-Metro Jobs
41670266
tmj_mo_usa_jobs
...
261
\0\0\0\0\0\0\0EO%^ùC@I
±
WÀ
39.1169851000
-92.2207350000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Interested in a #job in #Hallsville, MO? This ...
None
None
None
864940993010737152
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
70
0
2017-05-18 13:52:47
114031593
2010-02-13 21:42:12
1453
109
71
Mike Coghill
114031593
mcoghill88
...
261
\0\0\0\0\0\0\0Õ/\tþÔxC@÷¥eAôWÀ
38.9440000100
-92.3274081700
<a href="http://instagram.com" rel="nofollow">...
3 years ago today this amazing girl graduated ...
None
None
None
865203513948819457
71
0
2017-05-18 13:54:48
22535712
2009-03-02 21:20:35
0
680
316
Missouri Nursing Job
22535712
tmj_MO_NURSING
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Can you recommend anyone for this #job? Staff ...
None
None
None
865204024454168576
72
0
2017-05-18 13:56:18
22047064
2009-02-26 19:10:14
0
422
299
TMJ-MO-US Sales Jobs
22047064
tmj_mo_sales
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
This #job might be a great fit for you: Sales ...
None
None
None
865204399508922368
73
0
2017-05-18 14:23:21
71313340
2009-09-03 17:47:57
2496
314
710
Henry Thomas Imler
71313340
hankimler
...
261
\0\0\0\0\0\0\0 hðtC@F\rC7=W38.9116219000
-92.3162363200
<a href="http://instagram.com" rel="nofollow">...
I love the way the layout creates a cloud on t...
None
None
None
865211210073079810
74
0
2017-05-18 14:26:32
41670266
2009-05-21 20:31:03
0
823
528
MO Non-Metro Jobs
41670266
tmj_mo_usa_jobs
...
261
\0\0\0\0\0\0\0É^J¸ëC@Ã6<ÔWÀ
39.2103186000
-92.1379539000
<a href="http://www.tweetmyjobs.com" rel="nofo...
See our latest #Centralia, MO #job and click t...
None
None
None
865212011067486208
75
0
2017-05-18 14:28:51
22535712
2009-03-02 21:20:35
0
680
316
Missouri Nursing Job
22535712
tmj_MO_NURSING
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Want to work at Boone Hospital? We're #hiring ...
None
None
None
865212594029645824
76
0
2017-05-18 14:36:26
59967225
2009-07-25 03:24:07
0
501
300
TMJ-MO Retail Jobs
59967225
tmj_mo_retail
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Join the Orscheln F&H team! See our latest...
None
None
None
865214499011215361
77
0
2017-05-18 14:56:42
41670266
2009-05-21 20:31:03
0
823
528
MO Non-Metro Jobs
41670266
tmj_mo_usa_jobs
...
261
\0\0\0\0\0\0\0EO%^ùC@I
±
WÀ
39.1169851000
-92.2207350000
<a href="http://www.tweetmyjobs.com" rel="nofo...
If you're looking for work in #Hallsville, MO,...
None
None
None
865219601856057345
78
0
2017-05-18 15:00:19
16001159
2008-08-26 19:10:57
1672
1667
1462
Sean Moore
16001159
SeanWMoore
...
261
\0\0\0\0\0\0\0Å©ÖÂ,~C@:WWÀ
38.9857410000
-92.2579390000
<a href="http://www.circlepix.com" rel="nofoll...
I would love to show you my #listing at 5205 M...
None
None
None
865220510979731456
79
0
2017-05-18 15:03:44
22535712
2009-03-02 21:20:35
0
680
316
Missouri Nursing Job
22535712
tmj_MO_NURSING
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Can you recommend anyone for this #job? Patien...
None
None
None
865221369906069504
80
0
2017-05-18 15:26:45
41670266
2009-05-21 20:31:03
0
823
528
MO Non-Metro Jobs
41670266
tmj_mo_usa_jobs
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
We're #hiring! Click to apply: Caregiver - htt...
None
None
None
865227163238154240
81
0
2017-05-18 15:32:09
613620134
2012-06-20 17:46:18
0
53
1
Columbia, MO Deals
613620134
SB_ColumbiaMO
...
261
\0\0\0\0\0\0\0ðHPtC@ÓÞà WÀ
38.9087000000
-92.3371000000
<a href="http://www.simplybestcoupons.com" rel...
44% Off Mani-Pedi at Studio Fit Day Spa https:...
None
None
None
865228523274948609
82
0
2017-05-18 15:42:26
2841269945
2014-10-22 17:06:44
0
932
1
Jobs at VA
2841269945
JobsatVA
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://tweetmyjobs.com" rel="nofollow...
Can you recommend anyone for this #job? Pharma...
None
None
None
865231110002720769
83
0
2017-05-18 15:45:11
22047064
2009-02-26 19:10:14
0
423
299
TMJ-MO-US Sales Jobs
22047064
tmj_mo_sales
...
261
\0\0\0\0\0\0\0}óÑÿyC@§Ñ? ÿWÀ
38.9531127000
-92.2968207000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Want to work at Lowe's Stores? We're #hiring i...
None
None
None
865231800288763907
84
0
2017-05-18 15:46:31
464806176
2012-01-15 17:03:43
13026
706
375
Tara Jolley
464806176
tnjolley
...
261
\0\0\0\0\0\0\0Õ/\tþÔxC@÷¥eAôWÀ
38.9440000100
-92.3274081700
<a href="http://instagram.com" rel="nofollow">...
Happy birthday KP ⤠I love you @ Mizzou http...
None
None
None
865232138714656768
85
0
2017-05-18 15:48:58
28572163
2009-04-03 14:23:32
219
223
589
Wil Hoffmann
28572163
pastorwil
...
261
\0\0\0\0\0\0\0 ,¾ü©lC@\rpA¶¬üVÀ
38.8489376000
-91.9480415000
<a href="http://instagram.com" rel="nofollow">...
My music for today. Sad news #chriscornell #RI...
None
None
None
865232753901670401
86
0
2017-05-18 15:56:20
59967225
2009-07-25 03:24:07
0
501
300
TMJ-MO Retail Jobs
59967225
tmj_mo_retail
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Can you recommend anyone for this #job in #Col...
None
None
None
865234609474228225
87
0
2017-05-18 16:18:37
23129267
2009-03-06 22:34:08
13
300
729
James Rowe
23129267
jro1170
...
261
\0\0\0\0\0\0\0BÏfÕçzC@QkwWÀ
38.9602000000
-92.2908000000
<a href="https://untappd.com" rel="nofollow">U...
Drinking an Unfiltered Wheat Beer by @Boulevar...
None
None
None
865240216306671616
88
0
2017-05-18 16:19:21
22535712
2009-03-02 21:20:35
0
680
316
Missouri Nursing Job
22535712
tmj_MO_NURSING
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Interested in a #job in #Columbia, MO? This co...
None
None
None
865240400314761216
89
0
2017-05-18 16:28:24
22047064
2009-02-26 19:10:14
0
423
299
TMJ-MO-US Sales Jobs
22047064
tmj_mo_sales
...
261
\0\0\0\0\0\0\0ä¥{C@$\\n-ëþWÀ
38.9659893000
-92.3749340000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Join the Mutual of Omaha team! See our latest ...
None
None
None
865242679973617668
90
0
2017-05-18 16:31:54
59967225
2009-07-25 03:24:07
0
501
300
TMJ-MO Retail Jobs
59967225
tmj_mo_retail
...
261
\0\0\0\0\0\0\0O©·«C@=ì*IÃ+WÀ
39.1458654000
-92.6837943000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Can you recommend anyone for this #job in #Fay...
None
None
None
865243557241552896
91
0
2017-05-18 16:37:00
2337018415
2014-02-10 17:21:43
351
377
660
The Domain
2337018415
domaincomo
...
261
\0\0\0\0\0\0\0£ &\\nBwC@¬ î²WÀ
38.9317028700
-92.2921710300
<a href="http://instagram.com" rel="nofollow">...
Needing a place to live next year!? Come check...
None
None
None
865244843991347200
92
0
2017-05-18 16:38:40
1485457496
2013-06-05 16:38:37
10173
320
351
Andrea Collette
1485457496
collette_andrea
...
261
\0\0\0\0\0\0\0Ð~nÇzC@êµ<7WÀ
38.9592111700
-92.3627463800
<a href="http://instagram.com" rel="nofollow">...
So grateful to see these two last night. Live ...
None
None
None
865245259806134274
93
0
2017-05-18 16:57:26
72034382
2009-09-06 13:30:48
0
340
270
MO Cust. Srv. Jobs
72034382
tmj_MO_cstsrv
...
261
\0\0\0\0\0\0\0}óÑÿyC@§Ñ? ÿWÀ
38.9531127000
-92.2968207000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Can you recommend anyone for this #job in #Col...
None
None
None
865249983531933696
94
0
2017-05-18 16:58:29
59967225
2009-07-25 03:24:07
0
501
300
TMJ-MO Retail Jobs
59967225
tmj_mo_retail
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Interested in a #job in #Columbia, MO? This co...
None
None
None
865250248985161730
95
0
2017-05-18 17:11:59
19974186
2009-02-03 14:38:01
48
88
110
nichole_photo
19974186
nichole_photo
...
261
\0\0\0\0\0\0\0ðHPLC@½b¹¥WÀ
38.5962000000
-92.2757400000
<a href="http://instagram.com" rel="nofollow">...
Made an exception yesterday and actually picke...
None
None
None
865253645515984896
96
0
2017-05-18 17:30:32
72034382
2009-09-06 13:30:48
0
340
270
MO Cust. Srv. Jobs
72034382
tmj_MO_cstsrv
...
261
\0\0\0\0\0\0\0}óÑÿyC@§Ñ? ÿWÀ
38.9531127000
-92.2968207000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Want to work at Lowe's Stores? We're #hiring i...
None
None
None
865258314283663360
97
0
2017-05-18 17:36:10
22535712
2009-03-02 21:20:35
0
680
316
Missouri Nursing Job
22535712
tmj_MO_NURSING
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Join the Boone Hospital team! See our latest #...
None
None
None
865259733615116288
98
0
2017-05-18 17:43:03
22535712
2009-03-02 21:20:35
0
680
316
Missouri Nursing Job
22535712
tmj_MO_NURSING
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
This #job might be a great fit for you: Patien...
None
None
None
865261463392276481
99
0
2017-05-18 17:55:22
22047064
2009-02-26 19:10:14
0
423
299
TMJ-MO-US Sales Jobs
22047064
tmj_mo_sales
...
261
\0\0\0\0\0\0\0w±zÑyC@Â 4qaWÀ
38.9517053000
-92.3340724000
<a href="http://www.tweetmyjobs.com" rel="nofo...
Interested in a #job in #Columbia, MO? This co...
None
None
None
865264565411393536
100 rows × 23 columns
Content source: MissouriDSA/twitter-locale
Similar notebooks: