R group_by %>% summarise equivalent in pandas

Question

I'm trying to rewrite some code from R to python.

my df is something like

size = 20
np.random.seed(456)
df = pd.DataFrame({"names": np.random.choice(["bob", "alb", "jr"], size=size, replace=True),
                  "income": np.random.normal(size=size, loc=1000, scale=100),
                  "costs": np.random.normal(size=size, loc=500, scale=100),
                   "date": np.random.choice(pd.date_range("2018-01-01", "2018-01-06"),
                                           size=size, replace=True)
                  })

Now I need to group the df by name and then perform some summarize operations.

In R, dplyr, I'm doing

 dfg <- group_by(df, names) %>%
    summarise(
            income.acc = sum(income),
            costs.acc = sum(costs),
            net = sum(income) - sum(costs),
            income.acc.bymax = sum(income[date==max(date)]),
            cost.acc.bymax = sum(costs[date==max(date)]),
            growth =  income.acc.bymax + cost.acc.bymax - net
    )

Please note that I'm just trying to ilustrate my data, it doesn't mean anything.

How can I achieve the same result using pandas?

I'm having a hard time because df.groupby().agg() is very limited!

---

Using R I get:

> print(dfg)
# A tibble: 3 x 7
  names income.acc costs.acc   net income.acc.bymax cost.acc.bymax   growth
  <chr>      <dbl>     <dbl> <dbl>            <dbl>          <dbl>    <dbl>
1 alb         7997      3996  4001             2998           1501   497   
2 bob         6003      3004  3000             2002           1002     3.74
3 jr          6002      3000  3002             1000            499 -1503  

---

Using @Jezrael answer:

I get

         income_acc    costs_acc          net  income_acc_bymax  \
names                                                            
alb    7997.466538  3996.053670  4001.412868       2997.855009   
bob    6003.488978  3003.540598  2999.948380       2001.533870   
jr     6002.056904  3000.346010  3001.710894        999.833162   

       cost_acc_bymax       growth  
names                               
alb       1500.876851   497.318992  
bob       1002.151162     3.736652  
jr         499.328510 -1502.549221 

Answer 1

Now you can do it with datar in the same way as you did in R:

>>> from datar.all import f, group_by, summarise, sum, max
>>> 
>>> dfg = group_by(df, f.names) >> summarise(
...     income_acc = sum(f.income),
...     costs_acc = sum(f.costs),
...     net = sum(f.income) - sum(f.costs),
...     income_acc_bymax = sum(f.income[f.date==max(f.date)]),
...     cost_acc_bymax = sum(f.costs[f.date==max(f.date)]),
...     growth =  f.income_acc_bymax + f.cost_acc_bymax - f.net
... )
>>> dfg
     names   income_acc    costs_acc          net  income_acc_bymax  cost_acc_bymax       growth
  <object>    <float64>    <float64>    <float64>         <float64>       <float64>    <float64>
0      alb  7746.653816  3605.367002  4141.286814       2785.500946     1587.685103   231.899235
1      bob  6348.897809  3354.059777  2994.838032       2153.386953     1215.116245   373.665167
2       jr  6205.690386  3034.601030  3171.089356        983.316234      432.851030 -1754.922093

I am the author of the package. Feel free to submit issues if you have any questions.

Answer 2

I think you need custom function:

def f(x):
    income_acc = x.income.sum()
    costs_acc = x.costs.sum()
    net = income_acc - costs_acc
    income_acc_bymax = x.loc[x.date == x.date.max(), 'income'].sum()
    cost_acc_bymax = x.loc[x.date == x.date.max(), 'costs'].sum()
    growth =  income_acc_bymax + cost_acc_bymax - net
    c = ['income_acc','costs_acc','net','income_acc_bymax','cost_acc_bymax','growth']
    return pd.Series([income_acc, costs_acc, net, income_acc_bymax, cost_acc_bymax, growth], 
                     index=c)

df1 = df.groupby('names').apply(f)
print (df1)
        income_acc    costs_acc          net  income_acc_bymax  \
names                                                            
alb    7746.653816  3605.367002  4141.286814       2785.500946   
bob    6348.897809  3354.059777  2994.838032       2153.386953   
jr     6205.690386  3034.601030  3171.089356        983.316234   

       cost_acc_bymax       growth  
names                               
alb       1587.685103   231.899235  
bob       1215.116245   373.665167  
jr         432.851030 -1754.922093