Create column of value_counts in Pandas dataframe
pandas, python
Solution
df['Counts'] = df.groupby(['Color'])['Value'].transform('count')
For example,
In [102]: df = pd.DataFrame({'Color': 'Red Red Blue'.split(), 'Value': [100, 150, 50]})
In [103]: df
Out[103]:
Color Value
0 Red 100
1 Red 150
2 Blue 50
In [104]: df['Counts'] = df.groupby(['Color'])['Value'].transform('count')
In [105]: df
Out[105]:
Color Value Counts
0 Red 100 2
1 Red 150 2
2 Blue 50 1
Note that `transform('count')` ignores NaNs. If you want to count NaNs, use `transform(len)`.
To the anonymous editor: If you are getting an error while using `transform('count')` it may be due to your version of Pandas being too old. The above works with pandas version 0.15 or newer.
Problem
I want to create a count of unique values from one of my Pandas dataframe columns and then add a new column with those counts to my original data frame. I've tried a couple different things. I created a pandas series and then calculated counts with the value_counts method. I tried to merge these values back to my original dataframe, but I the keys that I want to merge on are in the Index(ix/loc). ``` Color Value Red 100 Red 150 Blue 50 ``` I want to return something like: ``` Color Value Counts Red 100 2 Red 150 2 Blue 50 1 ```