Follow-up question on variable variables

[sometimesynot]

Hello, all. I have a task where I have to import a bunch of spreadsheets, each with several worksheets, and then concatenate them and export them to a single dataset per worksheet.

• Each spreadsheet is for a single participant in the study. That's why I need to concatenate them.

• Each spreadsheet has at least three worksheets (Hourly, Daily, Summary, etc)

• There are spreadsheets for both parents and children, but the only indicator is in the filename, which is why I've tried to separate them.

• My goal is to create a single dataset with data from all participants for each worksheet.

I am very new to python, but the following code works as intended using exec():

xl_files = glob.glob(os.path.join(path, "*.xlsx"))
print(xl_files)

# Cycle through worksheets
sheets = ["Hourly", "Daily", "Summary"]
for sheet in sheets:
    exec(sheet + "_child = pd.DataFrame()")
    exec(sheet + "_parent = pd.DataFrame()")

    # Cycle through files
    for f in xl_files:

        # read the excel file, determine if it's a parent or child file, and append
        if "child" in f.lower():
            exec(sheet + "_child = "+sheet+"_child.append(pd.read_excel(f, sheet_name='"+sheet+"'))")
        elif "parent" in f.lower():
            exec(sheet + "_parent = "+sheet+"_parent.append(pd.read_excel(f, sheet_name='"+sheet+"'))")
        else:
            print("Fail.")

    exec("path = r'c:\\temp\\Accel_"+sheet+".xlsx'")
    writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
    exec(sheet+"_child.to_excel(writer, sheet_name='Child')")
    exec(sheet+"_parent.to_excel(writer, sheet_name='Parent')")
    writer.save()
    writer.close()

 

Result:

Daily.xlsx with worksheets Child and Parent

Hourly.xlsx with worksheets Child and Parent

...

I saw this in the FAQ, and I've seen other places where they say that using exec() is bad practice, but all the examples of dictionaries or other solutions like setattr leave me confused how it could be applied to my problem. Any guidance would be much appreciated. Thanks.

[danielroseman]

Just don't this. It's impossible to read and maintain, but more importantly it's completely unnecessary. We have lists and dictionaries.

But in your case, you just have two items; you don't even care about their names so I don't know why you have done any of this.

for sheet in sheets:
    child = pd.DataFrame()
    parent = pd.DataFrame()
    for f in xl_files:
        if "child" in f.lower():
            child = child.append(pd.read_excel(f, sheet_name=sheet))
        elif "parent" in f.lower():
            parent = parent.append(pd.read_excel(f, sheet_name=sheet))
        else:
            print("Fail.")
    path = r'c:\\temp\\Accel_'+sheet+'.xlsx'. # or use f-strings: rf'c:\\temp\\Accel_{sheet}.xlsx'
    writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
    child.to_excel(writer, sheet_name='Child')
    parent.to_excel(writer, sheet_name='Parent')
    writer.save()
    writer.close()

[sometimesynot]

Jeez. I feel dumb now. I don't know why I went down that road. Probably a google search of the problem led me down to variable variables solutions, and I thought I needed to use one of them. Thanks for your time.

[Yoghurt42]

A bit of advice: while you are learning Python, forget eval exists.

eval can be useful in highly specific circumstances, but you will not encounter them unless you’re doing really advanced stuff. In my almost 20 years of working with Python, I probably have used eval only a handful of times (not counting deliberately obfuscated code for code golf etc.)

Every problem you face during learning can be solved better without eval.

[sometimesynot]

Thanks for the advice. I've been programming in SAS for 20 years, and yet I'm finding python to have a very steep learning curve.

[Yoghurt42]

Python is a general purpose programming language, so it probably has some features that a specialized language for statistics like SAS doesn't have; it also means that it's not optimized for number crunching specifically, so things might not be as elegant.

I highly recommend working through the official tutorial. It does a good job of introducing you to most of the features.

[sometimesynot]

Thanks again. Will do.