一場pandas與SQL的巔峰大戰（三）

點擊上方“超哥的雜貨鋪”，輕鬆關注

本文目錄如下：

在前兩篇文章中，我們從多個角度，由淺入深，對比了pandas和SQL在數據處理方面常見的一些操作。

具體來講，第一篇文章

一場pandas與SQL的巔峰大戰

涉及到數據查看，去重計數，條件選擇，合併連接，分組排序等操作。

涉及到等操作。

第二篇文章

一場pandas與SQL的巔峰大戰（二）

涉及字符串處理，窗口函數，行列轉換， 類型轉換等操作。您可以點擊往期鏈接進行閱讀回顧。

涉及等操作。您可以點擊往期鏈接進行閱讀回顧。

在日常工作中，我們經常會與日期類型打交道，會在不同的日期格式之間轉來轉去。

本文依然沿著前兩篇文章的思路，對pandas和SQL中的日期操作進行總結，其中SQL採用Hive SQL+MySQL兩種方式，內容與前兩篇相對獨立又彼此互為補充。一起開始學習吧！

◆ ◆ ◆ ◆ ◆

數據概況

數據方面，我們依然採用前面文章的訂單數據，樣例如下。在正式開始學習之前，我們需要把數據加載到dataframe和數據表中。本文的數據、代碼以及清晰的PDF版本可以在公眾號後臺回覆“對比三”獲取哦~

pandas加載數據

<code>
import
 pandas 
as
 pd
data
 = pd.read_excel(
'order.xlsx'
)
#data2 = pd.read_excel(
'order.xlsx'
, parse_dates=[
'ts'
])
data
.head()
data
.dtypes
/<code>

需要指出，pandas讀取數據對於日期類型有特殊的支持。

無論是在read_csv中還是在read_excel中，都有parse_dates參數，可以把數據集中的一列或多列轉成pandas中的日期格式。

上面代碼中的data是使用默認的參數讀取的，在data.dtypes的結果中ts列是<code>datetime64[ns]/<code>格式，而data2是顯式指定了ts為日期列，因此data2的ts類型也是<code>datetime[ns]/<code>。

如果在使用默認方法讀取時，日期列沒有成功轉換，就可以使用類似data2這樣顯式指定的方式。

MySQL加載數據

我準備了一個sql文件<code>t_order.sql/<code>，推薦使用navicate客戶端，按照上圖所示方式，直接導入即可。

Hive加載數據

<code>
create
 
table
 
`t_order`
(
`id`
 
int
,
`ts`
 
string
,
`uid`
 
string
,
`orderid`
  
string
,
`amount`
 
float

)
row
 
format
 
delimited
 
fields
 
terminated
 
by
 
','

stored
 
as 
 textfile;

load
 
data
 
local
 inpath 
't_order.csv'
 overwrite 
into
 
table
 t_order;
select
 * 
from
 t_order 
limit
 
20
;
/<code>

在hive中加載數據我們需要先建立表，然後把文本文件中的數據load到表中，結果如下圖所示。

我們在MySQL和Hive中都把時間存儲成字符串，這在工作中比較常見，使用起來也比較靈活和習慣，因此沒有使用專門的日期類型。

開始學習

我們把日期相關的操作分為日期獲取，日期轉換，日期計算三類。下面開始逐一學習。

日期獲取

1.獲取當前日期，年月日時分秒

pandas中可以使用now()函數獲取當前時間，但需要再進行一次格式化操作來調整顯示的格式。我們在數據集上新加一列當前時間的操作如下：

MySQL有多個函數可以獲取當前時間：

now()，current_timestamp，current_timestamp()，sysdate()，localtime()，localtime，localtimestamp，localtimestamp()等。

點擊圖片查看大圖

hive中獲取當前時間，可以使用 current_timestamp()， current_timestamp，得到的是帶有毫秒的，如果想保持和上面同樣的格式，需要使用substr截取一下。如下圖所示：

圖中代碼：

<code>
data[
'current_dt'
] = pd.datetime.now()
data
[
'current_dt'
] = 
data
[
'current_dt'
].apply(lambda x : x.strftime(
'%Y-%m-%d %H:%M:%S'
))
data
.head()



SELECT
 *, 
now
(),
current_timestamp
(),
current_timestamp

FROM
 
`t_order`
;
SELECT
 *, 
sysdate
(),ocaltime(),
localtime

FROM
 
`t_order` 
;
SELECT
 *, 
localtimestamp
, 
localtimestamp
()
FROM
 
`t_order`
;


select
 *, 
