Pandas merge two columns into Json

Question

I have a pandas dataframe like below

    Col1    Col2
0     a     apple
1     a     anar
2     b     ball
3     b     banana

I am looking to output json which outputs like

{ 'a' : ['apple', 'anar'], 'b' : ['ball', 'banana'] }

Answer 1

Use groupby with apply and last convert Series to json by Series.to_json:

j = df.groupby('Col1')['Col2'].apply(list).to_json()
print (j)
{"a":["apple","anar"],"b":["ball","banana"]}

If want write json to file:

s = df.groupby('Col1')['Col2'].apply(list)
s.to_json('file.json')

Check difference:

j = df.groupby('Col1')['Col2'].apply(list).to_json()
d = df.groupby('Col1')['Col2'].apply(list).to_dict()

print (j)
{"a":["apple","anar"],"b":["ball","banana"]}
print (d)
{'a': ['apple', 'anar'], 'b': ['ball', 'banana']}

print (type(j))
<class 'str'>

print (type(d))
<class 'dict'>

Answer 2

You can groupby() 'Col1' and apply() list to 'Col2' and convert to_dict(), Use:

df.groupby('Col1')['Col2'].apply(list).to_dict()

Output:

{'a': ['apple', 'anar'], 'b': ['ball', 'banana']}