Using column values as key in pandas json-CodePudding

I have a pandas dataframe as below. I'm just wondering if there's any way to have my column values as my key to the json.

df: |symbol | price| |:------|------| |a. |120| |b. |100| |c |200|

I expect the json to look like {'a': 120, 'b': 100, 'c': 200}

I've tried the below and got the result as {symbol: 'a', price: 120}{symbol: 'b', price: 100}{symbol: 'c', price: 200}

df.to_json('price.json', orient='records', lines=True)

CodePudding user response：

Let's start by creating the dataframe that OP mentions

import pandas as pd

df = pd.DataFrame({'symbol': ['a', 'b', 'c'], 'price': [120, 100, 200]})

Considering that OP doesn't want the JSON values as a list (as OP commented here), the following will do the work

df.groupby('symbol').price.apply(lambda x: x.iloc[0]).to_dict()

[Out]: {'a': 120, 'b': 100, 'c': 200}

If one wants the JSON values as a list, the following will do the work

df.groupby('symbol').price.apply(list).to_json()

[Out]: {"a":[120],"b":[100],"c":[200]}

CodePudding user response：

Try like this :

import pandas as pd

d = {'symbol': ['a', 'b', 'c'], 'price': [120, 100, 200]}
df = pd.DataFrame(data=d)

print(df)

print (df.set_index('symbol').rename(columns={'price':'json_data'}).to_json())

# EXPORT TO FILE
df.set_index('symbol').rename(columns={'price':'json_data'}).to_json('price.json')

Output :

  symbol  price
0      a    120
1      b    100
2      c    200
{"json_data":{"a":120,"b":100,"c":200}}