I am trying to export datapoints from mongodb. I was unable to directly connect it to rstudio unfortunately. So from the query outcome I created a text file and attempted to read it as text file in R.

"cityid", "count"
"102","2"
"55","31"
"119","7"
"206","1"
"18","2"
"15","1"
"32","3"
"14","1"
"54","2"
"23","85"
"158","3"
"266","1"
"9","1"
"34","1"
"159","1"
"31","1"
"22","2"
"209","2"
"121","4"
"73","12"
"350","2"
"311","2"
"377","2"
"230","7"
"290","1"
"49","2"
"379","2"
"75","1"
"59","6"
"165","3"
"19","8"
"13","40"
"126","13"
"243","12"
"325","1"
"17","1"
"null","235"
"144","2"
"334","1"
"40","12"
"7","34"
"181","40"
"349","4"

So bascially the format is like above and I would like to convert this into a data frame of which I can make as reference for calculation with other datasets.

This is what I tried to do to make as data frame...

L <- readLines(file.choose())
L.df <- as.data.frame(L)

list <- strsplit(L.df, ",")
library("plyr")
df <- ldply(list)
colnames(df) <- c("city_id", "count")
str(df)
df$city_id <- suppressWarnings(as.numeric(as.character(df$city_id)))

At the last line, I tried to convert the character value as numeric value only to fail and coerced them into NA.

Does anyone have better suggestion to make them as numeric value table? OR is there actually better way to bring the mongodb into R without copying and pasting them as text files? I was successful to connect to mongodb using Rmongo, but the syntax was way too complicated for me to understand.. The query I used is:

db.getCollection('logging_app_location_view_logs').aggregate([
{"$group": {"_id": "$city_id", "total": {"$sum":1}}}
]).forEach(function(l){

  print('"' + l._id + '","' + l.total + '"');

});

Thanks in advance for your help!