substr
(
current_timestamp
, 
1
, 
19
), 
substr
(
current_timestamp
(), 
1
, 
19
) 
from
 t_order 
limit
 
20
 
;
/<code>

2.獲取當前時間，年月日

pandas中似乎沒有直接獲取當前日期的方法，我們沿用上一小節中思路，進行格式轉換得到當前日期。當然這不代表python中的其他模塊不能實現，有興趣的朋友可以自己查閱相關文檔。

MySQL中可以直接獲取當前日期，使用curdate()即可，hive中也有相對應的函數：current_date()。

圖片中的代碼：

<code>
data[
'dt_date'
] = pd.datetime.now().strftime(
'%Y-%m-%d'
)

data.head()






 

/<code>

3.提取日期中的相關信息

日期中包含有年月日時分秒，我們可以用相應的函數進行分別提取。下面我們提取一下ts字段中的天,時間,年,月,日,時,分,秒信息。

在MySQL和Hive中，由於ts字段是字符串格式存儲的，我們只需使用字符串截取函數即可。兩者的代碼是一樣的，只需要注意截取的位置和長度即可，效果如下：

圖片中代碼：

<code>
data[
'dt_day'
] = 
data
[
'ts'
].dt.date
data
[
'year'
] = 
data
[
'ts'
].dt.year
data
[
'month'
] = 
data
[
'ts'
].dt.month
data
[
'day'
 
] = 
data
[
'ts'
].dt.day
data
[
'dt_time'
] = 
data
[
'ts'
].dt.time
data
[
'hour'
] = 
data
[
'ts'
].dt.hour
data
[
'minute'
] = 
data
[
'ts'
].dt.minute
data
[
 
'second'
] = 
data
[
'ts'
].dt.second
data
.head()


select
 ts, 
substr
(ts, 
1
, 
10
), 
substr
(ts, 
1
, 
4
), 
substr
(ts, 
6
, 
2
), 
substr
(ts,  
9
, 
2
), 
substr
(ts, 
12
, 
8
), 
substr
(ts, 
12
, 
2
), 
substr
(ts, 
15
, 
2
), 
substr
(ts, 
18
, 
2
)
from t_order; 



select
 ts, 
substr
(ts, 
1
, 
10
), 
substr
(ts, 
1
, 
4
), 
substr
(ts, 
6
, 
2
), 
substr
(ts, 
9
, 
2
), 
substr
(ts, 
12 
, 
8
), 
substr
(ts, 
12
, 
2
), 
substr
(ts, 
15
, 
2
), 
substr
(ts, 
18
, 
2
)
from t_order limit 
20
;
/<code>

日期轉換

1.可讀日期轉換為unix時間戳

在pandas中，我找到的方法是先將<code>datetime64[ns]/<code>轉換為字符串，再調用time模塊來實現，代碼如下：

可以驗證最後一列的十位數字就是ts的時間戳形式。

ps.在此之前，我嘗試了另外一種藉助numpy的方式，進行類型的轉換，但轉出來結果不正確，比期望的結果多8個小時，我寫在這裡，歡迎有經驗的讀者指正。

<code>
import
 numpy 
as
 np
data
[
'ts_timestamp'
] = (
data
.ts.astype(np.int64)/
1e9
).astype(np.int64)
data
.head()
#得到的ts_timestamp結果
#
1564650940
  
1564653606
 
1564653875
等剛好比正確的結果多
8
個小時
/<code>

MySQL和Hive中可以使用時間戳轉換函數進行這項操作，其中MySQL得到的是小數形式，需要進行一下類型轉換，Hive不需要。

圖中代碼：

<code>
def 
transfer_time_format
(
x
):
 
import
 time
 tmp_time 
= time.strptime(x,  
'%Y-%m-%d %H:%M:%S'
)
 res_time = 
int
(time.mktime(tmp_time))
 
return
 res_time

data[
'str_ts'
] = 
data
[
'ts'
].dt.strftime(
'%Y-%m-%d %H:%M:%S'
)
data
[
'str_timestamp'
] = 
data
[
'str_ts'
].apply(transfer_time_format)
data
.head()




select 
 *, cast(unix_timestamp(ts) 
as
 
int
)
from
 t_order;


select
 *, unix_timestamp(ts) 
from
 t_order limit 
20
;
/<code>

2.unix時間戳轉換為可讀日期

這一操作為上一小節的逆向操作。

在pandas中，我們看一下如何將str_timestamp列轉換為原來的ts列。這裡依然採用time模塊中的方法來實現。

