In this recipe, you'll learn how to create an Excel report directly from a Pandas DataFrame using the to_excel()
function. We will be writing all the code in IPython Notebook.
First, import the Python libraries that you need:
from pymongo import MongoClient import pandas as pd from time import strftime
Next, create a connection to MongoDB and specify the
accidents
collection:client = MongoClient('localhost', 27017) db = client.pythonbicookbook collection = db.accidents
Once you've created the connection, run a query to retrieve the first
1000
records where an accident happened on a Friday:data = collection.find({"Day_of_Week": 6}).limit(1000)
Next, create a new DataFrame from the MongoDB query:
df = pd.DataFrame(list(data))
Show the first five rows to ensure that you have the right data:
df.head()
Now delete the
_id
column as we don't need it, and we can't write to the Excel file with it. The reason we cannot write it is because...