How to reshape, group by and rename Julia dataframe?

user_12776809 0
Viewed 355

I have the following DataFrame :

  Police Product  PV1  PV2  PV3   PM1   PM2  PM3
0       1      AA   10    8   14   150   145  140
1       2      AB   25    4    7   700   650  620
2       3      AA   13   22    5   120    80   60
3       4      AA   12    6   12   250   170  120
4       5      AB   10   13    5   500   430  350
5       6      BC    7   21   12  1200  1000  900

PV1 is the item PV for year 1, PV2 for year 2, .... I would like to combine reshaping and group by operations + some renaming stuffs to obtain the DF below :

  Product Item  Year1  Year2  Year3
0      AA   PV     35     36     31
1      AA   PM    520    395    320
2      AB   PV     35     17     12
3      AB   PM   1200   1080    970
4      BC   PV      7     21     12
5      BC   PM   1200   1000    900

It makes a group by operation on product name and reshape the DF to pass the item as a column and put the sum of each in new columns years.

I found a way to do it in Python but I am now looking for a solution passing my code in Julia. No problem for the groupby operation, but I have more issues with the reshaping / renaming part.

If you have any idea, I would be very grateful.

Thanks for any help

Edit :

As you recommended, I have installed Julia 1.5 and updated the DataFrames pkg to 0.22 version. As a result, the code runs well. The only remaining issue is related to the non constant lenght of column names in my real DF, which makes the transform part of the code not completly suitable. I will search for a way to split char/num with regular expression.

Thanks a lot for your time and sorry for the mistakes on editing.

julia reshape dataframe
1 Answers

There are probably several ways how you can do it. Here is an example using in-built functions (also taking advantage of several advanced features at once, so if you have any questions regarding the code please comment and I can explain):

julia> using CSV, DataFrames, Chain

julia> str = """
       Police Product  PV1  PV2  PV3   PM1   PM2  PM3
            1      AA   10    8   14   150   145  140
            2      AB   25    4    7   700   650  620
            3      AA   13   22    5   120    80   60
            4      AA   12    6   12   250   170  120
            5      AB   10   13    5   500   430  350
            6      BC    7   21   12  1200  1000  900""";

julia> @chain str begin
           IOBuffer
           CSV.read(DataFrame, ignorerepeated=true, delim=" ")
           groupby(:Product)
           combine(names(df, r"\d") .=> sum, renamecols=false)
           stack(Not(:Product))
           transform!(:variable => ByRow(x -> (first(x, 2), last(x, 1))) => [:Item, :Year])
           unstack([:Product, :Item], :Year, :value, renamecols = x -> Symbol("Year", x))
           sort!(:Product)
       end
6×5 DataFrame
 Row │ Product  Item    Year1   Year2   Year3
     │ String   String  Int64?  Int64?  Int64?
─────┼─────────────────────────────────────────
   1 │ AA       PV          35      36      31
   2 │ AA       PM         520     395     320
   3 │ AB       PV          35      17      12
   4 │ AB       PM        1200    1080     970
   5 │ BC       PV           7      21      12
   6 │ BC       PM        1200    1000     900

I used Chain.jl just to show how it can be employed in practice (but of course it is not needed).

You can add @aside show(_) annotation after any stage of the processing to see the results of the processing steps.

Edit:

Is this the regex you need (split non-digit characters followed by digit characters)?

julia> match(r"([^\d]+)(\d+)", "fsdfds123").captures
2-element Array{Union{Nothing, SubString{String}},1}:
 "fsdfds"
 "123"

Then just write:

ByRow(x -> match(r"([^\d]+)(\d+)", x).captures)

as your transformation

user_12695670
Related