ps.你可能發現了上面代碼中有一列是ori_dt，雖然看上去是正確的，但格式多少有那麼點奇怪，這也是我在學習過程中看到的一個不那麼正確的寫法，貼出來供大家思考。

<code>
data
[
'ori_dt'
] = pd.to_datetime(
data
[
'str_timestamp'
].values, unit=
's'
, utc=
True
).tz_convert(
'Asia/Shanghai'
)
data
.head()


/<code>

回到MySQL和Hive，依然只是用一個函數就解決了。

圖中代碼如下：

<code>
def transfer_time_format2(x):
 
import
 time
 time_local = time.localtime(x)
 res_time = time.strftime(
'%Y-%m-%d %H:%M:%S'
, time_local)
 
return
 res_time
data[
'ori_ts'
] =  
data
[
'str_timestamp'
].apply(transfer_time_format2)
data
.head()


select
 *, from_unixtime(
cast
(
unix_timestamp
(ts) 
as
 
int
))
from
 t_order;


select
 *, from_unixtime(
unix_timestamp
(ts)) 
from
 t_order 
limit
 
20
;
/<code>

3.10位日期轉8位

對於初始是ts列這樣年月日時分秒的形式，我們通常需要先轉換為10位年月日的格式，再把中間的橫槓替換掉，就可以得到8位的日期了。

由於打算使用字符串替換，我們先要將ts轉換為字符串的形式，在前面的轉換中，我們生成了一列str_ts，該列的數據類型是object，相當於字符串，可以在此基礎上進行這裡的轉換。

MySQL和Hive中也是同樣的套路，截取和替換幾乎是最簡便的方法了。

圖中代碼：

<code>
data[
'str_ts_8'
] = 
data
[
'str_ts'
].astype(str).str[:
10
].apply(lambda x: x.replace(
'-'
,
''
))
data
.head()


select
 
replace
(
substr
(ts, 
1
, 
10
), 
'-'
, 
''
) 
from 
 t_order;


select
 *, regexp_replace(
substr
(ts, 
1
, 
10
),
'-'
,
''
)
from
 t_order 
limit
 
20
;
/<code>

當然，我們也有另外的解法：使用先將字符串轉為unix時間戳的形式，再格式化為8位的日期。

圖中代碼：

<code>
select
 *, 
from_unixtime
(
cast
(
unix_timestamp
(ts)  
as
 
int
), 
'%Y%M%d'
)
from
 
t_order
;


select
 *, 
from_unixtime
(
unix_timestamp
(ts),
'yyyyMMdd'
) 
from
 
t_order
 
limit
 
20
;
/<code>

pandas中我們也可以直接在unix時間戳的基礎上進行操作，轉為8位的日期。具體做法只要上面的transfer_time_format2函數即可，效果如下圖所示。

<code>
def
 
transfer_time_format3
(x)
:

 
import
 time 

 time_local = time.localtime(x)
 res_time = time.strftime(
'%Y%m%d'
, time_local)
 
return
 res_time
data[
'str_ts_8_2'
] = 
data
[
'str_timestamp'
].apply(transfer_time_format3)
data
.head()
/<code>

4.8位日期轉10位

這一操作同樣為上一小節的逆向操作。

結合上一小節，實現10位轉8位，我們至少有兩種思路。可以進行先截取後拼接，把橫線<code>-/<code>拼接在日期之間即可。二是藉助於unix時間戳進行中轉。SQL中兩種方法都很容易實現，在pandas我們還有另外的方式。

方法一：

pandas中的拼接也是需要轉化為字符串進行。如下：

MySQL和Hive中，可以使用concat函數進行拼接：

圖中代碼如下：

<code>
data[
'str_ts_10'
] = 
data
[
'str_ts_8'
].apply(lambda x : x[:
4
] + 
"-"
 + x[ 
4
:
6
] + 
"-"
 + x[
6
:])
data
.head()


select
 
id
, ts, 
concat
(
substr
(dt8, 
1
, 
4
), 
'-'
, 
substr
(dt8, 
5
, 
2
), 
'-'
, 
substr
(dt8,  
7
,
2
))
from

(
select
 *, 
replace
(
substr
(ts, 
1
, 
10
), 
'-'
, 
''
) 
as
 dt8
from
 t_order
) a 


select
 
id
, ts,  
concat
(
substr
(dt8, 
1
, 
4
), 
'-'
, 
substr
(dt8, 
5
, 
2
), 
'-'
, 
substr
(dt8, 
7
,
2
))
from

(
select
 *, regexp_replace(
substr
(ts, 
 
1
, 
10
),
'-'
,
''
) 
as
 dt8
from
 t_order
) a 
limit
 
