python - Python中的列选择

我正在尝试找到以下给定问题的解决方案，但似乎我在方法上错了

我有一组Excel，其中包含一些列，例如ISBN，标题等。Excel中的列名称格式不正确。在某些Excel文件中，ISBN被命名为ISBN，而在另一些文件中，它被命名为ISBN-13，Alias，ISBN13等。对于“标题”和其他列也是如此。

我已经使用读取的Excel将所有这些Excel读取为python中的数据框，并使用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没有名称与列表相似的任何列，则会引发错误。同样，代码不适用于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

最佳答案

只要您没有匹配项或完全匹配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

关于python - Python中的列选择，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53916054/