ValueError: Length mismatch - when tried to read multiple xlsx files with multiple sheets in pandas?-CodePudding

I am trying to read multiple xlsx files where each has 51 sheets and I want to read, reformat and concatenate them in one dataframe with pandas. However, I am able to read one xlsx with 51 sheets correctly but when I use same logic for reading multiple xlsx files where each has 51 sheets, I am getting following error:

--------------------------------------------------------------------------- ValueError Traceback (most recent call last) Input In [31], in 12 print(names) 13 print(len(names)) ---> 14 src_df = (pd.concat({k: df.assign(State=df.columns[0]).set_axis(['County','Orientation','State'], axis=1) for k, df in pd.read_excel(xls, sheet_name = names).items()}).rename_axis(['ST', None], axis=0).reset_index(level=0)) 15 dfs.append(src_df)

Input In [31], in (.0) 12 print(names) 13 print(len(names)) ---> 14 src_df = (pd.concat({k: df.assign(State=df.columns[0]).set_axis(['County','Orientation','State'], axis=1) for k, df in pd.read_excel(xls, sheet_name = names).items()}).rename_axis(['ST', None], axis=0).reset_index(level=0)) 15 dfs.append(src_df) .. ..

ValueError: Length mismatch: Expected axis has 4 elements, new values have 3 elements

my current attempt:

here are the example data that I used for my attempt:

VenueMap_Counties_04-02-15.xlsx
VenueMap_Counties_06-13-18.xlsx
VenueMap_Counties_11-04-19.xlsx

and here is my attempt to read those xlsx files with multiple sheets into one dataframe:

files = glob.glob('https://github.com/adamFlyn/test_rl/*.xlsx')
files_xlsx = [f for f in files if f[-4:] == 'xlsx']
if len(files_xlsx)>0:
    print("more than one xlsx files found")

# read multiple xlsx files into one
dfs = pd.DataFrame()
for f in files_xlsx:
    xls = pd.ExcelFile(f)
    sheetnames = xls.sheet_names[1:]
    print(sheetnames)
    print(len(sheetnames))
    src_df = (pd.concat({k: df.assign(State=df.columns[0]).set_axis(['County','Orientation','State'], axis=1) for k, df in pd.read_excel(xls, sheet_name = sheetnames).items()}).rename_axis(['ST', None], axis=0).reset_index(level=0))
    dfs.append(src_df)

Does anyone have any ideas how to fix this valueError? Any thoughts?

desired output:

in my desired output, I want Index(['ST', 'County', 'Orientation', 'State'], dtype='object') as of columns in final dataframe. this is top 5 rows of desired output dataframe:

desired_outputDf.head().to_dict()= {'ST': {0: 'AK', 1: 'AK', 2: 'AK', 3: 'AK', 4: 'AK'},
 'County': {0: 'ALEUTIANS EAST',
  1: 'ALEUTIANS WEST',
  2: 'ANCHORAGE',
  3: 'BETHEL',
  4: 'BRISTOL BAY'},
 'Orientaion': {0: 'Plaintiff ',
  1: 'Plaintiff ',
  2: 'Neutral',
  3: 'Plaintiff ',
  4: 'Plaintiff '},
 'State': {0: 'Alaska', 1: 'Alaska', 2: 'Alaska', 3: 'Alaska', 4: 'Alaska'}}

Can anyone point me out how to fix this issue? any thoughts? Thanks

CodePudding user response：

Two of your three xlsx files have a stray backtick character in column C of the NC sheet. Fix these and it should work.

UPDATE:

Here is code that should do what you want:

files_xlsx='''
./VenueMap_Counties_04-02-15.xlsx
./VenueMap_Counties_06-13-18.xlsx
./VenueMap_Counties_11-04-19.xlsx
'''.split('\n')[1:-1]
import pandas as pd
dfs = pd.concat([
    df.rename(columns={df.columns[0]:'County'}).assign(
    State=df.columns[0], ST=ST)[['ST', 'County', 'Orientation', 'State']] for f in files_xlsx for ST, df in pd.read_excel(f, sheet_name=None).items() if ST != 'Master'])
print(f'shape: {dfs.shape}')
print(dfs.head())

Output:

shape: (9351, 4)
   ST          County Orientation   State
0  AK  Aleutians East  Plaintiff   Alaska
1  AK  Aleutians West  Plaintiff   Alaska
2  AK       Anchorage     Neutral  Alaska
3  AK          Bethel  Plaintiff   Alaska
4  AK     Bristol Bay  Plaintiff   Alaska