获取datafram中匹配列和不匹配列数据的计数

Question

我有两个数据帧， 这是输入csv数据

Document_ID OFFSET  PredictedFeature
    0         0            2000
    0         8            2000
    0         16           2200
    0         23           2200
    0         30           2200
    1          0            2100
    1          5            2100
    1          7            2100

现在我也有了输出数据

 Document_ID    OFFSET   PredictedFeature
        0         0            2000
        0         8            2100
        0         16           2100
        0         23           2100
        0         30           2200
        1          0           2000
        1          5           2000
        1          7           2100

现在，我要做的是，匹配他们得到的结果

所以我做了

df1_inputPredictedFeature_column['new'] = df1_inputPredictedFeature_column['PredictedFeature'] == df1_predictedFeature_column['PredictedFeature']

这将添加一个列，告诉您它是否与predictedfeature列匹配

现在我要做的是

共有2个功能，其中2000在输入csv的predictedfeature中。但在输出csv中，只有第一位匹配，而不是第二位

所以我想得到这样的数据

predictedFeatureClass  inputCsvOccured   outputcsvmatched  

 2000                        2                1

2200                         3                 1

那么，我怎样才能得到这些数据呢？任何帮助都会很好

Answer 1

一种想法是通过^{}将new列转换为整数，然后通过指定新列名称的元组列表将new列与size和sum聚合：

df1['new'] = (df1['PredictedFeature'] == df2['PredictedFeature']).view('i1')

df = (df1.groupby("PredictedFeature")['new']
         .agg([('inputCsvOccured','size'), ('outputcsvmatched','sum')])
         .reset_index())
print (df)
   PredictedFeature  inputCsvOccured  outputcsvmatched
0              2000                2                 1
1              2100                3                 1
2              2200                3                 1

0.25+溶液：

df1['new'] = (df1['PredictedFeature'] == df2['PredictedFeature']).view('i1')

df = (df1.groupby("PredictedFeature")
         .agg(inputCsvOccured=pd.NamedAgg(column='new', aggfunc='size'),
              outputcsvmatched=pd.NamedAgg(column='new', aggfunc='sum'))
         .reset_index())

Answer 2

你可以像下面这样使用groupby

df1_inputPredictedFeature_column = pd.DataFrame([['0', '0', '2000'], ['0', '8', '2000'], ['0', '16', '2200'], ['0', '23', '2200'], ['0', '30', '2200'], ['1', '0', '2100'], ['1', '5', '2100'], ['1', '7', '2100']], columns=('Document_ID', 'OFFSET', 'PredictedFeature'))
df1_predictedFeature_column = pd.DataFrame([['0', '0', '2000'], ['0', '8', '2100'], ['0', '16', '2100'], ['0', '23', '2100'], ['0', '30', '2200'], ['1', '0', '2000'], ['1', '5', '2000'], ['1', '7', '2100']], columns=('Document_ID', 'OFFSET', 'PredictedFeature'))

df1_inputPredictedFeature_column['new'] = (df1_inputPredictedFeature_column['PredictedFeature'] == df1_predictedFeature_column['PredictedFeature']).astype(np.int)

result = df1_inputPredictedFeature_column.groupby("PredictedFeature").agg({"PredictedFeature":"count", "new":np.sum})

result.columns = ["inputCsvOccured", "outputcsvmatched"]
result.index.name = "predictedFeatureClass"

result.reset_index(inplace=True)
print(result)

结果

predictedFeatureClass  inputCsvOccured  outputcsvmatched
0                  2000                2                 1
1                  2100                3                 1
2                  2200                3                 1