# Converting string to DateTime in DataFrame
**URL:** https://discourse.julialang.org/t/converting-string-to-datetime-in-dataframe/49524
**Category:** New to Julia
**Created:** [November 3, 2020, 1:45pm UTC](https://discourse.julialang.org/t/converting-string-to-datetime-in-dataframe/49524 "2020-11-03T13:45:32Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@alunap](https://discourse.julialang.org/u/alunap)
#### Post date: [November 3, 2020, 1:45pm UTC](https://discourse.julialang.org/t/converting-string-to-datetime-in-dataframe/49524/1 "2020-11-03T13:45:32Z")
I’m reading in a json file, putting it into DataFrames to clean it up, before dumping into MongoDB (via mongoc). It is almost there, my only problem is that one of the fields is a timestamp, which is coming in as a string. The strings are of the form: “2019-11-18T13:09:31Z”. The problem is that DateTime doesn’t like that Zulu timezone, and Timezones.jl doesn’t help. So I am now trying to strip off the last character (since in practice they are all recorded in UTC). the files actually contain JSON in just the last line, I strip out the HTTP headers in the first few lines.
I am using DrWatson, JSONTables, DataFrames, Dates. I have a function
function read\_jsondump(filename)
cap\_file = readlines(datadir(“import”, filename))
df = DataFrame(jsontable(cap\_file[end]))
transform(df, :time =\> chop.(:time))
df[!, :time] = convert.(DateTime, df[:, :time])
return df
end
I’m still getting confused in the use of broadcasting is places like this. the transform line doesn’t work, doesn’t like chop. For reference, in pandas what I do here is
df[‘timestamp’] = pd.to\_datetime(df[‘time’])
which actually creates a new field and I then delete the original, but that doesn’t matter. The point is that pandas to\_datetime can handle the ‘Z’, but Julia DateTime can’t, so I have to work around it.
I am working my way through the Introduction to Dataframes tutorial, and have also just bought Tom Kwong’s book, so hopefully my confusion about handling things like this will disappear soon.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [November 3, 2020, 2:20pm UTC](https://discourse.julialang.org/t/converting-string-to-datetime-in-dataframe/49524/2 "2020-11-03T14:20:13Z")
Read the docs for `transform` more closely. `transform` works like
```julia
transform(df, source => fun => dest)
```
where `fun` is a function. So you you want
```julia
transform(df, :time => (t -> chop.(t)) => :time)
```
OR, you can use `ByRow`
```julia
transform(df, :time => ByRow(chop) => :time)
```
OR you can use DataFramesMeta to get the syntax I think you are expecting
```julia
@transform(df, time = chop.(:time))
```
---
### Author:  [@MarcMush](https://discourse.julialang.org/u/MarcMush)
#### Post date: [November 3, 2020, 2:23pm UTC](https://discourse.julialang.org/t/converting-string-to-datetime-in-dataframe/49524/3 "2020-11-03T14:23:33Z")
if you’re sure data will always have this format, you can specify the dateformat:
```julia
julia> using Dates
julia> df = dateformat"y-m-dTH:M:SZ"
dateformat"y-m-dTH:M:SZ"
julia> DateTime("2019-11-18T13:09:31Z", df)
2019-11-18T13:09:31
```
or better, to accept timezones,
```julia
julia> using TimeZones
julia> ZonedDateTime("2019-11-18T13:09:31Z", dateformat"yyyy-mm-dd\THH:MM:SSz")
2019-11-18T13:09:31+00:00
```
Although I think this dateformat should be recognized by default since it’s so common
---
### Author:  [@alunap](https://discourse.julialang.org/u/alunap)
#### Post date: [November 3, 2020, 2:35pm UTC](https://discourse.julialang.org/t/converting-string-to-datetime-in-dataframe/49524/4 "2020-11-03T14:35:17Z")
I see, thanks. Yes, I tried each of those, and after discovering that it doesn’t do it in place, it does chop the string, but it returns a SubString rather than a String, and I find that you can’t convert SubStrings to DateTimes either, so this approach has got me no nearer to converting those strings to DateTimes. I’ll dig some more. Thanks.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [November 3, 2020, 2:42pm UTC](https://discourse.julialang.org/t/converting-string-to-datetime-in-dataframe/49524/5 "2020-11-03T14:42:09Z")
You can always call `string` on the result.
```julia
@transform(df, time = string.(chomp.(:time)))
```
But it seems to work for me on 1.5.0
```julia
julia> DateTime("1996-01-01")
1996-01-01T00:00:00
julia> DateTime(chomp("1996-01-01"))
1996-01-01T00:00:00
```
---
### Author:  [@alunap](https://discourse.julialang.org/u/alunap)
#### Post date: [November 3, 2020, 3:00pm UTC](https://discourse.julialang.org/t/converting-string-to-datetime-in-dataframe/49524/6 "2020-11-03T15:00:45Z")
got it at last!
```julia
function read_jsondump(filename)
cap_file = readlines(datadir("import", filename))
df = DataFrame(jsontable(cap_file[end]))
df = @transform(df, time = string.(chop.(:time)))
@transform(df, time = DateTime.(:time))
end
```
Phew! Thanks for all your help!