如果列标题是另一列的子字符串，则创建true/false列

Question

我在this post之后创建了许多列，这些列基于另一列中是否存在子字符串而为真/假

在使用上述文章中的代码之前，我先查看一个名为LANGUAGES的字段，该字段的值为"ENG, SPA, CZE"或"ENG, SPA"。不幸的是，数据是逗号分隔的字符串，而不是列表，但没问题，在一行中，我可以得到25个唯一值的列表

一旦我得到了唯一值的列表，我想为每个值创建一个新的列，例如df[ENG]、df[SPA]等列。我希望根据标题是否为原始语言列的子字符串，将这些列设置为true/false

在文章之后，我使用df.apply(lambda x: language in df.LANGUAGES, axis = 1)。但是，当我检查列的值（值在最后一个for循环中计数）时，所有值都显示为false

如何基于列的标题是另一列的子字符串来创建true/false列

我的代码：

import json
import pandas as pd
import requests

url  = r"https://data.hud.gov/Housing_Counselor/search?AgencyName=&City=&State=&RowLimit=&Services=&Languages="

response = requests.get(url)

if response.status_code == 200:
    res = response.json()
    df = pd.DataFrame(res)
    df.columns = [str(h).upper() for h in list(df)]
    #
    # the below line is confusing but it creates a sorted list of all unique languages
    #
    languages = [str(s) for s in sorted(list(set((",".join(list(df["LANGUAGES"].unique()))).split(","))))]
    for language in languages:
        print(language)
        df[language] = df.apply(lambda x: language in df.LANGUAGES, axis = 1)
    for language in languages:
        print(df[language].value_counts())
        print("\n")
else:
    print("\nConnection was unsuccesful: {0}".format(response.status_code))

编辑：请求原始数据输入和预期输出。以下是列的外观：

+-------+-----------------+
| Index |    LANGUAGES    |
+-------+-----------------+
|     0 | 'ENG, OTH, RUS' |
|     1 | 'ENG'           |
|     2 | 'ENG, CZE, SPA' |
+-------+-----------------+

这是预期输出：

+-------+-----------------+------+-------+-------+-------+-------+
| Index |    LANGUAGES    | ENG  |  CZE  |  OTH  |  RUS  |  SPA  |
+-------+-----------------+------+-------+-------+-------+-------+
|     0 | 'ENG, OTH, RUS' | TRUE | FALSE | TRUE  | TRUE  | FALSE |
|     1 | 'ENG'           | TRUE | FALSE | FALSE | FALSE | FALSE |
|     2 | 'ENG, CZE, SPA' | TRUE | TRUE  | FALSE | TRUE  | FALSE |
+-------+-----------------+------+-------+-------+-------+-------+

Answer 1

我在this post中找到了以下代码行：

df[language] = df.apply(lambda x: language in df.LANGUAGES, axis = 1)

对于以下两行：

    criteria = lambda row : language in row["LANGUAGES"]
    df[language] = df.apply(criteria, axis =1)

它是有效的

import json
import pandas as pd
import requests

url  = r"https://data.hud.gov/Housing_Counselor/search?AgencyName=&City=&State=&RowLimit=&Services=&Languages="

response = requests.get(url)

if response.status_code == 200:
    res = response.json()
    df = pd.DataFrame(res)
    df.columns = [str(h).upper() for h in list(df)]
    #
    # the below line is confusing but it creates a sorted list of all unique languages
    #
    languages = [str(s) for s in sorted(list(set((",".join(list(df["LANGUAGES"].unique()))).split(","))))]
    for language in languages:
        criteria = lambda row : language in row["LANGUAGES"]
        df[language] = df.apply(criteria, axis =1)
    for language in languages:
        print(df[language].value_counts())
        print("\n")
else:
    print("\nConnection was unsuccesful: {0}".format(response.status_code))

这种线路交换也可以起作用：

for language in languages:
    df[language] = df.LANGUAGES.apply(lambda x: 'True' if language in x else 'False')
    print("{}:{}".format(language, df[df[language] == 'True'].shape[0]))

Answer 2

两步走,

首先，我们分解您的列表并创建一个透视表，以便根据索引重新连接到原始df

s  = df['LANGUAGES'].str.replace("'",'').str.split(',').explode().to_frame()

cols = s['LANGUAGES'].drop_duplicates(keep='first').tolist()

df2 = pd.concat([df, pd.crosstab(s.index, s["LANGUAGES"])[cols]], axis=1).replace(
    {1: True, 0: False}
)
print(df2)
         LANGUAGES   ENG    OTH    RUS    CZE    SPA
0  'ENG, OTH, RUS'  True   True   True  False  False
1            'ENG'  True  False  False  False  False
2  'ENG, CZE, SPA'  True  False  False   True   True