根据列将多个无标题列中的值替换为0、1、2 [英] Replace values in multiple untitled columns to 0, 1, 2 depending on column

问题描述

按评论编辑

背景：这是当前数据框的外观。行标签是原始excel文件中的信息文本。但是我希望这种小的数据复制足以解决问题吗？实际文件大约有100列和200行。

Background: Here is what the current dataframe looks like. The row labels are information texts in original excel file. But I hope this small reproduction of data will be enough for a solution? Actual file has about 100 columns and 200 rows.

列标题和行＃0的值按以下所示的模式重复进行-除了 Sales 或 Validation 每次出现具有现有标题的列时都会更改文本。

Column headers and Row #0 values are repeated with pattern shown below -- except the Sales or Validation text changes at every occurrence of column with an existing title.

再多一个销售前 列，每行中都有文字。 X的映射为此测试完成。不幸的是，下面没有找到一种优雅的方式来显示文本作为输出的一部分。

One more column before sales with text in each row. Mapping of Xs done for this test. Unfortunately, found no elegant way of displaying text as part of output below.

 Sales Unnamed: 2  Unnamed: 3  Validation Unnamed: 5 Unnamed: 6
0       Commented  No comment             Commented  No comment                                   
1     x                                             x                        
2                            x          x                                                
3                x                                             x             

期望输出：将X分别用0、1、2和2替换（注释/无评论）

Expected Output: Replacing the X with 0s, 1s and 2s depending on which column they are in (Commented / No Comment)

 Sales Unnamed: 2  Unnamed: 3  Validation Unnamed: 5 Unnamed: 6
0       Commented  No comment             Commented  No comment                                   
1     0                                            1                        
2                            2          0                                                
3                1                                             2  

可能的代码：我认为循环看起来像此：

Possible Code: I assume the loop would look something like this:

while in row 9:
    if column value = "commented":

        replace all "x" with 1

    elif row 9 when column valkue = "no comment":

        replace all "x" with 2

    else:

        replace all "x" with 0

但是python新手，我不确定如何将其转换为工作代码。感谢所有的支持和帮助。

But being a python novice, I am not sure how to convert this to a working code. I'd appreciate all support and help.

推荐答案

这是一种实现方法：

1. 定义一个函数来替换x：

import re

def replaceX(col):
    cond = ~((col == "x") | (col == "X"))
    # Check if the name of the column is undefined
    if not re.match(r'Unnamed: \d+', col.name):
        return col.where(cond, 0)
    else:
        # Check what is the value of the first row
        if col.iloc[0] == "Commented":
            return col.where(cond, 1)
        elif col.iloc[0] == "No comment":
            return col.where(cond, 2)
    return col

或者如果您的第一行的标题列不包含 Commented或 No comment，则可以使用不带正则表达式的解决方案：

Or if your first row don't contain "Commented" or "No comment" for titled columns you can have a solution without regex:

def replaceX(col):
    cond = ~((col == "x") | (col == "X"))
    # Check what is the value of the first row
    if col.iloc[0] == "Commented":
        return col.where(cond, 1)
    elif col.iloc[0] == "No comment":
        return col.where(cond, 2)
    return col.where(cond, 0)

1. 在DataFrame上应用此功能：

# Apply the function on every column (axis not specified so equal 0)
df.apply(lambda col: replaceX(col))

输出：

  title Unnamed: 2  Unnamed: 3
0        Commented  No comment
1                             
2     0                      2
3                1            

文档：

Documentation:

• 应用：根据轴在每个列/行上应用函数


• 在何处：检查一系列条件是否满足，如果不满足，则用指定的值替换。

• Apply: apply a function on every columns/rows depending on the axis
• Where: check where a condition is met on a series, if it is not met, replace with value specified.

这篇关于根据列将多个无标题列中的值替换为0、1、2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！