Iterate over files in a folder to populate a dataframe (python)

Question

I am working with compiling weather data from different datafiles in a folder. Stations.csv - These datafiles are defined as location points in this csv. temperature.csv - The weather data needs to be compiled in this dataframe. Issue with the iteration loop:

stations = pd.read_csv('SMHIstationset.csv', index_col='Unnamed: 0') stations.head()

stations dataframe

temperature = pd.read_csv('SMHITemp.csv', index_col='Unnamed: 0')
temperature.head()

Compile data from folder files in this 'temperature' dataframe

I've used this loop to identify the files in the data folder;

x = []
filelist= os.listdir("C:\\Users\\SEOP24209\\Desktop\\API\\")
for filename in filelist:
    if filename.endswith(".xlsx"):
        x.append(filename)

The issue is with continuing the result from the parts below to form a loop to iterate over files in a folder to populate the dataframe (temperature). Part 1:

x = stations.loc[stations['Id'] == 188790, 'Name']
#y = (x[2])
print (type(x))
print(x)

Out: <class 'pandas.core.series.Series'> 2 Abisko Aut Name: Name, dtype: object

Part 2:

city = 'Abisko Aut'
temperature[city]=df['T2M']
temperature.rename(columns={'index':'Timestamp'}, inplace=True)
temperature.head()

I want the above parts (1&2) to be in a loop while I read every file in the folder. If you understand how I could take the input of variable 'city' as the output of Part 1, which is a series, please guide.

Answer 1

Converted the series to a list using .tolist() and then took the data at index 0 to acquire the result fed to the variable city, thus completing the loop.