20
;
/<code>

方法二，通過unix時間戳轉換：

在pandas中，藉助unix時間戳轉換並不方便，我們可以使用datetime模塊的格式化函數來實現，如下所示。

Mysql和Hive中unix_timestamp接收的參數不一樣，前者必須輸入為整數，後者可以為字符串。我們的目標是輸入一個8位的時間字符串，輸出一個10位的時間字符串。由於原始數據集中沒有8位時間，我們臨時構造了一個。代碼如下：

圖中代碼如下：

<code>
def transfer_time_format4(x):
 from datetime 
import
 datetime
 tmp_time = datetime.strptime(
'20190801'
, 
'%Y%m%d'
)
 res_time = datetime.strftime(tmp_time, 
'%Y-%m-%d'
)
 
return
 res_time
data[
'str_ts_10_2'
] = 
data
[
'str_ts_8'
].apply(transfer_time_format4)
data
.head()


select
 *, 
replace
(
substr
(ts, 
1
, 
10
 
),
'-'
, 
''
),
from_unixtime(
unix_timestamp
(
cast
(
replace
(
substr
(ts, 
1
, 
10
),
'-'
, 
''
)
as
 
int
)),
'%Y-%m-%d'
)
from
 t_order
;

 

select
 *, 
regexp_replace(
substr
(ts, 
1
, 
10
),
'-'
, 
''
),
from_unixtime(
unix_timestamp
(regexp_replace(
substr
(ts, 
1
, 
10
),
'-'
, 
''
), 
'yyyyMMdd'
),
'yyyy-MM-dd'
 
)
from
 t_order
limit
 
20

;
/<code>

ps.關於時間Hive中的時間轉換，我在之前總結Hive函數的文章的最後一部分中已經有過梳理，例子比此處更加具體，歡迎翻閱：

常用Hive函數的學習和總結

日期計算

日期計算主要包括日期間隔(加減一個數變為另一個日期)和計算兩個日期之間的差值。

1.日期間隔

pandas中對於日期間隔的計算需要藉助datetime 模塊。我們來看一下如何計算ts之後5天和之前3天。

使用timedelta函數既可以實現天為單位的日期間隔，也可以按周，分鐘，秒等進行計算。

在MySQL和Hive中有相應的日期間隔函數date_add，date_sub函數，但使用的格式略有差異。

需要注意的是Hive計算的結果沒有時分秒，如果需要，依然可以使用拼接的方式獲得，此處略。

2.日期差

這一小節仍然是上一小節的逆操作。(怎麼這麼多逆操作，累不累啊......)我們來看一下如何計算兩個時間的日期差。

在pandas中，如果事件類型是datetime64[ns]類型，直接作差就可以得出日期差，但是得到的數據後面還有一個"days"的單位，這其實就是上一小節提到的timedelta類型。

為了便於使用，我們使用map函數獲取其days屬性，得到我們想要的數值的差。如下所示：

如果不是datetime格式，可以先用下面的代碼進行一次轉換。

<code>
data
[
'dt_ts'
] = pd.to_datetime(
data
[
'str_ts'
], 
format
=
'%Y-%m-%d %H:%M:%S'
)
/<code>

Hive和MySQL中的日期差有相應的函數datediff。但需要注意它的輸入格式。

可以看到輸入的形式既可以是具體到時分秒的格式，也可以是年月日格式。但是要注意Hive中輸入的日期必須是10位的格式，否則得不到正確的結果，比如輸入8位的，結果會是NULL，而MySQL則可以進行8位日期的計算。

◆ ◆ ◆ ◆ ◆

小結

本文涉及到的對比操作和相應的解法如上圖所示。整體看起來比之前的要“亂”一些，但仔細看看並沒有多少內容。

需要指出，關於日期操作，本文只是總結了一些pandas和SQL都有的部分操作，也都是比較常見的。python中和SQL本身關於日期操作還有很多其他用法，限於時間關係就省略了。

由於時間匆忙，行文不當之處還請多多包含。如果你有好的想法，歡迎一起交流學習。本文的代碼和數據可以在公眾號後臺回覆“對比三”獲取，祝學習愉快！

以清淨心看世界；

用歡喜心過生活。

超哥的雜貨鋪，你值得擁有~

添加微信hitchenghengchao進入交流群~

長按二維碼關注我們

推薦閱讀：

1.

一場pandas與SQL的巔峰大戰

2.

一場pandas與SQL的巔峰大戰（二）

3.

常用Hive函數的學習和總結