CODEWITHSUNDEEP
python-3.xpandasdataframepandas-groupby
DanielTheRocketMan
DanielTheRocketMan

Reputation: 3249

Efficient evaluation of weighted average variable in a Pandas Dataframe

Please, considere the dataframe df generated below:

import pandas as pd

def creatingDataFrame():

    raw_data = {'code': [1, 2, 3, 2 , 3, 3],                
                'var1': [10, 20, 30, 20 , 30, 30],
                'var2': [2,4,6,4,6,6],
                'price': [20, 30, 40 , 50, 10, 20],
                'sells': [3, 4 , 5, 1, 2, 3]}
    df = pd.DataFrame(raw_data, columns = ['code', 'var1','var2', 'price', 'sells'])
    return df


if __name__=="__main__":

    df=creatingDataFrame()

    setCode=set(df['code'])


    listDF=[]
    for code in setCode:
        dfCode=df[df['code'] == code].copy()
        print(dfCode)
        lenDfCode=len(dfCode)
        if(lenDfCode==1):
            theData={'code': [dfCode['code'].iloc[0]],                
                'var1': [dfCode['var1'].iloc[0]],
                'var2': [dfCode['var2'].iloc[0]],
                'averagePrice': [dfCode['price'].iloc[0]],
                'totalSells': [dfCode['sells'].iloc[0]]
            }
        else:
            dfCode['price*sells']=dfCode['price']*dfCode['sells']
            sumSells=np.sum(dfCode['sells'])
            sumProducts=np.sum(dfCode['price*sells'])
            dfCode['totalSells']=sumSells
            av=sumProducts/sumSells
            dfCode['averagePrice']=av
            theData={'code': [dfCode['code'].iloc[0]],                
                'var1': [dfCode['var1'].iloc[0]],
                'var2': [dfCode['var2'].iloc[0]],
                'averagePrice': [dfCode['averagePrice'].iloc[0]],
                'totalSells': [dfCode['totalSells'].iloc[0]]
            }
        dfPart=pd.DataFrame(theData, columns = ['code', 'var1','var2', 'averagePrice','totalSells'])
        listDF.append(dfPart)
    newDF = pd.concat(listDF)
    print(newDF)

I have this dataframe

   code  var1  var2  price  sells
0     1    10     2     20      3
1     2    20     4     30      4
2     3    30     6     40      5
3     2    20     4     50      1
4     3    30     6     10      2
5     3    30     6     20      3

I want to generate the following dataframe:

   code  var1  var2  averagePrice  totalSells
0     1    10     2          20.0           3
0     2    20     4          34.0           5
0     3    30     6          28.0          10

Note that this dataframe is created from the first by evaluating the average price and total sells for each code. Furthermore, var1 and var2 are the same for each code. The python code above does that, but I know that it is inefficient. I believe that a desired solution can be done using groupby, but I am not able to generate it.

Upvotes: 0

Views: 113

Answers (1)

BENY
BENY

Reputation: 323326

It is different , apply with pd.Series

df.groupby(['code','var1','var2']).apply(lambda x : pd.Series({'averagePrice': sum(x['sells']*x['price'])/sum(x['sells']),'totalSells':sum(x['sells'])})).reset_index()
Out[366]: 
   code  var1  var2  averagePrice  totalSells
0     1    10     2          20.0         3.0
1     2    20     4          34.0         5.0
2     3    30     6          28.0        10.0

Upvotes: 2

Related Questions