mysql資料合并去重複内容自動填充内容_對比sql學python（Python資料處理基本知識點）...

學習資料分析，我先學的是Python，學完才發現還有sqlｰ(｡-_-｡)

不得不承認在資料篩選，sql更勝一籌。不過既然學過，那就總結對比下，以後說不定還要重試精進

圖檔放大可看

本文按照以下順序展開

1）表格操作

1. 導入/導出表格
2. 索引設定
3. 提取行/列

2）資料清洗

1. 重複值（查找唯一進制素/查找重複值/删除重複值）
2. 空缺值（查找空缺值/填充空缺值/删除空缺值）
3. 資料一緻化（字元轉時間/字元轉數值/文本轉數值）
4. 異常值（判斷資料中是否有異常值）

3）資料抽取

按百分比，按個數是否有放回抽取

4）資料合并

1. 表内合并
2. 兩表合并（橫向合并/縱向合并）

5）數值操作

1. 數值計算（簡單四則運算/求分位數）
2. 數值排序/排名
3. 資料分組（groupby/cut）
4. 透視表（df.pivot_table()）
5. 字元資料（字段比對/字元替換/字元串截取/字元串計數/去除空格）
6. 日期函數（文本轉時間元素/自定義時間/抽取時間元素/日期計算）

然後會發現，

Python與sql兩表縱向連接配接不同
sql求時間間隔更簡單，篩選前20%資料python更簡單
數值排名，二者是有差别的
           

【mark】s.value_counts()是一種檢視表格某列中有多少個不同值的快捷方法，并計算每個不同值有在該列中有多少重複值。

一、表格操作

1）導入/導出表格

• 導入csv pd.read_csv('路徑',encoding,sep,usecols)
• 導入txt pd.read_table('路徑',encoding,sep)
• 導入excel pd.read_table('路徑',sheet_name)

columns=['name','gender','birht','score']
student=pd.read_csv('path',names=columns,usecols=['name'],skiprows=1,nrows=2)
           

• 導出 df.to_csv('路徑',sep,index,header,encoding) #index,header是否輸出索引和列名，預設True

2）索引設定

• 重置索引 df.reset_index()
• 将某列設定成索引 df.set_index('列名')
• 将索引重命名 df.index=['索引1','索引2'...]
• 将某個索引重命名 df.rename(index={'舊索引':'新索引'})

3）提取行/列

#提取第i行
df.iloc[i-1] 索引所在行
df.loc['索引名稱']

#提取第i行~第i+n行
df.iloc[i-1:i+n] 索引所在行
df.loc[['索引名稱1','索引名稱2']]

#提取第i列
df.iloc[:,i-1]
df['列名']

#提取第i列~第i+n列
df.iloc[:,i-1:i+n]
df[['列名1','列名2']]

【SQL提取】
提取列：
select 列名 from table；
提取行：
SELECT * from student
limit 2,2;（跳過前2行，取2行）
           

二、清洗資料

1）重複值

• 查找出唯一進制素的值

print(df['name'].unique())
print(len(df['name'].unique()))
           

• 查找重複值

df.duplicated(subset,keep='first')

keep 保留哪個重複值，預設是first,可選last

print(student.duplicated(subset='gender'))
print(student[student.duplicated(subset='gender',keep='first')])
           

SQL查找重複值用【group by】及【having count()>1】完成

#查找某列重複值
SELECT * from student
group by gender
having count(gender)>1;

#查找出某列唯一進制素
SELECT DISTINCT name from result
;
           

• 删除重複值

df.dorp_duplicates(subset,keep)

• subset 用哪些列來判斷重複
• keep 保留哪一個，預設first,可選last

print(df.drop_duplicates())
print(df.drop_duplicates(subset='name'))
print(df.drop_duplicates(subset='name',keep='last'))
           

2）空缺值 #查找空缺值 df.isnull()

print(student.info())
print(student.isnull())
print(student.isnull().sum())
           

空缺值處理
#填充空缺值
print(df.fillna(0)) 
print(df.fillna(df['amount'].mean()))
print(df.fillna({'amount':0}))

#删除空缺值所在行
print(df.dropna())
print(df.dropna(how='all')) #整行都是空缺值才被删除，預設‘any’

#删除空缺值所在列
print(df.dropna(axis=1)) 
           

#查找缺失值（區分空值、空格、null）
SELECT * from result
where amount is null  #如果單元格為null，則此處應該用is null來篩選
;

SELECT * from result
where amount=''
;

#填充空缺值
#使用case...when...填充
SELECT id,name,
case 
when amount =0 then 100
else amount
end amount
from result
;
           

3）資料一緻化

#字元轉時間

【方法1】student['birth']=student['birth'].astype('datetime64[M]')
【方法2】student['birth']=pd.to_datetime(student['birht'],format='%Y-%m-%d %H:%M:%S')
           

#數值轉字元/字元轉數值

