Change dataframe value to NaN in Julia

3
votes

I have a dataset that uses ".." instead of NaN and I am currently trying to convert those strings into NaN. I have tried the following:

for i in 1:length(final_df[:,1])
    for j in 1:length(final_df[1,:])
        if final_df[i,j] == ".."
            final_df[i,j] = NaN
        end 
    end 
end

However I keep getting the following error: MethodError: Cannot 'convert' an object of type Float64 to an object of type String.

Here is a portion of the dataset for reference.

Any help is appreciated, thanks!

3
dataframejulianan
Can you post the result of eltypes(df) where df is your DataFrame? This is so we know the types of the columns of your DataFrame. Also, based on the image you posted it appears the ".." only appear in in the fourth column of your DataFrame, so why are you iterating over the entire DataFrame in the code you posted? Couldn't you just iterate over the 4th column? - Colin T Bowers

3 Answers

2
votes

NaN is a reserved word for undefined float operations, e.g. for 1/0, and should not be abused for other purposes. Use missing or nothing instead.

In your example, the DataFrame column you want to assign NaN to has String type, but NaN is a Float64. You should put the parsed data into a new DataFrame, converting the element type of the array to Float64 (or Any, in this case remove float. in the solution below).

using DataFrames
df = DataFrame(rand(["..", 5.5, -3.9], 5, 5))
df2 = DataFrame([float.(replace(c, (".." => NaN))) for c in eachcol(df)])

For missing:

df2 = DataFrame([float.(replace(c, (".." => missing))) for c in eachcol(df)])
2
votes

An alternative is to use broadcasting like this:

julia> using DataFrames

julia> df = DataFrame(rand(["..", 5.5, -3.9], 5, 5), :auto)
5×5 DataFrame
 Row │ x1    x2    x3    x4    x5  
     │ Any   Any   Any   Any   Any 
─────┼─────────────────────────────
   1 │ 5.5   5.5   -3.9  -3.9  ..
   2 │ -3.9  -3.9  ..    ..    5.5
   3 │ ..    -3.9  ..    5.5   5.5
   4 │ ..    ..    ..    5.5   5.5
   5 │ 5.5   -3.9  -3.9  ..    5.5

julia> ifelse.(df .== "..", missing, df)
5×5 DataFrame
 Row │ x1         x2         x3         x4         x5        
     │ Float64?   Float64?   Float64?   Float64?   Float64?  
─────┼───────────────────────────────────────────────────────
   1 │       5.5        5.5       -3.9       -3.9  missing   
   2 │      -3.9       -3.9  missing    missing          5.5
   3 │ missing         -3.9  missing          5.5        5.5
   4 │ missing    missing    missing          5.5        5.5
   5 │       5.5       -3.9       -3.9  missing          5.5

(note the :auto argument in constructor that generates column names automatically)

Here I give an example with missing because, as lungben noted NaN is not used in Julia to signal missingness (but you could have used it in this code equally well).

The benefit of using broadcasting is that you do not have to think about type promotion - it will happen automatically so you should not get errors.

0
votes

You are actually asking how to process a file with a specific missing symbol.

Suppose this is your file:

dataset="c1#c2
a#12.5
b#..
c#3.3"

You can read this Julia as:

julia> d = CSV.File(IOBuffer(dataset), delim="#", missingstring="..") |> DataFrame
3×2 DataFrame
 Row │ c1      c2
     │ String  Float64?
─────┼───────────────────
   1 │ a            12.5
   2 │ b       missing
   3 │ c             3.3

Note that the type of the c2 column is:

julia> eltype(d.c2)
Union{Missing, Float64}