Group/filter and make calculations on lines of a dataframe

user_4764604 0
Viewed 27

I have a dataframe with the items sold by different stores every day:

    date    date_block_num  shop_id item_id item_price  item_cnt_day    day month_year
1953691 24.09.2014  20  5   1039    899.0   1.0 24  09.2014
1953692 27.09.2014  20  5   1015    449.0   1.0 27  09.2014
1953693 07.09.2014  20  5   1329    399.0   1.0 07  09.2014
1953694 27.09.2014  20  5   984 399.0   1.0 27  09.2014
1953695 08.09.2014  20  5   984 399.0   1.0 08  09.2014

I would like to get the results for each store. So I tried:

revenues = {}
for row in transactions_december_2014.sort('shop_id').iterrows():
    if last_shop_id == row.shop_id:
        revenues[shop_id] += row.item_price * row.item_cnt_day
        last_shop_id = row.shop_id
    else:
        revenues[shop_id] = row.item_price * row.item_cnt_day
print(max(revenues))

But it gives me back:

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-26-391a52cd0210> in <module>()
      9 # transactions_december_2014.groupby("shop_id").sum(transactions_december_2014.item_price * transactions_december_2014.item_cnt_day)
     10 revenues = {}
---> 11 for row in transactions_december_2014.sort('shop_id').iterrows():
     12     if last_shop_id == row.shop_id:
     13         revenues[shop_id] += row.item_price * row.item_cnt_day

/opt/conda/lib/python3.6/site-packages/pandas/core/generic.py in __getattr__(self, name)
   3079             if name in self._info_axis:
   3080                 return self[name]
-> 3081             return object.__getattribute__(self, name)
   3082 
   3083     def __setattr__(self, name, value):

AttributeError: 'DataFrame' object has no attribute 'sort'

I also planned to use groupbys:

transactions_december_2014.groupby("shop_id").sum(transactions_december_2014.item_price * transactions_december_2014.item_cnt_day)

But it never works. I'm now thinking of doing it now with lambda as I am using for loops.

pandas dataframe python-3.x python
1 Answers

As I understand, you want to compute the total price of the items sold by a certain store. You can do it like this:

df["cost"] = df["item_price"] * df["item_cnt_day"]
df.groupby("shop_id")["cost"].sum()

First you create a column that saves the total value of the items sold (price * how many got sold). Then you groupby based on store to finally get the sum of the costs.

user_114000160
Related