I have a DataFrame of x and y occurrences. I would like to count how often each occurrence happens in the DataFrame and what percentage of the :y occurrences that combination represents. I have the first part down now, thanks to a previous question.
using DataFrames
mydf = DataFrame(y = rand('a':'h', 1000), x = rand('i':'p', 1000))
mydfsum = by(mydf, [:x, :y], df -> DataFrame(n = length(df[:x])))
This successfully creates a column that counts how often each value of :x occurs with each value of :y. Now I need to be able to generate a new column that counts how often each value of :y occurs. I could next create a new DataFrame using:
mydfsumy = by(mydf, [:y], df -> DataFrame(ny = length(df[:x])))
Join the DataFrames together.
mydfsum = join(mydfsum, mydfsumy, on = :y)
And create the percentage :yp column
mydfsum[:yp] = mydfsum[:n] ./ mydfsum[:ny]
But this seems like a clunky workaround for a common data management problem. In R I would do all of this in one line using dplyr:
mydf %>% groupby(x,y) %>% summarize(n = n()) %>% groupby(y) %>% mutate(yp = n/sum(n))