CODEWITHSUNDEEP
pythonjsonpandas
CM-Dev
CM-Dev

Reputation: 393

Unable to convert pandas DataFrame to json using to_json

I am aware that there are several other posts on Stack Overflow regarding this same issue, however, not a single solution found on those posts, or any other post I've found online for that matter, has worked. I have followed numerous tutorials, videos, books, and Stack Overflow posts on pandas and all mentioned solutions have failed.

The frustrating thing is that all the solutions I have found are correct, or at least they should be; I am fairly new to pandas so my only conclusion is that I am probably doing something wrong.

Here is the pandas documentation that I started with: Pandas to_json Doc. I can't seem to get pandas to_json to convert a pandas DataFrame to a json object or json string.

Basically, I want to convert a csv string into a DataFrame, then convert that DataFrame into a json object or json string (I don't care which one). Then, once I have my json data structure, I'm going to bind it to a D3.js bar chart

Here is an example of what I am trying to do:

# Declare my csv string (Works):
csvStr = '"pid","dos","facility","a1c_val"\n"123456","2013-01-01 13:37:00","UOFU",5.4\n"65432","2014-01-01 14:32:00","UOFU",5.8\n"65432","2013-01-01 13:01:00","UOFU",6.4'
print (csvStr) # Just checking the variables contents

# Read csv and convert to DataFrame (Works):
csvDf = pandas.read_csv(StringIO.StringIO(csvStr))
print (csvDf) # Just checking the variables contents

# Convert DataFrame to json (Three of the ways I tried - None of them work):
myJSON = csvDf.to_json(path_or_buf = None, orient = 'record', date_format = 'epoch', double_precision = 10, force_ascii = True, date_unit = 'ms', default_handler = None) # Attempt 1
print (myJSON) # Just checking the variables contents

myJSON = csvDf.to_json() # Attempt 2
print (myJSON) # Just checking the variables contents

myJSON = pandas.io.json.to_json(csvDf)
print (myJSON) # Just checking the variables contents

The error that I am getting is:

argument 1 must be string or read-only character buffer, not DataFrame

Which is misleading because the documentation says "A Series or DataFrame can be converted to a valid JSON string."

Regardless, I tried giving it a string anyway, and it resulted in the exact same error.

I have tried creating a test scenario, following the exact steps from books and other tutorials and/or posts and it just results in the same error. At this point, I need a simple solution asap. I am open to suggestions, but I must emphasize that I do not have time waste on learning a completely new library.

Upvotes: 4

Views: 8821

Answers (2)

CM-Dev
CM-Dev

Reputation: 393

It turns out that the problem was becuase of my own stupid mistake. While testing my use of to_json, I copy and pasted an example into my code and went from there. Thinking I had commented out that code, I proceeded to try using to_json with my test data. Turns out the error I was receiving was being thrown from the example code that I had copy and pasted. Once I deleted everything and re-wrote it using my test data it worked.

However, as user667648 (Bair) pointed out, there was another mistake in my code. The orient param was suppose to be orient = 'records' and NOT orient = 'record'.

Upvotes: 0

Dair
Dair

Reputation: 16240

For you first attempt, the correct string is 'records' not 'record' This worked for me:

myJSON = csvDf.to_json(path_or_buf = None, orient = 'records', date_format = 'epoch', double_precision = 10, force_ascii = True, date_unit = 'ms', default_handler = None) # Attempt 1

Printing gives:

[{"pid":123456,"dos":"2013-01-01 13:37:00","facility":"UOFU","a1c_val":5.4},
 {"pid":65432,"dos":"2014-01-01 14:32:00","facility":"UOFU","a1c_val":5.8},
 {"pid":65432,"dos":"2013-01-01 13:01:00","facility":"UOFU","a1c_val":6.4}]

Upvotes: 5

Related Questions