熊猫数据帧和摘录列表

Question

我正在处理一个数据框架，它包括公司名称、代码id以及代码交换id。抓取数据的代码工作得很好。下面是数据的输出。

    Company name          (Company symbol, Company exchange)
0   Abbott Laboratories   (ABT, NYQ)
1   ABBVIE                (ABBV, NYQ)
2   ASML.AS               (ASML.AS, AMS)
3   AD.AS                 (AD.AS, AMS) 

Index(['Company name', ('Company symbol', 'Company exchange')], dtype='object')
type(df_companies)= pandas.core.frame.DataFrame

我试图删除冗余符号()，代码如下：

df[df_companies.columns] = df_companies.apply(lambda x: x.str.strip())
df_companies.applymap(lambda x: x.strip() if isinstance(x, str) else x)

这些代码不起作用，它们导致了NaN。我试图转接数据帧，但这也没有帮助。这样做的目的是创建这样的df_companies：

Company name              Company symbol  Company exchange
0   Abbott Laboratories   ABT             NYQ
1   ABBVIE                ABBV            NYQ
2   ASML.AS               ASML.AS         AMS
3   AD.AS                 AD.AS           AMS

最后的想法是为每个交换创建一个列表，如下所示：

NYQ=['ABT', 'ABBV']
AMS=['ASMl.AS', 'AD.AS']

对如何解决这个问题有什么想法吗？

Answer 1

您可以首先从符号/交换列中删除()括号，然后使用expand=True在,上进行Series.str.split操作

df[['Company Symbol','Company Exchange']] = df['(Company symbol, Company exchange)'].str.strip('()')\
                                        .str.split(',',expand=True)
df.drop('(Company symbol, Company exchange)',axis=1,inplace=True)

这意味着：

          Company name Company Symbol Company Exchange
0  Abbott Laboratories            ABT              NYQ
1               ABBVIE           ABBV              NYQ
2              ASML.AS        ASML.AS              AMS
3                AD.AS          AD.AS              AMS

最后：

>>> df.groupby('Company Exchange')['Company Symbol'].agg(list)

Company Exchange
 AMS    [ASML.AS, AD.AS]
 NYQ         [ABT, ABBV]

样本df

>>> df.to_dict()

{'Company name': {0: 'Abbott Laboratories',
  1: 'ABBVIE',
  2: 'ASML.AS',
  3: 'AD.AS'},
 '(Company symbol, Company exchange)': {0: '(ABT, NYQ)',
  1: '(ABBV, NYQ)',
  2: '(ASML.AS, AMS)',
  3: '(AD.AS, AMS)'}}

请检查以下最后一段代码：

df.columns = [str(s).replace('(','').replace(')','').replace("'",'') for s in df.columns]
df[['Company Symbol','Company Exchange']] = pd.DataFrame(df["Company symbol, Company exchange"].tolist(), index=df.index)
df.drop("Company symbol, Company exchange",axis=1,inplace=True)
df.groupby('Company Exchange')['Company Symbol'].agg(list)

Answer 2

可以使用groupby将另一列聚合到列表中。

data = {
    "Company exchange": ["NYQ", "NYQ", "AMS", "AMS"],
    "Company Symbol" : ["ABT", "ABBV", "ASML.AS", "AD.AS"]
}

df = pd.DataFrame(data)
print(df)

产出：

​

​

df.groupby('Company exchange')['Company Symbol'].apply(list)

产出：

​

​

Answer 3

来自您的DataFrame：

>>> data = {
...    "Company name": ["Abbott Laboratories", "ABBVIE", "ASML.AS", "AD.AS"],
...    "(Company symbol, Company exchange)" : ["(ABT, NYQ)", "(ABBV, NYQ)", "(ASML.AS, AMS)", "(AD.AS, AMS)"]}
>>> df = pd.DataFrame(data)
>>> df
    Company name        (Company symbol, Company exchange)
0   Abbott Laboratories (ABT, NYQ)
1   ABBVIE              (ABBV, NYQ)
2   ASML.AS             (ASML.AS, AMS)
3   AD.AS               (AD.AS, AMS)

我们开始将()替换为[]，以处理元组的列表整数：

>>> df["(Company symbol, Company exchange)"] = df["(Company symbol, Company exchange)"].replace(to_replace='\(', value="[", regex=True)
>>> df["(Company symbol, Company exchange)"] = df["(Company symbol, Company exchange)"].replace(to_replace='\)', value="]", regex=True)
>>> df
    Company name        (Company symbol, Company exchange)
0   Abbott Laboratories [ABT, NYQ]
1   ABBVIE              [ABBV, NYQ]
2   ASML.AS             [ASML.AS, AMS]
3   AD.AS               [AD.AS, AMS]

现在，我们可以在两个新列中“爆炸”括号内的数据：

>>> df[['Company symbol','Company exchange']] = pd.DataFrame(df["(Company symbol, Company exchange)"].apply(lambda x: x[1:-1].split(',')).tolist(), index= df.index)
>>> df
    Company name        (Company symbol, Company exchange)  Company symbol  Company exchange
0   Abbott Laboratories [ABT, NYQ]                          ABT             NYQ
1   ABBVIE              [ABBV, NYQ]                         ABBV            NYQ
2   ASML.AS             [ASML.AS, AMS]                      ASML.AS         AMS
3   AD.AS               [AD.AS, AMS]                        AD.AS           AMS

为了完成并获得预期的结果，我们可以使用一个groupby并获得想要的列表：

>>> df.groupby('Company exchange')['Company symbol'].apply(list)
Company exchange
 AMS    [ASML.AS, AD.AS]
 NYQ         [ABT, ABBV]
Name: Company symbol, dtype: object