is there a way to read multiple excel tab/sheets from single xlsx to multiple dataframes with each dataframe named with sheet name?

user_9735423 0
Viewed 1116

I am not good in python please forgive me for this question but I need to create a function which does the following thing:

  1. Create multiple data frames from multiple excel tab/sheet present in a single xlsx file and be named on the sheet name.
  2. The columns' values should be concatenated and checked if there is no duplicate value.
  3. if the concat value has a duplicate then it should be told as yes/No in another column.
  4. all the dataframes then should be written into a single workbook as different worksheets inside. values inside () are columns for better understanding

example:

sheet1

(a) (b) (c) (d)
a1  b1  c1  d1
a2  b2  c2  d2

result:

(c) (d) (concate) (is duplicate)
c1  d1  c1_d1     no
c2  d2  c2_d2     no

sheet2

(a) (b) (e) (f)
a3  b3  e1  f1
a4  b4  e1  f1
a5  b5  e2  f2
a6  b6  e4  f4
a7  a8  e4  f5

result:

(e) (f) (concat) (has duplicate)
e1 f1 e1_f1 yes
e2 f2 e2_f2 no
e4 f4 e4_f4 no
e4 f5 e4_f5 no
pyspark-dataframes pandas dataframe python-3.x python
2 Answers

Here you go:

import pandas as pd
from pandas import ExcelWriter

def detect_duplicate(group):
    group['is_duplicate'] = ['No'] + ['Yes'] * (len(group) - 1)
    return group

with ExcelWriter('output.xlsx') as output:
    for sheet_name, df in pd.read_excel('input.xlsx', sheet_name=None).items():
        df = df.drop(['a', 'b'], axis=1)
        df['concat'] = df.apply(lambda row: '_'.join(row), axis=1)
        df = df.groupby(['concat']).apply(detect_duplicate)
        df = df.drop_duplicates(keep='last', subset=['concat'])
        df.to_excel(output, sheet_name=sheet_name, index=False)

Check output.xlsx for the output.

user_126114090

First of all, to read an excel file with multiple sheets, use pandas ExcelFile function.

e.g. df = pd.ExcelFile(filepath)

And, after reading the excel from the step above, you can read each sheet in a seperate dataframe using the read_excel function, e.g.

df1 = pd.read_excel(df, 'sheet_name_1')
df2 = pd.read_excel(df, 'sheet_name_2')

insert different sheet names and read the sheets in different dataframes.

I didn't understand the latter part of your question please elaborate a bit more.

user_73310800
Related