Python Forum
Replace null values in Json file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Replace null values in Json file
#1
Hi all!!
i am new in forum so i have very much to read!

I have one problem with some code in python. I am trying to replace None values in Json file to "vacio" values.

My example of read file is:

data = []
for reng in open("con_error.json", "r"):
    data.append(json.loads(reng))
and the result is:

[{u'contributors': None,
u'coordinates': None,
u'created_at': u'Sun Jun 10 14:21:53 +0000 2018',
u'entities': {u'hashtags': [],
u'symbols': [],
u'urls': [],
u'user_mentions': [{u'id': 254599256,
u'id_str': u'254599256',
u'indices': [3, 11],
u'name': u'Alejandro Fargosi',
u'screen_name': u'fargosi'}]},
u'favorite_count': 0,
u'favorited': False,
u'filter_level': u'low',
u'geo': None,
u'id': 1005817329459097600L,
u'id_str': u'1005817329459097600',
u'in_reply_to_screen_name': None,
u'in_reply_to_status_id': None}]

Now, i use the next code for replace None for "vacio":

json_string=json.dumps(data).replace("null", "vacio")
and the result is:

'[{"quote_count": 0, "contributors": vacio, "truncated": false, "text": "RT @fargosi: Del 20-4 al 10-6-18, nuestros defensores:\\nMauro Maldonado muerto\\nAbigail Alvarez muerta\\nJorge Cusi muerto\\nDaniel Rios, muerto\\u2026", "is_quote_status": false, "in_reply_to_status_id": vacio, "reply_count": 0, "id": 1005817329459097600, "favorite_count": 0, "entities": {"user_mentions": [{"id": 254599256, "indices": [3, 11], "id_str": "254599256", "screen_name": "fargosi", "name": "Alejandro Fargosi"}], "symbols": [], "hashtags": [], "urls": []}, "retweeted": false, "coordinates": vacio, "timestamp_ms": "1528640513182", "source": "<a href=\\"http://twitter.com/download/iphone\\" rel=\\"nofollow\\">Twitter for iPhone</a>", "in_reply_to_screen_name": vacio, "id_str": "1005817329459097600", "retweet_count": 0, "in_reply_to_user_id": vacio, "favorited": false, "retweeted_status":... }]

Now, i want to transform this result to a new object in format of dict for extract data more easy, such as first result. I tried
json.loads(json_string)
but i have error : No JSON object could be decoded

I next would execute next code, for this reason i need access by dict format:
usuarios = []
texto = []
fechas = []
localizaciones = []

for line in open('con_error.json', 'r'):  
    usuarios.append(json.loads(line)['user']['name']) 
    texto.append(json.loads(line)['text']) 
    fechas.append(json.loads(line)['created_at']) 
    
    localizaciones.append(json.loads(line)['place']['name'])
Thank you very much and sorry if there is some mystake in my post. It is my first post!
Reply
#2
retracted
Reply
#3
1. the way you read the json is not right
with open("con_error.json", "r") as f:
    data = json.load(f)
now you have your json as data dict

2. if use str.replace to replace "null"(?!). what you do is to read json, then immediately dump it back as string. So then why read it as json at all? just read the file as text and replace "null" if you are going to do this

3. once you read the json properly as a dict you can loop over it and replace None. However it looks like complex data structure, so I cannot be more specific
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#4
null value is not enclosed in double quotes - but string values are. You have essentially corrupted your JSON file by inserting unquoted string.

Your replace line should have been
json_string=json.dumps(data).replace("null", '"vacio"')
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply
#5
(Jun-10-2018, 06:50 PM)buran Wrote: 1. the way you read the json is not right
with open("con_error.json", "r") as f:
    data = json.load(f)
now you have your json as data dict

2. if use str.replace to replace "null"(?!). what you do is to read json, then immediately dump it back as string. So then why read it as json at all? just read the file as text and replace "null" if you are going to do this

3. once you read the json properly as a dict you can loop over it and replace None. However it looks like complex data structure, so I cannot be more specific

Thank you very much to all for answer my question. I am trying to realize for loop in a dict object , but some values are replace and other no. The code is:

for i, j in data.iteritems():
    if j is None:
        data[i] = 'vacio'
i dont understand why no replace all values

Thank you very much!
Reply
#6
As I said - you have complex structure, so you need to check if the value is (for example) list/dict, then you need to check values within this data structure too.
In your example you have
Output:
u'user_mentions': [{u'id': 254599256, u'id_str': u'254599256', u'indices': [3, 11], u'name': u'Alejandro Fargosi', u'screen_name': u'fargosi'}]}, u'favorite_count': 0, u'favorited': False, u'filter_level': u'low', u'geo': None, u'id': 1005817329459097600L, u'id_str': u'1005817329459097600', u'in_reply_to_screen_name': None, u'in_reply_to_status_id': None}]
the value is list of dicts, and there are elements in the dict that has value None.
so it is at least two levels deep that you have to check

what is the best way to do it depends on your data and you know your data best
can you post example of your json file so that we can get better understanding of the data structure that you work with?
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#7
My advice - above- with string replacement would have worked too, but this is a proper way.