student['score'].astype(int)---類型還可以為float/str

#SQL資料類型轉換 cast(value as type)
           

#文本轉數值(對文本進行切片，提取數值)

#python
student['maths_score']=student['maths'].apply(lambda x:float(x[5:]))

#sql
SELECT *,substr(math,6,2) as math_score
from student;
           

三、資料抽取

df.sample(n/frac,replace)

• n/frac隻能二選一
• replace預設為False不可放回抽樣，True為放回抽樣

四、資料合并

1）表内兩列合并 轉化成文本型，再進行加号運算

student['new']=student['name']+'-'+student['gender']
print(student)
           

【SQL】
ALTER table student add column new varchar(20);---------先在表中增加列
UPDATE student set new=CONCAT(name,'-',gender);------再更改列中的值
SELECT * from student 
;
           

2)橫向合并

• pd.merge(table1,table2,left_on,right_on,how)
• how有4種用法left，right，inner，outer

【SQL橫向合并表格】

• left/right/inner/outer join...on
• cross join （交叉連接配接）
• outer join有些系統無法支援，若要實作則需要先左連接配接，再右連接配接

#左連接配接
print(pd.merge(student,student_class,left_on='name',right_on='student_name',how='left'))

#右連接配接
print(pd.merge(student,student_class,left_on='name',right_on='student_name',how='right'))

#内連接配接
print(pd.merge(student,student_class,left_on='name',right_on='student_name',how='inner'))

#全連接配接
print(pd.merge(student,student_class,left_on='name',right_on='student_name',how='outer'))
           

3）縱向連接配接

• pd.concat([table1,table2])
• 在Python中concat方法相當于MySQL的全連接配接(UNION ALL)，concat不會去重，要達到去重的效果可以使用drop_duplicates方法

【SQL縱向連接配接】

• union去重連接配接，比union all全連接配接效率低
• 必須列數相同，才能縱向連接配接

print(pd.concat([student,student_class]))
           

五、數值操作

1）數值計算 #輸出百分數，保留兩位小數

df['列'].apply(lambda x: format(x, '.2%'))

#求分位數

• df['列名'].quantile(q.interpolation='nearest')
• 選擇nearest，和百分位數最靠近的值
• 求出的分位數還可用作pd.cut()函數裡的bins值，如果q為清單，如q=[0,0.4,1]

#求排在前30%的成績
print(student['score'].quantile(0.7,interpolation='nearest'))
           

【SQL求解】

①row_number()對數值進行排名，得到ranking ②ranking<=(select count() from table)*百分比

#将score空值先填充，再求出排在前30%的成績

SELECT t1.score from
(SELECT t.name,t.score_fillna,
row_number() over (order by t.score_fillna desc) as ranking from
(SELECT *,
case when score is null then (SELECT avg(score) from student) else score end as score_fillna
from student) t) t1
WHERE t1.ranking<=(SELECT count(name) from student)*0.3
;
           

2）數值排序

• df.sortvalues(['列名1','列名2'],ascending,na_position)
• 預設升序，ascending=False降序
• 空值預設排在最後，na_position='first'

#按照成績升序排列，按照出生日期降序排列
print(student.sort_values(['score','birth'],ascending=[True,False]))
           

【SQL】
SELECT * from student 
order by score ,birth DESC
;
           

3）數值排名

• df.rank(ascending,method)
• method有四種方式：min/max/average/first
• method='fisrt'與sql裡的row_number()排名一緻

【sql排名】

rownumber/rank/dense_rank() over (order by 列名)

#出生日期按升序排名
student['rank_min']=student['birth'].rank(method='min')
student['rank_max']=student['birth'].rank(method='max')
student['rank_avg']=student['birth'].rank(method='average')
student['rank_first']=student['birth'].rank(method='first')
           

4）資料分組 #分組打标簽

• pd.cut(列,bins,right,labels)
• 預設區間是左開右閉，right=False區間左閉右開
• 得到的label的type:category，需要.astype(str)轉化資料類型，才能用groupby(此處可以借用統計不同年齡段的使用者人均消費頻次加深了解)

labels=['[0,80)','[80,90)','[90,100)']
student['cut']=pd.cut(student['score'],bins=[0,80,90,100])
student['label']=pd.cut(student['score'],bins=[0,80,90,100],right=False,labels=labels)
           

SELECT *,
case 
when score<80 then '[0,80)'
when score<90 then '[80,90)'
when score<100 then '[90,100)'
END as cut 
from student 
;
           

#分組分析

• df.groupby(by,as_index=True)[columns].agg(funs)
• as_index：分組的列是否作為索引列，預設為True
• funs常用：計數count，均值mean，方差std，求和sum，最大值max

#按照性别分組，并計數
df1=student.groupby('gender')['name'].count()

#按照性别分組，并計數求并均值
df2=student.groupby('gender').agg(['count','mean'])

