Compare dataframes regardless of column order

General Usage
1

Hi!
I’m probably doing something wrong but I cannot seem to find an easy solution to this. Imagine I have a dataframe (e.g. the Palmer penguins data) and I convert it from wide to tidy format:

using PalmerPenguins, DataFrames

df = DataFrame(PalmerPenguins.load())

#Create an ID column
df.id = 1:size(df,1)
df_long = stack(df, Not([:species, :id])

Now if I want to go back to the original table, I should do something like:

df_wide = unstack(df_long, :variable, :value)

This is all good, except that while I know that df contains the same data as df_wide, both isequal(df, df_wide) and isequal.(df, df_wide) fail (either by returning false or saying that column names cannot be broadcasted since they are not in the same order). Is there a way to compare two dataframes regardless of column order (instead of me writing a for loop to compare each column separately)? Thanks!

2

Maybe use a dict to handle the order invariance?

df1 = DataFrame(a = [1, 2], b = [3, 4])
df2 = DataFrame(b = [3, 4], a = [1, 2])

Dict(names(df1) .=> eachcol(df1)) == Dict(names(df2) .=> eachcol(df2))
# true
5 Likes
3

This does work and does what I want it to do. I still think that in general an equality relationship between dataframes should not depend on order (unlike e.g. matrices), so maybe I could ask whether there is something that can be done from the package directly to be more user-friendly. Thank you!

1 Like
4

I guess it would be nice to have functions like issubset and issubsetequal for a DataFrame, but I couldn’t find such functions in DataFrames.jl yet.

One of us would have to open a PR in DataFrames.jl to get this feature in. But I bet there are all kinds of edge cases to consider. Like do you really want ‘set’ equality for your case, or you also want to check for the same number of duplicate rows?

5 
Set(eachcol(df)) == Set(eachcol(df_wide))
1 Like
6

And when it is important to differentiate the column names as well:

Set(zip(names(df1), eachcol(df1))) == Set(zip(names(df2), eachcol(df2)))
7

Another method:

julia> isequal(map(t->stack(t, Not(:id), :id; view=true), (df, df_wide))...)
true

isequal plays nice with missing and compares DataFrames.
The t->... map “iterates” on all the cells of the DataFrame.

But this isn’t probably robust to row order changes.

8

In general, it is certainly necessary to check the names too.
But, in this case, the names are taken from the same set in a different order.
I wonder if there is a function in the Tables package that directly gives the dictionary of rows or columns that is suitable for “our” case.

9

Thank you for all the inputs!
I’ll attach my first solution as well, which was just me looping through the columns:

for col in names(df)
    isequal(df[!,col], df_wide[!,col])
end

I’m guessing this also doesn’t consider some edge case that I’m not thinking about now. I guess the question is if it is sensitive to send in a PR to DataFrames.jl proposing to “promote” one of these as the isequal() method for DataFrames? Not sure if it is such a common use cases since I also came across it kind of recently, but might be worthwhile…