R与Python数据处理代码结果不一致问题排查

数据结构 #

原始DataFrame的列及类型如下：

DOMAINNAME                   object
CUSTOMERNUMBER                int64
CREDITCHECKSOURCE            object
RESULTTEXT                   object
RESULTCODE                   object
FUNCTION                     object
LASTMODIFIED         datetime64[ns]
APPROVEDAMOUNT              float64
ISANONYMIZED                 object
SALESBRAND                   object
COUNTRY                      object
DATE                 datetime64[ns]
DAY                           int64
MONTH                         int64
WEEK                         UInt32
YEAR                          int64
dtype: object

其中DATE为'2024-01-08 15:32:07'格式的datetime类型，原始列唯一值数量为296723。

处理后差异 #

分别用R语言dplyr和Python代码处理后，DATE列唯一值数量变为293673（R）和280531（Python），二者存在明显差异。

R语言处理代码 #

df2 <- df1 %>%
  select(-RESULTCODE) %>% 
  filter(RESULTTEXT == "APPROVED" | RESULTTEXT == "DENIED", !is.na(FUNCTION), SALESBRAND != "Stayhard") %>% 
  distinct(across(-DATE), .keep_all = TRUE) %>% 
  select( -CUSTOMERNUMBER )

Python处理代码 #

def filter_transform_alternative(df):
   df_filtered = df[(df['RESULTTEXT'].isin(["APPROVED", "DENIED"])) & 
                     df['FUNCTION'].notna() & 
                    (df['SALESBRAND'] != "Stayhard")]
        
   df_filtered = df_filtered.drop(columns=['CUSTOMERNUMBER'])
        
   cols_for_dupes = [col for col in df_filtered.columns if col not in ['DATE', 'CUSTOMERNUMBER']]
   df_filtered['unique_id'] = df_filtered[cols_for_dupes].astype(str).apply(lambda x: '_'.join(x), axis=1)
        
   df_filtered['is_dupe'] = df_filtered.duplicated(subset='unique_id', keep='first')
   
   df_no_duplicates = df_filtered[df_filtered['is_dupe'] == False].drop(columns=['unique_id', 'is_dupe'])

   return df_no_duplicates

可复现样本数据集 #

DOMAINNAME  CUSTOMERNUMBER CREDITCHECKSOURCE RESULTTEXT  
0         Ellos-EllosNO      1246087421       MULTIUPPLYS   APPROVED   
1         Ellos-EllosSE      1246087439       MULTIUPPLYS   APPROVED   
2   Homeroom-HomeroomSE      1244949952       MULTIUPPLYS   APPROVED   
3         Ellos-EllosSE       534334891       MULTIUPPLYS   APPROVED   
4         Jotex-JotexSE      1246087165       MULTIUPPLYS   APPROVED   
5   Homeroom-HomeroomNO      1246087298       MULTIUPPLYS   APPROVED   
6         Jotex-JotexDK      1246087207       MULTIUPPLYS   APPROVED   
7         Ellos-EllosNO      1246086639       MULTIUPPLYS   APPROVED   
8         Ellos-EllosSE       936355635       MULTIUPPLYS   APPROVED   
9         Jotex-JotexSE       646132969       MULTIUPPLYS   APPROVED   
10        Jotex-JotexNO       943056952       MULTIUPPLYS   APPROVED   
11        Ellos-EllosSE      3169943333       MULTIUPPLYS     DENIED   
12        Jotex-JotexNO      1246086944       MULTIUPPLYS   APPROVED   
13        Ellos-EllosSE      1245979081       MULTIUPPLYS   APPROVED   
14        Ellos-EllosFI      1246086878       MULTIUPPLYS   APPROVED   
15        Ellos-EllosSE       936355635       MULTIUPPLYS   APPROVED   
16        Ellos-EllosSE      1246074783       MULTIUPPLYS   APPROVED   
17  Homeroom-HomeroomSE      1145457782       MULTIUPPLYS     DENIED   
18        Ellos-EllosSE      1246086803       MULTIUPPLYS   APPROVED   
19        Ellos-EllosNO      1245818248       MULTIUPPLYS   APPROVED   

   RESULTCODE FUNCTION        LASTMODIFIED  APPROVEDAMOUNT ISANONYMIZED  
