Insert Value to a Cell with result of group by pandas-CodePudding

I have a huge excel file, like that:
Table 1
my desire Table is like:
my dsire Table
I use group by, count and sum like :

import pandas as pd
import openpyxl as op
from openpyxl import load_workbook
from openpyxl import Workbook
import numpy as np
path1 = r"users.xlsx"
data = pd.read_excel(path1, engine='openpyxl')
df = pd.DataFrame(data)
NumberOfChild = df.groupby('Parent ID')['Parent ID'].count().to_frame('Employees Number')
NumberOfBooking = df.groupby('Parent ID')['Reservations Count'].transform('sum')

that gives me right number of Booking and Child, but i can't these value in the Columns numberOfChild and numberOfBooking

CodePudding user response：

Assuming you have the following dataframe

>>> df
  id  parent_id  reservations  
0   1        NaN             1 
1   2        1.0             3 
2   3        1.0             5
3   4        NaN             2 
4   5        4.0             6
5   6        NaN             7

First compute the number of children

>>> children = df.groupby("parent_id").id.count().rename("children")
>>> children
parent_id
1.0    2
4.0    1
Name: children, dtype: int64

Then create an aggregate a new column which will be either id if the row has no parent_id, or parent_id otherwise

>>> df["book_key"] = df.parent_id.fillna(df.id).astype(int)
>>> df
   id  parent_id  reservations  book_key
0   1        NaN             1         1
1   2        1.0             3         1
2   3        1.0             5         1
3   4        NaN             2         4
4   5        4.0             6         4
5   6        NaN             7         6

Use this new key to compute the total number of reservations

>>> reservations = df.groupby("book_key").reservations.sum().rename("total")
>>> reservations 
book_key
1    9
4    8
6    7
Name: total, dtype: int64

Finally join the dataframes, drop the book_key columns and optionaly replace NaN with ""

>>> df = df.set_index("id").join(children).join(reservations).drop(columns="book_key").fillna("")
>>> df
   parent_id  reservations children total
id
1                        1      2.0   9.0
2        1.0             3
3        1.0             5
4                        2      1.0   8.0
5        4.0             6
6                        7            7.0