CODEWITHSUNDEEP
pythonpandasgroup-by
Emilia Delizia
Emilia Delizia

Reputation: 353

How to groupby in pandas and add values from another column and count them

I have a data frame like that, I want somehow to groupby the user_search column, count how many of each prediction in the predicted column, add the predicted coin and the numeric value.

u_search predicted
bitcoin litecoin
polkadot burgerswap
cardano pancake swap
bitcoin litecoin
bitcoin ethereum

I want to create a dataframe like this

u_search predicted
bitcoin litecoin 2, ethereum 1
polkadot burgerswap 1
cardano pancake swap 1 
user_data = pd.read_csv('static/user_searched_coins_df.csv')
user_data=user_data.groupby(['user searched coin']).....

Upvotes: 1

Views: 118

Answers (1)

sushanth
sushanth

Reputation: 8302

Here is a solution you can try out, using pd.GroupBy + Series.value_counts

df.groupby('u_search', as_index=False)['predicted'].apply(
    lambda x: ", ".join(f"{k} {v}" for k, v in x.value_counts().items())
)

Out[*]: 

   u_search               predicted
0   bitcoin  litecoin 2, ethereum 1
1   cardano           pancakeswap 1
2  polkadot            burgerswap 1

Upvotes: 1

Related Questions