{"action":"closed","issue":{"url":"https://api.github.com/repos/davidanthoff/Query.jl/issues/104","repository_url":"https://api.github.com/repos/davidanthoff/Query.jl","labels_url":"https://api.github.com/repos/davidanthoff/Query.jl/issues/104/labels{/name}","comments_url":"https://api.github.com/repos/davidanthoff/Query.jl/issues/104/comments","events_url":"https://api.github.com/repos/davidanthoff/Query.jl/issues/104/events","html_url":"https://github.com/davidanthoff/Query.jl/issues/104","id":219446868,"number":104,"title":"Possible to join and then do a groupby?","user":{"login":"tlnagy","id":1661487,"avatar_url":"https://avatars1.githubusercontent.com/u/1661487?v=3","gravatar_id":"","url":"https://api.github.com/users/tlnagy","html_url":"https://github.com/tlnagy","followers_url":"https://api.github.com/users/tlnagy/followers","following_url":"https://api.github.com/users/tlnagy/following{/other_user}","gists_url":"https://api.github.com/users/tlnagy/gists{/gist_id}","starred_url":"https://api.github.com/users/tlnagy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tlnagy/subscriptions","organizations_url":"https://api.github.com/users/tlnagy/orgs","repos_url":"https://api.github.com/users/tlnagy/repos","events_url":"https://api.github.com/users/tlnagy/events{/privacy}","received_events_url":"https://api.github.com/users/tlnagy/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2017-04-05T02:28:01Z","updated_at":"2017-04-05T20:42:56Z","closed_at":"2017-04-05T20:42:56Z","body":"Say I have two dataframes that look like:\r\n\r\n```julia\r\ndf1 = DataFrame(id=[1,2,3], rel_freq=[0.5, 1, 1.5], class=[\"a\", \"a\", \"c\"])\r\ndf2 = DataFrame(id=[1,2,3,4], rel_freq=[0.75, 0.75, 1.75, 1], class=[\"a\", \"a\", \"c\", \"c\"])\r\nprintln(df1)\r\nprintln(df2)\r\n\r\n#output\r\n\r\n3×3 DataFrames.DataFrame\r\n│ Row │ id │ rel_freq │ class │\r\n├─────┼────┼──────────┼───────┤\r\n│ 1   │ 1  │ 0.5      │ \"a\"   │\r\n│ 2   │ 2  │ 1.0      │ \"a\"   │\r\n│ 3   │ 3  │ 1.5      │ \"c\"   │\r\n4×3 DataFrames.DataFrame\r\n│ Row │ id │ rel_freq │ class │\r\n├─────┼────┼──────────┼───────┤\r\n│ 1   │ 1  │ 0.75     │ \"a\"   │\r\n│ 2   │ 2  │ 0.75     │ \"a\"   │\r\n│ 3   │ 3  │ 1.75     │ \"c\"   │\r\n│ 4   │ 4  │ 1.0      │ \"c\"   │\r\n```\r\n\r\nIs it possible to join them based on `id` and group by `class` and then compute the mean difference between the `rel_freq` columns in one query? \r\n\r\nHere's my attempt to do it in two queries:\r\n\r\n```julia\r\nx = @from i in df1 begin\r\n    @join j in df2 on i.id equals j.id \r\n    @select {i.id, rel_freq1=i.rel_freq, rel_freq2=j.rel_freq, i.class}\r\n    @collect DataFrame\r\nend\r\n\r\ny = @from i in x begin\r\n    @group i by i.class into g\r\n    @select {g.key, avg_freq=mean(g.rel_freq2-g.rel_freq1)}\r\n    @collect DataFrame\r\nend\r\n```\r\nI couldn't figure out what exactly what values `g` has so it's not complete. The desired dataframe is\r\n\r\n```\r\n2×2 DataFrames.DataFrame\r\n│ Row │ class │ avg_freq_diff │\r\n├─────┼───────┼───────────────┤\r\n│ 1   │ \"a\"   │ 0.0           │\r\n│ 2   │ \"c\"   │ 0.25          │\r\n```\r\n\r\n`a` = `((0.75-0.5)+(0.75-1))/2`\r\n`c` = `(1.75-1.5)/1`"}}
{"actor":{"display_login":"tlnagy"}}