# Ignore consecutive whitespaces with CSV.read(...)
**URL:** https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304
**Category:** Data
**Tags:** question
**Created:** [May 31, 2018, 7:33pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304 "2018-05-31T19:33:07Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@e3c6](https://discourse.julialang.org/u/e3c6)
#### Post date: [May 31, 2018, 7:33pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/1 "2018-05-31T19:33:07Z")
I am reading a file with `CSV.read(...)` that contains many consecutive spaces between columns because the colmuns are padded. I passed the keyword argument `delim=' '`, but it thinks that every space counts as a new column, instead of ignoring long stretches of consecutive white spaces.
How can I do this?
---
### Author:  [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)
#### Post date: [May 31, 2018, 7:45pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/2 "2018-05-31T19:45:46Z")
What delimiter is it using? Have you tried `CSV.read(filename, delim=delim)` without anything else? That should work. If at all possible you should try to have csv’s with quote characters (e.g. “”") for strings, though of course one cannot always control what one gets.
Worst case scenario you can just use `strip` on relevant column, for example
```julia
df[:bad_column] = strip.(df[:bad_column])
```
---
### Author:  [@e3c6](https://discourse.julialang.org/u/e3c6)
#### Post date: [May 31, 2018, 7:59pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/3 "2018-05-31T19:59:48Z")
> [@ExpandingMan](#):
>
> Have you tried `CSV.read(filename, delim=delim)` without anything else?
That’s what I did. Precisely
```julia
CSV.read(filename, delim=' ')
```
---
### Author:  [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)
#### Post date: [May 31, 2018, 8:03pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/4 "2018-05-31T20:03:55Z")
Sorry, I meant what is the actual file delimiter (as in commas, tabs)? I think it should work if you only give it that and nothing else, otherwise you’ll have to use `strip`.
---
### Author:  [@e3c6](https://discourse.julialang.org/u/e3c6)
#### Post date: [May 31, 2018, 8:15pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/5 "2018-05-31T20:15:01Z")
The delimiter is whitespace. But the columns are padded. This means that there is more than one space between columns
---
### Author:  [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)
#### Post date: [May 31, 2018, 8:17pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/6 "2018-05-31T20:17:56Z")
> [@e3c6](#):
>
> The delimiter is whitespace.
Whitespace as in tab? If it’s tab you should do `delim='\t'`. Maybe you’re implying that it’s _just_ fixed width and there are _no_ delimiters. I’ve never encountered such a thing, but I’ve seen some crazy stuff so I guess nothing would shock me at this point. If that’s the case you might be hard pressed to find any CSV readers that support this. I can’t think of any hack that would get CSV.jl to read such a thing properly, but if that really is your use case the CSV.jl contributors may have some ideas.
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [May 31, 2018, 8:19pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/7 "2018-05-31T20:19:35Z")
If that’s indeed a fixed-width file, see:
> [@Is there no standard way to read files with fixed width columns in the new DataFrames ecosystem?](https://discourse.julialang.org/t/is-there-no-standard-way-to-read-files-with-fixed-width-columns-in-the-new-dataframes-ecosystem/7324):
>
> I’ve been using DataFrames.readtable() to read files with fixed width columns, but that function is now deprecated in favor of CSV.read(). However it seems that CSV.read() lacks the flag to “treat consecutive whitespace delimiters as one” that would be required to make it handle fixed width data. Here’s a [sample file](http://www.pik-potsdam.de/~mmalte/rcps/data/RCP3PD_MIDYEAR_CONCENTRATIONS.DAT) from climate science (standardized scenarios of greenhouse gas concentrations called “Representative Concentration Pathways”). [More similar files available here](http://www.pik-potsdam.de/~mmalte/rcps/index.htm). DataFrames.readtab…
> [@Reading Fixed-Width Column Data](https://discourse.julialang.org/t/reading-fixed-width-column-data/4020):
>
> Hi All, I’m trying to read in a file with rows that look like 13 MEXICO 130971 14 INDONESIA 126582 15 UNITED KINGDOM 114486 Note the possible presence of spaces in the 2nd column. I haven’t been able to find any tool that will easily do this for me (saw [this](https://github.com/JuliaLang/julia/issues/5391), but the suggestion there doesn’t work here). Does one exist that I’m not finding, or do I hav…
---
### Author:  [@e3c6](https://discourse.julialang.org/u/e3c6)
#### Post date: [May 31, 2018, 8:53pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/8 "2018-05-31T20:53:55Z")
Thanks, that’s just my issue. Good to know it’s on the Todo list
---
### Author:  [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)
#### Post date: [May 31, 2018, 11:28pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/9 "2018-05-31T23:28:23Z")
For [CSVFiles.jl](https://github.com/davidanthoff/CSVFiles.jl) you can use the `spacedelim` option:
```julia
using CSVFiles, DataFrames
df = DataFrame(load("filename.csv", spacedelim=true))
```
---
### Author:  [@aakhmetz](https://discourse.julialang.org/u/aakhmetz)
#### Post date: [June 1, 2018, 1:36am UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/10 "2018-06-01T01:36:39Z")
I think it is a normal behaviour for counting double space as the one with missing column. Otherwise, there is an ambiguity on how to define possible NAs
---
### Author:  [@e3c6](https://discourse.julialang.org/u/e3c6)
#### Post date: [June 1, 2018, 1:48am UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/11 "2018-06-01T01:48:14Z")
I see your point. But the interesting thing is that `DataFrames.read_table(..., separator=' ')` handles this without problems. It automatically ignores the padding blank spaces.
---
### Author:  [@kevbonham](https://discourse.julialang.org/u/kevbonham)
#### Post date: [June 2, 2018, 1:28pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/12 "2018-06-02T13:28:46Z")
I’ve been using `CSVFiles.jl` (linked above) for a while now, and find it **much** more flexible than the “recommended” `CSV.jl`. If the only source of my files were programmers with good hygiene, CSV might work. But as I get all kinds of crap, I need that flexibility.
Another option (slightly dangerous) option is just to process the files to replace consectutive whitespace with tabs. I end up doing stuff like this all the time with `sed`, eg:
```julia
$ cat $FILE | sed -E -e 's/[\t]+/\t/g' > new_file.tsv
```
You could do the same thing in Julia too:
```julia
open("old_file.txt") do bad; open("new_file.tsv", "w+") do good
for l in eachline(bad, strip=false)
replace!(l, r"[\t]+", "\t")
write(good, l)
end
end; end
```
Or something… Note: both of these solutions are approximate, I didn’t test them and (esp on the Julia) some of the syntax may be off
---
### Author:  [@e3c6](https://discourse.julialang.org/u/e3c6)
#### Post date: [June 2, 2018, 11:40pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/13 "2018-06-02T23:40:04Z")
Is there a way to do the stripping in Julia, but without creating a new file? Maybe one can modify the stream before it is fed to the DataFrame reader.
---
### Author:  [@kevbonham](https://discourse.julialang.org/u/kevbonham)
#### Post date: [June 3, 2018, 4:36pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/14 "2018-06-03T16:36:14Z")
I’m sure there is. I don’t know how to do it though…
---
### Author:  [@crsl4](https://discourse.julialang.org/u/crsl4)
#### Post date: [January 8, 2019, 4:03pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/15 "2019-01-08T16:03:00Z")
Hello,
I am afraid that I have the same issue. I have a table with multiple spaces separating columns (not necessarily fixed size):
```julia
shell> head merlin.err
FAMILY PERSON MARKER RATIO
A EP01223 rs710865 0.0214
A EP01227 rs11249215 0.0107
A EP01228 rs11249215 0.00253
A EP01228 rs10903129 0.0116
```
The function readtable in DataFrames is able to read this table by treating repeated spaces as delimiter:
```julia
julia> using DataFrames
julia> dat=readtable("merlin.err",header=true, separator=' ')
WARNING: readtable is deprecated, use CSV.read from the CSV package instead
944×4 DataFrames.DataFrame
│ Row │ FAMILY │ PERSON │ MARKER │ RATIO │
├─────┼────────────┼────────────┼────────────┼─────────┤
│ 1 │ A │ EP01223 │ rs710865 │ 0.0214 │
│ 2 │ A │ EP01227 │ rs11249215 │ 0.0107 │
│ 3 │ A │ EP01228 │ rs11249215 │ 0.00253 │
│ 4 │ A │ EP01228 │ rs10903129 │ 0.0116 │
```
But I cannot do this with CSV.read, even with the ignorerepeated option:
```julia
julia> using CSV
julia> dat = CSV.read("merlin.err",header=true, delim=' ', ignorerepeated=true)
941×5 DataFrames.DataFrame
│ Row │ Column1 │ FAMILY │ PERSON │ MARKER │ RATIO │
│ │ String⍰ │ String⍰ │ String⍰ │ String⍰ │ Float64⍰ │
├─────┼────────────┼────────────┼────────────┼────────────┼──────────┤
│ 1 │ missing │ A │ EP01223 │ rs710865 │ 0.0214 │
│ 2 │ missing │ A │ EP01227 │ rs11249215 │ 0.0107 │
│ 3 │ missing │ A │ EP01228 │ rs11249215 │ 0.00253 │
│ 4 │ missing │ A │ EP01228 │ rs10903129 │ 0.0116 │
```
I am in julia 1.0.0, with CSV v0.4.3.
I should add that it is not just the first column with all missing values. The first column has some non-missing values as well, and other columns have missing values too (missing values caused by repeated spaces).
Any help is appreciated, thanks!!
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [January 8, 2019, 4:15pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/16 "2019-01-08T16:15:05Z")
Can you file a bug against CSV.jl, with a small example to reproduce the problem?
---
### Author:  [@davidavdav](https://discourse.julialang.org/u/davidavdav)
#### Post date: [October 8, 2019, 8:01am UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/17 "2019-10-08T08:01:10Z")
Argh… I remember having to move heaven and earth to get a PR for readtable() accepted that interpreted multiple whitespace characters as a single delimiter, as in the cases above, and has been the custom in several decades of unix text file tables. Now readtable() is deprecated and CSV.read() shows the same old behavior, which IMHO is a form of regression…
---
### Author:  [@nalimilan](https://discourse.julialang.org/u/nalimilan)
#### Post date: [October 8, 2019, 12:02pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/18 "2019-10-08T12:02:18Z")
Doesn’t `ignorerepeated=true` give the intended behavior? See [https://github.com/JuliaData/CSV.jl/pull/266](https://github.com/JuliaData/CSV.jl/pull/266)
---
### Author:  [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)
#### Post date: [October 8, 2019, 4:34pm UTC](https://discourse.julialang.org/t/ignore-consecutive-whitespaces-with-csv-read/11304/19 "2019-10-08T16:34:02Z")
You can also try the `spacedelim=true` option with [CSVFiles.jl](https://github.com/queryverse/CSVFiles.jl).