Split multiple values in a row into multiple rows using pandas

Question

My data is as follows:

d = [{'id':1, 'a':10, 'b':10}, {"id":2,"a":20, "b":20}]

The result I want is as follows:

res = [{'id': 1, 'prop': 'a', 'value': 10},
 {'id': 1, 'prop': 'b', 'value': 10},
 {'id': 2, 'prop': 'a', 'value': 20},
 {'id': 2, 'prop': 'b', 'value': 20}]

Since each row has two properties I want to split each row having the same id but different property and the corresponding value to that property

I am using iterrows as

import pandas as pd
df = pd.DataFrame(d)
l = []
for _, r in df.iterrows(): 
    for p in ['a','b']: 
        l.append({'id':r.id, 'prop':p, 'value':r[p]})

I get what I want but what I wish to know is whether there is any way to use purely pandas command to achieve my goal

tawab_shakeel · Accepted Answer · 2019-07-29 05:32:21Z

2

use pd.melt then use to_json with orient=records

convert string json to proper list format using json.loads

import json
d = [{'id':1, 'a':10, 'b':10}, {"id":2,"a":20, "b":20}]

df = pd.DataFrame(d)

res = pd.melt(df,id_vars=['id'],value_vars=['a','b'],var_name='prop')

json_res= json.loads(res.to_json(orient='records'))

[{"id":1,"prop":"a","value":10},{"id":2,"prop":"a","value":20},{"id":1,"prop":"b","value":10},{"id":2,"prop":"b","value":20}]

edited Jul 29, 2019 at 5:32

answered Jul 29, 2019 at 5:03

tawab_shakeel

3,75912 silver badges26 bronze badges

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

Comments

jezrael · Accepted Answer · 2019-07-29 05:25:55Z

2

Create DataFrame by constructor, then use DataFrame.melt and last DataFrame.to_dict with parameter r for records:

df = pd.DataFrame(d)

#melt all columns without id
df1 = df.melt('id', var_name='prop')
#if necessary specify columns
#df1 = df.melt('id', var_name='prop', value_vars=['a','b'])

res = df1.to_dict('r')
print (res)
[{'id': 1, 'prop': 'a', 'value': 10}, {'id': 2, 'prop': 'a', 'value': 20}, 
 {'id': 1, 'prop': 'b', 'value': 10}, {'id': 2, 'prop': 'b', 'value': 20}]

print (type(res))
<class 'list'>

edited Jul 29, 2019 at 5:25

answered Jul 29, 2019 at 5:01

jezrael

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

Collectives™ on Stack Overflow

Split multiple values in a row into multiple rows using pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related