enter image description here

I want to convert a json file into a dataframe in pandas (Python). I tried with read_json() but got the error:

UnicodeDecodeError:'charmap' codec can't decode byte 0x81 in position 21596351:character maps to <undefined> 

I think I have some unwanted data in the json file like noise. The data is server generated.

This is a collection from the json file:

{"_id":{"$oid":"57a30ce368fd0809ec4d1b41"},"session":{"start_timestamp":{"$numberLong":"1470151881189"},"session_id":"8356bd90-20160802-153121189"},"metrics":{},"arrival_timestamp":{"$numberLong":"1470152028294"},"event_type":"OfferViewed","event_timestamp":{"$numberLong":"1470151943271"},"event_version":"3.0","application":{"package_name":"com.think.vito","title":"Vito","version_code":"5","app_id":"7ffa58dab3c646cea642e961ff8a8070","cognito_identity_pool_id":"us-east-1:4d9cf803-0487-44ec-be27-1e160d15df74","version_name":"","sdk":{"version":"2.2.2","name":"aws-sdk-android"}},"client":{"cognito_id":"us-east-1:1d507b8f-857c-42a4-a705-8db07d46bc8f","client_id":"aa092911-b9a7-498a-82da-76318356bd90"},"device":{"locale":{"country":"US","code":"en_US","language":"en"},"platform":{"version":"5.1.1","name":"ANDROID"},"make":"Xiaomi","model":"Redmi Note 3"},"attributes":{"Category":"90000","CustomerID":"4077","OfferID":"11846"}}
  • $\begingroup$ Have you tried to change the encoding option of read_json()? Default is utf-8 and the character 0x81 (which is an A with an accent) cannot be read with this encoding option. I would give a try to some other options such as encoding='iso-8859-1' to see if it changes the error you get. $\endgroup$ – Eskapp Jan 31 '17 at 20:52
  • $\begingroup$ can you provide a valid JSON file? $\endgroup$ – MaxU Jan 31 '17 at 22:54
  • $\begingroup$ their is some problem in my json file i just use a tool google open refine and change that file to csv and than load it in pandas using read_csv and it work $\endgroup$ – Abhishek Pathak Feb 1 '17 at 16:45

You have to read the file line by line, you can find a detailed answer in this question of stackoverflow

  • $\begingroup$ No, one doesn't have to (also, lines is deprecated in newer versions). It is sufficient to data = pd.read_json(codecs.open(file_name, 'r', 'utf-8')). $\endgroup$ – gented Nov 14 '17 at 14:30
import codecs
import pandas as pd

This should work.

  • $\begingroup$ Comments are not for extended discussion; this conversation has been moved to chat. $\endgroup$ – Sean Owen Feb 1 '17 at 16:22
  • $\begingroup$ This is the correct answer actually. $\endgroup$ – gented Nov 14 '17 at 14:29

You can try this.

import pandas as pd
#read line by line
with open('json_file','rb') as f:
Cleaned=[str(line).rstrip() for line in lines]
#Removes \n
Json="[" + ','.join(str(cl) for cl in Cleaned) + "]"

  • $\begingroup$ Comments are not for extended discussion; this conversation has been moved to chat. $\endgroup$ – Sean Owen Feb 1 '17 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.