# DateFrame.jl slow, convert one string-column to UTC time (Int)
**URL:** https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904
**Category:** General Usage
**Tags:** package
**Created:** [June 24, 2018, 7:52am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904 "2018-06-24T07:52:29Z")
**Posts on this page:** 15
**Page:** 1
### Author:  [@zhangliye](https://discourse.julialang.org/u/zhangliye)
#### Post date: [June 24, 2018, 7:52am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/1 "2018-06-24T07:52:29Z")
I have one DataFrame with a column of Time in the format of String. I want to convert this column to UTC time in second (Int). The code is as follows. However, it is quite slow. Could anyone give some advice to improve the speed? Thanks!
```julia
function string2UTC(x::String)
t = -1
try
td = Dates.DateTime(x, "yyyy-mm-dd HH:MM:SS")
t = convert(Int, datetime2unix(td) )
catch
return Missing
end
return t
end
@time df_ais[:t] = string2UTC.( df_ais[:vmim_timestamp] )
```
The table has only 3,233,003 rows. The operation takes **_207.216866 seconds_** (556.08 M allocations: 22.040 GiB, 4.91% gc time). The similar operation using Pandas + Python seems much faster.
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [June 24, 2018, 8:04am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/2 "2018-06-24T08:04:10Z")
Since you are using the same format multiple times, it is perhaps better to use [a `DateFormat` object](https://docs.julialang.org/en/latest/stdlib/Dates/), eg
```julia
dateformat = Dates.DateFormat("yyyy-mm-dd HH:MM:SS")
```
then parse with
```julia
Dates.DateTime(x, dateformat)
```
Also note that `tryparse` has a method for parsing dates (via `DateFormat`), but that will still give a `Nullable` in `v0.6.3`, so you have to convert to `missing` manually.
Finally, perhaps you want to return `missing` (the value), not the type `Missing`.
---
### Author:  [@zhangliye](https://discourse.julialang.org/u/zhangliye)
#### Post date: [June 24, 2018, 8:31am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/3 "2018-06-24T08:31:05Z")
Hi, @Tamas_Papp Thank you so much for your advice. I am using Julia v0.6.2 and the converted time column type is Unin{Missing, Int64} .
I have modified the code, however, there is no speed increase.
```julia
function string2UTC2(x::String) :: Union{Missing,Int}
dateformat = Dates.DateFormat("yyyy-mm-dd HH:MM:SS")
t = Missing
try
td = Dates.DateTime(x, dateformat)
t = convert(Int, datetime2unix(td) )
catch
return Missing
end
return t
end
```
Running time: **_227.854824 seconds_** (562.55 M allocations: 22.137 GiB, 5.23% gc time)
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [June 24, 2018, 8:49am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/4 "2018-06-24T08:49:49Z")
> [@zhangliye](#):
>
> there is no speed increase
Possibly because you did not do what I suggested? Eg (to make an MWE)
```julia
using Base.Dates
using Missings
N = 3233003
dform = DateFormat("yyyy-mm-dd HH:MM:SS")
dates = [Dates.format(DateTime(rand(1900:2100), rand(1:12), rand(1:28),
rand(0:23), rand(0:59), rand(0:59)), dform)
for _ in 1:N];
function datestring2UTC(str, dform)
maybedate = tryparse(DateTime, str, dform)
isnull(maybedate) ? missing : convert(Int, datetime2unix(get(maybedate)))
end
datestring2UTC(dates[1], dform) # to compile
```
then
```julia
julia> @time datestring2UTC.(dates, dform);
0.836423 seconds (12.93 M allocations: 320.658 MiB, 32.44% gc time)
```
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [June 24, 2018, 8:52am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/5 "2018-06-24T08:52:30Z")
Also, in `v0.7`,
```julia
using Dates
N = 3233003
dform = DateFormat("yyyy-mm-dd HH:MM:SS")
dates = [Dates.format(DateTime(rand(1900:2100), rand(1:12), rand(1:28),
rand(0:23), rand(0:59), rand(0:59)), dform)
for _ in 1:N];
function datestring2UTC(str, dform)
date = tryparse(DateTime, str, dform)
date == nothing ? missing : convert(Int, datetime2unix(date))
end
```
then
```julia
julia> @time datestring2UTC.(dates, dform);
0.498187 seconds (14 allocations: 24.666 MiB, 1.38% gc time)
```
---
### Author:  [@ScottPJones](https://discourse.julialang.org/u/ScottPJones)
#### Post date: [June 24, 2018, 9:07am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/6 "2018-06-24T09:07:13Z")
Does it make any difference to change `dform` to a `const`?
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [June 24, 2018, 9:18am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/7 "2018-06-24T09:18:45Z")
I would be surprised if it made any difference, since I am passing it as an argument to the broadcasted function.
---
### Author:  [@zhangliye](https://discourse.julialang.org/u/zhangliye)
#### Post date: [June 24, 2018, 9:32am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/8 "2018-06-24T09:32:59Z")
@Tamas_Papp Thanks for your help! It runs quite fast now! The reason of the slow speed is caused by the definition of the local variable “dform” within the function. The code is as follows.
```julia
dform = DateFormat("yyyy-mm-dd HH:MM:SS")
function string2UTC(str::String, dform)
date = tryparse(DateTime, str, dform)
#date == nothing ? Missing : convert(Int, datetime2unix( date ) )
date == nothing ? Missing : convert(Int, datetime2unix( get(date) ) )
end
@time df_ais[:t] = string2UTC.( df_ais[:vmim_timestamp], dform )
```
The running time is **_2.617471 seconds_** (9.71 M allocations: 271.882 MiB, 69.95% gc time)
---
### Author:  [@zhangliye](https://discourse.julialang.org/u/zhangliye)
#### Post date: [June 24, 2018, 9:48am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/9 "2018-06-24T09:48:08Z")
@ScottPJones There is no significant speed increase by change dform to a const. Thank you for your reply! Defining a local variable slows the speed by hundreds of times. Feel surprised!
The const variable version is as follows.
```julia
function string2UTC2(str::String)
const dform = DateFormat("yyyy-mm-dd HH:MM:SS")
date = tryparse(DateTime, str, dform)
#date == nothing ? Missing : convert(Int, datetime2unix( date ) )
date == nothing ? Missing : convert(Int, datetime2unix( get(date) ) )
end
@show string2UTC2( "2017-09-01 03:48:46" )
@time df_ais[:t] = string2UTC2.( df_ais[:vmim_timestamp] )
```
The running time is _ **202.683401 seconds** _ (552.85 M allocations: 22.089 GiB, 4.97% gc time)
---
### Author:  [@ScottPJones](https://discourse.julialang.org/u/ScottPJones)
#### Post date: [June 24, 2018, 9:49am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/10 "2018-06-24T09:49:25Z")
I hadn’t looked to see how Julia would optimize that, if you made dform a const, and didn’t pass it do datestring2UTC.
---
### Author:  [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)
#### Post date: [June 24, 2018, 9:52am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/11 "2018-06-24T09:52:36Z")
> [@zhangliye](#):
>
> The reason of the slow speed is caused by the definition of the local variable “dform” within the function.
Not quite, it is caused by `DateFormat` constructing a parser object. The idea is to do this once.
---
### Author:  [@zhangliye](https://discourse.julialang.org/u/zhangliye)
#### Post date: [June 24, 2018, 10:00am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/12 "2018-06-24T10:00:08Z")
@Tamas_Papp Thank you much! I never think this DateFormat object construction operation is soon time-consuming. With this operation it costs about 200 seconds, while only costs about 2 seconds without this operation. That is to say, “DateFormat()” construction function cost about 198 seconds, while “tryparse” and “convert” functions cost only 2 seconds. Also feel surprised!
---
### Author:  [@ScottPJones](https://discourse.julialang.org/u/ScottPJones)
#### Post date: [June 24, 2018, 10:03am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/13 "2018-06-24T10:03:22Z")
Yes, I understand that that is by far the largest part of it, I was just curious, if making it a const (outside of the function, and then not passing it, might make a difference).
(edit) OK, I just got out of bed and tested it, and no measurable difference.
---
### Author:  [@cdsousa](https://discourse.julialang.org/u/cdsousa)
#### Post date: [June 25, 2018, 11:38am UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/14 "2018-06-25T11:38:27Z")
Hi @zhangliye, you still have not got all of it…
It must be:
```julia
const dform = DateFormat("yyyy-mm-dd HH:MM:SS") #### !!!!!!!!!!!!! const outside
function string2UTC2(str::String)
date = tryparse(DateTime, str, dform)
date == nothing ? #=!!!!=# missing #=!!!!=# : convert(Int, datetime2unix( get(date) ) )
##### !!!!!!!!!!!!!! `Missing` is the type, `missing` is the instance of that ype (like `true` is an instance of `Bool`)
end
@show string2UTC2( "2017-09-01 03:48:46" )
@time df_ais[:t] = string2UTC2.( df_ais[:vmim_timestamp] )
```
---
### Author:  [@zhangliye](https://discourse.julialang.org/u/zhangliye)
#### Post date: [June 25, 2018, 3:16pm UTC](https://discourse.julialang.org/t/dateframe-jl-slow-convert-one-string-column-to-utc-time-int/11904/15 "2018-06-25T15:16:34Z")
Thanks! It runs fast when the DateFormat object is defined out side the function no matter whether DateFormat is const.