Pandas sorted ranking of column A sorted by column B












0















Currently I have the following python code



forumposts = pd.DataFrame({'UserId': [1,1,2,3,2,1,3], 'FirstPostDate': [2018,2018,2017,2019,2017,2018,2019], 'PostDate': [201801,201802,201701,201901,201801,201803,201902]})



data = forumposts.groupby(['UserId', 'PostDate','FirstPostDate']).size().reset_index()



rankedUserIdByFirstPostDate = data.groupby(['UserId', 'FirstPostDate']).size().reset_index().sort_values('FirstPostDate').reset_index(drop=True).reset_index()



data.loc[:,'Rank'] = data.merge(rankedUserIdByFirstPostDate , how='left', on='UserId')['index'].values


The code works as intended but its complicated is there a more pandas like way of doing this? The intent is the following:



Create a dense rank over the UserId column sorted by the FirstPostDate such that the user with the earliest posting gets rank 0 and the user with the second earliest first post gets rank 1 and so on.



Using forumposts.UserId.rank(method='dense') gives me a ranking but its sorted by the order of the UserId.






python pandas pandas-groupby







share|improve this question

























  • Can you add some sample data?

    – jezrael
    Nov 22 '18 at 8:06











  • @jezrael Done :) Output should rank user 2 first, user 1 second and user 3 third.

    – CodeMonkey
    Nov 22 '18 at 8:13


















0















Currently I have the following python code



forumposts = pd.DataFrame({'UserId': [1,1,2,3,2,1,3], 'FirstPostDate': [2018,2018,2017,2019,2017,2018,2019], 'PostDate': [201801,201802,201701,201901,201801,201803,201902]})



data = forumposts.groupby(['UserId', 'PostDate','FirstPostDate']).size().reset_index()



rankedUserIdByFirstPostDate = data.groupby(['UserId', 'FirstPostDate']).size().reset_index().sort_values('FirstPostDate').reset_index(drop=True).reset_index()



data.loc[:,'Rank'] = data.merge(rankedUserIdByFirstPostDate , how='left', on='UserId')['index'].values


The code works as intended but its complicated is there a more pandas like way of doing this? The intent is the following:



Create a dense rank over the UserId column sorted by the FirstPostDate such that the user with the earliest posting gets rank 0 and the user with the second earliest first post gets rank 1 and so on.



Using forumposts.UserId.rank(method='dense') gives me a ranking but its sorted by the order of the UserId.






python pandas pandas-groupby







share|improve this question

























  • Can you add some sample data?

    – jezrael
    Nov 22 '18 at 8:06











  • @jezrael Done :) Output should rank user 2 first, user 1 second and user 3 third.

    – CodeMonkey
    Nov 22 '18 at 8:13
















0












0








0








Currently I have the following python code



forumposts = pd.DataFrame({'UserId': [1,1,2,3,2,1,3], 'FirstPostDate': [2018,2018,2017,2019,2017,2018,2019], 'PostDate': [201801,201802,201701,201901,201801,201803,201902]})



data = forumposts.groupby(['UserId', 'PostDate','FirstPostDate']).size().reset_index()



rankedUserIdByFirstPostDate = data.groupby(['UserId', 'FirstPostDate']).size().reset_index().sort_values('FirstPostDate').reset_index(drop=True).reset_index()



data.loc[:,'Rank'] = data.merge(rankedUserIdByFirstPostDate , how='left', on='UserId')['index'].values


The code works as intended but its complicated is there a more pandas like way of doing this? The intent is the following:



Create a dense rank over the UserId column sorted by the FirstPostDate such that the user with the earliest posting gets rank 0 and the user with the second earliest first post gets rank 1 and so on.



Using forumposts.UserId.rank(method='dense') gives me a ranking but its sorted by the order of the UserId.






python pandas pandas-groupby







share|improve this question
















Currently I have the following python code



forumposts = pd.DataFrame({'UserId': [1,1,2,3,2,1,3], 'FirstPostDate': [2018,2018,2017,2019,2017,2018,2019], 'PostDate': [201801,201802,201701,201901,201801,201803,201902]})



data = forumposts.groupby(['UserId', 'PostDate','FirstPostDate']).size().reset_index()



rankedUserIdByFirstPostDate = data.groupby(['UserId', 'FirstPostDate']).size().reset_index().sort_values('FirstPostDate').reset_index(drop=True).reset_index()



data.loc[:,'Rank'] = data.merge(rankedUserIdByFirstPostDate , how='left', on='UserId')['index'].values


The code works as intended but its complicated is there a more pandas like way of doing this? The intent is the following:



Create a dense rank over the UserId column sorted by the FirstPostDate such that the user with the earliest posting gets rank 0 and the user with the second earliest first post gets rank 1 and so on.



