pandas dataframe convert values in array of objects

Question

I want to convert the below pandas data frame

data = pd.DataFrame([[1,2], [5,6]], columns=['10+', '20+'], index=['A', 'B'])
data.index.name = 'City'
data.columns.name= 'Age Group'
print data

Age Group  10+  20+
City               
A            1    2
B            5    6

in to an array of dictionaries, like

[
 {'Age Group': '10+', 'City': 'A', 'count': 1},
 {'Age Group': '20+', 'City': 'A', 'count': 2},
 {'Age Group': '10+', 'City': 'B', 'count': 5},
 {'Age Group': '20+', 'City': 'B', 'count': 6}
]

I am able to get the above expected result using the following loops

result = []
cols_name = data.columns.name
index_names = data.index.name
for index in data.index:
    for col in data.columns:
        result.append({cols_name: col, index_names: index, 'count': data.loc[index, col]})

Is there any better ways of doing this? Since my original data will be having large number of records, using for loops will take more time.

jezrael · Accepted Answer · 2016-12-02 07:23:56Z

1

I think you can use stack with reset_index for reshape and last to_dict:

print (data.stack().reset_index(name='count'))
  City Age Group  count
0    A       10+      1
1    A       20+      2
2    B       10+      5
3    B       20+      6

print (data.stack().reset_index(name='count').to_dict(orient='records'))
[
  {'Age Group': '10+', 'City': 'A', 'count': 1}, 
  {'Age Group': '20+', 'City': 'A', 'count': 2}, 
  {'Age Group': '10+', 'City': 'B', 'count': 5}, 
  {'Age Group': '20+', 'City': 'B', 'count': 6}
]

edited Dec 2, 2016 at 7:23

answered Dec 2, 2016 at 7:17

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jezrael Over a year ago

Sure, no problem.

Collectives™ on Stack Overflow

pandas dataframe convert values in array of objects

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related