Framed Data

Count the occurrence of the city names, and list the top 20. Additional condition: population has to be greater than 100.

Query:

ct.filter( r => r.population>100).
   map( r => (r.asciiname, 1) ).
   reduceByKey( (_ + _)).
   sortBy(-_._2).
   take(20).
   foreach(println)

Result:

(San Antonio,31)
(San Miguel,31)
(San Francisco,28)
(San Jose,26)
(San Isidro,25)
(Santa Cruz,25)
(Buenavista,24)
(Clinton,24)
(Newport,24)
(San Vicente,23)
(Victoria,23)
(Santa Maria,23)
(Richmond,22)
(San Carlos,21)
(Santa Ana,21)
(Georgetown,21)
(San Pedro,20)
(Springfield,20)
(Franklin,20)
(Salem,19)

Another way is to use the RDD.countByValue function which for RDD[T] returns Map[T,Long]. BUT this turns our RDD into a scala.collection.Map[String,Long], ie. it's now a 'local' collection, and no longer distributed.

ct.filter( r => r.population>100).
   map( r => r.asciiname ).
   countByValue().
   toList.
   sortBy(-_._2).
   take(20).foreach(println)

Query:

city_df.filter(" population>100 ").
        groupBy("asciiname").count().
        orderBy(desc("count")).
        take(20).
        foreach(println)

Result:

[San Miguel,31]                                                                 
[San Antonio,31]
[San Francisco,28]
[San Jose,26]
[San Isidro,25]
[Santa Cruz,25]
[Clinton,24]
[Buenavista,24]
[Newport,24]
[Victoria,23]
[Santa Maria,23]
[San Vicente,23]
[Richmond,22]
[Santa Ana,21]
[San Carlos,21]
[Georgetown,21]
[Springfield,20]
[Franklin,20]
[San Pedro,20]
[Greenville,19]

Query:

sqlctx.sql("""
select asciiname, count(1) as cnt
from city   
where population>100
group by asciiname 
order by cnt desc
limit 20
""").collect().foreach(println)

Result:

[San Antonio,31]                                                                
[San Miguel,31]
[San Francisco,28]
[San Jose,26]
[San Isidro,25]
[Santa Cruz,25]
[Clinton,24]
[Buenavista,24]
[Newport,24]
[Santa Maria,23]
[Victoria,23]
[San Vicente,23]
[Richmond,22]
[San Carlos,21]
[Georgetown,21]
[Santa Ana,21]
[Franklin,20]
[Springfield,20]
[San Pedro,20]
[La Union,19]

Making use of the count function of plyr:

library(plyr)

ff=count(df[df$population>100,], "asciiname")
ff[order(-ff$freq)[1:20],]

          asciiname freq
75375   San Antonio   31
76514    San Miguel   31
75784 San Francisco   28
76042      San Jose   26
76000    San Isidro   25
77019    Santa Cruz   25
12900    Buenavista   24
19349       Clinton   24
59090       Newport   24
77184   Santa Maria   23
77720   San Vicente   23
92062      Victoria   23
71507      Richmond   22
30887    Georgetown   21
75521    San Carlos   21
76926     Santa Ana   21
29256      Franklin   20
76679     San Pedro   20
82832   Springfield   20
32755    Greenville   19

dt[population>100][,.N,by=asciiname][order(-N)][1:20]

        asciiname  N
 1:    San Miguel 31
 2:   San Antonio 31
 3: San Francisco 28
 4:      San Jose 26
 5:    San Isidro 25
 6:    Santa Cruz 25
 7:       Newport 24
 8:    Buenavista 24
 9:       Clinton 24
10:   San Vicente 23
11:   Santa Maria 23
12:      Victoria 23
13:      Richmond 22
14:     Santa Ana 21
15:    San Carlos 21
16:    Georgetown 21
17:     San Pedro 20
18:      Franklin 20
19:   Springfield 20
20:       Windsor 19

Applying value_counts() to a dataframe column returns a series.

df[df.population>100]["asciiname"].value_counts()[:20]

San Antonio      31
San Miguel       31
San Francisco    28
San Jose         26
Santa Cruz       25
San Isidro       25
Newport          24
Clinton          24
Buenavista       24
Santa Maria      23
San Vicente      23
Victoria         23
Richmond         22
San Carlos       21
Santa Ana        21
Georgetown       21
San Pedro        20
Franklin         20
Springfield      20
Greenville       19

Another way is to use the collections.Counter:

import collections
ctr=collections.Counter( df[df.population>100]["asciiname"])
ctr.most_common(20) 

[('San Miguel', 31),
 ('San Antonio', 31),
 ('San Francisco', 28),
 ('San Jose', 26),
 ('Santa Cruz', 25),
 ('San Isidro', 25),
 ('Buenavista', 24),
 ('Newport', 24),
 ('Clinton', 24),
 ('Santa Maria', 23),
 ('San Vicente', 23),
 ('Victoria', 23),
 ('Richmond', 22),
 ('San Carlos', 21),
 ('Santa Ana', 21),
 ('Georgetown', 21),
 ('San Pedro', 20),
 ('Franklin', 20),
 ('Springfield', 20),
 ('Salem', 19)]

postgres

select asciiname,count(1) 
from t_city 
where population>100
group by asciiname 
order by 2 desc 
limit 20

   asciiname   | count 
---------------+-------
 San Antonio   |    31
 San Miguel    |    31
 San Francisco |    28
 San Jose      |    26
 San Isidro    |    25
 Santa Cruz    |    25
 Buenavista    |    24
 Clinton       |    24
 Newport       |    24
 San Vicente   |    23
 Santa Maria   |    23
 Victoria      |    23
 Richmond      |    22
 Santa Ana     |    21
 Georgetown    |    21
 San Carlos    |    21
 Franklin      |    20
 Springfield   |    20
 San Pedro     |    20
 Greenville    |    19
(20 rows)

Frequency