Using forumposts.UserId.rank(method='dense') gives me a ranking but its sorted by the order of the UserId.






python pandas pandas-groupby




python pandas pandas-groupby






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 '18 at 8:12







CodeMonkey

















asked Nov 22 '18 at 8:05









CodeMonkeyCodeMonkey

1,31211628




1,31211628













  • Can you add some sample data?

    – jezrael
    Nov 22 '18 at 8:06











  • @jezrael Done :) Output should rank user 2 first, user 1 second and user 3 third.

    – CodeMonkey
    Nov 22 '18 at 8:13





















  • Can you add some sample data?

    – jezrael
    Nov 22 '18 at 8:06











  • @jezrael Done :) Output should rank user 2 first, user 1 second and user 3 third.

    – CodeMonkey
    Nov 22 '18 at 8:13



















Can you add some sample data?

– jezrael
Nov 22 '18 at 8:06





Can you add some sample data?

– jezrael
Nov 22 '18 at 8:06













@jezrael Done :) Output should rank user 2 first, user 1 second and user 3 third.

– CodeMonkey
Nov 22 '18 at 8:13







@jezrael Done :) Output should rank user 2 first, user 1 second and user 3 third.

– CodeMonkey
Nov 22 '18 at 8:13














1 Answer
1






active

oldest

votes


















0














Use map by dictionary created by sort_values with drop_duplicates for order zipped with np.arange:



data = (forumposts.groupby(['UserId', 'PostDate','FirstPostDate'])

                  .size()

                  .reset_index(name='count'))



users = data.sort_values('FirstPostDate').drop_duplicates('UserId')['UserId']

d = dict(zip(users, np.arange(len(users))))

data['Rank'] = data['UserId'].map(d)

print (data)

   UserId  PostDate  FirstPostDate  count  Rank

0       1    201801           2018      1     1

1       1    201802           2018      1     1

2       1    201803           2018      1     1

3       2    201701           2017      1     0

4       2    201801           2017      1     0

5       3    201901           2019      1     2

6       3    201902           2019      1     2


Another solution:



data['Rank'] = (data.groupby('UserId')['FirstPostDate']

                   .transform('min')

                   .rank(method='dense')

                   .sub(1)

                   .astype(int))





share|improve this answer





















  • 1





    Great, learned something new, clever use of dict/zip and map!

    – CodeMonkey
    Nov 22 '18 at 8:43











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53426350%2fpandas-sorted-ranking-of-column-a-sorted-by-column-b%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














Use map by dictionary created by sort_values with drop_duplicates for order zipped with np.arange:



data = (forumposts.groupby(['UserId', 'PostDate','FirstPostDate'])

                  .size()

                  .reset_index(name='count'))



users = data.sort_values('FirstPostDate').drop_duplicates('UserId')['UserId']

d = dict(zip(users, np.arange(len(users))))

data['Rank'] = data['UserId'].map(d)

print (data)

   UserId  PostDate  FirstPostDate  count  Rank

0       1    201801           2018      1     1

1       1    201802           2018      1     1

2       1    201803           2018      1     1

3       2    201701           2017      1     0

4       2    201801           2017      1     0

5       3    201901           2019      1     2

6       3    201902           2019      1     2


Another solution:



data['Rank'] = (data.groupby('UserId')['FirstPostDate']

                   .transform('min')

                   .rank(method='dense')

                   .sub(1)

                   .astype(int))





share|improve this answer





















  • 1





    Great, learned something new, clever use of dict/zip and map!

    – CodeMonkey
    Nov 22 '18 at 8:43
















0














Use map by dictionary created by sort_values with drop_duplicates for order zipped with np.arange:



data = (forumposts.groupby(['UserId', 'PostDate','FirstPostDate'])

                  .size()

                  .reset_index(name='count'))



users = data.sort_values('FirstPostDate').drop_duplicates('UserId')['UserId']

d = dict(zip(users, np.arange(len(users))))

data['Rank'] = data['UserId'].map(d)

print (data)

   UserId  PostDate  FirstPostDate  count  Rank

0       1    201801           2018      1     1

1       1    201802           2018      1     1

2       1    201803           2018      1     1

3       2    201701           2017      1     0

4       2    201801           2017      1     0

5       3    201901           2019      1     2

6       3    201902           2019      1     2


Another solution:



data['Rank'] = (data.groupby('UserId')['FirstPostDate']

                   .transform('min')

                   .rank(method='dense')

                   .sub(1)

                   .astype(int))





share|improve this answer





















  • 1





    Great, learned something new, clever use of dict/zip and map!

    – CodeMonkey
    Nov 22 '18 at 8:43














0












0








0







Use map by dictionary created by sort_values with drop_duplicates for order zipped with np.arange:



data = (forumposts.groupby(['UserId', 'PostDate','FirstPostDate'])

                  .size()

                  .reset_index(name='count'))



