pandas分组聚合和常见操作

1.查询方法和数据对齐

import pandas as pd
import numpy as np

# query() 条件查询
data = {
    'A': [1,2,3,4,5],
    'B': [10,20,30,40,50]
}
df1 = pd.DataFrame(data)
# print(df1)
# print('-'*30)
# print(df1.query('A>2'))  # query()里是一个字符串，里面写的是条件
# print('-'*30)
# print(df1.query('(A>2) and (B<40)'))

# isin() 包含查询
data1 = {
    'City': ['长沙','北京','上海','成都','云南'],
    'Population': [84,39,27,23,15]
}
df2 = pd.DataFrame(data1)
# print(df2)
# print('-'*30)
# # 查询城市里包括含长沙和云南的数据
# print(df2[df2['City'].isin(['长沙', '云南'])])
# print('-'*30)
# # 取反 不包括长沙和云南的数据 前面加上~符号
# print(df2[~df2['City'].isin(['长沙', '云南'])])

# 展开为多列
data2 = {
    'A': [1,2,3],
    'B': [['x','y'],['p','q','r'],['m']]
}
df3 = pd.DataFrame(data2)
# print(df3)
# # 展开多列
# df3[['B1','B2','B3']] = df3['B'].apply(pd.Series)
# print(df3)

# 使用正则
df4 = pd.DataFrame(np.arange(9).reshape(3, 3), index=['sh', 'cs', 'bj'], columns=['a', 'b', 'c'])
# print(df4)
# print('-'*30)
# # 先把c列变成字符串
# df4['c'] = df4['c'].astype(str)
# # 把c列的数值替换为空
# # replace()替换  regex=True开关 \d+ 匹配至少一个数字
# print(df4['c'].replace(to_replace='\d+', value=np.nan, regex=True))

# 数据对齐
# DataFrame对齐
df5 = pd.DataFrame(np.arange(9).reshape(3, 3), index=['Beijing', 'Shanghai', 'Changsha'], columns=list('bcd'))
print(df5)
print('-'*30)
df6 = pd.DataFrame(np.arange(12).reshape(4, 3), index=['Shenzhen', 'Beijing', 'Changsha', 'Shanghai'], columns=list('bde'))
print(df6)
print('-'*30)
print(df5 + df6)

2.函数的应用和映射

import pandas as pd
import numpy as np

# apply()
df = pd.DataFrame(np.random.randn(4, 3), columns=list('bde'), index=['Shenzhen', 'Beijing', 'Changsha', 'Shanghai'])
# print(df)
# # # 自定义规则
# # # apply()应用 指定一个规则，然后把这个规则应用到指定的数据上，按照规则执行数据
# f = lambda x: x.max()-x.min()  # x参数  x.max()-x.min()返回值
# # print(df.apply(f))
#
# df['b'] = df['b'].apply(lambda x: x+1)
# print(df)
# # 应用内置的规则
# print(df.apply(np.max))
# # 按axis=1进行映射
# print(df.apply(f,axis=1))

# map()
# 保留两位小数
# format = lambda x: '%.2f'%x
# # Series用map来进行映射
# print(df['e'].map(format))
# # 还可以使用字典方式来进行映射
# s = pd.Series(['apple', 'banana', 'carrot', 'apple', 'banana', 'carrot'])
# price = {'apple': 5.0, 'banana': 2.5, 'carrot': 1.2}
# print(s.map(price))

# 重复标签索引
df1 = pd.Series(range(5), index=list('aabbc'))
print(df1)
# 尽量不使用重复的标签索引
print(df1['a'])

3.汇总统计和数据处理

import pandas as pd
import numpy as np

# 汇总和统计
data = {
    '姓名': ['Lcuy', 'Lily', 'Tom', 'Jack'],
    '数学': [85, 92, 78, 65],
    '英语': [76, 88, 92, 71],
    '历史': [90, 82, 68, 56]
}
df = pd.DataFrame(data)
# print(df)
# print('-'*30)
# # 设置名字为索引，inplace=True在原数据上进行修改
# df.set_index('姓名', inplace=True)
# print(df)
# print('-'*30)
# # mean()求平均值，median()求中位数，std()求标准差
# print('求和\n', df.sum())
# # idxmax()返回最大值对应的标签索引
# print(df.idxmax())
# print(df.idxmin())

# 数据处理
# 空值   删除  填充
data1 = {
    '月份': [1, 2, 3, 4, 5],
    '房租': [2000, 2000, 2000, 2000, 2000],
    '水电费': [300, 280, None, 310, 290],
    '食品': [500, None, None, 510, 490],
    '交通': [150, 140, None, None, 160]
}
df1 = pd.DataFrame(data1)
# print(df1)
# # 判断数据是否有空值
# print('-'*30)
# print(df1.isnull())
# print('-'*30)
# # 删除 dropna() 默认只要有空值全部会进行删除
# print(df1.dropna())
# print(df1.dropna(axis=1))
# # 全部为空时才删除
# print(df1.dropna(how='all'))
# print('-'*30)
# # 指定列进行删除
# print(df1.dropna(subset=['食品']))
# print('-'*30)
# 空值填充  用常数0填充
# print(df1.fillna(0))
# # 用平均数填充
# print(df1.fillna(df1.mean()))
# # 向上填充  这种方式在pandas1.3之后被弃用
# # print(df1.fillna(method='ffill'))
# print(df1.ffill())
# # 向下填充
# print(df1.bfill())


# 去重和替换
sales_data = pd.DataFrame({
    'product_id': ['A', 'B', 'A', 'C', 'B', 'C', 'A'],
    'sale_data': ['2023-01-01', '2023-01-02', '2023-01-03', '2023-01-04', '2023-01-05', '2023-01-06', '2023-01-07'],
    'sales_amount': [100, 200, 150, 300, 250, 350, 100]
})
print(sales_data)
print('-'*30)
# 去重 drop_duplicates()
# 获取唯一值数据
print(sales_data['sales_amount'].unique())
print('-'*30)
print(sales_data.drop_duplicates())  # 默认按整行去重
print('-'*30)
print(sales_data.drop_duplicates(['sales_amount']))
# 替换replace(旧，新)
sales_data['sale_data'] = sales_data['sale_data'].replace('2023-01-05','2023-10-05')
print(sales_data)