Pandas - insert a dataframe to MongoDB

Question

I am trying to insert a dataframe into MongoDB. Each row should be one document.

from pymongo import MongoClient
import pandas as pd

client = MongoClient()
col = client['test']['test']

d = {'name': ['Braund', 'Cummings', 'Heikkinen', 'Allen'],
     'age': [22,38,26,35],
     'fare': [7.25, 71.83, 0 , 8.05],
     'survived?': [False, True, True, False]}

df = pd.DataFrame(d)

col.insert_many(df)

However, the above code returns an error: ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Changing col.insert_many(df) to col.insert_many(df.to_dict()) or col.insert_many(df.to_json()) causes TypeError: document must be an instance of dict, bson.son.SON, bson.raw_bson.RawBSONDocument, or a type that inherits from collections.MutableMapping

Answer 1

You were very close, we can re-use your code.

Note, we still use pymongo.MongoClient and pandas.DataFrame.to_dict, with one param added in the latter. to_dict(orient='records')
we would then have

from pymongo import MongoClient
import pandas as pd

client = MongoClient()  # Remember your uri string
col = client['test']['test']

df = pd.DataFrame({'name': ['Braund','Cummings','Heikkinen','Allen'],
                   'age': [22,38,26,35],
                   'fare': [7.25, 71.83, 0 , 8.05],
                   'survived?': [False, True, True, False]})

data = df.to_dict(orient='records')  # Here's our added param..

col.insert_many(data)

In short, by specifying orient='records' the output is a list of dicts, the same format accepted by insert_many. Also, as a bonus - to_dict does better with datetime-type columns as compared to to_json!