Python中的列选择

Question

我试图找到以下问题的解决办法，但似乎我的方法是错误的

我有一套Excel，其中有些列如ISBN、Title等。Excel中的列名格式不正确。ISBN在一些Excel文件中命名为ISBN，而在其他文件中命名为ISBN-13、Alias、ISBN13等。标题和其他列也是如此。你知道吗

我已经使用read Excel在python中将所有这些Excel作为数据帧读取，并使用str.contains查找基于子字符串的列。请查找以下代码：

searchfor = ['ISBN13','BAR CODE','ISBN NO#','ISBN','ISBN1','ISBN 
13','ISBN_13','ITEM','ISBN NUMBER','ISBN No','ISBN-13','ISBN (13 
DIGITS)','EAN','ALIAS','ITEMCODE']


searchfor1 = ['TITLE','BOOK NAME','NAME','TITLE 
NAME','TITLES','BOOKNAME','BKDESC','PRODUCT NAME','ITEM DESCRIPTION','TITLE 
18','COMPLETETITLE']

for f, i in zip(files_txt1, num1): 
df = pd.read_excel(f,encoding='sys.getfilesystemencoding()') 
df.columns = df.columns.str.upper() 
df1['Isbn'] = df[df.columns[df.columns.str.contains('|'.join(searchfor))]]
df1['Title']= 
df[df.columns[df.columns.to_series().str.contains('|'.join(searchfor1))]]

如果我有excel和列表中的文本，代码工作正常。但是，如果excel没有任何名称类似于列表的列，则会抛出错误。代码也不适用于ISBN。你知道吗

请参见下面的详细错误：

--------------------------------------------------------------------------- ValueError                                Traceback (most recent call last) C:\Users\Ruchir_Kumar_Jha\AppData\Local\Enthought\Canopy\edm\envs\User\lib\site-packages\pandas\core\common.py in _asarray_tuplesafe(values, dtype)
    376                 result = np.empty(len(values), dtype=object)
--> 377                 result[:] = values
    378             except ValueError:

ValueError: could not broadcast input array from shape (31807,0) into shape (31807)

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last) C:\Users\Ruchir_Kumar_Jha\AppData\Local\Enthought\Canopy\edm\envs\User\lib\site-packages\pandas\core\frame.py in _ensure_valid_index(self, value)    2375             try:
-> 2376                 value = Series(value)    2377             except:

C:\Users\Ruchir_Kumar_Jha\AppData\Local\Enthought\Canopy\edm\envs\User\lib\site-packages\pandas\core\series.py in __init__(self, data, index, dtype, name, copy, fastpath)
    247                 data = _sanitize_array(data, index, dtype, copy,
--> 248                                        raise_cast_failure=True)
    249 

C:\Users\Ruchir_Kumar_Jha\AppData\Local\Enthought\Canopy\edm\envs\User\lib\site-packages\pandas\core\series.py in _sanitize_array(data, index, dtype, copy, raise_cast_failure)    3028         else:
-> 3029             subarr = _asarray_tuplesafe(data, dtype=dtype)    3030 

C:\Users\Ruchir_Kumar_Jha\AppData\Local\Enthought\Canopy\edm\envs\User\lib\site-packages\pandas\core\common.py in _asarray_tuplesafe(values, dtype)
    379                 # we have a list-of-list
--> 380                 result[:] = [tuple(x) for x in values]
    381 

ValueError: cannot copy sequence with size 0 to array axis with dimension 31807

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last) <ipython-input-23-9e043c13fef2> in <module>()
     11     df.columns = df.columns.str.upper()
     12     #print(list(df.columns))
---> 13     df1['Isbn'] = df[df.columns[df.columns.str.contains('|'.join(searchfor))]]
     14     df1['Title'] = df[df.columns[df.columns.to_series().str.contains('|'.join(searchfor1))]]
     15     df1['Curr'] = df[df.columns[df.columns.to_series().str.contains('|'.join(searchfor2))]]

C:\Users\Ruchir_Kumar_Jha\AppData\Local\Enthought\Canopy\edm\envs\User\lib\site-packages\pandas\core\frame.py in __setitem__(self, key, value)    2329         else:    2330        
# set column
-> 2331             self._set_item(key, value)    2332     2333     def _setitem_slice(self, key, value):

C:\Users\Ruchir_Kumar_Jha\AppData\Local\Enthought\Canopy\edm\envs\User\lib\site-packages\pandas\core\frame.py in _set_item(self, key, value)    2394         """    2395 
-> 2396         self._ensure_valid_index(value)    2397         value = self._sanitize_column(key, value)    2398         NDFrame._set_item(self, key, value)

C:\Users\Ruchir_Kumar_Jha\AppData\Local\Enthought\Canopy\edm\envs\User\lib\site-packages\pandas\core\frame.py in _ensure_valid_index(self, value)    2376                 value = Series(value)    2377             except:
-> 2378                 raise ValueError('Cannot set a frame with no defined index '    2379                                  'and a value that cannot be converted to a '    2380                                'Series')

ValueError: Cannot set a frame with no defined index and a value that cannot be converted to a Series

Answer 1

只要您没有匹配项或只有1个匹配项，这将起作用

searchfor = ['ISBN13','BAR CODE','ISBN NO#','ISBN','ISBN1','ISBN 13','ISBN_13','ITEM','ISBN NUMBER','ISBN No','ISBN-13','ISBN (13 DIGITS)','EAN','ALIAS','ITEMCODE']
searchfor1 = ['TITLE','BOOK NAME','NAME','TITLE NAME','TITLES','BOOKNAME','BKDESC','PRODUCT NAME','ITEM DESCRIPTION','TITLE 18','COMPLETETITLE']

for f, i in zip(files_txt1, num1): 
    df = pd.read_excel(f,encoding='sys.getfilesystemencoding()') 
    df.columns = df.columns.str.upper()

    cols = df.columns

    is_isbn = cols.isin(searchfor)
    df1['Isbn'] = df[cols[is_isbn]] if is_isbn.any() else None

    is_title = cols.isin(searchfor1)
    df1['Title'] = df[cols[is_title]] if is_title.any() else None

Answer 2

你不需要这些，如果你事先知道你的列，只要在创建dataFrame和导出文件到Pandas本身的时候尝试一下，这样你也会大大减少内存的使用。你知道吗

df = pd.read_csv(file_name, usecols=['ISBN13','BAR CODE','ISBN NO#','ISBN','ISBN1','ISBN 13','ISBN_13','ITEM','ISBN NUMBER','ISBN No','ISBN-13','ISBN (13 DIGITS)','EAN','ALIAS','ITEMCODE']).fillna('')