import pandas as pd
import glob, os



pd.set_option('display.max_colwidth', -1)

def rename(dir, pathAndFilename, pattern, titlePattern):
    os.rename(pathAndFilename, os.path.join(dir, titlePattern))

# search for csv files in the working folder
path = os.path.expanduser("*.csv")

# iterate and rename them one by one with the number of the iteration
for i, fname in enumerate(glob.glob(path)):
    rename(os.path.expanduser(''), fname, r'*.csv', r'test{}.csv'.format(i))


# change separator for CSV file
df1 = pd.read_csv('~/Projects/MYP/Datasets/test/test0.csv', sep="@")
df2 = pd.read_csv('~/Projects/MYP/Datasets/test/test1.csv', sep="@")
df3 = pd.read_csv('~/Projects/MYP/Datasets/test/test29.csv', sep="@")
df4 = pd.read_csv('~/Projects/MYP/Datasets/test/test3.csv', sep="@")
df5 = pd.read_csv('~/Projects/MYP/Datasets/test/test4.csv', sep="@")
df6 = pd.read_csv('~/Projects/MYP/Datasets/test/test5.csv', sep="@")
df7 = pd.read_csv('~/Projects/MYP/Datasets/test/test6.csv', sep="@")
df8 = pd.read_csv('~/Projects/MYP/Datasets/test/test7.csv', sep="@")
df9 = pd.read_csv('~/Projects/MYP/Datasets/test/test8.csv', sep="@")
df10 = pd.read_csv('~/Projects/MYP/Datasets/test/test9.csv', sep="@")
df11 = pd.read_csv('~/Projects/MYP/Datasets/test/test28.csv', sep="@")
df12 = pd.read_csv('~/Projects/MYP/Datasets/test/test11.csv', sep="@")
df13 = pd.read_csv('~/Projects/MYP/Datasets/test/test12.csv', sep="@")
df14 = pd.read_csv('~/Projects/MYP/Datasets/test/test13.csv', sep="@")
df15 = pd.read_csv('~/Projects/MYP/Datasets/test/test14.csv', sep="@")
df16 = pd.read_csv('~/Projects/MYP/Datasets/test/test15.csv', sep="@")
df17 = pd.read_csv('~/Projects/MYP/Datasets/test/test16.csv', sep="@")
df18 = pd.read_csv('~/Projects/MYP/Datasets/test/test17.csv', sep="@")
df19 = pd.read_csv('~/Projects/MYP/Datasets/test/test18.csv', sep="@")
df20 = pd.read_csv('~/Projects/MYP/Datasets/test/test19.csv', sep="@")
df21 = pd.read_csv('~/Projects/MYP/Datasets/test/test20.csv', sep="@")
df22 = pd.read_csv('~/Projects/MYP/Datasets/test/test21.csv', sep="@")
df23 = pd.read_csv('~/Projects/MYP/Datasets/test/test22.csv', sep="@")
df24 = pd.read_csv('~/Projects/MYP/Datasets/test/test23.csv', sep="@")
df25 = pd.read_csv('~/Projects/MYP/Datasets/test/test24.csv', sep="@")
df26 = pd.read_csv('~/Projects/MYP/Datasets/test/test25.csv', sep="@")
df27 = pd.read_csv('~/Projects/MYP/Datasets/test/test26.csv', sep="@")
df28 = pd.read_csv('~/Projects/MYP/Datasets/test/test27.csv', sep="@")

#frames = [df1, df2, df3, df4, df5, df6, df7, df8, df9, df10, df11, df12, df13, df14, df15, df16, df17, df18, df19, df20, df21, df22, df23, df24, df25, df26, df27, df28]

###concatenate multiple data CSV files
##all = pd.concat(frames)
##
##print(df1.shape)
##print(df2.shape)
##print(all.shape)


#Dynamically Load multiple csv file into Dataframe

result = pd.DataFrame()

path = os.path.expanduser("*.csv")

for fname in glob.glob(path):
    head, tail = os.path.split(fname)
    df = pd.read_csv(fname, sep="@")
    df2 = df.sort_values(by=['Views'], ascending=False).drop(['Favorite', 'videoID'], axis=1).iloc[15:20,:]
    df2['channel'] = tail
    result = pd.concat([result, df2])
result.sort_values(by=['channel']).iloc[0:10,]