0

I have a dataframe which looks like:

|--------------|------------|------------|
|     User     |    Text    |   Effort   |
|--------------|------------|------------|
|    user122   |   TextA    |  2 Weeks   |
|--------------|------------|------------|
|    user124   |   TextB    |  2 Weeks   |
|--------------|------------|------------|
|    user125   |   TextC    |  3 Weeks   |
|--------------|------------|------------|
|    user126   |   TextD    |  2 Weeks   |
|--------------|------------|------------|
|    user126   |   TextE    |  2 Weeks   |
|--------------|------------|------------|

My goal is to group the table by Effort and get the unique count of each user per group. I am able to do that by:

df.groupby(['Effort']).agg({"User": pd.Series.nunique})

And this results into this table:

|--------------|------------|
|     Effort   |    User    |
|--------------|------------|
|    2 weeks   |     3      |
|--------------|------------|
|    3 weeks   |     1      |
|--------------|------------|

However, by doing so I am loosing my text column information. Another solution I tried is to keep the first occurrence of that column, but I am still unhappy because I loose something on the way.

Question Is there any way in which I can keep my initial dataframe without loosing any row and column but at the same time still group by Effort?

Erfan
  • 40,971
  • 8
  • 66
  • 78
Krye Kuzhinieri
  • 95
  • 1
  • 5
  • 3
    Please don't use markdown tables for sample data. The data has to be manually extracted. Please see [How to provide a reproducible copy of your DataFrame using `df.head(30).to_clipboard(sep=',')`](https://stackoverflow.com/questions/52413246). – Trenton McKinney Oct 06 '20 at 22:13

1 Answers1

4

The best option you have is using transform if you ask me. This way you keep the shape of your original data, but still get the results of a groupby.

df['Nunique'] = df.groupby('Effort')['User'].transform('nunique')

      User   Text   Effort  Nunique
0  user122  TextA  2 Weeks        3
1  user124  TextB  2 Weeks        3
2  user125  TextC  3 Weeks        1
3  user126  TextD  2 Weeks        3
4  user126  TextE  2 Weeks        3
Erfan
  • 40,971
  • 8
  • 66
  • 78