Quartils calculation in Julia DataFrame

user_12213858 0
Viewed 211

I have this dataframe

Seller     Product     Price
  1           A         10.00
  2           A         5.0
  3           A         7.50
  4           A         3.75

What I want as Output:

Seller     Product     Price    Quartil_1  Quartil_2  Quartil_3   Quartil_4  Rank_Price
  1           A         10.00      2.5       5.0        7.5         10         4
  2           A         5.0        2.5       5.0        7.5         10         2
  3           A         7.50       2.5       5.0        7.5         10         3
  4           A         3.35       2.5       5.0        7.5         10         1

So, my dataframe will have several sellers selling the same price for different prices. I need to calculate de 4 quartils of prices, considering all sellers for he same product and then classify each seller's price in this quartils, like the example above.

I know how to do it in python, using groupby, quartiles and rank but I am pretty new to Julia.

If someone can give a hand, please, i appreciate.

julia dataframe
1 Answers

Your input and output tables do not match. Also note that there are normally three quartiles defined Q1, Q2, and Q3 (see e.g. https://en.wikipedia.org/wiki/Quartile).

This is what I assume you want:

julia> using DataFrames, CategoricalArrays, DataFramesMeta

julia> df = DataFrame(seller=repeat(1:10, outer=2), product=repeat(["A", "B"], inner=10), price=1.5:1.0:20.5)
20×3 DataFrame
 Row │ seller  product  price
     │ Int64   String   Float64
─────┼──────────────────────────
   1 │      1  A            1.5
   2 │      2  A            2.5
   3 │      3  A            3.5
   4 │      4  A            4.5
   5 │      5  A            5.5
   6 │      6  A            6.5
   7 │      7  A            7.5
   8 │      8  A            8.5
   9 │      9  A            9.5
  10 │     10  A           10.5
  11 │      1  B           11.5
  12 │      2  B           12.5
  13 │      3  B           13.5
  14 │      4  B           14.5
  15 │      5  B           15.5
  16 │      6  B           16.5
  17 │      7  B           17.5
  18 │      8  B           18.5
  19 │      9  B           19.5
  20 │     10  B           20.5

julia> @transform groupby(df, :product) begin
       :quantile_1 = quantile(:price, 0.25)
       :quantile_2 = quantile(:price, 0.5)
       :quantile_3 = quantile(:price, 0.75)
       :rank_price = levelcode.(cut(:price, 4))
       end
20×7 DataFrame
 Row │ seller  product  price    quantile_1  quantile_2  quantile_3  rank_price
     │ Int64   String   Float64  Float64     Float64     Float64     Int64
─────┼──────────────────────────────────────────────────────────────────────────
   1 │      1  A            1.5        3.75         6.0        8.25           1
   2 │      2  A            2.5        3.75         6.0        8.25           1
   3 │      3  A            3.5        3.75         6.0        8.25           1
   4 │      4  A            4.5        3.75         6.0        8.25           2
   5 │      5  A            5.5        3.75         6.0        8.25           2
   6 │      6  A            6.5        3.75         6.0        8.25           3
   7 │      7  A            7.5        3.75         6.0        8.25           3
   8 │      8  A            8.5        3.75         6.0        8.25           4
   9 │      9  A            9.5        3.75         6.0        8.25           4
  10 │     10  A           10.5        3.75         6.0        8.25           4
  11 │      1  B           11.5       13.75        16.0       18.25           1
  12 │      2  B           12.5       13.75        16.0       18.25           1
  13 │      3  B           13.5       13.75        16.0       18.25           1
  14 │      4  B           14.5       13.75        16.0       18.25           2
  15 │      5  B           15.5       13.75        16.0       18.25           2
  16 │      6  B           16.5       13.75        16.0       18.25           3
  17 │      7  B           17.5       13.75        16.0       18.25           3
  18 │      8  B           18.5       13.75        16.0       18.25           4
  19 │      9  B           19.5       13.75        16.0       18.25           4
  20 │     10  B           20.5       13.75        16.0       18.25           4

or (maybe a bit more direct method but requiring an extra package)

julia> using StatsBase

julia> res = @transform groupby(df, :product) begin
       :quantile_1 = quantile(:price, 0.25)
       :quantile_2 = quantile(:price, 0.5)
       :quantile_3 = quantile(:price, 0.75)
       :rank_price = denserank(cut(:price, 4))
       end
20×7 DataFrame
 Row │ seller  product  price    quantile_1  quantile_2  quantile_3  rank_price
     │ Int64   String   Float64  Float64     Float64     Float64     Int64
─────┼──────────────────────────────────────────────────────────────────────────
   1 │      1  A            1.5        3.75         6.0        8.25           1
   2 │      2  A            2.5        3.75         6.0        8.25           1
   3 │      3  A            3.5        3.75         6.0        8.25           1
   4 │      4  A            4.5        3.75         6.0        8.25           2
   5 │      5  A            5.5        3.75         6.0        8.25           2
   6 │      6  A            6.5        3.75         6.0        8.25           3
   7 │      7  A            7.5        3.75         6.0        8.25           3
   8 │      8  A            8.5        3.75         6.0        8.25           4
   9 │      9  A            9.5        3.75         6.0        8.25           4
  10 │     10  A           10.5        3.75         6.0        8.25           4
  11 │      1  B           11.5       13.75        16.0       18.25           1
  12 │      2  B           12.5       13.75        16.0       18.25           1
  13 │      3  B           13.5       13.75        16.0       18.25           1
  14 │      4  B           14.5       13.75        16.0       18.25           2
  15 │      5  B           15.5       13.75        16.0       18.25           2
  16 │      6  B           16.5       13.75        16.0       18.25           3
  17 │      7  B           17.5       13.75        16.0       18.25           3
  18 │      8  B           18.5       13.75        16.0       18.25           4
  19 │      9  B           19.5       13.75        16.0       18.25           4
  20 │     10  B           20.5       13.75        16.0       18.25           4

I am using DataFramesMeta.jl as then the code is probably a bit easier to read than vanilla DataFrames.jl code.

user_12695670
Related