How to set column headers while converting dict to pandas DataFrame (where column names do not match dict keys)?

user_14744714 0
Viewed 275

Using mongodb, I am outputting the unique field values and their number. Now I need to put this list in the ramp for further convenient work. But I don't quite understand how to do it because of the structure of the list.

unique_data = db.small_311.aggregate([ 
{'$group' : {'_id' : "$Complaint Type", 'uniqueCount' : {'$sum': 1}}}])

Part of the list

print(list(unique_data))
[{'_id': 'Posting Advertisement', 'uniqueCount': 5}, {'_id': 'APPLIANCE', 'uniqueCount': 445}, {'_id': 'Complaint', 'uniqueCount': 9},...]

I am trying to add to the dataframe

data_result = list(unique_data)
df = pd.DataFrame(data_result, columns =['Complaint Type', 'Value'])
df[:5]

But the result is an empty dataframe (it contains only the name of the fields, no data) Result dataframe

pandas mongodb dataframe python
1 Answers

If you're just trying to rename your columns, you can use set_axis:

pd.DataFrame(d).set_axis(['A', 'B'], axis=1)

                       A    B
0  Posting Advertisement    5
1              APPLIANCE  445
2              Complaint    9

or just assign df.columns to a list directly.

To explain the output you're seeing, notice the columns argument does not match the keys in the records being passed to pd.DataFrame, so pandas doesn't select any columns. It doesn't understand you're trying to rename the columns.

Just passing your data as-is without any keyword arguments works:

pd.DataFrame(d)

                     _id  uniqueCount
0  Posting Advertisement            5
1              APPLIANCE          445
2              Complaint            9

If you want just the _id column, that's when you would use columns param.

pd.DataFrame(d, columns=['_id'])

                     _id
0  Posting Advertisement
1              APPLIANCE
2              Complaint

Passing a non-existent column will make it silently spit out nothing:

pd.DataFrame(d, columns=['blah'])

   blah
0   NaN
1   NaN
2   NaN
user_49090870
Related