users = data.sort_values('FirstPostDate').drop_duplicates('UserId')['UserId']

d = dict(zip(users, np.arange(len(users))))

data['Rank'] = data['UserId'].map(d)

print (data)

   UserId  PostDate  FirstPostDate  count  Rank

0       1    201801           2018      1     1

1       1    201802           2018      1     1

2       1    201803           2018      1     1

3       2    201701           2017      1     0

4       2    201801           2017      1     0

5       3    201901           2019      1     2

6       3    201902           2019      1     2


Another solution:



data['Rank'] = (data.groupby('UserId')['FirstPostDate']

                   .transform('min')

                   .rank(method='dense')

                   .sub(1)

                   .astype(int))





share|improve this answer















Use map by dictionary created by sort_values with drop_duplicates for order zipped with np.arange:



data = (forumposts.groupby(['UserId', 'PostDate','FirstPostDate'])

                  .size()

                  .reset_index(name='count'))



users = data.sort_values('FirstPostDate').drop_duplicates('UserId')['UserId']

d = dict(zip(users, np.arange(len(users))))

data['Rank'] = data['UserId'].map(d)

print (data)

   UserId  PostDate  FirstPostDate  count  Rank

0       1    201801           2018      1     1

1       1    201802           2018      1     1

2       1    201803           2018      1     1

3       2    201701           2017      1     0

4       2    201801           2017      1     0

5       3    201901           2019      1     2

6       3    201902           2019      1     2


Another solution:



data['Rank'] = (data.groupby('UserId')['FirstPostDate']

                   .transform('min')

                   .rank(method='dense')

                   .sub(1)

                   .astype(int))






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 22 '18 at 8:30

























answered Nov 22 '18 at 8:21









jezraeljezrael

327k23270348




327k23270348








  • 1





    Great, learned something new, clever use of dict/zip and map!

    – CodeMonkey
    Nov 22 '18 at 8:43














  • 1





    Great, learned something new, clever use of dict/zip and map!

    – CodeMonkey
    Nov 22 '18 at 8:43








1




1





Great, learned something new, clever use of dict/zip and map!

– CodeMonkey
Nov 22 '18 at 8:43





Great, learned something new, clever use of dict/zip and map!

– CodeMonkey
Nov 22 '18 at 8:43


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53426350%2fpandas-sorted-ranking-of-column-a-sorted-by-column-b%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

To store a contact into the json file from server.js file using a class in NodeJS

Image
0 I have created three files -contactPresentation.js, contactService.js, contacts.json. I will run the contactService.js file. And it will navigate to contactPresentation.js file that will only choose an option to either add or display a file. If add(), then add the data into add of service file.And the data will be added to the contacts.json file . And if display(), then display the display() of service file. And the display will take the data from contacts.json file. However, i know my code is flawed. Please correct it . I am also getting an error - this.contacts.push(new ContactService(userName, contactNumber, emailId)); ^ TypeError: Cannot read property 'push' of undefined at ContactService.add (e:NodeJSProjectcontactManager2contactService.js:57:23) at new C
Read more

Redirect URL with Chrome Remote Debugging Android Devices

Image
1 How can i redirect an URL with Chrome Remote Debugging Android Devices? like Requestly Chrome extension on desktop. google-chrome debugging url redirect testing share | improve this question edited Nov 24 '18 at 8:16 sachinjain024 14.7k 23 73 129 asked Nov 23 '18 at 16:09 Momen Momen 91 10
Read more

Dieringhausen

Image
Dieringhausen Stadt Gummersbach 50.982777777778 7.5316666666667 165 Koordinaten: 50° 58′ 58″  N , 7° 31′ 54″  O Höhe: 165  (160–245)  m Einwohner: 5055  (30. Jun. 2016) Postleitzahl: 51645 Vorwahl: 02261 Lage von Dieringhausen in Gummersbach Im Hohl ca. 1927–1930 Ansicht von Südwesten bei Bünghausen Dieringhausen (im örtlichen Dialekt Dierkesen ) ist ein Ortsteil von Gummersbach im Oberbergischen Kreis, Regierungsbezirk Köln, Nordrhein-Westfalen (Deutschland). Inhaltsverzeichnis 1 Geographie 2 Geschichte 3 Kultur 3.1 Sehenswürdigkeiten 3.2 Regelmäßige Veranstaltungen 4 Infrastruktur und Wirtschaft 4.1 Verkehr 4.1.1 Schienen- und Busverkehr 4.1.2 Straßen 4.2 Öffentliche Einrichtungen 4.2.1 Schulen und Bildungseinrichtungen 4.2.2 Kirchliche Einrichtungen 5 Persönlichkeiten 5.1 In Dieringhausen geboren 5.2 In Dieringhausen gelebt 6 Einzelnachweise 7
Read more