Import multiple files in different folders and concatenate them, using format in Python

Question

I have a folder with subfolders for each state, as follows:

Inside the subfolders there are multiple excel files of the same census, as follows:

As you can see, each file is called with the name of the module and the code of the state.

What I want to do is reading all files of the same module and concatenate them. I tried to replace the folder path with formats and dictionaries, but it's not working.

states = {'05ALABAMA','12CALIFORNIA', '15TEXAS', '20ARKANSAS','23NEWYORK'} 
id_states = {'05', '12', '15', '20', '23'}

for i in states, id_states:
    data = pd.read_excel(r'census{}CEN_S4_{}.csv'.format(states, id_states))

Any ideas? Thanks!

Answer 1

The structure of your for loop is iterating over states and id_states as if they were 2 items in a list. If you were to print(i), you would see that it simply spits out the contents of states and then id_states.

Without changing your code much, I would suggest modifying states and id_states to be lists, and then altering your loop to be something like for i in range(len(states)) and then using i to index your lists. You can access the items in your lists by doing something like states[i].