# Parsing date column when reading in CSV

**URL:** <https://discourse.julialang.org/t/parsing-date-column-when-reading-in-csv/95641>\
**Category:** General Usage\
**Tags:** dates, dataframes, csv\
**Created:** [March 6, 2023, 11:34pm UTC](https://discourse.julialang.org/t/parsing-date-column-when-reading-in-csv/95641 "2023-03-06T23:34:59Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![mbah](https://avatars.discourse-cdn.com/v4/letter/m/bcef8e/32.png) [@mbah](https://discourse.julialang.org/u/mbah)\
**Post date:** [March 6, 2023, 11:34pm UTC](https://discourse.julialang.org/t/parsing-date-column-when-reading-in-csv/95641/1 "2023-03-06T23:34:59Z")

</div>

Hello all,

I have a CSV file with a date column that I am trying to parse when reading it in. I have tried several options but they don’t work.

```julia
df = DataFrame(CSV.File(joinpath(data_path, "PJM_2018.csv"))) 

```

```julia
8751×8 DataFrame
  Row │ time_stamp Coal Nuclear Gas Oil Wind Hydro Solar   
      │ String31 Float64 Float64 Float64 Float64 Float64 Float64 Float64 
──────┼─────────────────────────────────────────────────────────────────────────────────
    1 │ 01.01.2018 00:00 48.55 35.53 23.38 1.54 3.91 0.7 0.0
    2 │ 01.01.2018 01:00 48.06 35.55 22.77 1.56 3.41 1.04 0.0
  ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮
 8751 │ 12/31/2018 14:00 24.49 34.76 29.05 0.22 3.84 2.14 0.0456

```

The data the following format

```julia
 "12/31/2018 14:00"

```

Any suggestions on how to parse the time\_stamp coloumn?

Thanks

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [March 7, 2023, 6:17am UTC](https://discourse.julialang.org/t/parsing-date-column-when-reading-in-csv/95641/2 "2023-03-07T06:17:52Z")

</div>

Have you tried to use the `dateformat` kwarg as specified in the docs?

[https://csv.juliadata.org/stable/reading.html#dateformat](https://csv.juliadata.org/stable/reading.html#dateformat)

Based on the sample data you posted above though this will fail because your `time_stamp` column has multiple different date formats. You’ll have to manually parse this after reading in the CSV.

---

<div class="post-metadata">

**Author:** ![mbah](https://avatars.discourse-cdn.com/v4/letter/m/bcef8e/32.png) [@mbah](https://discourse.julialang.org/u/mbah)\
**Post date:** [March 7, 2023, 9:18am UTC](https://discourse.julialang.org/t/parsing-date-column-when-reading-in-csv/95641/3 "2023-03-07T09:18:22Z")

</div>

Thanks for pointing out the different date formats. I formatted them to a consistent format (mm/dd/yyyy) and parsed them using the following code:

```plaintext
df.time_stamp = DateTime.(df.time_stamp, "mm/dd/yyyy HH:MM")

```

Thanks

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [March 7, 2023, 10:53am UTC](https://discourse.julialang.org/t/parsing-date-column-when-reading-in-csv/95641/4 "2023-03-07T10:53:44Z")

</div>

I don’t know if the following is useful to the problem nor, even less, if it is efficient, but it was just to do an exercise on what is illustrated [here](https://docs.julialang.org/en/v1/manual/strings/)

```julia
data = """
col1;col2;col3;col4;col5
"05.02.2023";1000,01;2000,02;3000,03;12:00:00
"06.02.2023";4000,04;5000,05;6000,06;12:00:00
"06.02.2023";4000,04;5000,05;6000,06;12:00:00
"02/06/2023";4000,05;5000,05;6000,06;12:00:00
"02/06/2023";4000,06;5000,05;6000,06;12:00:00
"02/06/2023";4000,07;5000,05;6000,06;12:00:00
"""
tbl = CSV.File(IOBuffer(data); delim=';') |> columntable

tbl.col1

replace.(tbl.col1, r"(\d\d/)(?<ng>\d\d/)" => s"\g<ng>\1","."=>"/")

```

I don’t have much experience with regular expressions, but this case seems like it could be solved maybe more clearly

```julia

replace.(tbl.col1, r"(\d\d/)(\d\d/)" => s"\2\1", "."=>"/")

```

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [March 7, 2023, 6:17pm UTC](https://discourse.julialang.org/t/parsing-date-column-when-reading-in-csv/95641/5 "2023-03-07T18:17:08Z")

</div>

```julia
using CSV, DataFrames, Dates

data = """
time_stamp,Coal,Nuclear,Gas,Oil,Wind,Hydro,Solar
01.01.2018 00:00,48.55,35.53,23.38,1.54,3.91,0.7,0.0
01.01.2018 01:00,48.06,35.55,22.77,1.56,3.41,1.04,0.0
12/31/2018 14:00,24.49,34.76,29.05,0.22,3.84,2.14,0.045
"""

df = CSV.read(IOBuffer(data), DataFrame)

df.time_stamp .= replace.(df.time_stamp, "." => "/")
df.time_stamp .= DateTime.(df.time_stamp, "mm/dd/yyyy HH:MM")
df

```

---

<div class="post-metadata">

**Author:** ![mbah](https://avatars.discourse-cdn.com/v4/letter/m/bcef8e/32.png) [@mbah](https://discourse.julialang.org/u/mbah)\
**Post date:** [March 7, 2023, 9:50pm UTC](https://discourse.julialang.org/t/parsing-date-column-when-reading-in-csv/95641/6 "2023-03-07T21:50:09Z")

</div>

@rafael.guerra thanks for the nifty code. Really helpful!

---

<div class="post-metadata">

**Author:** ![mbah](https://avatars.discourse-cdn.com/v4/letter/m/bcef8e/32.png) [@mbah](https://discourse.julialang.org/u/mbah)\
**Post date:** [March 7, 2023, 9:52pm UTC](https://discourse.julialang.org/t/parsing-date-column-when-reading-in-csv/95641/7 "2023-03-07T21:52:40Z")

</div>

@rocco_sprmnt21 thanks for this