How to save data from dataframe to json file?-CodePudding

How can I convert to Json file form Dataframe in python using pandas. I don't know how to get name and carmodel column, I was only get the price from dataframe

I have an Dataframe like:

name   carmodel 
ACURA  CL           6.806155e 08
       OTHER        2.280000e 08
       EL           1.300000e 08
       MDX          7.828750e 08
       RDX          3.850000e 08
                        ...
VOLVO  XC90         3.748778e 09
ZOTYE  OTHER        1.887500e 08
       HUNTER       1.390000e 08
       T600         4.200000e 08
       Z8           4.754000e 08

so I want to convert it like:

{
    'ACURA': 
        {
            'CL':6.806155e 08,
            'OTHER':2.280000e 08,
            'MDX':7.828750e 08,
        },
        
    'VOLVO':
        {
            'XC90':3.748778e 09,
        },
    'ZOTYE':
        {
            'OTHER':1.887500e 08,
            'HUNTER':1.390000e 08,
            'T600':4.200000ee 08,
        }
}

Here is my code:

df = pd.read_csv(r'D:\vucar\scraper\result.csv',dtype='unicode')
df['price'] = pd.to_numeric(df['price'],downcast='float')
cars = df[['name','carmodel','price']].sort_values('name').groupby(['name','carmodel']).mean()['price']
print(cars)

CodePudding user response：

You could reset the index on cars, then groupby name again, while merging carmodel and name to a dictionary, then save as json:

cars = df[['name','carmodel','price']].sort_values('name').groupby(['name','carmodel']).mean().reset_index(level='carmodel')
cars.groupby('name').apply(lambda x: x.set_index('carmodel')['price'].to_dict()).to_json('filename.json', orient='index')

output:

{"ACURA":{"CL":680615500.0,"EL":130000000.0,"MDX":782875000.0,"OTHER":228000000.0,"RDX":385000000.0},"VOLVO":{"XC90":3748778000.0},"ZOTYE":{"HUNTER":139000000.0,"OTHER":188750000.0,"T600":420000000.0,"Z8":475400000.0}}