剝離Pandas資料框并提取每個股票代碼交易所的串列

我正在處理一個資料框，它由公司名稱、股票代碼 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']

關于如何解決這個問題的任何想法？

uj5u.com熱心網友回復：

您可以使用 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)

輸出：

uj5u.com熱心網友回復：

你可以先洗掉()從您的符號/交換柱支架，然后 Series.str.split在,用expand=True：

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)

uj5u.com熱心網友回復：

從您的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)

我們開始替換()by[]以使用串列 intead of tuples ：

>>> 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

要完成并獲得預期結果，我們可以使用 agroupby并獲取通緝串列：

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

標籤：Python 熊猫 数据框 跳闸

上一篇：拆分pyspark資料框列

下一篇：比較其他列常見的熊貓資料框中的行