<jupyter_start><jupyter_text>DuckDB query graph<jupyter_code>profiling_dir = cwd+"/profiling"
print(profiling_dir)
%cd profiling
%ls
counter = 0
for json_filename in os.listdir(profiling_dir):
    if(json_filename != ".DS_Store"):
        counter+=1
        html_filename = json_filename +'.html'
        json_dir = profiling_dir +"/"+ json_filename
        html_dir = profiling_dir +"/"+ html_filename

        print(json_dir, " --> ", html_dir)
        test = duckdb_query_graph.generate(json_dir, html_dir)
    
print("🧽🧽🧽🧽🧽🧽🧽🧽🧽🧽🧽🧽counter = ", counter)<jupyter_output><empty_output><jupyter_text>Plotting plotting the *.json under duckdb-rl-back-up-visualization/query-graph/profiling<jupyter_code># set the style of plot
plt.rcParams["figure.figsize"] = (20,3)
# function: clean the data
# drop useless columns, rename column names, move 'job_query' column to the first, sort by 'job_query'
def clean_df(df):
    df = df.drop(['tree.name', 'tree.timing', 'tree.cardinality', 'tree.extra_info', 'tree.children'], axis = 1)
    df.columns = df.columns.str.replace('[>]','.', regex=True)
    df.columns = df.columns.str.replace('[ ]','', regex=True)
    df = df[ ['job_query'] + [ col for col in df.columns if col != 'job_query' ] ]
    df.sort_values(by = 'job_query')
    return df
# function: load json files
def read_json_from_dir(folder_dir, df):
    count_json = 0 #initialize amount of json files
    for file in os.listdir(folder_dir):
        if file.endswith(".json"):
            file_dir = folder_dir+'/'+file
            with open(file_dir, 'r') as f:
                count_json+=1
                data = f.read()
                data_df = pd.json_normalize(json.loads(data))
                data_df['job_query'] = file
                #print(type(data_df))
                df = pd.concat([df,data_df], sort=False)
    df = clean_df(df)
    print(str(count_json) + " json files have been loaded!")
    return df<jupyter_output><empty_output><jupyter_text>1. JOB with DuckDB Optimizer<jupyter_code>job_duckdb_dir = cwd + '/records/with_complete_imdb/0426 profiling with DuckDB optimizer'
df_1 = pd.DataFrame() #initialize datafram
df_1 = read_json_from_dir(job_duckdb_dir, df_1)
display(df_1)
# df_1[['job_query','result']].plot(x='job_query')
# plt.show()
# df_1.plot(kind='bar',x='job_query',y='result',color='red',width = 1)
# plt.show()<jupyter_output><empty_output><jupyter_text>2. JOB without any Optimizer<jupyter_code>job_no_optimizer_dir = cwd + '/records/with_complete_imdb/0427 profiling without optimizer'
df_2 = pd.DataFrame() #initialize datafram
df_2 = read_json_from_dir(job_no_optimizer_dir, df_2)
display(df_2)
# df_2[['job_query','result']].plot(x='job_query')
# plt.show()
# df_2.plot(kind='bar',x='job_query',y='result',color='blue',width = 1)<jupyter_output><empty_output><jupyter_text>3. JOB without join-order Optimizertodo...  now plot them together in one graph:<jupyter_code>ax = df_1[['job_query','result']].plot(x='job_query')
df_2[['job_query','result']].plot(x='job_query', ax = ax)
bar_pos_1 = np.arange(len(df_1['job_query']))

bar_pos_2 = [x + bar_width for x in bar_pos_1]
bar_pos_1
#bar_pos_2
bar_width = 0.3

# position on x-axis for different df
bar_pos_1 = np.arange(len(df_1['job_query']))
bar_pos_2 = [x + bar_width for x in bar_pos_1]


plt.bar(bar_pos_1, df_1['result'].to_numpy(), color ='r', width = bar_width, label ='JOB with DuckDB optimizer')
plt.bar(bar_pos_2, df_2['result'].to_numpy(), color ='g', width = bar_width, label ='JOB without any optimizer')

plt.xlabel('JOB Query', fontweight ='bold', fontsize = 15)
plt.ylabel('Running time', fontweight ='bold', fontsize = 15)

plt.xticks([r + bar_width for r in range(len(df_1['job_query']))],
            df_1['job_query'].to_numpy(),
            rotation=90)

plt.legend()
plt.show()
# ax = df_1.plot(kind='bar', bar_pos_1, x='job_query',y='result',color='red',width = bar_width, label ='JOB with DuckDB optimizer')
# df_2.plot(kind='bar',x='job_query',y='result',color='blue', ax = ax)
# https://www.geeksforgeeks.org/bar-plot-in-matplotlib/<jupyter_output><empty_output><jupyter_text>------------------ draft --------------------<jupyter_code>test_data = {
    'A':['A1', 'A2', 'A3', 'A4', 'A5'], 
    'B':['B1', 'B2', 'B3', 'B4', 'B5'], 
    'C':['C1', 'C2', 'C3', 'C4', 'C5'], 
    'D':['D1', 'D2', 'D3', 'D4', 'D5'], 
    'E':['E1', 'E2', 'E3', 'E4', 'E5'] }
test_df = pd.DataFrame(test_data)
display(test_df)
test_df.drop(['C','D'], axis =1)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18228355219229914, 'fraction_numerical': 0.01958602270198086, 'mean_word_length': 4.4738124238733255, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 27, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '150528', 'license': 'MIT', 'n_tokens_mistral': 1808, 'n_tokens_neox': 1738, 'n_words': 321, 'path': 'chuying/visualization/script.ipynb', 'repo_name': 'ChuyingHe/duckdb-master-rl', 'size': '4493'}