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,
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"}]