Julia - Function to search for rows which have all columns missing and delete those rows

user_6599898 0
Viewed 503

I am trying to find a similar functionality like in Python where in I can delete the rows which have ‘all’ null values in a row -

code in python - Using Pandas dataframe ‘closed_prices’

closed_prices.dropna(axis=0, how=‘all’, inplace=True)

Basically I want to drop rows which have missing values for all columns - I am using a stock data and want to remove the weekends and holidays, each column represent closing price of a particular stock. So if all the column values are null/missing for a particular row I want to delete that row.

I am using the below code -

using DataFrames
using DataFramesMeta
using CSV
using Dates
using Query


fh_5 = CSV.read("D:\\Julia_Dataframe\\JuliaCon2020-DataFrames-Tutorial\\fh_5yrs.csv", DataFrame)

min_date = minimum(fh_5[:, "date"])
max_date = maximum(fh_5[:, "date"])
date_seq = string.(collect(Dates.Date(min_date) : Dates.Day(1) : Dates.Date(max_date)))
date_range = df = DataFrame(dates = date_seq)
date_range.dates = Date.(date_range.dates, "yyyy-mm-dd")

for s in unique(fh_5.symbol)
    df = fh_5[fh_5.symbol .== s, ["date","close"]]
    date_range = leftjoin(date_range, df, on =:"dates" => :"date")
    rename!(date_range, Dict(:close => s))
end
size(date_range, 1)
size(filter(x -> any(!ismissing, x), date_range), 1)
size(date_range, 1)
julia dataframe
1 Answers

I assume you are working with DataFrames.jl. Then if df is your data frame just write:

filter(x -> any(!ismissing, x), df)

or filter! if you want an in-place operation.

Example:

julia> using DataFrames, Random

julia> Random.seed!(1234);

julia> df = DataFrame(rand([1, missing], 10, 2), :auto)
10×2 DataFrame
 Row │ x1       x2
     │ Int64?   Int64?
─────┼──────────────────
   1 │       1        1
   2 │ missing        1
   3 │ missing  missing
   4 │ missing  missing
   5 │       1        1
   6 │       1  missing
   7 │ missing  missing
   8 │       1  missing
   9 │       1        1
  10 │ missing        1

julia> filter(x -> any(!ismissing, x), df)
7×2 DataFrame
 Row │ x1       x2
     │ Int64?   Int64?
─────┼──────────────────
   1 │       1        1
   2 │ missing        1
   3 │       1        1
   4 │       1  missing
   5 │       1  missing
   6 │       1        1
   7 │ missing        1
user_12695670
Related