1. 知识点

1.18 分箱与统计个数

• 分箱操作

  # float('inf')正无穷
  bins=[0,20000,50001,float('inf')]
  labels=['Low Salary', 'Average Salary', 'High Salary']
  accounts['category']=pd.cut(accounts['income'],bins=bins,labels=labels,right=False)
  

• 数值统计

  accounts_new=accounts['category'].value_counts().reset_index()
  

1.19 分组与求和统计

• 分组统计

  employees=employees.groupby(['event_day','emp_id']).agg({'total_time':'sum'}).reset_index()
  

• 多种数值统计

  employees=employees.groupby(['event_day','emp_id']).agg(
  total_add=('total_time','sum'),
  total_mean=('total_time','mean')
  ).reset_index()
  

1.20 分组获取最小值

• 分组获取最小值

  activity=activity.groupby('player_id').agg(first_login=('event_date','min')).reset_index()
  

1.21 分组获取值个数

• 分组获取最小值

  teacher=teacher.groupby('teacher_id').agg(cnt=('subject_id','count')).reset_index()
  

1.22 分组与条件查询

courses=courses.groupby('class').agg(counts=('student','count')).reset_index() # 分组
courses=courses.query('`counts`>5')[['class']] # 条件查询

1.23 分组与条件查询及获取最大值

• 分组统计

  orders=orders.groupby('customer_number').agg(counts=('order_number','count')).reset_index()
  

• 最大值

  max_orders=orders['counts'].max()
  

• 条件查询

  results=orders.query(f'`counts`=={max_orders}')[['customer_number']]
  

1.24 分组及自定义函数

def get_join(x):
        x_list=sorted(list(set(x)))
        return ','.join(x_list)
    def get_count(x):
        x_list=set(x)
        return len(x_list)
    activities=activities.groupby(['sell_date']).agg(num_sold=('product',get_count),products=('product',get_join)).reset_index()

1.25 分组+lambda函数统计

daily_sales=daily_sales.groupby(['date_id','make_name']).agg(
        unique_leads=('lead_id',lambda x:len(set(x))),
        unique_partners=('partner_id',lambda x:len(set(x)))
        ).reset_index()

2. 题目

2.18 按分类统计薪水（数据统计）

import pandas as pd

def count_salary_categories(accounts: pd.DataFrame) -> pd.DataFrame:
    # float('inf')
    bins=[0,20000,50001,float('inf')]
    labels=['Low Salary', 'Average Salary', 'High Salary']
    accounts['category']=pd.cut(accounts['income'],bins=bins,labels=labels,right=False)
    accounts_new=accounts['category'].value_counts().reset_index()
    accounts_new=accounts_new.rename(columns={'count':'accounts_count'})
    accounts_new=accounts_new.sort_values('category',ascending=False)
    return accounts_new

2.19 查找每个员工花费的总时间(数据分组)

import pandas as pd

def total_time(employees: pd.DataFrame) -> pd.DataFrame:
	# pandas流
    employees=employees.assign(total_time = employees.out_time-employees.in_time).groupby(['event_day','emp_id']).agg({'total_time':'sum'}).reset_index().rename(columns = {'event_day':'day'})
    # employees['total_time']=employees['out_time']-employees['in_time']
    # employees=employees.groupby(['event_day','emp_id'])['total_time'].sum().reset_index()
    # employees=employees.rename(columns={'event_day':'day'})
    # employees.sort_values('emp_id',inplace=True)
    return employees

2.20 游戏玩法分析 I(数据分组)

import pandas as pd

def game_analysis(activity: pd.DataFrame) -> pd.DataFrame:
    activity=activity.groupby('player_id').agg(first_login=('event_date','min')).reset_index()
    return activity

2.21 每位教师所教授的科目种类的数量(数据分组)

import pandas as pd

def count_unique_subjects(teacher: pd.DataFrame) -> pd.DataFrame:
    teacher.drop_duplicates(['teacher_id','subject_id'],inplace=True)
    teacher=teacher.groupby('teacher_id').agg(cnt=('subject_id','count')).reset_index()
    return teacher

2.22 超过5名学生的课(数据分组)

import pandas as pd

def find_classes(courses: pd.DataFrame) -> pd.DataFrame:
    courses=courses.groupby('class').agg(counts=('student','count')).reset_index()
    return courses.query('`counts`>5')[['class']]

2.23 订单最多的客户(数据分组)

import pandas as pd

def largest_orders(orders: pd.DataFrame) -> pd.DataFrame:
    orders=orders.groupby('customer_number').agg(counts=('order_number','count')).reset_index()
    max_orders=orders['counts'].max()
    results=orders.query(f'`counts`=={max_orders}')[['customer_number']]
    return results

2.24 按日期分组销售产品(数据分组)

import pandas as pd

def categorize_products(activities: pd.DataFrame) -> pd.DataFrame:
    activities=activities.groupby(['sell_date']).agg(
        num_sold=('product',lambda x:len(set(x))),
        products=('product',lambda x:','.join(sorted(list(set(x)))))
        ).reset_index()
    return activities
    

2.25 每天的领导和合伙人(数据分组)

import pandas as pd

def daily_leads_and_partners(daily_sales: pd.DataFrame) -> pd.DataFrame:
    daily_sales=daily_sales.groupby(['date_id','make_name']).agg(
        unique_leads=('lead_id',lambda x:len(set(x))),
        unique_partners=('partner_id',lambda x:len(set(x)))
        ).reset_index()
    return daily_sales