pythonpandasdataframepymongo
pymongo cursor with nested values into dataframe
I have the following pymongo cursor :
{'_id': ObjectId('61d7088333a0edbcbdc7332f'), 'TICKET_ID': {'0': '35592159', '1': '35592159', '2': '35592159', '3': '35592159', '4': '35592159'}, 'MOIS_VENTE': {'0': '10', '1': '10', '2': '10', '3': '10', '4': '10'}, 'PRIX_NET': {'0': '1.67', '1': '1.66', '2': '7.45', '3': '5.95', '4': '1.67'}, 'FAMILLE': {'0': 'HYGIENE', '1': 'HYGIENE', '2': 'SOINS DU VISAGE', '3': 'SOINS DU VISAGE', '4': 'HYGIENE'}, 'UNIVERS': {'0': 'HYG_DOUCHE JARDINMONDE', '1': 'HYG_DOUCHE JARDINMONDE', '2': 'VIS_CJOUR Jeunes Specifique', '3': 'VIS_DEMAQ AAAR', '4': 'HYG_DOUCHE JARDINMONDE'}, 'MAILLE': {'0': 'HYG_JDM', '1': 'HYG_JDM', '2': 'VIS_JEUNE_ET_LEVRE', '3': 'VIS_AAAR_DEMAQLOTION', '4': 'HYG_JDM'}, 'LIBELLE': {'0': 'GD JDM4 PAMPLEMOUSSE FL 200ML', '1': 'GD JDM4 PAMPLEMOUSSE FL 200ML', '2': 'CR JR PARF BIO.SPE AC.SENT.50ML', '3': 'EAU MICELLAIRE 3 THES FL200ML', '4': 'GD JDM4 TIARE FL 200ML'}, 'CLI_ID': {'0': '1490281', '1': '1490281', '2': '1490281', '3': '1490281', '4': '1490281'}}
And i would like to make the following dataframe from the cursor above :
TICKET_ID MOIS_VENTE PRIX_NET FAMILLE UNIVERS MAILLE LIBELLE CLI_ID
0 35592159 10 1.67 HYGIENE HYG_DOUCHE JARDINMONDE HYG_JDM GD JDM4 PAMPLEMOUSSE FL 200ML 1490281
1 35592159 10 1.66 HYGIENE HYG_DOUCHE JARDINMONDE HYG_JDM GD JDM4 PAMPLEMOUSSE FL 200ML 1490281
2 35592159 10 7.45 SOINS DU VISAGE VIS_CJOUR Jeunes Specifique VIS_JEUNE_ET_LEVRE CR JR PARF BIO.SPE AC.SENT.50ML 1490281
3 35592159 10 5.95 SOINS DU VISAGE VIS_DEMAQ AAAR VIS_AAAR_DEMAQLOTION EAU MICELLAIRE 3 THES FL200ML 1490281
4 35592159 10 1.67 HYGIENE HYG_DOUCHE JARDINMONDE HYG_JDM GD JDM4 TIARE FL 200ML 1490281
I have tried the following and the result was not what I expected...
fields = ['TICKET_ID', 'MOIS_VENTE', 'PRIX_NET', 'FAMILLE', 'UNIVERS', 'MAILLE', 'LIBELLE','CLI_ID']
r = pd.DataFrame(list(cursor), columns = fields)
r
Thanks in advance for your advice!
Solution
As all your data is in one record, use find_one() as it returns a dict by default. Then you can just pass the dict to pandas without the need for further options and your dataframe will be constructed just as you want it.
import pandas as pd
from pymongo import MongoClient
# Load some test data
db = MongoClient()['mydatabase']['mycollection']
db.insert_one({'TICKET_ID': {'0': '35592159', '1': '35592159', '2': '35592159', '3': '35592159', '4': '35592159'}, 'MOIS_VENTE': {'0': '10', '1': '10', '2': '10', '3': '10', '4': '10'}, 'PRIX_NET': {'0': '1.67', '1': '1.66', '2': '7.45', '3': '5.95', '4': '1.67'}, 'FAMILLE': {'0': 'HYGIENE', '1': 'HYGIENE', '2': 'SOINS DU VISAGE', '3': 'SOINS DU VISAGE', '4': 'HYGIENE'}, 'UNIVERS': {'0': 'HYG_DOUCHE JARDINMONDE', '1': 'HYG_DOUCHE JARDINMONDE', '2': 'VIS_CJOUR Jeunes Specifique', '3': 'VIS_DEMAQ AAAR', '4': 'HYG_DOUCHE JARDINMONDE'}, 'MAILLE': {'0': 'HYG_JDM', '1': 'HYG_JDM', '2': 'VIS_JEUNE_ET_LEVRE', '3': 'VIS_AAAR_DEMAQLOTION', '4': 'HYG_JDM'}, 'LIBELLE': {'0': 'GD JDM4 PAMPLEMOUSSE FL 200ML', '1': 'GD JDM4 PAMPLEMOUSSE FL 200ML', '2': 'CR JR PARF BIO.SPE AC.SENT.50ML', '3': 'EAU MICELLAIRE 3 THES FL200ML', '4': 'GD JDM4 TIARE FL 200ML'}, 'CLI_ID': {'0': '1490281', '1': '1490281', '2': '1490281', '3': '1490281', '4': '1490281'}})
# Fetch the data from MongoDB
data = db.find_one({}, {'_id': 0})
df = pd.DataFrame(data)
# Pretty up the output
pd.options.display.max_columns = None
pd.options.display.width = None
print(df)
prints:
TICKET_ID MOIS_VENTE PRIX_NET FAMILLE UNIVERS MAILLE LIBELLE CLI_ID
0 35592159 10 1.67 HYGIENE HYG_DOUCHE JARDINMONDE HYG_JDM GD JDM4 PAMPLEMOUSSE FL 200ML 1490281
1 35592159 10 1.66 HYGIENE HYG_DOUCHE JARDINMONDE HYG_JDM GD JDM4 PAMPLEMOUSSE FL 200ML 1490281
2 35592159 10 7.45 SOINS DU VISAGE VIS_CJOUR Jeunes Specifique VIS_JEUNE_ET_LEVRE CR JR PARF BIO.SPE AC.SENT.50ML 1490281
3 35592159 10 5.95 SOINS DU VISAGE VIS_DEMAQ AAAR VIS_AAAR_DEMAQLOTION EAU MICELLAIRE 3 THES FL200ML 1490281
4 35592159 10 1.67 HYGIENE HYG_DOUCHE JARDINMONDE HYG_JDM GD JDM4 TIARE FL 200ML 1490281