数据合并Pandas

Question

我在不同的机器上做了一些pcmark测试。最后，我要巩固机器的结果。我修改了最终结果以显示。我尝试过用熊猫进行不同形式的合并，但我无法得到预期的结果，但这已经足够接近了。如有任何建议，将不胜感激

机器1：数据帧：

|------------|---------------------------|--------------|------------|  
|Test Case   | SubTest                   | App          | Count      |  
|------------|---------------------------|--------------|------------|   
|pcmark10    | AppStartUp                | NaN          | NaN        |  
|pcmark10    | PhotoEditing              | NaN          | NaN        |  
|pcmark10    | RenderingAndVisualization | NaN          | NaN        |  
|pcmark10    | Spreadsheet               | soffice.bin  | 1.0        |  
|pcmark10    | VideoConferencing         | NaN          | NaN        |  
|pcmark10    | VideoEditing              | NaN          | NaN        |  
|pcmark10    | WebBrowsing               | NaN          | NaN        |  
|pcmark10    | Writing                   | NaN          | NaN        |  
|------------|---------------------------|--------------|------------|  

来自机器2的Dataframe：

|------------|---------------------------|--------------|------------|  
|Test Case   | SubTest                   | App          | Count      |  
|------------|---------------------------|--------------|------------|   
|pcmark10    | AppStartUp                | NaN          | NaN        |  
|pcmark10    | PhotoEditing              | NaN          | NaN        |  
|pcmark10    | RenderingAndVisualization | NaN          | NaN        |  
|pcmark10    | Spreadsheet               | NaN          | NaN        |  
|pcmark10    | VideoConferencing         | NaN          | NaN        |  
|pcmark10    | VideoEditing              | NaN          | NaN        |  
|pcmark10    | WebBrowsing               | chrome.exe   | 2          |  
|pcmark10    | Writing                   | NaN          | NaN        |  
|------------|---------------------------|--------------|------------|  

我希望结果是这样的：

|------------|---------------------------|--------------|------------|------------|  
|Test Case   | SubTest                   | App          | Count_x    | Count_y    |
|------------|---------------------------|--------------|------------|------------|
|pcmark10    | AppStartUp                | NaN          | NaN        | NaN        |  
|pcmark10    | PhotoEditing              | NaN          | NaN        | NaN        |  
|pcmark10    | RenderingAndVisualization | NaN          | NaN        | NaN        |  
|pcmark10    | Spreadsheet               | soffice.bin  | 1.0        | NaN        |  
|pcmark10    | VideoConferencing         | NaN          | NaN        | NaN        |  
|pcmark10    | VideoEditing              | NaN          | NaN        | NaN        |    
|pcmark10    | WebBrowsing               | chrome.exe   | NaN        | 2          |  
|pcmark10    | Writing                   | NaN          | NaN        | NaN        |  
|------------|---------------------------|--------------|------------|------------|  

我试着把所有的键合并在一起，这就是我得到的。利用外部函数引导pcmark10的行值，以网页浏览为空白。应用程序栏中缺少Chrome。

|------------|---------------------------|--------------|------------|------------|  
|Test Case   | SubTest                   | App          | Count_x    | Count_y    |
|------------|---------------------------|--------------|------------|------------|
|pcmark10    | AppStartUp                | NaN          | NaN        | NaN        |  
|pcmark10    | PhotoEditing              | NaN          | NaN        | NaN        |  
|pcmark10    | RenderingAndVisualization | NaN          | NaN        | NaN        |  
|pcmark10    | Spreadsheet               | soffice.bin  | 1.0        | NaN        |  
|pcmark10    | VideoConferencing         | NaN          | NaN        | NaN        |  
|pcmark10    | VideoEditing              | NaN          | NaN        | NaN        |    
|pcmark10    | WebBrowsing               | NaN          | NaN        | 2          |  
|pcmark10    | Writing                   | NaN          | NaN        | NaN        |  
|------------|---------------------------|--------------|------------|------------|  

合并命令：- pd.merge(df1，df2，on='Test‘，'SubTest'，'App'，how="outer"，indicator=True)

Answer 1

在您的示例中，在Test Case和SubTest上合并，然后使用ffill或bfill创建App

(df1.merge(df2, on=['Test Case', 'SubTest'])
    .assign(App=lambda x: x.filter(like='App').bfill(1).iloc[:,0])
    .drop(['App_x','App_y'], axis=1)
)

输出：

  Test Case                    SubTest  Count_x  Count_y          App
0  pcmark10                 AppStartUp      NaN      NaN          NaN
1  pcmark10               PhotoEditing      NaN      NaN          NaN
2  pcmark10  RenderingAndVisualization      NaN      NaN          NaN
3  pcmark10                Spreadsheet      1.0      NaN  soffice.bin
4  pcmark10          VideoConferencing      NaN      NaN          NaN
5  pcmark10               VideoEditing      NaN      NaN          NaN
6  pcmark10                WebBrowsing      NaN      2.0   chrome.exe
7  pcmark10                    Writing      NaN      NaN          NaN