How to correctly convert the column in csv that contains the dates into JSON

user_12010283 0
Viewed 541

In my csv file, the "ESTABLİSHMENT DATE" column is delimited by the slashes like this: 01/22/2012.

I am converting the csv format into the JSON format, which needs to be done with pandas, but the "ESTABLİSHMENT DATE" column isn't correctly translated to JSON.

df = pd.read_csv(my_csv)
df.to_json("some_path", orient="records")

I don't understand why it awkwardly adds the backward slashes.

"ESTABLİSHMENT DATE":"01\/22\/2012",

However, I need to write the result to a file as the following:

"ESTABLİSHMENT DATE":"01/22/2012",
pandas python
1 Answers
  • Forward slash in json file from pandas dataframe answers why it awkwardly adds the backward slashes, and this answer shows how to use the json library to solve the issue.
    • As long as the date format is 01/22/2012, the / will be escaped with \.
  • To correctly convert the column in a csv that contains the dates into JSON, using pandas, can be done by converting the 'date' column to a correct datetime dtype, and then using .to_json.
    • 2012-01-22 is the correct datetime format, but .to_json will convert that to 1327190400000. After using pd.to_datetime to set the correct format as %Y-%m-%d, the type must be set to a string.
import pandas as pd

# test dataframe
df = pd.DataFrame({'date': ['01/22/2012']})

# display(df)
         date
0  01/22/2012

# to JSON
print(df.to_json(orient='records'))
[out]: [{"date":"01\/22\/2012"}]

# set the date column to a proper datetime
df.date = pd.to_datetime(df.date, format='%m/%d/%Y')

# display(df)
        date
0 2012-01-22

# to JSON
print(df.to_json(orient='records'))
[out]: [{"date":1327190400000}]

# set the date column type to string
df.date = df.date.astype(str)

# to JSON
print(df.to_json(orient='records'))
[out]: [{"date":"2012-01-22"}]

# as a single line of code
df.date = pd.to_datetime(df.date, format='%m/%d/%Y').astype(str)
user_77588040
Related