pandasの処理速度検証

test1.py

"""DataFrameでdatetime変換する時の速度比較. date formatなし vs date formatあり"""
import pandas as pd
import timeit

def read_csv_with_no_dateformat():
    # 2列8760行のCSV
    df = pd.read_csv('testdata.csv')
    df['date_time'] = pd.to_datetime(df['date_time'])

def read_csv_with_dateformat():
    df = pd.read_csv('testdata.csv')
    df['date_time'] = pd.to_datetime(df['date_time'], format='%d/%m/%y %H:%M')

if __name__ == '__main__':
    result1 = timeit.timeit(read_csv_with_no_dateformat, number=3)
    print(f'format指定なし\n  > {round(result1, 3)}s')

    result2 = timeit.timeit(read_csv_with_dateformat, number=3)
    print(f'format指定あり\n  > {round(result2, 3)}s')

    print(f'指定ありのほうが指定なしより{round(result1 / result2)}倍高速')

test2.py

"""DataFrameの各行に異なる計算をする場合の速度比較
* 普通のループ（indexアクセス）
* 普通のループ（イテレーション）
* df.apply & lambda
* isinでグループごとに計算
* cutでグループごとに計算
→上から下に行くにつれて速くなる
"""
import pandas as pd
from timeit import timeit

df = pd.DataFrame()

def apply_tariff(kwh, hour):
    """1日の内の時間帯に応じたコストを返す"""
    if 0 <= hour < 7:
        rate = 12
    elif 7 <= hour < 17:
        rate = 20
    elif 17 <= hour < 24:
        rate = 28
    else:
        raise ValueError(f'Invalid hour: {hour}')
    return rate * kwh

def apply_tariff_loop():
    """Calculate costs in loop.  Modifies `df` inplace."""
    energy_cost_list = []
    for i in range(len(df)): # apply_tariff_iterrows()とここだけ違う
        # Get electricity used and hour of day
        energy_used = df.iloc[i]['energy_kwh']
        hour = df.iloc[i]['date_time'].hour
        energy_cost = apply_tariff(energy_used, hour)
        energy_cost_list.append(energy_cost)
    df['cost_cents'] = energy_cost_list

def apply_tariff_iterrows():
    energy_cost_list = []
    for index, row in df.iterrows(): # apply_tariff_loopとここだけ違う
        # Get electricity used and hour of day
        energy_used = row['energy_kwh']
        hour = row['date_time'].hour
        # Append cost list
        energy_cost = apply_tariff(energy_used, hour)
        energy_cost_list.append(energy_cost)
    df['cost_cents'] = energy_cost_list

def apply_tariff_withapply():
    """DataFrame.applyを使って各行の計算"""
    df['cost_cents'] = df.apply(
        lambda row: apply_tariff(kwh=row['energy_kwh'], hour=row['date_time'].hour),
        axis=1
    )

def apply_tariff_isin():
    # Define hour range Boolean arrays
    peak_hours = df.index.hour.isin(range(17, 24))
    shoulder_hours = df.index.hour.isin(range(7, 17))
    off_peak_hours = df.index.hour.isin(range(0, 7))

    # Apply tariffs to hour ranges
    df.loc[peak_hours, 'cost_cents'] = df.loc[peak_hours, 'energy_kwh'] * 28
    df.loc[shoulder_hours,'cost_cents'] = df.loc[shoulder_hours, 'energy_kwh'] * 20
    df.loc[off_peak_hours,'cost_cents'] = df.loc[off_peak_hours, 'energy_kwh'] * 12

def apply_tariff_cut():
    cents_per_kwh = pd.cut(x=df.index.hour,
                           bins=[0, 7, 17, 24],
                           include_lowest=True,
                           labels=[12, 20, 28]).astype(int)
    # print(f'{cents_per_kwh=}')
    df['cost_cents'] = cents_per_kwh * df['energy_kwh']

if __name__ == '__main__':
    df = pd.read_csv('testdata.csv')
    df['date_time'] = pd.to_datetime(df['date_time'], format='%d/%m/%y %H:%M')

    result1 = timeit(apply_tariff_loop, number=3)
    print(f'普通のループ(indexアクセス)\n  > {round(result1, 3)}s')

    result2 = timeit(apply_tariff_iterrows, number=3)
    print(f'普通のループ(イテレーション)\n  > {round(result2, 3)}s')

    result3 = timeit(apply_tariff_withapply, number=3)
    print(f'df.apply & lambda\n  > {round(result3, 3)}s')

    df.set_index('date_time', inplace=True)
    result4 = timeit(apply_tariff_isin, number=3)
    print(f'isinでグループごとに計算\n  > {round(result4, 3)}s')

    result5 = timeit(apply_tariff_cut, number=3)
    print(f'cutでグループごとに計算\n  > {round(result5, 3)}s')

    print(f'結論: cutの方がdf.applyより{round(result3 / result5)}倍高速')

test3.py

"""DataFrameで各行計算する時の速度比較. df.apply vs np.vectorize"""
import pandas as pd
import numpy as np
from timeit import timeit
from typing import Union
from decimal import Decimal, ROUND_HALF_UP

df = pd.DataFrame()

class RoundUtil:
  @staticmethod
  def round(value: Union[int, float], digit: int = 0) -> Decimal:
    """
    python3のroundは一般的な四捨五入ではないので、Decimalを使って一般的な四捨五入のroundを行う
    https://tvision-jp.backlog.com/view/INFRA-2334#comment-64959155
    """
    exp = '0' if digit == 0 else '0.' + ''.join(['0' for x in range(digit)])
    return Decimal(str(value)).quantize(Decimal(exp), rounding=ROUND_HALF_UP)

def df_apply():
    df['z'] = df.apply(lambda series: series['x'] / series['y'] * 100, axis=1)
    print('after df_apply----')
    print(df.head)

def df_series():
    df['z'] = df['x'] / df['y'] * 100
    print('after df_series----')
    print(df.head)

def np_vectorize():
    df['z'] = pd.Series(
        np.vectorize(lambda x,y: x / y * 100)(df['x'], df['y']), index=df.index)
    print('after np_vectorize----')
    print(df.head)

def makedata():
    """テストデータ生成"""
    np.random.seed(0)
    N = 100000
    df = pd.DataFrame({'x': np.random.randn(N),
                       'y': np.random.randn(N)})
    return df


if __name__ == '__main__':
    df = makedata()
    result1 = timeit(df_apply, number=2)
    result1_2 = timeit(df_series, number=2)
    result2 = timeit(np_vectorize, number=2)

    print(f'df.apply\n  > {round(result1, 3)}s')
    print(f'df series\n  > {round(result1_2, 3)}s')
    print(f'np.vectorize\n  > {round(result2, 3)}s')

    print(f'df seriesの方がdf.applyより{round(result1 / result1_2)}倍高速')
    print(f'np.vectorizeの方がdf.applyより{round(result1 / result2)}倍高速')

testdata.csv