Python pandas groupby by dictionary

Question

I have the following problem.

In a pandas data frame I have one column with all the full words and abbreviations. The second column comes with some floats.

polarity crypto_match_1

data frame

new_dict

I want to groupby() this columns by its words & abbreviations through a dictionary. However the function groups the words and abbreviations separately.

crypto_ranking = dataframe_crypto.set_index('crypto_match_1').groupby(by=new_dict,axis=0).mean().reset_index()

Please post the data instead of a screenshot so that others can easily use it to test out the solution. You may also want to post the code that you wrote for review. — PaW, Feb 12 '18 at 10:54

score 0 · Answer 1 · answered Feb 12 '18 at 11:15

The reason is that in your dictionary, you have both abbreviations to words mapping, and their reverse. So in your dataframe, the abbreviations get mapped to words and the words get mapped to abbreviations.

So the solution here would be to provide a dict which has only abbreviations to words mapping or words to abbreviations mapping.

score 0 · Answer 2 · answered Feb 12 '18 at 11:22

If I understand your problem correctly, this is similar to a prior question,but instead of wanting to map based on the keys, you want to map the new column based on the values, correct?

This is not "good" programming, but for one-time use and known 1-to-1 matching between abbreviations and full words it's probably your fastest fix. Just reverse the dictionary before applying the map as usual.

#reverse the dict
d2 = {}
for k,v in d.items():
    d2[v]= k

#map as usual
df['new_col']=df['abbrev'].map(d2)

Python pandas groupby by dictionary

2 Answers2