BUG: why arrow no affect in Intel chips？

[wonb168]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

pd.options.mode.copy_on_write = True

def ensure_arrow_format(df):
        if not isinstance(df._mgr, pd.core.internals.ArrayManager):  # 检查是否已经是 Arrow 格式
            return pa.Table.from_pandas(df).to_pandas()
        return df

Issue Description

I updated pandas from 2.1.4 to 2.2.3,
and open copy on write,
and convert to arrow before df merge,
then the cost from 61s to 36s,
faster.

Expected Behavior

My pc is mac mini m2, from 61s to 36s,
but in x86 centos or win10, only from 109 to 102,
why no affect in intel cpu?

Installed Versions

pandas 2.2.3

[rhshadrach]

Thanks for the report. Can you provide a fully reproducible example. We need the DataFrame you are operating on to be able to look further into the issue.

pa.Table.from_pandas(df).to_pandas()

In addition, it would be helpful to report benchmarks for each of these two operations.

pa_table = pa.Table.from_pandas(df)
result = pa_table.to_pandas()

[wonb168]

data in pickle.zip，

Link: https://pan.baidu.com/s/1ezuj2uPf6rBg2g1prSEsGQ?pwd=j21g code: j21g

and code：

#%%
import pandas as pd
import pyarrow as pa
from typing import Literal
from collections.abc import Iterable
import time
# %%
pd.options.mode.copy_on_write = True
def df_merge(
    left,
    right,
    how: Literal["left", "right", "inner", "outer", "cross"] = "inner",
    on=None,
    left_on=None,
    right_on=None,
    left_index: bool = False,
    right_index: bool = False,
    sort: bool = False,
    suffixes=("_x", "_y"),
    copy: bool = True,
    indicator: bool = False,
    validate=None,
):
    """使用优化的 DuckDB SQL 实现的 DataFrame merge 函数，针对特定场景优化
    """
    # 将数据转换为 Arrow 表
    # 检查是否需要转换为 Arrow 后端
    # if not pd.api.types.is_dtype_backend(left, "pyarrow"):
    #     left = pa.Table.from_pandas(left).to_pandas(use_threads=True)
    # if not pd.api.types.is_dtype_backend(right, "pyarrow"):
    #     right = pa.Table.from_pandas(right).to_pandas(use_threads=True)

    try:
        left = left.convert_dtypes(dtype_backend="pyarrow")
        right = right.convert_dtypes(dtype_backend="pyarrow")
    except:
        # 如果转换失败，使用传统方式转换
        left = pa.Table.from_pandas(left).to_pandas(use_threads=True)
        right = pa.Table.from_pandas(right).to_pandas(use_threads=True)

    if left.empty and right.empty:
        columns_left = left.columns
        columns_right = right.columns
        if isinstance(on, str):
            on = [on]

        if on is None:
            on_set = set(columns_left) & set(columns_right)
            on = list(on_set)
        else:
            on_set = set(on)

        # 除了用以on之外，left和right共有的columns
        common_columns_but_on = list((set(columns_left) & set(columns_right)) - on_set)
        # left单属columns
        columns_left_only = list(set(columns_left) - on_set - set(common_columns_but_on))
        # right单属columns
        columns_right_only = list(set(columns_right) - on_set - set(common_columns_but_on))

        # 最终要形成的dataframe中含有的列
        return_columns = (
            on
            + columns_left_only
            + [col + suffixes[0] for col in common_columns_but_on]
            + columns_right_only
            + [col + suffixes[1] for col in common_columns_but_on]
        )
        result = pd.DataFrame(columns=return_columns)
        return result

    if left.empty or right.empty:
        result = pd.merge(
            left,
            right,
            how=how,
            on=on,
            left_on=left_on,
            right_on=right_on,
            left_index=left_index,
            right_index=right_index,
            sort=sort,
            suffixes=suffixes,
            copy=copy,
            indicator=indicator,
            validate=validate,
        )
        return result

    if suffixes[0]:
        columns_left = left.columns
        columns_right = right.columns
        if isinstance(on, str):
            on = [on]

        if on is None:
            on_set = set(columns_left) & set(columns_right)
        else:
            on_set = set(on)

        # 除了用以on之外，left和right共有的columns
        common_columns_but_on = list((set(columns_left) & set(columns_right)) - on_set)
        if common_columns_but_on:
            left = left.rename(columns={x: x + suffixes[0] for x in common_columns_but_on})

    # 不管大小都用pd.merge（若慢，后面改 duckdb 的 sql join）       
    result = pd.merge(left, right.set_index(on), left_on=on, how=how, right_index=True, suffixes=suffixes)

    return result


def df_merge2(
    left,
    right,
    how: Literal["left", "right", "inner", "outer", "cross"] = "inner",
    on=None,
    left_on=None,
    right_on=None,
    left_index: bool = False,
    right_index: bool = False,
    sort: bool = False,
    suffixes=("_x", "_y"),
    copy: bool = True,
    indicator: bool = False,
    validate=None,
):
    if left.empty and right.empty:
        columns_left = left.columns
        columns_right = right.columns
        if isinstance(on, str):
            on = [on]

        if on is None:
            on_set = set(columns_left) & set(columns_right)
            on = list(on_set)
        else:
            on_set = set(on)

        # 除了用以on之外，left和right共有的columns
        common_columns_but_on = list((set(columns_left) & set(columns_right)) - on_set)
        # left单属columns
        columns_left_only = list(set(columns_left) - on_set - set(common_columns_but_on))
        # right单属columns
        columns_right_only = list(set(columns_right) - on_set - set(common_columns_but_on))

        # 最终要形成的dataframe中含有的列
        return_columns = (
            on
            + columns_left_only
            + [col + suffixes[0] for col in common_columns_but_on]
            + columns_right_only
            + [col + suffixes[1] for col in common_columns_but_on]
        )
        result = pd.DataFrame(columns=return_columns)
        return result

    result = pd.merge(
        left,
        right,
        how=how,
        on=on,
        left_on=left_on,
        right_on=right_on,
        left_index=left_index,
        right_index=right_index,
        sort=sort,
        suffixes=suffixes,
        copy=copy,
        indicator=indicator,
        validate=validate,
    )
    return result
# %%
sku_store_base=pd.read_pickle('sku_store_base.pickle')
print(len(sku_store_base))
# %%
stock_move_relationship=pd.read_pickle('stock_move_relationship.pickle')
print(len(stock_move_relationship))


# %%
t=time.time()
rst2=df_merge2(sku_store_base, stock_move_relationship.rename({'receive_stockorg_sk': 'stockorg_sk'}, axis=1)[['stockorg_sk', 'min_qty_per_pkg']].drop_duplicates(), on=['stockorg_sk'], how='left')
print(len(rst2))
print(round(time.time()-t,2))

# %%

t=time.time()
rst2=df_merge(sku_store_base, stock_move_relationship.rename({'receive_stockorg_sk': 'stockorg_sk'}, axis=1), on=['stockorg_sk'], how='left', suffixes=('_x', '_y'))
print(len(rst2))
print(round(time.time()-t,2))
# %%
t=time.time()
sku_store_base = sku_store_base.convert_dtypes(dtype_backend="pyarrow")
print(round(time.time()-t,2))
#%%
stock_move_relationship = stock_move_relationship.convert_dtypes(dtype_backend="pyarrow")
#%%
t=time.time()
rst2=df_merge(sku_store_base, stock_move_relationship.rename({'receive_stockorg_sk': 'stockorg_sk'}, axis=1), on=['stockorg_sk'], how='left', suffixes=('_x', '_y'))
print(len(rst2))
print(round(time.time()-t,2))