1
$\begingroup$

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":"2.0.0.0","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"}}
Improve this question
$\endgroup$
3
  • 1
    $\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, 2017 at 20:52
  • $\begingroup$ can you provide a valid JSON file? $\endgroup$
    – MaxU - stand with Ukraine
    Jan 31, 2017 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, 2017 at 16:45

3 Answers 3

Reset to default
1
$\begingroup$

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

Improve this answer
$\endgroup$
1
  • 1
    $\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, 2017 at 14:30
0
$\begingroup$ 
import codecs
import pandas as pd
pd.read_json(codecs.open('json_file','r','utf-8'))

This should work.

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

You can try this. 

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

pd.read_json(Json)
Improve this answer
$\endgroup$
1
  • $\begingroup$ Comments are not for extended discussion; this conversation has been moved to chat. $\endgroup$
    – Sean Owen
    Feb 1, 2017 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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