get_file_name_with_loop.py

## import lit from sql functions - useful to add withcolumn a constant value
from pyspark.sql.functions import lit

## Provide mount with directory where the files exists
mount_path = '/mnt/<Your mount name>/<directory>'

## loop through the files 
for file in dbutils.fs.ls(mount_path):
    ## This could be better with defining a schema
    if 'flights1.csv' in file.name:
        df1 = spark.read.csv(f'{mount_path}/{file.name}', header = True, inferSchema = True)
        df1 = df1.withColumn('filename',lit(f"{mount_path}/{file.name}"))
        uniondf = df1
    else:
        df2 = spark.read.csv(f'{mount_path}/{file.name}', header = False, inferSchema = True)
        df2 = df2.withColumn('filename',lit(f"{mount_path}/{file.name}"))
        uniondf = uniondf.union(df2)
    
## Register a temp view
uniondf.createOrReplaceTempView("flights_data")

## run a group by command on temp view to get number of records per file - This could be done with data frame groupBy as well
resultdf  = spark.sql("select filename, count(*) from flights_data group by filename")
resultdf.display()