# Best way to import a CSV with a repeated structure

**URL:** <https://discourse.julialang.org/t/best-way-to-import-a-csv-with-a-repeated-structure/119377>\
**Category:** Data\
**Tags:** io\
**Created:** [September 13, 2024, 3:44pm UTC](https://discourse.julialang.org/t/best-way-to-import-a-csv-with-a-repeated-structure/119377 "2024-09-13T15:44:21Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![sylvaticus](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sylvaticus/32/203883_2.png) [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)\
**Post date:** [September 13, 2024, 3:44pm UTC](https://discourse.julialang.org/t/best-way-to-import-a-csv-with-a-repeated-structure/119377/1 "2024-09-13T15:44:21Z")

</div>

Aside any comment on whoever published data with this layout, I have to import a CSV like this, where the data of interest is repeated on each day of the year:

 ![image](https://global.discourse-cdn.com/julialang/original/3X/7/8/78704411a54b2fd2b08eed3a3c91ef9a0bac2a97.png)

Does CSV.jl has options to load the (small) file at once ? Or should I go for a manual implementation using readline ? Or perhaps I should CSV-load the file piece by piece in a daily loop, accounting for the different row indexes… at least it is constant… ?

---

<div class="post-metadata">

**Author:** ![dlakelan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dlakelan/32/8491_2.png) [@dlakelan](https://discourse.julialang.org/u/dlakelan)\
**Post date:** [September 13, 2024, 4:10pm UTC](https://discourse.julialang.org/t/best-way-to-import-a-csv-with-a-repeated-structure/119377/2 "2024-09-13T16:10:41Z")

</div>

Another option would be to write a function that reads the file and outputs it as a single table, by including a column for the date, then read the CSV as usual.

---

<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:** [September 14, 2024, 6:06pm UTC](https://discourse.julialang.org/t/best-way-to-import-a-csv-with-a-repeated-structure/119377/3 "2024-09-14T18:06:08Z")

</div>

You could try:

```julia
using CSV, DataFrames, Dates

lines = readlines(file)
ix = findall(contains.(lines, "Données"))
dates = Date.(last.(split.(lines[ix], "du ")))
header = string.(split(lines[ix[1]+1]))
df = DataFrame()
for i in ix
   append!(df, CSV.read(IOBuffer(join(lines[i+2:i+25],"\n")), delim=" ", ignorerepeated=true, DataFrame, header=header))
end
insertcols!(df, 1, "Jour" => repeat(dates, inner=24))

```

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [September 14, 2024, 8:20pm UTC](https://discourse.julialang.org/t/best-way-to-import-a-csv-with-a-repeated-structure/119377/4 "2024-09-14T20:20:44Z")

</div>

Suppose your file looks like:

```julia
$ cat in.txt 
Day 1
1	10
2	20
3	15
4	20

Day 2
1	25
2	30
3	40
4	35

Day 3
1	40
2	45
3	30
4	15

```

A little Unix shell magic can be used to help Julia parse this as follows:

```julia
julia> using CSV, DataFrames

julia> CSV.read(pipeline(`awk 'BEGIN { day = "0"; print "day\ttime\tvalue" } /^Day/ { day = $2} /^[^D]/ {print day,"\t" $0}' in.txt`), DataFrame; delim='\t')
12×3 DataFrame
 Row │ day time value 
     │ Int64 Int64 Int64 
─────┼─────────────────────
   1 │ 1 1 10
   2 │ 1 2 20
   3 │ 1 3 15
   4 │ 1 4 20
   5 │ 2 1 25
   6 │ 2 2 30
   7 │ 2 3 40
   8 │ 2 4 35
   9 │ 3 1 40
  10 │ 3 2 45
  11 │ 3 3 30
  12 │ 3 4 15

```

The key useful info is the way `awk` tool is used to separate the important data lines from the intra-file headers, and to process the data in the header to enable adding it later to each line separately. This is the `awk` command on its own:

```julia
awk 'BEGIN { day = "0"; print "day\ttime\tvalue" } /^Day/ { day = $2} /^[^D]/ {print day,"\t" $0}' in.txt

```

Hopefully the awk logic is clear enough.

P.S. screenshots are annoying in a post. Try to insert copy-pasteable stuff

---

<div class="post-metadata">

**Author:** ![sylvaticus](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sylvaticus/32/203883_2.png) [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)\
**Post date:** [September 15, 2024, 8:44am UTC](https://discourse.julialang.org/t/best-way-to-import-a-csv-with-a-repeated-structure/119377/5 "2024-09-15T08:44:13Z")

</div>

Tahnk you. Indeed I should have posted a minimal syntetic example. I posted a screenshot as I didn’t expect a complete solution, only on which path to take.

At the end I used the CSV.read options `header` and `limit`, but I am surprised that CSV has a `select` option for the columns but not one for the rows.

---

<div class="post-metadata">

**Author:** ![TimG](https://avatars.discourse-cdn.com/v4/letter/t/82dd89/32.png) [@TimG](https://discourse.julialang.org/u/TimG)\
**Post date:** [September 15, 2024, 11:31am UTC](https://discourse.julialang.org/t/best-way-to-import-a-csv-with-a-repeated-structure/119377/6 "2024-09-15T11:31:29Z")

</div>

> [@sylvaticus](#):
>
> but I am surprised that CSV has a `select` option for the columns but not one for the rows.

[Here, quinnj](https://github.com/JuliaData/CSV.jl/issues/503#issuecomment-538697038) explains why it is not worth filtering rows.