{"action":"created","comment":{"url":"https://api.github.com/repos/pydata/pandas/pulls/comments/61424826","id":61424826,"diff_hunk":"@@ -372,10 +372,99 @@ In pandas, you can use :meth:`~pandas.concat` in conjunction with\n \n     pd.concat([df1, df2]).drop_duplicates()\n \n+Pandas equivalents for some SQL analytic and aggregate functions\n+----------------------------------------------------------------\n+Top N rows with offset\n+\n+.. code-block:: sql\n+\n+    -- MySQL\n+    SELECT * FROM tips\n+    ORDER BY tip DESC\n+    LIMIT 10 OFFSET 5;\n+\n+In pandas:\n+\n+.. ipython:: python\n+\n+    tips.nlargest(10+5, columns='tip').tail(10)\n+\n+Top N rows per group\n+\n+.. code-block:: sql\n+\n+    -- Oracle's ROW_NUMBER() analytic function\n+    SELECT * FROM (\n+      SELECT\n+        t.*,\n+        ROW_NUMBER() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rn\n+      FROM tips t\n+    )\n+    WHERE rn < 3\n+    ORDER BY day, rn;\n+\n+Let's add a helper column: `RN` (Row Number)\n+\n+.. ipython:: python\n+\n+    tips['rn'] = (tips.sort_values(['total_bill'], ascending=False)\n+                      .groupby(['day'])\n+                      .cumcount() + 1\n+                 )\n+    tips.loc[tips['rn'] < 3].sort_values(['day','rn'])\n+\n+the same using `rank(method='first')` function\n+\n+.. ipython:: python\n+\n+    tips['rnk'] = (tips.groupby(['day'])['total_bill']\n+                       .rank(method='first', ascending=False)\n+                  )\n+    tips.loc[tips['rnk'] < 3].sort_values(['day','rnk'])\n+\n+.. code-block:: sql\n+\n+    -- Oracle's RANK() analytic function\n+    SELECT * FROM (\n+      SELECT\n+        t.*,\n+        RANK() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rnk\n+      FROM tips t\n+    )\n+    WHERE rnk < 3\n+    ORDER BY day, rn;\n+\n+.. ipython:: python\n+\n+    tips['rnk_min'] = (tips.groupby(['day'])['total_bill']\n+                           .rank(method='min', ascending=False)\n+                      )","path":"doc/source/comparison_with_sql.rst","position":70,"original_position":70,"commit_id":"6a4522c676adc995100909715ca21ac502f2c0c6","original_commit_id":"6a4522c676adc995100909715ca21ac502f2c0c6","user":{"login":"jreback","id":953992,"avatar_url":"https://avatars.githubusercontent.com/u/953992?v=3","gravatar_id":"","url":"https://api.github.com/users/jreback","html_url":"https://github.com/jreback","followers_url":"https://api.github.com/users/jreback/followers","following_url":"https://api.github.com/users/jreback/following{/other_user}","gists_url":"https://api.github.com/users/jreback/gists{/gist_id}","starred_url":"https://api.github.com/users/jreback/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jreback/subscriptions","organizations_url":"https://api.github.com/users/jreback/orgs","repos_url":"https://api.github.com/users/jreback/repos","events_url":"https://api.github.com/users/jreback/events{/privacy}","received_events_url":"https://api.github.com/users/jreback/received_events","type":"User","site_admin":false},"body":"same here","created_at":"2016-04-28T13:19:01Z","updated_at":"2016-04-28T13:19:01Z","html_url":"https://github.com/pydata/pandas/pull/12932#discussion_r61424826","pull_request_url":"https://api.github.com/repos/pydata/pandas/pulls/12932","_links":{"self":{"href":"https://api.github.com/repos/pydata/pandas/pulls/comments/61424826"},"html":{"href":"https://github.com/pydata/pandas/pull/12932#discussion_r61424826"},"pull_request":{"href":"https://api.github.com/repos/pydata/pandas/pulls/12932"}}},"pull_request":{"url":"https://api.github.com/repos/pydata/pandas/pulls/12932","id":67183873,"html_url":"https://github.com/pydata/pandas/pull/12932","diff_url":"https://github.com/pydata/pandas/pull/12932.diff","patch_url":"https://github.com/pydata/pandas/pull/12932.patch","issue_url":"https://api.github.com/repos/pydata/pandas/issues/12932","number":12932,"state":"open","locked":false,"title":"DOC: More examples  comparison with sql","user":{"login":"maxu777","id":11424941,"avatar_url":"https://avatars.githubusercontent.com/u/11424941?v=3","gravatar_id":"","url":"https://api.github.com/users/maxu777","html_url":"https://github.com/maxu777","followers_url":"https://api.github.com/users/maxu777/followers","following_url":"https://api.github.com/users/maxu777/following{/other_user}","gists_url":"https://api.github.com/users/maxu777/gists{/gist_id}","starred_url":"https://api.github.com/users/maxu777/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maxu777/subscriptions","organizations_url":"https://api.github.com/users/maxu777/orgs","repos_url":"https://api.github.com/users/maxu777/repos","events_url":"https://api.github.com/users/maxu777/events{/privacy}","received_events_url":"https://api.github.com/users/maxu777/received_events","type":"User","site_admin":false},"body":" - [ ] closes #xxxx\r\n - [ ] tests added / passed\r\n - [x] passes ``git diff upstream/master | flake8 --diff``\r\n - [x] whatsnew entry\r\n\r\nadded the following examples:\r\n - Top N rows with offset\r\n - Top N rows per group\r\n - ROW_NUMBER() OVER(PARTITION BY day ORDER BY total_bill DESC)\r\n - UPDATE\r\n - DELETE\r\n","created_at":"2016-04-20T13:11:53Z","updated_at":"2016-04-28T13:19:01Z","closed_at":null,"merged_at":null,"merge_commit_sha":"9073439af1a420488b25d036ac65cb1f263307d8","assignee":null,"milestone":{"url":"https://api.github.com/repos/pydata/pandas/milestones/38","html_url":"https://github.com/pydata/pandas/milestones/0.18.1","labels_url":"https://api.github.com/repos/pydata/pandas/milestones/38/labels","id":1570594,"number":38,"title":"0.18.1","description":"after 0.18.0 of course!","creator":{"login":"jreback","id":953992,"avatar_url":"https://avatars.githubusercontent.com/u/953992?v=3","gravatar_id":"","url":"https://api.github.com/users/jreback","html_url":"https://github.com/jreback","followers_url":"https://api.github.com/users/jreback/followers","following_url":"https://api.github.com/users/jreback/following{/other_user}","gists_url":"https://api.github.com/users/jreback/gists{/gist_id}","starred_url":"https://api.github.com/users/jreback/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jreback/subscriptions","organizations_url":"https://api.github.com/users/jreback/orgs","repos_url":"https://api.github.com/users/jreback/repos","events_url":"https://api.github.com/users/jreback/events{/privacy}","received_events_url":"https://api.github.com/users/jreback/received_events","type":"User","site_admin":false},"open_issues":16,"closed_issues":265,"state":"open","created_at":"2016-02-08T15:29:59Z","updated_at":"2016-04-27T18:32:25Z","due_on":"2016-05-10T04:00:00Z","closed_at":null},"commits_url":"https://api.github.com/repos/pydata/pandas/pulls/12932/commits","review_comments_url":"https://api.github.com/repos/pydata/pandas/pulls/12932/comments","review_comment_url":"https://api.github.com/repos/pydata/pandas/pulls/comments{/number}","comments_url":"https://api.github.com/repos/pydata/pandas/issues/12932/comments","statuses_url":"https://api.github.com/repos/pydata/pandas/statuses/6a4522c676adc995100909715ca21ac502f2c0c6","head":{"label":"maxu777:more_examples__comparison_with_sql","ref":"more_examples__comparison_with_sql","sha":"6a4522c676adc995100909715ca21ac502f2c0c6","user":{"login":"maxu777","id":11424941,"avatar_url":"https://avatars.githubusercontent.com/u/11424941?v=3","gravatar_id":"","url":"https://api.github.com/users/maxu777","html_url":"https://github.com/maxu777","followers_url":"https://api.github.com/users/maxu777/followers","following_url":"https://api.github.com/users/maxu777/following{/other_user}","gists_url":"https://api.github.com/users/maxu777/gists{/gist_id}","starred_url":"https://api.github.com/users/maxu777/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maxu777/subscriptions","organizations_url":"https://api.github.com/users/maxu777/orgs","repos_url":"https://api.github.com/users/maxu777/repos","events_url":"https://api.github.com/users/maxu777/events{/privacy}","received_events_url":"https://api.github.com/users/maxu777/received_events","type":"User","site_admin":false},"repo":{"id":56618929,"name":"pandas","full_name":"maxu777/pandas","owner":{"login":"maxu777","id":11424941,"avatar_url":"https://avatars.githubusercontent.com/u/11424941?v=3","gravatar_id":"","url":"https://api.github.com/users/maxu777","html_url":"https://github.com/maxu777","followers_url":"https://api.github.com/users/maxu777/followers","following_url":"https://api.github.com/users/maxu777/following{/other_user}","gists_url":"https://api.github.com/users/maxu777/gists{/gist_id}","starred_url":"https://api.github.com/users/maxu777/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maxu777/subscriptions","organizations_url":"https://api.github.com/users/maxu777/orgs","repos_url":"https://api.github.com/users/maxu777/repos","events_url":"https://api.github.com/users/maxu777/events{/privacy}","received_events_url":"https://api.github.com/users/maxu777/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/maxu777/pandas","description":"Flexible and powerful data analysis / manipulation library for Python, providing labeled data structures similar to R data.frame objects, statistical functions, and much more","fork":true,"url":"https://api.github.com/repos/maxu777/pandas","forks_url":"https://api.github.com/repos/maxu777/pandas/forks","keys_url":"https://api.github.com/repos/maxu777/pandas/keys{/key_id}","collaborators_url":"https://api.github.com/repos/maxu777/pandas/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/maxu777/pandas/teams","hooks_url":"https://api.github.com/repos/maxu777/pandas/hooks","issue_events_url":"https://api.github.com/repos/maxu777/pandas/issues/events{/number}","events_url":"https://api.github.com/repos/maxu777/pandas/events","assignees_url":"https://api.github.com/repos/maxu777/pandas/assignees{/user}","branches_url":"https://api.github.com/repos/maxu777/pandas/branches{/branch}","tags_url":"https://api.github.com/repos/maxu777/pandas/tags","blobs_url":"https://api.github.com/repos/maxu777/pandas/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/maxu777/pandas/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/maxu777/pandas/git/refs{/sha}","trees_url":"https://api.github.com/repos/maxu777/pandas/git/trees{/sha}","statuses_url":"https://api.github.com/repos/maxu777/pandas/statuses/{sha}","languages_url":"https://api.github.com/repos/maxu777/pandas/languages","stargazers_url":"https://api.github.com/repos/maxu777/pandas/stargazers","contributors_url":"https://api.github.com/repos/maxu777/pandas/contributors","subscribers_url":"https://api.github.com/repos/maxu777/pandas/subscribers","subscription_url":"https://api.github.com/repos/maxu777/pandas/subscription","commits_url":"https://api.github.com/repos/maxu777/pandas/commits{/sha}","git_commits_url":"https://api.github.com/repos/maxu777/pandas/git/commits{/sha}","comments_url":"https://api.github.com/repos/maxu777/pandas/comments{/number}","issue_comment_url":"https://api.github.com/repos/maxu777/pandas/issues/comments{/number}","contents_url":"https://api.github.com/repos/maxu777/pandas/contents/{+path}","compare_url":"https://api.github.com/repos/maxu777/pandas/compare/{base}...{head}","merges_url":"https://api.github.com/repos/maxu777/pandas/merges","archive_url":"https://api.github.com/repos/maxu777/pandas/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/maxu777/pandas/downloads","issues_url":"https://api.github.com/repos/maxu777/pandas/issues{/number}","pulls_url":"https://api.github.com/repos/maxu777/pandas/pulls{/number}","milestones_url":"https://api.github.com/repos/maxu777/pandas/milestones{/number}","notifications_url":"https://api.github.com/repos/maxu777/pandas/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/maxu777/pandas/labels{/name}","releases_url":"https://api.github.com/repos/maxu777/pandas/releases{/id}","deployments_url":"https://api.github.com/repos/maxu777/pandas/deployments","created_at":"2016-04-19T17:51:47Z","updated_at":"2016-04-19T17:51:52Z","pushed_at":"2016-04-27T21:07:08Z","git_url":"git://github.com/maxu777/pandas.git","ssh_url":"git@github.com:maxu777/pandas.git","clone_url":"https://github.com/maxu777/pandas.git","svn_url":"https://github.com/maxu777/pandas","homepage":"http://pandas.pydata.org","size":82696,"stargazers_count":0,"watchers_count":0,"language":"Python","has_issues":false,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"pydata:master","ref":"master","sha":"de3295329c824940660760386d01a3117274cbbb","user":{"login":"pydata","id":1284191,"avatar_url":"https://avatars.githubusercontent.com/u/1284191?v=3","gravatar_id":"","url":"https://api.github.com/users/pydata","html_url":"https://github.com/pydata","followers_url":"https://api.github.com/users/pydata/followers","following_url":"https://api.github.com/users/pydata/following{/other_user}","gists_url":"https://api.github.com/users/pydata/gists{/gist_id}","starred_url":"https://api.github.com/users/pydata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pydata/subscriptions","organizations_url":"https://api.github.com/users/pydata/orgs","repos_url":"https://api.github.com/users/pydata/repos","events_url":"https://api.github.com/users/pydata/events{/privacy}","received_events_url":"https://api.github.com/users/pydata/received_events","type":"Organization","site_admin":false},"repo":{"id":858127,"name":"pandas","full_name":"pydata/pandas","owner":{"login":"pydata","id":1284191,"avatar_url":"https://avatars.githubusercontent.com/u/1284191?v=3","gravatar_id":"","url":"https://api.github.com/users/pydata","html_url":"https://github.com/pydata","followers_url":"https://api.github.com/users/pydata/followers","following_url":"https://api.github.com/users/pydata/following{/other_user}","gists_url":"https://api.github.com/users/pydata/gists{/gist_id}","starred_url":"https://api.github.com/users/pydata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pydata/subscriptions","organizations_url":"https://api.github.com/users/pydata/orgs","repos_url":"https://api.github.com/users/pydata/repos","events_url":"https://api.github.com/users/pydata/events{/privacy}","received_events_url":"https://api.github.com/users/pydata/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/pydata/pandas","description":"Flexible and powerful data analysis / manipulation library for Python, providing labeled data structures similar to R data.frame objects, statistical functions, and much more","fork":false,"url":"https://api.github.com/repos/pydata/pandas","forks_url":"https://api.github.com/repos/pydata/pandas/forks","keys_url":"https://api.github.com/repos/pydata/pandas/keys{/key_id}","collaborators_url":"https://api.github.com/repos/pydata/pandas/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/pydata/pandas/teams","hooks_url":"https://api.github.com/repos/pydata/pandas/hooks","issue_events_url":"https://api.github.com/repos/pydata/pandas/issues/events{/number}","events_url":"https://api.github.com/repos/pydata/pandas/events","assignees_url":"https://api.github.com/repos/pydata/pandas/assignees{/user}","branches_url":"https://api.github.com/repos/pydata/pandas/branches{/branch}","tags_url":"https://api.github.com/repos/pydata/pandas/tags","blobs_url":"https://api.github.com/repos/pydata/pandas/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/pydata/pandas/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/pydata/pandas/git/refs{/sha}","trees_url":"https://api.github.com/repos/pydata/pandas/git/trees{/sha}","statuses_url":"https://api.github.com/repos/pydata/pandas/statuses/{sha}","languages_url":"https://api.github.com/repos/pydata/pandas/languages","stargazers_url":"https://api.github.com/repos/pydata/pandas/stargazers","contributors_url":"https://api.github.com/repos/pydata/pandas/contributors","subscribers_url":"https://api.github.com/repos/pydata/pandas/subscribers","subscription_url":"https://api.github.com/repos/pydata/pandas/subscription","commits_url":"https://api.github.com/repos/pydata/pandas/commits{/sha}","git_commits_url":"https://api.github.com/repos/pydata/pandas/git/commits{/sha}","comments_url":"https://api.github.com/repos/pydata/pandas/comments{/number}","issue_comment_url":"https://api.github.com/repos/pydata/pandas/issues/comments{/number}","contents_url":"https://api.github.com/repos/pydata/pandas/contents/{+path}","compare_url":"https://api.github.com/repos/pydata/pandas/compare/{base}...{head}","merges_url":"https://api.github.com/repos/pydata/pandas/merges","archive_url":"https://api.github.com/repos/pydata/pandas/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/pydata/pandas/downloads","issues_url":"https://api.github.com/repos/pydata/pandas/issues{/number}","pulls_url":"https://api.github.com/repos/pydata/pandas/pulls{/number}","milestones_url":"https://api.github.com/repos/pydata/pandas/milestones{/number}","notifications_url":"https://api.github.com/repos/pydata/pandas/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/pydata/pandas/labels{/name}","releases_url":"https://api.github.com/repos/pydata/pandas/releases{/id}","deployments_url":"https://api.github.com/repos/pydata/pandas/deployments","created_at":"2010-08-24T01:37:33Z","updated_at":"2016-04-28T11:57:36Z","pushed_at":"2016-04-28T12:38:55Z","git_url":"git://github.com/pydata/pandas.git","ssh_url":"git@github.com:pydata/pandas.git","clone_url":"https://github.com/pydata/pandas.git","svn_url":"https://github.com/pydata/pandas","homepage":"http://pandas.pydata.org","size":151827,"stargazers_count":6146,"watchers_count":6146,"language":"Python","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":true,"forks_count":2519,"mirror_url":null,"open_issues_count":1702,"forks":2519,"open_issues":1702,"watchers":6146,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/pydata/pandas/pulls/12932"},"html":{"href":"https://github.com/pydata/pandas/pull/12932"},"issue":{"href":"https://api.github.com/repos/pydata/pandas/issues/12932"},"comments":{"href":"https://api.github.com/repos/pydata/pandas/issues/12932/comments"},"review_comments":{"href":"https://api.github.com/repos/pydata/pandas/pulls/12932/comments"},"review_comment":{"href":"https://api.github.com/repos/pydata/pandas/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/pydata/pandas/pulls/12932/commits"},"statuses":{"href":"https://api.github.com/repos/pydata/pandas/statuses/6a4522c676adc995100909715ca21ac502f2c0c6"}}}}