Python 实用宝典

Question 1

I am having trouble using json.loads to convert to a dict object and I can’t figure out what I’m doing wrong.The exact error I get running this is

ValueError: Expecting property name: line 1 column 2 (char 1)

Here is my code:

from kafka.client import KafkaClient
from kafka.consumer import SimpleConsumer
from kafka.producer import SimpleProducer, KeyedProducer
import pymongo
from pymongo import MongoClient
import json

c = MongoClient("54.210.157.57")
db = c.test_database3
collection = db.tweet_col

kafka = KafkaClient("54.210.157.57:9092")

consumer = SimpleConsumer(kafka,"myconsumer","test")
for tweet in consumer:
    print tweet.message.value
    jsonTweet=json.loads(({u'favorited': False, u'contributors': None})
    collection.insert(jsonTweet)

I’m pretty sure that the error is occuring at the 2nd to last line

jsonTweet=json.loads({u'favorited': False, u'contributors': None})

but I do not know what to do to fix it. Any advice would be appreciated.

Question 2

json.loads will load a json string into a python dict, json.dumps will dump a python dict to a json string, for example:

>>> json_string = '{"favorited": false, "contributors": null}'
'{"favorited": false, "contributors": null}'
>>> value = json.loads(json_string)
{u'favorited': False, u'contributors': None}
>>> json_dump = json.dumps(value)
'{"favorited": false, "contributors": null}'

So that line is incorrect since you are trying to load a python dict, and json.loads is expecting a valid json string which should have <type 'str'>.

So if you are trying to load the json, you should change what you are loading to look like the json_string above, or you should be dumping it. This is just my best guess from the given information. What is it that you are trying to accomplish?

Also you don’t need to specify the u before your strings, as @Cld mentioned in the comments.

Question 3

I encountered another problem that returns the same error.

Single quote issue

I used a json string with single quotes :

{
    'property': 1
}

But json.loads accepts only double quotes for json properties :

{
    "property": 1
}

Final comma issue

json.loads doesn’t accept a final comma:

{
  "property": "text", 
  "property2": "text2",
}

Solution: `ast` to solve single quote and final comma issues

You can use ast (part of standard library for both Python 2 and 3) for this processing. Here is an example :

import ast
# ast.literal_eval() return a dict object, we must use json.dumps to get JSON string
import json

# Single quote to double with ast.literal_eval()
json_data = "{'property': 'text'}"
json_data = ast.literal_eval(json_data)
print(json.dumps(json_data))
# Displays : {"property": "text"}

# ast.literal_eval() with double quotes
json_data = '{"property": "text"}'
json_data = ast.literal_eval(json_data)
print(json.dumps(json_data))
# Displays : {"property": "text"}

# ast.literal_eval() with final coma
json_data = "{'property': 'text', 'property2': 'text2',}"
json_data = ast.literal_eval(json_data)
print(json.dumps(json_data))
# Displays : {"property2": "text2", "property": "text"}

Using ast will prevent you from single quote and final comma issues by interpet the JSON like Python dictionnary (so you must follow the Python dictionnary syntax). It’s a pretty good and safely alternative of eval() function for literal structures.

Python documentation warned us of using large/complex string :

Warning It is possible to crash the Python interpreter with a sufficiently large/complex string due to stack depth limitations in Python’s AST compiler.

json.dumps with single quotes

To use json.dumps with single quotes easily you can use this code:

import ast
import json

data = json.dumps(ast.literal_eval(json_data_single_quote))

`ast` documentation

ast Python 3 doc

ast Python 2 doc

Tool

If you frequently edit JSON, you may use CodeBeautify. It helps you to fix syntax error and minify/beautify JSON.

I hope it helps.

Question 4

replace all single quotes with double quotes
replace ‘u”‘ from your strings to ‘”‘ … so basically convert internal unicodes to strings before loading the string into json

>> strs = "{u'key':u'val'}"
>> strs = strs.replace("'",'"')
>> json.loads(strs.replace('u"','"'))

Question 5

All other answers may answer your query, but I faced same issue which was due to stray , which I added at the end of my json string like this:

{
 "key":"123sdf",
 "bus_number":"asd234sdf",
}

I finally got it working when I removed extra , like this:

{
 "key":"123sdf",
 "bus_number":"asd234sdf"
}

Hope this help! cheers.

Question 6

used ast, example

In [15]: a = "[{'start_city': '1', 'end_city': 'aaa', 'number': 1},\
...:      {'start_city': '2', 'end_city': 'bbb', 'number': 1},\
...:      {'start_city': '3', 'end_city': 'ccc', 'number': 1}]"
In [16]: import ast
In [17]: ast.literal_eval(a)
Out[17]:
[{'end_city': 'aaa', 'number': 1, 'start_city': '1'},
 {'end_city': 'bbb', 'number': 1, 'start_city': '2'},
 {'end_city': 'ccc', 'number': 1, 'start_city': '3'}]

Question 7

A different case in which I encountered this was when I was using echo to pipe the JSON into my python script and carelessly wrapped the JSON string in double quotes:

echo "{"thumbnailWidth": 640}" | myscript.py

Note that the JSON string itself has quotes and I should have done:

echo '{"thumbnailWidth": 640}' | myscript.py

As it was, this is what the python script received: {thumbnailWidth: 640}; the double quotes were effectively stripped.

Question 8

I have a large amount of data in a collection in mongodb which I need to analyze. How do i import that data to pandas?

I am new to pandas and numpy.

EDIT: The mongodb collection contains sensor values tagged with date and time. The sensor values are of float datatype.

Sample Data:

{
"_cls" : "SensorReport",
"_id" : ObjectId("515a963b78f6a035d9fa531b"),
"_types" : [
    "SensorReport"
],
"Readings" : [
    {
        "a" : 0.958069536790466,
        "_types" : [
            "Reading"
        ],
        "ReadingUpdatedDate" : ISODate("2013-04-02T08:26:35.297Z"),
        "b" : 6.296118156595,
        "_cls" : "Reading"
    },
    {
        "a" : 0.95574014778624,
        "_types" : [
            "Reading"
        ],
        "ReadingUpdatedDate" : ISODate("2013-04-02T08:27:09.963Z"),
        "b" : 6.29651468650064,
        "_cls" : "Reading"
    },
    {
        "a" : 0.953648289182713,
        "_types" : [
            "Reading"
        ],
        "ReadingUpdatedDate" : ISODate("2013-04-02T08:27:37.545Z"),
        "b" : 7.29679823731148,
        "_cls" : "Reading"
    },
    {
        "a" : 0.955931884300997,
        "_types" : [
            "Reading"
        ],
        "ReadingUpdatedDate" : ISODate("2013-04-02T08:28:21.369Z"),
        "b" : 6.29642922525632,
        "_cls" : "Reading"
    },
    {
        "a" : 0.95821381,
        "_types" : [
            "Reading"
        ],
        "ReadingUpdatedDate" : ISODate("2013-04-02T08:41:20.801Z"),
        "b" : 7.28956613,
        "_cls" : "Reading"
    },
    {
        "a" : 4.95821335,
        "_types" : [
            "Reading"
        ],
        "ReadingUpdatedDate" : ISODate("2013-04-02T08:41:36.931Z"),
        "b" : 6.28956574,
        "_cls" : "Reading"
    },
    {
        "a" : 9.95821341,
        "_types" : [
            "Reading"
        ],
        "ReadingUpdatedDate" : ISODate("2013-04-02T08:42:09.971Z"),
        "b" : 0.28956488,
        "_cls" : "Reading"
    },
    {
        "a" : 1.95667927,
        "_types" : [
            "Reading"
        ],
        "ReadingUpdatedDate" : ISODate("2013-04-02T08:43:55.463Z"),
        "b" : 0.29115237,
        "_cls" : "Reading"
    }
],
"latestReportTime" : ISODate("2013-04-02T08:43:55.463Z"),
"sensorName" : "56847890-0",
"reportCount" : 8
}

Question 9

pymongo might give you a hand, followings are some codes I’m using:

import pandas as pd
from pymongo import MongoClient


def _connect_mongo(host, port, username, password, db):
    """ A util for making a connection to mongo """

    if username and password:
        mongo_uri = 'mongodb://%s:%s@%s:%s/%s' % (username, password, host, port, db)
        conn = MongoClient(mongo_uri)
    else:
        conn = MongoClient(host, port)


    return conn[db]


def read_mongo(db, collection, query={}, host='localhost', port=27017, username=None, password=None, no_id=True):
    """ Read from Mongo and Store into DataFrame """

    # Connect to MongoDB
    db = _connect_mongo(host=host, port=port, username=username, password=password, db=db)

    # Make a query to the specific DB and Collection
    cursor = db[collection].find(query)

    # Expand the cursor and construct the DataFrame
    df =  pd.DataFrame(list(cursor))

    # Delete the _id
    if no_id:
        del df['_id']

    return df

Question 10

You can load your mongodb data to pandas DataFrame using this code. It works for me. Hopefully for you too.

import pymongo
import pandas as pd
from pymongo import MongoClient
client = MongoClient()
db = client.database_name
collection = db.collection_name
data = pd.DataFrame(list(collection.find()))

Question 11

Monary does exactly that, and it’s super fast. (another link)

See this cool post which includes a quick tutorial and some timings.

Question 12

As per PEP, simple is better than complicated:

import pandas as pd
df = pd.DataFrame.from_records(db.<database_name>.<collection_name>.find())

You can include conditions as you would working with regular mongoDB database or even use find_one() to get only one element from the database, etc.

and voila!

Question 13

import pandas as pd
from odo import odo

data = odo('mongodb://localhost/db::collection', pd.DataFrame)

Question 14

For dealing with out-of-core (not fitting into RAM) data efficiently (i.e. with parallel execution), you can try Python Blaze ecosystem: Blaze / Dask / Odo.

Blaze (and Odo) has out-of-the-box functions to deal with MongoDB.

A few useful articles to start off:

Introducing Blaze Expessions (with MongoDB query example)
ReproduceIt: Reddit word count
Difference between Dask Arrays and Blaze

And an article which shows what amazing things are possible with Blaze stack: Analyzing 1.7 Billion Reddit Comments with Blaze and Impala (essentially, querying 975 Gb of Reddit comments in seconds).

P.S. I’m not affiliated with any of these technologies.

Question 15

Another option I found very useful is:

from pandas.io.json import json_normalize

cursor = my_collection.find()
df = json_normalize(cursor)

this way you get the unfolding of nested mongodb documents for free.

Question 16

Using

pandas.DataFrame(list(...))

will consume a lot of memory if the iterator/generator result is large

better to generate small chunks and concat at the end

def iterator2dataframes(iterator, chunk_size: int):
  """Turn an iterator into multiple small pandas.DataFrame

  This is a balance between memory and efficiency
  """
  records = []
  frames = []
  for i, record in enumerate(iterator):
    records.append(record)
    if i % chunk_size == chunk_size - 1:
      frames.append(pd.DataFrame(records))
      records = []
  if records:
    frames.append(pd.DataFrame(records))
  return pd.concat(frames)

Question 17

http://docs.mongodb.org/manual/reference/mongoexport

export to csv and use read_csv or JSON and use DataFrame.from_records()

Question 18

Following this great answer by waitingkuo I would like to add the possibility of doing that using chunksize in line with .read_sql() and .read_csv(). I enlarge the answer from Deu Leung by avoiding go one by one each ‘record’ of the ‘iterator’ / ‘cursor’. I will borrow previous read_mongo function.

def read_mongo(db, 
           collection, query={}, 
           host='localhost', port=27017, 
           username=None, password=None,
           chunksize = 100, no_id=True):
""" Read from Mongo and Store into DataFrame """


# Connect to MongoDB
#db = _connect_mongo(host=host, port=port, username=username, password=password, db=db)
client = MongoClient(host=host, port=port)
# Make a query to the specific DB and Collection
db_aux = client[db]


# Some variables to create the chunks
skips_variable = range(0, db_aux[collection].find(query).count(), int(chunksize))
if len(skips_variable)<=1:
    skips_variable = [0,len(skips_variable)]

# Iteration to create the dataframe in chunks.
for i in range(1,len(skips_variable)):

    # Expand the cursor and construct the DataFrame
    #df_aux =pd.DataFrame(list(cursor_aux[skips_variable[i-1]:skips_variable[i]]))
    df_aux =pd.DataFrame(list(db_aux[collection].find(query)[skips_variable[i-1]:skips_variable[i]]))

    if no_id:
        del df_aux['_id']

    # Concatenate the chunks into a unique df
    if 'df' not in locals():
        df =  df_aux
    else:
        df = pd.concat([df, df_aux], ignore_index=True)

return df

Question 19

A similar approach like Rafael Valero, waitingkuo and Deu Leung using pagination:

def read_mongo(
       # db, 
       collection, query=None, 
       # host='localhost', port=27017, username=None, password=None,
       chunksize = 100, page_num=1, no_id=True):

    # Connect to MongoDB
    db = _connect_mongo(host=host, port=port, username=username, password=password, db=db)

    # Calculate number of documents to skip
    skips = chunksize * (page_num - 1)

    # Sorry, this is in spanish
    # https://www.toptal.com/python/c%C3%B3digo-buggy-python-los-10-errores-m%C3%A1s-comunes-que-cometen-los-desarrolladores-python/es
    if not query:
        query = {}

    # Make a query to the specific DB and Collection
    cursor = db[collection].find(query).skip(skips).limit(chunksize)

    # Expand the cursor and construct the DataFrame
    df =  pd.DataFrame(list(cursor))

    # Delete the _id
    if no_id:
        del df['_id']

    return df

Question 20

You can achieve what you want with pdmongo in three lines:

import pdmongo as pdm
import pandas as pd
df = pdm.read_mongo("MyCollection", [], "mongodb://localhost:27017/mydb")

If your data is very large, you can do an aggregate query first by filtering data you do not want, then map them to your desired columns.

Here is an example of mapping Readings.a to column a and filtering by reportCount column:

import pdmongo as pdm
import pandas as pd
df = pdm.read_mongo("MyCollection", [{'$match': {'reportCount': {'$gt': 6}}}, {'$unwind': '$Readings'}, {'$project': {'a': '$Readings.a'}}], "mongodb://localhost:27017/mydb")

read_mongo accepts the same arguments as pymongo aggregate

Question 21

With PyMongo, when I try to retrieve objects sorted by their ‘number’ and ‘date’ fields like this:

db.test.find({"number": {"$gt": 1}}).sort({"number": 1, "date": -1})

I get this error:

TypeError: if no direction is specified, key_or_list must be an instance of list

What’s wrong with my sort query?

Question 22

sort should be a list of key-direction pairs, that is

db.test.find({"number": {"$gt": 1}}).sort([("number", 1), ("date", -1)])

The reason why this has to be a list is that the ordering of the arguments matters and dicts are not ordered in Python < 3.6

Question 23

I’m trying to use the sort feature when querying my mongoDB, but it is failing. The same query works in the MongoDB console but not here. Code is as follows:

import pymongo

from  pymongo import Connection
connection = Connection()
db = connection.myDB
print db.posts.count()
for post in db.posts.find({}, {'entities.user_mentions.screen_name':1}).sort({u'entities.user_mentions.screen_name':1}):
    print post

The error I get is as follows:

Traceback (most recent call last):
  File "find_ow.py", line 7, in <module>
    for post in db.posts.find({}, {'entities.user_mentions.screen_name':1}).sort({'entities.user_mentions.screen_name':1},1):
  File "/Library/Python/2.6/site-packages/pymongo-2.0.1-py2.6-macosx-10.6-universal.egg/pymongo/cursor.py", line 430, in sort
  File "/Library/Python/2.6/site-packages/pymongo-2.0.1-py2.6-macosx-10.6-universal.egg/pymongo/helpers.py", line 67, in _index_document
TypeError: first item in each key pair must be a string

I found a link elsewhere that says I need to place a ‘u’ infront of the key if using pymongo, but that didn’t work either. Anyone else get this to work or is this a bug.

Question 24

.sort(), in pymongo, takes key and direction as parameters.

So if you want to sort by, let’s say, id then you should .sort("_id", 1)

For multiple fields:

.sort([("field1", pymongo.ASCENDING), ("field2", pymongo.DESCENDING)])

Question 25

You can try this:

db.Account.find().sort("UserName")  
db.Account.find().sort("UserName",pymongo.ASCENDING)   
db.Account.find().sort("UserName",pymongo.DESCENDING)

Question 26

This also works:

db.Account.find().sort('UserName', -1)
db.Account.find().sort('UserName', 1)

I’m using this in my code, please comment if i’m doing something wrong here, thanks.

Question 27

Why python uses list of tuples instead dict?

In python, you cannot guarantee that the dictionary will be interpreted in the order you declared.

So, in mongo shell you could do .sort({'field1':1,'field2':1}) and the interpreter would sort field1 at first level and field 2 at second level.

If this syntax was used in python, there is a chance of sorting by field2 at first level. With tuple, there is no such risk.

.sort([("field1",pymongo.ASCENDING), ("field2",pymongo.DESCENDING)])

Question 28

.sort([("field1",pymongo.ASCENDING), ("field2",pymongo.DESCENDING)])

Python uses key,direction. You can use the above way.

So in your case you can do this

for post in db.posts.find().sort('entities.user_mentions.screen_name',pymongo.ASCENDING):
        print post

Question 29

TLDR: Aggregation pipeline is faster as compared to conventional .find().sort().

Now moving to the real explanation. There are two ways to perform sorting operations in MongoDB:

Using .find() and .sort().
Or using the aggregation pipeline.

As suggested by many .find().sort() is the simplest way to perform the sorting.

.sort([("field1",pymongo.ASCENDING), ("field2",pymongo.DESCENDING)])

However, this is a slow process compared to the aggregation pipeline.

Coming to the aggregation pipeline method. The steps to implement simple aggregation pipeline intended for sorting are:

$match (optional step)
$sort

NOTE: In my experience, the aggregation pipeline works a bit faster than the .find().sort() method.

Here’s an example of the aggregation pipeline.

db.collection_name.aggregate([{
    "$match": {
        # your query - optional step
    }
},
{
    "$sort": {
        "field_1": pymongo.ASCENDING,
        "field_2": pymongo.DESCENDING,
        ....
    }
}])

Try this method yourself, compare the speed and let me know about this in the comments.

Edit: Do not forget to use allowDiskUse=True while sorting on multiple fields otherwise it will throw an error.

Question 30

Say, you want to sort by ‘created_on’ field, then you can do like this,

.sort('{}'.format('created_on'), 1 if sort_type == 'asc' else -1)

问题：JSON ValueError：期望的属性名称：第1行第2列（字符1）

回答 0

回答 1

单引号

最终逗号问题

解决方案：ast解决单引号和最终逗号问题

带有单引号的json.dumps

ast 文件资料

工具

Single quote issue

Final comma issue

Solution: ast to solve single quote and final comma issues

json.dumps with single quotes

ast documentation

Tool

回答 2

回答 3

回答 4

回答 5

问题：如何从mongodb导入数据到熊猫？

回答 0

回答 1

回答 2

回答 3

回答 4

回答 5

回答 6

回答 7

回答 8

回答 9

回答 10

回答 11

问题：在PyMongo中使用.sort

回答 0

问题：如何用pymongo排序mongodb

回答 0

回答 1

回答 2

回答 3

回答 4

回答 5

回答 6

有趣好用的Python教程

解决方案：`ast`解决单引号和最终逗号问题

`ast` 文件资料

Solution: `ast` to solve single quote and final comma issues

`ast` documentation