Specific case in Julia Dataframe

user_6571261 0
Viewed 61

I need to do something quite specific and i'm trying to do it the good way , especially i want it to be optimized .

So i have a DataFrame that look like this :

│ Row      │ USER_ID │ GENRE_MAIN        │ ALBUM_NAME                                                                  │ ALBUM_ARTIST_NAME              │ TOTAL_LISTENED │ TOTAL_COUNT_LISTENED │
│          │ String  │ String            │ String                                                                      │ String                         │ DecFP.Dec128   │ DecFP.Dec128         │
├──────────┼─────────┼───────────────────┼─────────────────────────────────────────────────────────────────────────────┼────────────────────────────────┼────────────────┼──────────────────────┤
│ 1        │ 9s2dsdd6  │ ROCK              │ The Thought's Boy                                                       │ AARON MADISON                  │ 5912.0         │ 91.0                 │
│ 1        │ 9s2dsdd6  │ ROCK              │ The wqeqwewe                                                          │ AARON MADISON                  │ 3212.0         │ 91.0                 │
│ 2        │ 11sdasd63 │ ROCK              │ Down On The Upside                                                          │ SOUNDGARDEN                    │ 3354.0         │ 14.0                 │
│ 3        │ 112sds334 │ CLASSICAL         │ Beethoven: Symphonies Nos. 1 & 2 - C.P.E. Bach: Symphonies, Wq 175 & 183/17 │ AKADEMIE FÜR ALTE MUSIK BERLIN │ 1372.0         │ 4.0                  │
│ 4        │ 145sdsd42 │ POP               │ My Life in the Bush of Ghosts                                               │ BRIAN ENO                      │ 3531.0         │ 17.0                 │

I want to aggregate it by user (i have many rows per user_id ) and do many calculations I'm doing aggregation with this :

gdf = DataFrames.groupby(df, :USER_ID)
combine(gdf,:TOTAL_LISTENED => sum => :TOTAL_SECONDS_LISTENED,
            :TOTAL_COUNT_LISTENED => sum => :TOTAL_TRACKS_LISTENED)

I need to calculate the top 1 ,2 ,3 ,4 ,5 genre, album name, artist name per user_id and it has to be like this :

USER_ID │ ALBUM1_NAME      │ ALBUM2_NAME  ......│ GENRE1       │ GENRE2

One line per user_id .

So i tried to do it with a countmap and then sort it , keep only the top 5 , and assign each value to a column in a Dataframe

transposed = sort(countmap(targetId[targetCol]), byvalue=true, rev=true) 

for (i, g) in enumerate(eachcol(transposed))
        rVal["ALBUM$(i)_NAME"] = g[1]
        rVal["ALBUM$(i)_ARTIST"] = g[3]
        rVal["ALBUM$(i)_TIME"] = g[2]
        rVal["ALBUM$(i)_ID"] = "ID"
        rVal["USER_ID"] = id
    end

but it doesn't work in a combine , its just very ugly and im sure i can do it a better way .

I hope its understandable , if someone can help me please =)

Thank you

EDIT : A way to reproduce the DataFrame:

v = ["x","y","z"][rand(1:3, 10)]
df = DataFrame(Any[collect(1:10), v, rand(10)], [:USER_ID, :GENRE_MAIN, :TOTAL_LISTENED])
julia dataframe
1 Answers

You have not provided an easy way to reproduce your source data, so I am writing the solution from my head and hope I have not made any typo (note that you need DataFrames.jl 0.22 for this to work, while you seem to be on some older version of the package):

using DataFrames, Pipe, Random, Pkg

Pkg.activate(".")
Pkg.add("DataFrames")
Pkg.add("Pipe")

Random.seed!(1234)

df = DataFrame(USER_ID=rand(1:10, 80),
               GENRE_MAIN=rand(string.("genre_", 1:6), 80),
               ALBUM_NAME=rand(string.("album_", 1:6), 80),
               ALBUM_ARTIST_NAME=rand(string.("artist_", 1:6), 80))

function top5(sdf, col, prefix)
    return @pipe groupby(sdf, col) |>
                 combine(_, nrow) |>
                 sort!(_, :nrow, rev=true) |>
                 first(_, 5) |>
                 vcat(_[!, 1], fill(missing, 5 - nrow(_))) |>
                 DataFrame([string(prefix, i) for i in 1:5] .=> _)
end

@pipe groupby(df, :USER_ID) |>
      combine(_,
              x -> top5(x, :GENRE_MAIN, "genre"),
              x -> top5(x, :ALBUM_NAME, "album"), 
              x -> top5(x, :ALBUM_ARTIST_NAME, "artist"))

The code is a bit complex as we have to handle the fact that there might be less than 5 entries per group.

It produces under Julia 1.5.3:

10×16 DataFrame
 Row │ USER_ID  genre1   genre2   genre3   genre4   genre5   album1   album2   album3   album4   album5   artist1   artist2   artist3   artist4   artist5
     │ Int64    String   String   String   String?  String?  String   String   String   String   String?  String    String    String    String?   String?
─────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │       1  genre_1  genre_3  genre_5  genre_2  genre_4  album_3  album_5  album_6  album_1  album_4  artist_1  artist_4  artist_3  artist_2  artist_6
   2 │       4  genre_1  genre_3  genre_6  genre_2  missing  album_2  album_4  album_5  album_6  missing  artist_4  artist_5  artist_2  missing   missing
   3 │       8  genre_2  genre_1  genre_6  genre_5  missing  album_1  album_5  album_4  album_2  missing  artist_5  artist_6  artist_4  artist_1  artist_3
   4 │       2  genre_1  genre_5  genre_2  genre_4  genre_3  album_6  album_3  album_4  album_2  album_1  artist_4  artist_2  artist_6  artist_1  artist_5
   5 │      10  genre_5  genre_3  genre_6  genre_4  genre_2  album_2  album_3  album_1  album_5  album_4  artist_1  artist_6  artist_2  artist_5  artist_3
   6 │       7  genre_5  genre_3  genre_2  genre_4  genre_1  album_2  album_4  album_3  album_5  missing  artist_4  artist_1  artist_3  artist_5  missing
   7 │       9  genre_3  genre_4  genre_2  missing  missing  album_1  album_3  album_4  album_2  missing  artist_4  artist_2  artist_6  artist_3  missing
   8 │       5  genre_2  genre_3  genre_4  genre_6  missing  album_2  album_1  album_3  album_4  missing  artist_6  artist_5  artist_4  artist_1  missing
   9 │       3  genre_6  genre_5  genre_4  genre_2  genre_1  album_3  album_4  album_1  album_5  missing  artist_4  artist_3  artist_6  artist_5  missing
  10 │       6  genre_3  genre_4  genre_1  genre_6  missing  album_2  album_4  album_5  album_3  missing  artist_4  artist_6  artist_5  missing   missing

which I assume you wanted?

user_12695670
Related