2

I am working on a dataset and I applied to_json() method to export a pandas dataframe in a json file. I will then use this json file to upload to MongoDB. However I realise that the datetime format was converted to epoch timestamp. How do I retain the datetime format while exporting to JSON file as well as uploading the json file to MongoDB? Also, I do not want null fields in json output.

df:

    user_id     datetime
0   69490717    [{'checkin_date': 2021-02-01 00:00:00}]
1   67125777    [{'checkin_date': 2021-02-01 00:00:00}]
2   62747294    NaN
3   63216896    [{'checkin_date': 2021-02-01 00:00:00}]
4   51466797    [{'checkin_date': 2021-01-31 00:00:00}]
... ... ...
96  82758550    NaN
97  44662827    NaN
98  36376189    [{'checkin_date': 2021-01-18 00:00:00}]
99  71910948    [{'checkin_date': 2021-01-18 00:00:00}, {'checkout_date': 2021-01-20 00:00:00}]
100 54620533    NaN

Snippet of json output:

[{"user_id":62507249,"datetime":[{"checkin_date":1612051200000},{"checkout_date":1612051200000}]}, 
{"user_id":69546481,"datetime":[{"checkin_date":1612051200000}]}, ......]

Below is my code for converting to json:

jsonresult = df.T.apply(lambda row: row[~row.isnull()].to_json())
json_wrapped = "[%s]" % ",".join(jsonresult)

# write JSON to file
with open('jsonresult.json', 'w') as f:
    f.write(json_wrapped)
snow
  • 217
  • 1
  • 8

2 Answers2

2

I think you need date_format='iso' parameter in DataFrame.to_json and for remove rows with missing values is possible use DataFrame.dropna:

a = [{"user_id":1,"datetime":[{"checkin_date":pd.Timestamp('2021-02-01 00:00:00')},
                                     {"checkout_date":pd.Timestamp('2021-02-01 00:00:00')}]},
     {"user_id":2,"datetime":[{"checkin_date":pd.Timestamp('2021-02-01 00:00:00')}]},
     {"user_id":4,"datetime": np.nan}]
df = pd.DataFrame(a)
print (df)
   user_id                                           datetime
0        1  [{'checkin_date': 2021-02-01 00:00:00}, {'chec...
1        2            [{'checkin_date': 2021-02-01 00:00:00}]
2        4                                                NaN

jsonresult = df.dropna(subset=['datetime']).to_json(date_format='iso', orient='records')
print (jsonresult)
[{"user_id":1,"datetime":[{"checkin_date":"2021-02-01T00:00:00.000Z"},
                          {"checkout_date":"2021-02-01T00:00:00.000Z"}]},
 {"user_id":2,"datetime":[{"checkin_date":"2021-02-01T00:00:00.000Z"}]}]

dictresult = df.dropna(subset=['datetime']).to_dict(orient='records')
print (dictresult)
[{'user_id': 1, 'datetime': [{'checkin_date': Timestamp('2021-02-01 00:00:00')},
                             {'checkout_date': Timestamp('2021-02-01 00:00:00')}]}, 
 {'user_id': 2, 'datetime': [{'checkin_date': Timestamp('2021-02-01 00:00:00')}]}]
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • hi, the first part of your answer helps to certain extent, it essentially retains the date format but it converts the dates data type to strings so when I upload the json file to MongoDB, the dates are in string format instead of datetime. How do I retain the datetime format ? – snow Mar 08 '21 at 05:42
  • my bad, I just realised that I did not specify in my question that I am uploading the json file to MongoDB. Will edit my question accordingly – snow Mar 08 '21 at 05:43
  • @snow - Hmmm, if need json then all data are converted to strings, so no datetimes there. – jezrael Mar 08 '21 at 05:43
  • @snow - Possible solution is convert values to dictonaries like change `.to_json(date_format='iso', orient='records')` to `.to_dict(orient='records')` – jezrael Mar 08 '21 at 05:44
  • i tried, it returns me a list of dictionaries and strings but I a unable to write to json. Error: TypeError: write() argument must be str, not list ```` with open('jsonresult.json', 'w') as f: f.write(dictresult) `` – snow Mar 08 '21 at 05:56
  • @snow - If need json, then it is impossible. Because json are strings only. – jezrael Mar 08 '21 at 05:57
  • @snow - Maybe is necessary change method of write, e.g. like [this](https://stackoverflow.com/a/62860176/2901002) – jezrael Mar 08 '21 at 05:58
  • @snow - But is seems `unix` or `iso` format is good way - [link](https://stackoverflow.com/questions/11875770/how-to-overcome-datetime-datetime-not-json-serializable) – jezrael Mar 08 '21 at 06:02
1

You need to convert your datetime column to string using Series.astype:

df['datetime'] = df['datetime'].astype(str)

Also, your code for converting this to json is unnecessary. You can do below:

jsonresult  = df.to_json(orient='records')
Mayank Porwal
  • 33,470
  • 8
  • 37
  • 58