Extract data from specific columns with the same name in multiple Excel files and sheets

Question

I have 20 folders with varying number of excels (as .xlsx) files in each folder. Each excel file has varying number of sheets and sheets has varying number of columns and different column names on the first row. The second row has column names which has a common name say "Number" (as shown in the screenshot of excel below). I need to extract unique values from specific columns with the name "number" in each sheet, from each excel, from each folder and then sum up to a column with unique values thus gathered.

Since, I am not that expert in excel, I tried with just one excel with the below code.

import os
import pandas as pd

workbook = r'C:\Users\Material.xlsx'
df = pd.concat(pd.read_excel(workbook, sheet_name = None , usecols = ['Number'], header = 1))
df = df.Number.unique()
df

I had faced this problems here:

this script reads all the sheets from an excel but if there is varying number of column with same name then it reads only the first column. This should not be the case. EX: I should get a unique column with all the unique values in column "Number" as in the "Sheet1" in the below screenshot.
It returns an array and I want in a df.

Also tried this code:

import os
import pandas as pd
folder_path = os.chdir(r'C:\Users\Material.xlsx')
files = os.listdir(folder_path)
print(files)
df2 = pd.DataFrame()
for i in range (len(files)):
    df = pd.read_excel(files[i], header=1)
    df1 = df.filter(regex='Number')
    df2 = pd.concat([df2, df1], axis=1, sort=False)
    i = i+1
df2 = df2.filter(regex='Number')
df2
df2.to_excel(r"r'C:\Users\output.xlsx', index = False)

The issues here are:

I get only the first column values in case there are many columns with same name.
Only single sheet is taken, other sheets in an excel are not considered.

Please help

user_9701668 · Answer

The main problem is that when creating Dataframe based on data with multiple column that have the same name, pandas rename the following columns and adds a number to the name. So, in your case if you have multiple columns the calls NUMBER pandas will rename them: NUMBER NUMBER.1 NUMBER.2 and so on. There for when you trying to call the columns with usecols = ['Number'] you are getting only the first column.

Optional solution is to iterate every column and check the name. Following is more comprehensive solution to your case:

import os
import pandas as pd

sum_df=pd.DataFrame()
base_path=r'YOUR_PATH'
for fldr in os.listdir(base_path):
    if os.path.isdir(base_path+'/'+fldr):
        curr_dir=base_path+'/'+fldr
        for xls in os.listdir(curr_dir):
            if xls.endswith('.xlsx'):
                dict=pd.read_excel(curr_dir+'/'+xls,sheet_name = None,header = 0)
                for sheet in dict.items():
                    for col in sheet[1].iteritems():
                        if ('NUMBER' == col[0]) or ('NUMBER' in col[0] and '.' in col[0]):
                            sum_df=sum_df.append(pd.DataFrame(data=col[1]._values,columns=['NUMBER']))
sum_df=sum_df.d.unique()
print(sum_df)

Extract data from specific columns with the same name in multiple Excel files and sheets

1 Answers