Easiest quickest way to search DataFrame w/wildcards

Specific Domains Data
1

Is there an easy straightforward already existing way to search a DataFrame with a String wildcard in a specific column, and then print the found entries/rows?

For example, you want to search if a certain name is in the data, but don’t remember it exactly. You wish to search with an approximate wildcard, such as *batman* (where the stars in this denote the wildcards).

If I can avoid regex, by the way, that’s good.

2

[c for c in names(dd) if occursin("batman", string(c))]? For any more complex patterns I don’t see how you get by without regex.

You also might have noticed that DataFrames has a convenience function that improves error messages for typos of column names - if the column you’re requesting is close to the name of an existing column it will suggest that similarly named column. So if it’s really just about not exactly remembering a name you can just try an approximate name and let the error message help you.

3

Something like this?

julia> df = DataFrame(:a => ["foo", "bar", "baz"])
3×1 DataFrame
│ Row │ a      │
│     │ String │
├─────┼────────┤
│ 1   │ foo    │
│ 2   │ bar    │
│ 3   │ baz    │

julia> df[findall(x -> occursin("ba", x), df[!, :a]), :]
2×1 DataFrame
│ Row │ a      │
│     │ String │
├─────┼────────┤
│ 1   │ bar    │
│ 2   │ baz    │
1 Like
4

Ah sorry I read the question as asking about the name of a column rather than the data within a column. That said, why don’t you broadcast occursin like

julia> df[occursin.("ba", df.a), :]
2×1 DataFrame
│ Row │ a      │
│     │ String │
├─────┼────────┤
│ 1   │ bar    │
│ 2   │ baz    │
3 Likes
5

Thanks, both. Very useful.

The use of occursin came to mind, but now you’ve shown me how to use it in this particular context.

Standing now in this case on your shoulders, I’m wondering if I can make a general quick function with this information. If I have time, I hope to do so.

6

It can be generalized a little

mysearch(smt, somewhere) = false
mysearch(smt::AbstractString, somewhere::AbstractString) = occursin(smt, somewhere)

findsomethingsomewhere(smt, df) =
    df[reduce((x, y) -> x .| mysearch.(smt, df[!, y]), names(df);
        init = BitArray([false for _ in 1:nrow(df)])), :]

julia> df = DataFrame(:a => ["foo", "bar", "baz", "baa"], :b => 1:4, :c => ["a", "b", "c", "ar"])
4×3 DataFrame
│ Row │ a      │ b     │ c      │
│     │ String │ Int64 │ String │
├─────┼────────┼───────┼────────┤
│ 1   │ foo    │ 1     │ a      │
│ 2   │ bar    │ 2     │ b      │
│ 3   │ baz    │ 3     │ c      │
│ 4   │ baa    │ 4     │ ar     │

julia> julia> findsomethingsomewhere("ar", df)
2×3 DataFrame
│ Row │ a      │ b     │ c      │
│     │ String │ Int64 │ String │
├─────┼────────┼───────┼────────┤
│ 1   │ bar    │ 2     │ b      │
│ 2   │ baa    │ 4     │ ar     │
2 Likes
7

Here is a somewhat more readable function

function findsomethingsomewhere(smt, df)
    positives = fill(false, nrow(df))
    for col in eachcol(df)
        if eltype(col) <: AbstractString
            positives .= positives .| occursin.(smt, col)
        end 
    end
    df[positives, :]
end
3 Likes
8

Great. Thank you both. That will be useful.