I am trying to save a JSON
file from a dataframe.
Sample data:
import pandas as pd
import json
df
Metric Value
0 Line1 10% off
1 Line2 15% off
2 Line3 20% off
3 Line4 25% off
4 Line5 30% off
5 revenueXaxis ['Week 1', 'Week 2', 'Week 3', 'Week 4', 'Week 5', 'Week 6', 'Week 7', 'Week 8']
6 Revenuedata1 [30, 30, 30, 30, 30, 30, 30, 30]
7 Revenuedata2 [25, 25, 25, 20, 25, 25, 25, 25]
8 Revenuedata3 [15, 15, 15, 15, 15, 15, 15, 15]
9 Revenuedata4 [15, 10, 10, 10, 10, 10, 10, 10]
10 Revenuedata5 [10, 10, 10, 10, 10, 10, 10, 10]
When I perform below zip
operation to convert it as a dictionary, the list values in Revenuedata1
to Revenuedata5
is converted into a string as below:
dict(zip(df.iloc[:,0], df.iloc[:,1]))
{'Line1': '10% off',
'Line2': '15% off',
'Line3': '20% off',
'Line4': '25% off',
'Line5': '30% off',
'revenueXaxis': "['Week 1', 'Week 2', 'Week 3', 'Week 4', 'Week 5', 'Week 6', 'Week 7', 'Week 8']",
'Revenuedata1': '[30, 30, 30, 30, 30, 30, 30, 30]',
'Revenuedata2': '[25, 25, 25, 20, 25, 25, 25, 25]',
'Revenuedata3': '[15, 15, 15, 15, 15, 15, 15, 15]',
'Revenuedata4': '[15, 10, 10, 10, 10, 10, 10, 10]',
'Revenuedata5': '[10, 10, 10, 10, 10, 10, 10, 10]'}
And when I write it to a file using json.dump
, this is the output I get:
"ExpectedRevenue": {
"Line1": "10% off",
"Line2": "15% off",
"Line3": "20% off",
"Line4": "25% off",
"Line5": "30% off",
"revenueXaxis": "['Week 1', 'Week 2', 'Week 3', 'Week 4', 'Week 5', 'Week 6', 'Week 7', 'Week 8']",
"Revenuedata1": "[50, 110, 180, 260, 350, 450, 550, 650]",
"Revenuedata2": "[20, 45, 75, 110, 150, 195, 245, 300]",
"Revenuedata3": "[5, 15, 28, 43, 60, 78, 98, 120]",
"Revenuedata4": "[4, 10, 17, 2, 35, 46, 58, 72]",
"Revenuedata5": "[3, 8, 13.5, 19.5, 26.5, 34.5, 44, 54]"
},
Could someone please let me know how to keep the integer list values as is and not a string.
Expected Output:
"ExpectedRevenue": [{
"Line1": "10% off",
"Line2": "15% off",
"Line3": "20% off",
"Line4": "25% off",
"Line5": "30% off",
"revenueXaxis": ["Week 1", "Week 2", "Week 3", "Week 4", "Week 5", "Week 6", "Week 7", "Week 8"],
"Revenuedata1": [50, 110, 180, 260, 350, 450, 550, 650],
"Revenuedata2": [20, 45, 75, 110, 150, 195, 245, 300],
"Revenuedata3": [5, 15, 28, 43, 60, 78, 98, 120],
"Revenuedata4": [4, 10, 17, 2, 35, 46, 58, 72],
"Revenuedata5": [3, 8, 13.5, 19.5, 26.5, 34.5, 44, 54]
}]
CodePudding user response:
If string starting by [
convert values to lists by ast.literal_eval
only for filtered rows:
import ast
m = df['Value'].str.startswith('[')
df.loc[m, 'Value'] = df.loc[m, 'Value'].apply(ast.literal_eval)
Last create dictionary:
print (df.set_index('Metric')['Value'].to_dict())
print (dict(zip(df.iloc[:,0], df.iloc[:,1])))