Menu
I am trying to send a pandas dataframe to_json and I am having some issues with the date. I am getting an addtional \ so that my records look like Updated:09\/06\/2016 03:09:44. Is it possible to not have this additional \ added? I am assuming that it is an escape character of some sort but I haven't been able to find any additional information regarding this.
I have been adjusting the various parameters but I havent had any luck df[0:10].to_json('splunkJsonFormat.txt', orient='records', date_format='ISO8601')
Sample Data:
b_Updated,
Updated:09/06/2016 03:09:44,
Updated:06/29/2016 08:16:52,
Updated:09/07/2016 07:54:37,
998
orient: String value, ('dict', 'list', 'series', 'split', 'records', 'index') Defines which dtype to convert Columns(series into). For example, 'list' would return a dictionary of lists with Key=Column name and Value=List (Converted series).
To convert the object to a JSON string, then use the Pandas DataFrame. to_json() function. Pandas to_json() is an inbuilt DataFrame function that converts the object to a JSON string. To export pandas DataFrame to a JSON file, then use the to_json() function.
If 'orient' is 'records' write out line delimited json format. Will throw ValueError if incorrect 'orient' since others are not list like. bool.
JSON is plain text, but has the format of an object, and is well known in the world of programming, including Pandas. In our examples we will be using a JSON file called 'data.json'.
Often, the JSON data you will be working on is stored locally as a .json file. However, Pandas json_normalize () function only accepts a dict or a list of dicts. To work around it, you need help from a 3rd module, for example, the Python json module: data = json.loads (f.read ()) loads data using Python json module.
What about keys that are not always present, for example, num_of_students is not available in the 2nd record. We can see that no error is thrown and those missing keys are shown as NaN. 2. Flattening a JSON with multiple levels Pandas json_normalize () works great for simple JSON (known as flattened JSON). What about JSON with multiple levels?
Pandas json_normalize() function is a quick, convenient, and powerful way for flattening JSON into a DataFrame. I hope this article will help you to save time in flattening JSON data. I recommend you to check out the documentation for the json_normalize() API and to know about other things you can do.
The JSON output you obtained is indeed correct and is the right behavior.
Allowing \/ helps when embedding JSON in a <script> tag, which doesn't allow </ inside strings. Hence, in JSON / and \/ are equivalent.
One workaround would be to separate the date from the string and convert it to a format more suitable where the datetime format is more evident.
df['b_Updated'] = df['b_Updated'].str.split(':', 1) \
.apply(lambda x: x[0] + ':' + str(pd.to_datetime(x[1])))
df.to_json(orient='records', date_format='iso')
[{"b_Updated":"Updated:2016-09-06 03:09:44"},
{"b_Updated":"Updated:2016-06-29 08:16:52"},
{"b_Updated":"Updated:2016-09-07 07:54:37"}]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
