Levine Levine - 11 days ago 8
Python Question

Create Column in Pandas from Aggregation

I have a dataframe 'xyz' and I want to create a new column based on a simple calculation, but when I run the code below, the result is NaN.

xyz =

account_id date
0 123 2016-01-01
1 124 2016-01-01
2 125 2016-01-01
3 126 2016-01-01
4 123 2016-01-02
5 124 2016-01-02
6 125 2016-01-02
7 126 2016-01-02


New column I want to create: number of days where I have data per account_id.

Code I'm executing:

xyz['new_column'] = xyz.groupby('account_id').date.nunique()


Result I get:

account_id date new_column
0 123 2016-01-01 NaN
1 124 2016-01-01 NaN
2 125 2016-01-01 NaN
3 126 2016-01-01 NaN
4 123 2016-01-02 NaN
5 124 2016-01-02 NaN
6 125 2016-01-02 NaN
7 126 2016-01-02 NaN


Thanks in advance!

Answer

You can use transform:

xyz['new_column'] = xyz.groupby('account_id').date.transform('nunique')