0         nan   CREDIT 2024-01-08 15:32:07          2999.0          nan   
1         nan   CREDIT 2024-01-08 15:31:34          4045.0          nan   
2         nan   CREDIT 2024-01-08 15:26:49           198.0          nan   
3         nan    LIMIT 2024-01-08 15:26:47         21407.0          nan   
4         nan   CREDIT 2024-01-08 15:26:45          9099.0          nan   
5         nan   CREDIT 2024-01-08 15:24:45           328.0          nan   
6         nan   CREDIT 2024-01-08 15:23:34           641.0          nan   
7         nan   CREDIT 2024-01-08 15:22:17          1438.0          nan   
8         nan    LIMIT 2024-01-08 15:20:57         17600.0          nan   
9         nan   CREDIT 2024-01-08 15:20:41           348.0          nan   
10        nan    LIMIT 2024-01-08 15:19:03          7448.0          nan   
11        nan    LIMIT 2024-01-08 15:15:49             0.0          nan   
12        nan   CREDIT 2024-01-08 15:15:35          5489.0          nan   
13        nan   CREDIT 2024-01-08 15:13:46           603.0          nan   
14        nan   CREDIT 2024-01-08 15:12:54           399.0          nan   
15        nan    LIMIT 2024-01-08 15:11:02         13711.0          nan   
16        nan   CREDIT 2024-01-08 15:09:54           520.0          nan   
17        nan   CREDIT 2024-01-08 15:09:08             0.0          nan   
18        nan   CREDIT 2024-01-08 15:09:05           614.0          nan   
19        nan   CREDIT 2024-01-08 15:04:38           885.0          nan   

   SALESBRAND COUNTRY                DATE  DAY  MONTH  WEEK  YEAR
0       Ellos      NO 2024-01-08 15:32:07    8      1     2  2024
1       Ellos      SE 2024-01-08 15:31:34    8      1     2  2024
2    Homeroom      SE 2024-01-08 15:26:49    8      1     2  2024
3       Ellos      SE 2024-01-08 15:26:47    8      1     2  2024
4       Jotex      SE 2024-01-08 15:26:45    8      1     2  2024
5    Homeroom      NO 2024-01-08 15:24:45    8      1     2  2024
6       Jotex      DK 2024-01-08 15:23:34    8      1     2  2024
7       Ellos      NO 2024-01-08 15:22:17    8      1     2  2024
8       Ellos      SE 2024-01-08 15:20:57    8      1     2  2024
9       Jotex      SE 2024-01-08 15:20:41    8      1     2  2024
10      Jotex      NO 2024-01-08 15:19:03    8      1     2  2024
11      Ellos      SE 2024-01-08 15:15:49    8      1     2  2024
12      Jotex      NO 2024-01-08 15:15:35    8      1     2  2024
13      Ellos      SE 2024-01-08 15:13:46    8      1     2  2024
14      Ellos      FI 2024-01-08 15:12:54    8      1     2  2024
15      Ellos      SE 2024-01-08 15:11:02    8      1     2  2024
16      Ellos      SE 2024-01-08 15:09:54    8      1     2  2024
17   Homeroom      SE 2024-01-08 15:09:08    8      1     2  2024
18      Ellos      SE 2024-01-08 15:09:05    8      1     2  2024
19      Ellos      NO 2024-01-08 15:04:38    8      1     2  2024

差异原因解析 #

1. 去重判断的核心差异 #

R代码的去重逻辑是：在过滤后，基于除DATE外的所有列（包括CUSTOMERNUMBER）进行去重，保留每个重复组的第一行DATE；而Python代码是先删除CUSTOMERNUMBER，再基于剩余列去重。

CUSTOMERNUMBER是唯一标识用户的列，删除后会导致原本因CUSTOMERNUMBER不同而不重复的行被判定为重复，进而被过滤掉，最终保留的DATE数量更少。

2. 数据类型转换的副作用 #

Python代码通过将所有列转成字符串拼接成unique_id来判断重复，存在以下问题：

• 浮点数（如APPROVEDAMOUNT）转字符串时可能出现精度差异（如2999.0转成"2999.0"，而R直接比较数值）；
• 缺失值表示不同：R中缺失值是NA，Python中是nan，转字符串后会导致相同逻辑的缺失值被判定为不同；
• 列值包含下划线时，拼接后的字符串可能出现冲突，导致不同行被误判为重复，或相同行被误判为不重复。

3. 步骤顺序差异 #

R代码先删除RESULTCODE，再过滤、去重，最后删除CUSTOMERNUMBER；Python代码先过滤，再删除CUSTOMERNUMBER，再去重。虽然RESULTCODE被删除不影响去重逻辑，但步骤顺序的差异间接导致了去重时的列集合不同。

修正后的Python代码 #

要让Python代码和R逻辑一致，需调整步骤顺序，直接基于原始列去重，避免字符串转换：

def filter_transform_correct(df):
    # 先删除RESULTCODE，与R步骤对齐
    df_processed = df.drop(columns=['RESULTCODE'])
    
    # 应用过滤条件
    df_processed = df_processed[
        (df_processed['RESULTTEXT'].isin(["APPROVED", "DENIED"])) &
        df_processed['FUNCTION'].notna() &
        (df_processed['SALESBRAND'] != "Stayhard")
    ]
    
    # 基于除DATE外的所有列去重，保留第一个出现的行
    dedupe_cols = [col for col in df_processed.columns if col != 'DATE']
    df_processed = df_processed[~df_processed.duplicated(subset=dedupe_cols, keep='first')]
    
    # 最后删除CUSTOMERNUMBER
    df_processed = df_processed.drop(columns=['CUSTOMERNUMBER'])
    
    return df_processed

内容的提问来源于stack exchange，提问作者Parseval