1

I have a dataset, where authors are ranked by the order of authorship (1, 2, 3, etc).

Authorid    Author  Article Articleid   Rank
1            John   article 1   1        1
1            John   article 2   2        2
1            John   article 3   3        3
1            John   article 4   4        3
2            Mary   article 5   5        1
2            Mary   article 6   6        2
2            Mary   article 7   7        1
2            Mary   article 8   8        8

I want to create three more Boolean columns If_first, If_second, If_last. The purpose of this - I want to show if the author is ranked 1, 2, or last in the article. The last means the maximum number in Rank column (the maximum number for this Authorid in the column Rank).

I can do If_first and If_second, that is pretty easy, but not sure how to resolve If_last.

df.loc[df['Rank'] == 1, 'If_first'] = 1
df.loc[df['Rank'] != 1, 'If_first'] = 0
df.loc[df['Rank'] == 2, 'If_second'] = 1
df.loc[df['Rank'] != 2, 'If_second'] = 0

Two rules here

  • If_first = if_last - treat him as if_first
  • If_second = if_last - treat him as if_second

Expected output:

Authorid    Author  Article Articleid   Rank    If_first    If_second   If_last
1            John   article 1   1        1       1              0         0
1            John   article 2   2        2       0              1         0
1            John   article 3   3        3       0              0         1 (third is the last here)
2            Mary   article 5   5        1       1              0         0
2            Mary   article 6   6        2       0              1         0
2            Mary   article 7   7        3       0              0         0 (third is not the last here, because of the fourth below, all zeros)
2            Mary   article 8   8        4       0              0         1 (fourth is the last here)
Improve this question
2
  • 1
    Is there an extra row in the sample data? The last one in Authorid == 1? It does not exist in your output. Or do we have to drop one of the rows if there are with the same rank within the same Authorid? Commented Dec 10, 2021 at 17:02
  • @sophocles, I updated the output again. If the 1,2,3 author is not the last - then all values must be zero. Somehow I need to calculate what is the max x rank for each author id and assign it to last. Hope it helps! Commented Dec 10, 2021 at 17:04

2 Answers 2

Reset to default
1

Try this:

df = df.reset_index(drop=True)
res = df.groupby('Authorid')['Rank'].apply(lambda x: [x.idxmin(), x.drop_duplicates()[1:].nsmallest(1).index[0], x.idxmax()])

df[['If_first', 'If_second', 'If_last']] = 0
df.loc[res.str[0].tolist(), 'If_first'] = 1
df.loc[res.str[1].tolist(), 'If_second'] = 1
df.loc[res.str[2].tolist(), 'If_last'] = 1

Output:

>>> df
  Authorid   Author  Article  Articleid  Rank  If_first  If_second  If_last
0     John  article        1          1     1         1          0        0
1     John  article        2          2     2         0          1        0
2     John  article        3          3     3         0          0        1
3     John  article        4          4     3         0          0        0
4     Mary  article        5          5     1         1          0        0
5     Mary  article        6          6     2         0          1        0
6     Mary  article        7          7     1         0          0        0
7     Mary  article        8          8     8         0          0        1
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

One approach might be to create a second dataframe grouped by Articleid collecting the statistic you're interested in:

df2 = df.groupby('Articleid').agg(mxrank=('Rank', 'max'))

then add the new column by merging the dataframes:

dfm = df.merge(df2, how='left', on='Articleid')

With example result (with some added rows to demonstrate an article "article4" with multiple ranks):

   Authorid Author   Article Articleid Rank mxrank
0         1   John  article1         1    1      1
1         1   John  article2         2    2      2
2         1   John  article3         3    3      3
3         1   John  article4         4    3      4
4         1    Foo  article4         4    1      4
5         1    Bar  article4         4    2      4
6         1    Baz  article4         4    4      4
7         2   Mary  article5         5    1      1
8         2   Mary  article6         6    2      2
9         2   Mary  article7         7    1      1
10        2   Mary  article8         8    8      8

Then compare the mxrank column to Rank to determine the flag for each row.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.