You cannot change nested structures without diving in with recursion

def replace_nulls(json_elem):
    if isinstance(json_elem, list):
        return [replace_nulls(elem) for elem in json_elem]
    elif isinstance(json_elem, dict):
        return {key: replace_nulls(value) for key, value in json_elem.items()}
    else:
        return 'vacio' if json_elem is None else json_elem
Output:
[{'contributors': 'vacio', 'coordinates': 'vacio', 'created_at': 'Sun Jun 10 14:21:53 +0000 2018', 'entities': {'hashtags': [], 'symbols': [], 'urls': [], 'user_mentions': [{'id': 254599256, 'id_str': '254599256', 'indices': [3, 11], 'name': 'Alejandro Fargosi', 'screen_name': 'fargosi'}]}, 'favorite_count': 0, 'favorited': False, 'filter_level': 'low', 'geo': 'vacio', 'id': 1005817329459097600, 'id_str': '1005817329459097600', 'in_reply_to_screen_name': 'vacio', 'in_reply_to_status_id': 'vacio'}]

This simple code produces the same result (but nobody listens Doh )
json.loads(json.dumps(data).replace('null', '"vacio"'))
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply
#8
(Jun-11-2018, 08:18 AM)volcano63 Wrote: This simple code produces the same result (but nobody listens Doh )
Python Code: (Double-click to select all)
1

json.loads(json.dumps(data).replace('null', '"vacio"'))

(Jun-10-2018, 06:50 PM)buran Wrote: what you do is to read json, then immediately dump it back as string. So then why read it as json at all? just read the file as text and replace "null" if you are going to do this
the simplest code wouldn't need to import/use json at all
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#9
(Jun-11-2018, 08:18 AM)volcano63 Wrote: My advice - above- with string replacement would have worked too, but this is a proper way. You cannot change nested structures without diving in with recursion
def replace_nulls(json_elem): if isinstance(json_elem, list): return [replace_nulls(elem) for elem in json_elem] elif isinstance(json_elem, dict): return {key: replace_nulls(value) for key, value in json_elem.items()} else: return 'vacio' if json_elem is None else json_elem
Output:
[{'contributors': 'vacio', 'coordinates': 'vacio', 'created_at': 'Sun Jun 10 14:21:53 +0000 2018', 'entities': {'hashtags': [], 'symbols': [], 'urls': [], 'user_mentions': [{'id': 254599256, 'id_str': '254599256', 'indices': [3, 11], 'name': 'Alejandro Fargosi', 'screen_name': 'fargosi'}]}, 'favorite_count': 0, 'favorited': False, 'filter_level': 'low', 'geo': 'vacio', 'id': 1005817329459097600, 'id_str': '1005817329459097600', 'in_reply_to_screen_name': 'vacio', 'in_reply_to_status_id': 'vacio'}]
Thank you very much!! It works very well. It is more fast and better that transform to string. Thank you very much. I learned more python with all of yours! Sorry for my english
Reply
#10
(Jun-11-2018, 08:44 AM)buran Wrote:
(Jun-11-2018, 08:18 AM)volcano63 Wrote: This simple code produces the same result (but nobody listens Doh )
Python Code: (Double-click to select all)
1

json.loads(json.dumps(data).replace('null', '"vacio"'))

(Jun-10-2018, 06:50 PM)buran Wrote: what you do is to read json, then immediately dump it back as string. So then why read it as json at all? just read the file as text and replace "null" if you are going to do this
the simplest code wouldn't need to import/use json at all

My point was that the approach initially taken by OP would have worked with a slight adjustment. Recursion is the proper way - but solution-like it is simpler with json(not everyone is proficient with recursions - it took me a Scala course to grok it better).
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  parse json field from csv file lebossejames 4 668 Nov-14-2023, 11:34 PM
Last Post: snippsat
  Replace a text/word in docx file using Python Devan 4 2,847 Oct-17-2023, 06:03 PM
Last Post: Devan
  Need to replace a string with a file (HTML file) tester_V 1 699 Aug-30-2023, 03:42 AM
Last Post: Larz60+
  Python Script to convert Json to CSV file chvsnarayana 8 2,343 Apr-26-2023, 10:31 PM
Last Post: DeaD_EyE
  Loop through json file and reset values [SOLVED] AlphaInc 2 1,960 Apr-06-2023, 11:15 AM
Last Post: AlphaInc
  How to express null value klatlap 3 815 Mar-25-2023, 10:40 AM
Last Post: klatlap
  Converting a json file to a dataframe with rows and columns eyavuz21 13 4,164 Jan-29-2023, 03:59 PM
Last Post: eyavuz21
Photo How to select NULL and blank values from MySQL table into csv python300 9 2,328 Dec-27-2022, 09:43 PM
Last Post: deanhystad
  validate large json file with millions of records in batches herobpv 3 1,221 Dec-10-2022, 10:36 PM
Last Post: bowlofred
  Write Null values as 0.0 (float) type in csv mg24 3 1,305 Dec-07-2022, 09:04 PM
Last Post: deanhystad

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020