#按照性别、出生月份分組，并計數
df3=student.groupby(['gender','month']).count()

##按照性别、出生月份分組，并求出各組最小出生日期及各組平均成績
df4=student.groupby(['gender','month']).agg({'birth':'min','score':'mean'})
           

• #求各性别裡的最高成績

print(student.sort_values(['gender','score'],ascending=False).groupby('gender').head(1))
           

5）透視表 df.pivot_table(index,values,aggfunc,margins)

• index可以有多個
• 可以對不同value應用不同的aggfunc
• margins=True有總值

6）字元串運算

#字元串比對

print(df[df['describe'].str.contains('生物')]) #模糊查找，包含有生物
print(df[df['describe'].isin(['生物'])]) #精确查找，隻有生物
print(df[df['describe'].str.startswith('生物')]) #以生物開頭
print(df[df['describe'].str.endswith('生物')]) #以生物結束
           

#文本替換 df[列名].replace(舊文本,新文本)

print(df['name'].replace('tom','TOM')) #一對一替換 
print(df['name'].replace(['tom','jack'],'NAME')) #多對一替換 
print(df['name'].replace({'tom':'TOM','jack':'JACK'})) #多對多替換
           

在sql裡如果要多對一替換，隻能做嵌套

SELECT id,
replace(name,'tom','TOM'),
描述 from result
;
           

#文本合并

df['合并']=df['id'].astype(str)+df['name']
df['合并']=df['id'].astype(str)+'-'+df['name']
           

pythone裡，隻需要轉換成str類型，然後相加即實作合并

SELECT concat(id,`name`) as '合并',
concat_ws('-',id,`name`) as '以-合并' from result
;
           

• 字元串截取

切片截取

#字元串切片
print(df['describe'].str[0:2])


#SQL截取
SELECT 描述,
left(描述,2) 左截取,
right(描述,2) 右截取,
substr(描述,2,2) 中間截取 #從第n個字元開始，截取m個字元
from result
;
           

按照分隔符截取

pd.series.str.split(分隔符，n，expand)

• n 分割為N+1列，-1代表傳回所有的分隔列
• expand 是否展開為資料框，預設FALSE，一般設定為TRUE

new_df=df['describe'].str.split('-',1,True)
new_df.columns=['describe1','describe2']
print(new_df)

select SUBSTRING_INDEX('2020-2', '-', 1)
           

• 字元串計數

print(len(df['describe'])) #統計的是該列多少行

SELECT CHAR_LENGTH(描述), #基于字元串計數
LENGTH(描述) from result  #基于位元組計數
           

• 去除字元串空格

print(df['describe'].str.strip()) #去除開頭和結尾的空格
print(df['describe'].str.lstrip()) #去除開頭的空格
print(df['describe'].str.rstrip()) #去除結尾的空格
print(df['describe'].str.replace(" ", ""))  #去除全部空格

select char_length(' 開 心 ') as str_length,
char_length(ltrim(' 開 心 ')) as left_trim_length,
char_length(rtrim(' 開 心 ')) as right_trim_length,
char_length(trim(' 開 心 ')) as trim_length
           

四、日期函數

1）文本轉化成時間

【方法1】pd.to_datetime(date,format='%Y-%m-%d %H:%M:%S')
              ------【SQL抽取】dateformat(date,'%Y-%m-%d %H:%i:%S') #分的抽取不一樣
【方法2】s.astype('datetime64[M]')
           

2）自定義時間

from datetime import datetime
#擷取目前時間 datetime.now()
#自定義時間 datetime(年,月,日,時,分,秒) --- 最起碼要有年、月、日
           

3）抽取時間

#從自定義的時間裡抽取

• 【提取年月】date.strftime('%Y-%m') -- (type:object64)
• 【提取年】date.year--(type:int64)

#從本有的時間元素裡提取

• 【提取年】date.dt.year
• 【提取年月】date.dt.strftime('%Y-%m')

select now(),date(now()),DATE_FORMAT(now(),'%Y-%m'),year(now())
           

4）時間計算 用python計算相隔年月遠沒有sql簡單！！！

student['age']=(datetime(2023,1,1)-student['birth'])
student['age1']=(datetime(2023,1,1)-student['birth']).dt.days
student['age2']=datetime(2023,1,1).year-student['birth'].dt.year

#SQL時間計算
SELECT *,TIMESTAMPDIFF(year,birth,20200101) as age
from student;

此外還經常用到 DATE_ADD(date,INTERVAL expr unit)
           

5）SQL擷取時間/抽取時間 dateformat(date,'%Y-%m-%d %H:%i:%S')

SELECT now() as 現在時間,
weekday(20201014) as 星期幾,
date(now()) as 現在日期,
DATE_FORMAT(now(),'%Y-%m') as 年月
           

─=≡Σ(((つ•̀ω•́)つ

繼續戰鬥

張小森：整理了70個Python實戰項目清單，都有完整且詳細的教程