remove quotes from a json file using python

Question

the dataframe 'dataset' is automatically generated by PowerBI here is the result of my dataset.head(10).to_clipboard(sep=',', index=False)

coordinates,status
"[143.4865219,-34.7560602]",not started
"[143.4865241,-34.7561332]",not started
"[143.4865264,-34.7562088]",not started
"[143.4865286,-34.7562818]",not started
"[143.4865305,-34.7563453]",not started
"[143.4865327,-34.7564183]",not started
"[143.486535,-34.756494]",not started
"[143.4865371,-34.756567]",not started
"[143.486539,-34.7566304]",not started
"[143.4865412,-34.7567034]",not started

then to get the json

i do this data=dataset.to_json(orient='records')

which give me this results

[{"coordinates":"[143.4865219,-34.7560602]","status":"not started"},{"coordinates":"[143.4865241,-34.7561332]","status":"not started"},

how i get this instead , no quotes on the coordinates values

[{"coordinates":[143.4865219,-34.7560602],"status":"not started"},{"coordinates":[143.4865241,-34.7561332],"status":"not started"},

edit

 print(type(data))

<class 'str'>

See https://stackoverflow.com/questions/12309269/how-do-i-write-json-data-to-a-file — Thierry Lathuille, Sep 24 '19 at 13:10

Thierry Lathuille · Accepted Answer · 2019-09-24T12:58:27.507

You could use ast.literal_eval:

Safely evaluate an expression node or a string containing a Python literal or container display. The string or node provided may only consist of the following Python literal structures: strings, bytes, numbers, tuples, lists, dicts, sets, booleans, and None.

This can be used for safely evaluating strings containing Python values from untrusted sources without the need to parse the values oneself.[...]

Your data seems to be a string, and not a list as Python would print it (it uses single quotes by default, the double quotes in your data seem to indicate that it is a string, ready to be saved in a json file for example). So, you have to convert it first to a Python object with json.loads:

from ast import literal_eval
import json

data = """[{"coordinates":"[143.4865219,-34.7560602]","status":"not started"},{"coordinates":"[143.4865241,-34.7561332]","status":"not started"}]"""
data = json.loads(data)

for d in data:
    d['coordinates'] = literal_eval(d['coordinates'])

print(data)
# [{'coordinates': [143.4865219, -34.7560602], 'status': 'not started'}, {'coordinates': [143.4865241, -34.7561332], 'status': 'not started'}]

It's probably because your data is a string, and not a list as presented in your question. How are you reading your data? — Thierry Lathuille, Sep 24 '19 at 12:29

kuco 23 · Answer 2 · 2019-09-24T12:58:53.423

4

import json

s = '[{"coordinates":"[143.4865219,-34.7560602]","status":"not started"},{"coordinates":"[143.4865241,-34.7561332]","status":"not started"}]'
d = json.loads(s)
d[0]['coordinates'] = json.loads(d[0]['coordinates'])

Applying this concept to every value can be done as in

for dic in d:
    for key, value in dic.items():
         try:
              temp = json.loads(value)
              if isinstance(temp, list):
                  dic[key] = temp
         except Exception:
              pass

or if you are sure there will be a coordinates key in ever dictionary and that key having a "list" value

for dic in d: dic['coordinates'] = json.loads(dic['coordinates'])

edited Sep 24 '19 at 12:58

answered Sep 24 '19 at 12:22

kuco 23

786
5
18

maybe show how to do this for all records? this only currently does it for the first record in the blob – gold_cy Sep 24 '19 at 12:23
TypeError: string indices must be integers – Mim Sep 24 '19 at 12:51
I'm sorry, I edited the third code, so you can try it again. – kuco 23 Sep 24 '19 at 12:59

cerofrais · Answer 3 · 2019-09-24T12:39:31.480

-1

simply u can use eval function.

new =[]


l =  '[{"coordinates":"[143.4865219,-34.7560602]","status":"not started"},{"coordinates":"[143.4865241,-34.7561332]","status":"not started"}]'
l=eval(l)
for each_element in l:
    temp={}
    for k,v in each_element.items():
          if k =='coordinates' :
              temp[k]=eval(v)
          else:
               temp[k]=v
     new.append(temp)
print(temp)

edited Sep 24 '19 at 12:39

answered Sep 24 '19 at 12:29

cerofrais

1,117
1
12
32

Using `eval` is generally discouraged, see for example https://stackoverflow.com/questions/1832940/why-is-using-eval-a-bad-practice . Iterating on all keys and items of the dict in order to find one key is extremely inefficient. Dicts exist to provide direct access to data without having to iterate, even under the hood, so just use `temp['coordinates'] = ...`. Also, if some part of the data lacks coordinates, your code would silently ignore it. It would be better for it to fail with an exception, or to include some special treatment in a `try ... except` block. – Thierry Lathuille Sep 24 '19 at 12:46

remove quotes from a json file using python

3 Answers3