1.查詢方法和數(shù)據(jù)對齊

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)
# # 查詢城市里包括含長沙和云南的數(shù)據(jù)
# print(df2[df2['City'].isin(['長沙', '云南'])])
# print('-'*30)
# # 取反 不包括長沙和云南的數(shù)據(jù) 前面加上~符號
# 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列的數(shù)值替換為空
# # replace()替換  regex=True開關(guān) \d+ 匹配至少一個數(shù)字
# print(df4['c'].replace(to_replace='\d+', value=np.nan, regex=True))

# 數(shù)據(jù)對齊
# 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.函數(shù)的應(yīng)用和映射

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)
# # # 自定義規(guī)則
# # # apply()應(yīng)用 指定一個規(guī)則，然后把這個規(guī)則應(yīng)用到指定的數(shù)據(jù)上，按照規(guī)則執(zhí)行數(shù)據(jù)
# f = lambda x: x.max()-x.min()  # x參數(shù)  x.max()-x.min()返回值
# # print(df.apply(f))
#
# df['b'] = df['b'].apply(lambda x: x+1)
# print(df)
# # 應(yīng)用內(nèi)置的規(guī)則
# print(df.apply(np.max))
# # 按axis=1進(jìn)行映射
# print(df.apply(f,axis=1))

# map()
# 保留兩位小數(shù)
# format = lambda x: '%.2f'%x
# # Series用map來進(jìn)行映射
# print(df['e'].map(format))
# # 還可以使用字典方式來進(jìn)行映射
# s = pd.Series(['apple', 'banana', 'carrot', 'apple', 'banana', 'carrot'])
# price = {'apple': 5.0, 'banana': 2.5, 'carrot': 1.2}
# print(s.map(price))

# 重復(fù)標(biāo)簽索引
df1 = pd.Series(range(5), index=list('aabbc'))
print(df1)
# 盡量不使用重復(fù)的標(biāo)簽索引
print(df1['a'])

3.匯總統(tǒng)計和數(shù)據(jù)處理

import pandas as pd
import numpy as np

# 匯總和統(tǒng)計
data = {
    '姓名': ['Lcuy', 'Lily', 'Tom', 'Jack'],
    '數(shù)學(xué)': [85, 92, 78, 65],
    '英語': [76, 88, 92, 71],
    '歷史': [90, 82, 68, 56]
}
df = pd.DataFrame(data)
# print(df)
# print('-'*30)
# # 設(shè)置名字為索引，inplace=True在原數(shù)據(jù)上進(jìn)行修改
# df.set_index('姓名', inplace=True)
# print(df)
# print('-'*30)
# # mean()求平均值，median()求中位數(shù)，std()求標(biāo)準(zhǔn)差
# print('求和\n', df.sum())
# # idxmax()返回最大值對應(yīng)的標(biāo)簽索引
# print(df.idxmax())
# print(df.idxmin())

# 數(shù)據(jù)處理
# 空值   刪除  填充
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)
# # 判斷數(shù)據(jù)是否有空值
# print('-'*30)
# print(df1.isnull())
# print('-'*30)
# # 刪除 dropna() 默認(rèn)只要有空值全部會進(jìn)行刪除
# print(df1.dropna())
# print(df1.dropna(axis=1))
# # 全部為空時才刪除
# print(df1.dropna(how='all'))
# print('-'*30)
# # 指定列進(jìn)行刪除
# print(df1.dropna(subset=['食品']))
# print('-'*30)
# 空值填充  用常數(shù)0填充
# print(df1.fillna(0))
# # 用平均數(shù)填充
# 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()
# 獲取唯一值數(shù)據(jù)
print(sales_data['sales_amount'].unique())
print('-'*30)
print(sales_data.drop_duplicates())  # 默認(rèn)按整行去重
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)