{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":50160,"databundleVersionId":7921029,"sourceType":"competition"}],"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\n\ndef find_most_similar_row(df1, df2):\n    df2['matched'] = False\n    df2['score'] = df2['score'].astype(float)\n    df2['diff_sum'] = 0.0\n\n    for i, row1 in df1.iterrows():\n        min_diff_sum = np.inf\n        most_similar_row = None\n\n        for j, row2 in df2.iterrows():\n            if not row2['matched']:\n                diff_sum = 0.0\n\n                for col in df1.columns:\n                    if col != 'target' and isinstance(row1[col], (int, float)):\n                        diff_sum += abs(row1[col] - row2[col])\n\n                if diff_sum < min_diff_sum:\n                    min_diff_sum = diff_sum\n                    most_similar_row = j\n\n        if most_similar_row is not None:\n            df2.at[most_similar_row, 'score'] -= 0.04\n            df2.at[most_similar_row, 'matched'] = True\n            df2.at[most_similar_row, 'diff_sum'] = min_diff_sum\n\n    return df2\n\n\ndf1 = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'target': ['x', 'y', 'z']})\ndf2 = pd.DataFrame({'A': [1.1, 2.2, 3.3, 5.5], 'B': [4.4, 5.5, 6.6, 8.8], 'score': [0.5, 0.6, 0.7, 0.8]})\n\nresult_df = find_most_similar_row(df1, df2)\nprint(result_df)\ndf2 = df2.drop(columns=[\"matched\"])\ndf2 = df2.drop(columns=[\"diff_sum\"])\nprint(df2)","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\n\ndef find_most_similar_row_fast(df1, df2):\n    df2['matched'] = False\n    df2['score'] = df2['score'].astype(float)\n    df2['diff_sum'] = 0.0\n\n    # 转换为NumPy数组\n    arr1 = df1.drop(columns='target').values\n    arr2 = df2.drop(columns=['score', 'matched', 'diff_sum']).values\n\n    # 计算差值矩阵\n    diff_matrix = np.abs(arr1[:, np.newaxis] - arr2)\n\n    # 计算每一行的差值和\n    diff_sums = np.sum(diff_matrix, axis=2)\n\n    # 找到最相似的行\n    most_similar_indices = np.argmin(diff_sums, axis=1)\n\n    for i, most_similar_row in enumerate(most_similar_indices):\n        df2.at[most_similar_row, 'score'] -= 0.04\n        df2.at[most_similar_row, 'matched'] = True\n        df2.at[most_similar_row, 'diff_sum'] = diff_sums[i, most_similar_row]\n\n    return df2\n\ndf1 = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'target': ['x', 'y', 'z']})\ndf2 = pd.DataFrame({'A': [1.1, 2.2, 3.3, 5.5], 'B': [4.4, 5.5, 6.6, 8.8], 'score': [0.5, 0.6, 0.7, 0.8]})\n\nresult_df = find_most_similar_row_fast(df1, df2)\nprint(result_df)\ndf2 = df2.drop(columns=[\"matched\"])\ndf2 = df2.drop(columns=[\"diff_sum\"])\nprint(df2)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"! python -m pip install -U setuptools pip\n!pip install cupy-cuda11x","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nimport pandas as pd\nimport cupy as cp\n\ndef find_most_similar_row_optimized(df1, df2):\n    df2['matched'] = False\n    df2['score'] = 0.0\n    df2['diff_sum'] = 0.0\n\n    # 选择数值型列\n    arr1 = df1.select_dtypes(include=[np.number]).values\n    arr2 = df2.drop(columns=['score', 'matched', 'diff_sum', \"WEEK_NUM\", \"case_id\"]).select_dtypes(include=[np.number]).values\n    \n    arr1 = arr1[:, :5]\n    arr2 = arr2[:, :5]\n    \n    # 处理缺失值\n    arr1[np.isnan(arr1)] = 0\n    arr2[np.isnan(arr2)] = 0\n\n    # 计算每一行的差值和\n    for i in range(len(arr1)):\n        diff_sum_row = np.sum(np.abs(arr1[i] - arr2), axis=1)\n        most_similar_row = np.argmin(diff_sum_row)\n        df2.at[most_similar_row, 'score'] = 0.02\n        df2.at[most_similar_row, 'matched'] = True\n        df2.at[most_similar_row, 'diff_sum'] = diff_sum_row[most_similar_row]\n\n    df2.loc[df2['score'] < 0, 'score'] = 0\n\n    return df2\n\ndf1 = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6], 'target': ['x', 'y', 'z']})\ndf2 = pd.DataFrame({'A': [1.1, 2.2, 3.3, 5.5], 'B': [4.4, 5.5, 6.6, 8.8], 'score': [0.5, 0.6, 0.7, 0.8],'WEEK_NUM':[1,1,1,1],'case_id':[1,2,3,4]})\n#df1 = df1.repeat(10000)\n#df2 = df2.repeat(10000)\ndf1 = df1.reindex(df1.index.repeat(10000))\ndf2 = df2.reindex(df2.index.repeat(10000))\nresult_df = find_most_similar_row_optimized(df1, df2)\nprint(result_df.shape[0])\nprint(result_df.head(10))\ndf2 = df2.drop(columns=[\"matched\"])\ndf2 = df2.drop(columns=[\"diff_sum\"])\n#print(df2)","metadata":{},"execution_count":null,"outputs":[]}]}