CODEWITHSUNDEEP
pythonpandas
thomas.mac
thomas.mac

Reputation: 1256

pandas groupby using dictionary values, applying sum

I have a defaultdict:

dd = defaultdict(list,
        {'Tech': ['AAPL','GOOGL'],
         'Disc': ['AMZN', 'NKE']  }

and a dataframe that looks like this:

         AAPL AMZN GOOGL NKE
1/1/10   100  200  500   200
1/2/10   100  200  500   200
1/310    100  200  500   200

and the output I'd like is to SUM the dataframe based on the values of the dictionary, with the keys as the columns:

         TECH DISC 
1/1/10   600  400 
1/2/10   600  400  
1/3/10   600  400

The pandas groupby documentation says it does this if you pass a dictionary but all I end up with is an empty df using this code:

df.groupby(by=dd).sum()   ##returns empty df

Upvotes: 5

Views: 1048

Answers (2)

Haleemur Ali
Haleemur Ali

Reputation: 28243

you can create a new dataframe using the defaultdict and dictionary comprehension in 1 line

pd.DataFrame({x: df[dd[x]].sum(axis=1) for x in dd})
# output:

        Disc  Tech
1/1/10   400   600
1/2/10   400   600
1/310    400   600

Upvotes: 2

BENY
BENY

Reputation: 323226

Create the dict in the right way , you can using by with axis=1

# map each company to industry
dd_rev = {w: k for k, v in dd.items() for w in v}
# {'AAPL': 'Tech', 'GOOGL': 'Tech', 'AMZN': 'Disc', 'NKE': 'Disc'}

# group along columns
df.groupby(by=dd_rev,axis=1).sum() 

Out[160]: 
        Disc  Tech
1/1/10   400   600
1/2/10   400   600
1/310    400   600

Upvotes: 7

Related Questions