pandasによる高度なデータ変換

Python で学ぶ ETL と ELT

Jake Roach

Data Engineer

pandasによる高度なデータ変換

変換コンポーネントが強調されたETLパイプライン。

Python で学ぶ ETL と ELT

pandasによる欠損値の補完

timestamps           volume      open     close             
1997-05-15 13:30:00  1443120000  0.121875  0.097917
1997-05-16 13:30:00   294000000  NaN       0.086458
1997-05-19 13:30:00   122136000  0.088021  NaN

# Fill all NaN with value 0
clean_stock_data = raw_stock_data.fillna(value=0)

timestamps           volume      open     close             
1997-05-15 13:30:00  1443120000  0.121875  0.097917
1997-05-16 13:30:00   294000000  0.000000  0.086458
1997-05-19 13:30:00   122136000  0.088021  0.000000
Python で学ぶ ETL と ELT

pandasによる欠損値の補完

timestamps           volume      open     close             
1997-05-15 13:30:00  1443120000  0.121875  0.097917
1997-05-16 13:30:00   294000000  NaN       0.086458
1997-05-19 13:30:00   122136000  0.088021  NaN

# Fill NaN values with specific value for each column
clean_stock_data = raw_stock_data.fillna(value={"open": 0, "close": .5}, axis=1)

timestamps           volume      open     close             
1997-05-15 13:30:00  1443120000  0.121875  0.097917
1997-05-16 13:30:00   294000000  0.000000  0.086458
1997-05-19 13:30:00   122136000  0.088021  0.500000
Python で学ぶ ETL と ELT

pandasによる欠損値の補完

timestamps           volume      open     close             
1997-05-15 13:30:00  1443120000  0.121875  0.097917
1997-05-16 13:30:00   294000000  NaN       0.086458
1997-05-19 13:30:00   122136000  0.088021  NaN

# Fill NaN value using other columns
raw_stock_data["open"].fillna(raw_stock_data["close"], inplace=True)

timestamps           volume      open     close             
1997-05-15 13:30:00  1443120000  0.121875  0.097917
1997-05-16 13:30:00   294000000  0.086458  0.086458
1997-05-19 13:30:00   122136000  0.088021  NaN
Python で学ぶ ETL と ELT

データのグループ化

SELECT
    ticker,
    AVG(volume),
    AVG(open),
    AVG(close)
FROM raw_stock_data
GROUP BY ticker;

.groupby() メソッドで上記クエリを pandas により再現できます

Python で学ぶ ETL と ELT

pandasによるデータのグループ化

ticker  volume        open        close             
AAPL    1443120000    0.121875    0.097917
AAPL     297000000    0.098146    0.086458
AMZN     124186000    0.247511    0.251290

# Use Python to group data by ticker, find the mean of the reamining columns
grouped_stock_data = raw_stock_data.groupby(by=["ticker"], axis=0).mean()

          volume        open        close
ticker               
AAPL      1.149287e+08    34.998377  34.986851
AMZN      1.434213e+08    30.844692  30.830233

.min()、.max()、.sum() でデータを集約できます

Python で学ぶ ETL と ELT

DataFrameへの高度な変換の適用

.apply() メソッドでより高度な変換が可能です

def classify_change(row):
    change = row["close"] - row["open"]
    if change > 0:
        return "Increase"
    else:
        return "Decrease"

# Apply transformation to DataFrame
raw_stock_data["change"] = raw_stock_data.apply(
    classify_change, 
    axis=1
)

変換前

ticker  ...  open        close             
AAPL         0.121875    0.097917
AAPL         0.098146    0.086458
AMZN         0.247511    0.251290

$$

変換後

ticker  ...  open        close       change  
AAPL         0.121875    0.097917    Decrease
AAPL         0.098146    0.086458    Decrease
AMZN         0.247511    0.251290    Increase
Python で学ぶ ETL と ELT

練習しましょう！

Python で学ぶ ETL と ELT

Preparing Video For Download...