<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Auto-caption
Date
Step1:  Read file
Step2:  Access data of multiIndex dataframe
pandas, how to access multiIndex dataframe?
Step3:  Dataframe that i want to match
Step5:  string matching funciton
1-to-1 matching (or mapping)
Github of fuzzywuzzy
Step6:  show all stats (Ans) and matching results (algorithm)
<ASSISTANT_TASK:>
Python Code:
# system
import os
import sys
# 3rd party lib
import pandas as pd
from sklearn.cluster import KMeans
from fuzzywuzzy import fuzz # stirng matching
print('Python verison: {}'.format(sys.version))
print('\n############################')
print('Pandas verison: {}'.format(pd.show_versions()))
Explanation: Auto-caption
Date: 2018/11/14
Purpose: swarm name matching using the data below
Data source:
auto_caption4.csv
auto_caption5.csv
auto_caption7.csv
auto_caption8.csv
auto_caption9.csv
auto_caption10.csv
auto_caption11.csv
End of explanation
standard_df = pd.read_csv('auto_caption4.csv', names=['cluster_ID','timestamp','event','name'])
print('There are {} clusters in standard_df\n'.format(len(standard_df['cluster_ID'].unique())))
print(standard_df.head(5))
# default is axis=0
standard_df_groupby = standard_df.groupby(['cluster_ID','name']).agg({'name':['count']})
print(standard_df.groupby(['cluster_ID','name']).agg({'name':['count']}))
Explanation: Read file
End of explanation
# get column names
df = standard_df_groupby.loc[0].reset_index() 
flat_column_names = []
for level in df.columns:        
    # tuple to list    
    flat_column_names.extend(list(level)) # extend(): in-place
    
# remove duplicate and empty
flat_column_names = filter(None, flat_column_names) # filter empty
flat_column_names = list(set(flat_column_names)) # deduplicate
print('original order: {}'.format(flat_column_names))
# change member order of list due to set is a random order
if flat_column_names[0] == 'count':
    myorder = [1,0]
    flat_column_names = [flat_column_names[i] for i in myorder]
    print('New order: {}'.format(flat_column_names))
standard_df_dict = {}
# Transform multi-index to single index, and update string to dict standard_df_dict
for id_of_cluster in standard_df['cluster_ID'].unique():
    print('\n# of cluster: {}'.format(id_of_cluster))
    df = standard_df_groupby.loc[id_of_cluster].reset_index() 
    df.columns = flat_column_names
    print(df.sort_values(by=['count'], ascending=False))
    
    standard_df_dict.update({id_of_cluster: df.name.str.cat(sep='  ', na_rep='?')})    
print('################################')
print('\nDictionary of swarm data: \n{}'.format(standard_df_dict))
Explanation: Access data of multiIndex dataframe
pandas, how to access multiIndex dataframe?
End of explanation
matching_df1 = pd.read_csv('auto_caption5.csv', names=['cluster_ID','timestamp','event','name'])
print('There are {} clusters in standard_df\n'.format(len(matching_df1['cluster_ID'].unique())))
print(matching_df1.head(5))
# default is axis=0
matching_df1_groupby = matching_df1.groupby(['cluster_ID','name']).agg({'name':['count']})
print(matching_df1.groupby(['cluster_ID','name']).agg({'name':['count']}))
# get column names
df = matching_df1_groupby.loc[0].reset_index() 
flat_column_names = []
for level in df.columns:        
    # tuple to list    
    flat_column_names.extend(list(level)) # extend(): in-place
# remove duplicate and empty
flat_column_names = filter(None, flat_column_names) # filter empty
flat_column_names = list(set(flat_column_names)) # deduplicate
print(flat_column_names)
# change member order of list due to set is a random order
if flat_column_names[0] == 'count':
    myorder = [1,0]
    flat_column_names = [flat_column_names[i] for i in myorder]
    print('New order: {}'.format(flat_column_names))
matching_df1_dict = {}
# Transform multi-index to single index, and update string to dict standard_df_dict
for id_of_cluster in matching_df1['cluster_ID'].unique():
    print('\n# of cluster: {}'.format(id_of_cluster))
    df = matching_df1_groupby.loc[id_of_cluster].reset_index() 
    df.columns = flat_column_names
    print(df.sort_values(by=['count'], ascending=False))
    
    matching_df1_dict.update({id_of_cluster: df.name.str.cat(sep='  ', na_rep='?')})    
print('################################')
print('\nDictionary of swarm data: \n{}'.format(matching_df1_dict))
Explanation: Dataframe that i want to match
End of explanation
def matching_two_dicts_of_swarm(standard_dict, matching_dict, res_dict):    
        
    match two dictoinaries with same amount of key-value pairs
    and return matching result, a dict of dict called res_dict.
    
    * standard_dict: The standard of dict
    * matching_dict: The dict that i want to match
    * res_dict: the result, a dict of dict
    
    key = 0 # key: number, no string    
    pop_list = [k for k,v in matching_dict.items()]
    print(pop_list)
    for i in standard_dict.keys(): # control access index of standard_dict. a more pythonic way
        threshold = 0
        for j in pop_list: # control access index of matching_dict
            f_ratio = fuzz.ratio(standard_dict[i], matching_dict[j])
            if f_ratio > threshold: # update matching result only when the fuzz ratio is greater
                print('New matching fuzz ratio {} is higher than threshold {}'\
                      .format(f_ratio, threshold))
                key = j # update key
                threshold = f_ratio # update threshold value
                print('Update new threshold {}'\
                      .format(threshold))                
                res_dict.update({i: {j: matching_dict[i]}}) #         
        # pop out matched key-value pair of matching dict
        if pop_list:
            pop_list.remove(key) # remove specific value. remove() fails when no elements remains
        print(res_dict)
    return res_dict
res_dict = {}
res_dict = matching_two_dicts_of_swarm(standard_df_dict, matching_df1_dict, res_dict)
print(res_dict)
Explanation: string matching funciton
1-to-1 matching (or mapping)
Github of fuzzywuzzy: link
Search keyword: You can try 'fuzzywuzzy' + 'pandas'
End of explanation
std_dict_to_df = pd.DataFrame.from_dict(standard_df_dict, orient='index', columns=['Before: function_name'])
std_dict_to_df['std_cluster_ID'] = std_dict_to_df.index
std_dict_to_df = std_dict_to_df[['std_cluster_ID', 'Before: function_name']]
std_dict_to_df
mtch_df1_dict_to_df = pd.DataFrame.from_dict(matching_df1_dict, orient='index', columns=['Matching function_name'])
mtch_df1_dict_to_df
res_dict_to_df = pd.DataFrame()
res_dict_to_df
res_list = [k for k,v in res_dict.items()]
for key in res_list:
    df = pd.DataFrame.from_dict(res_dict[key], orient='index', columns=['After: funciton name']) # res_dict[key]: a dict
    df['mtch_cluster_ID'] = df.index
    #print(df)
    res_dict_to_df = res_dict_to_df.append(df, ignore_index=True) # df.append(): not in-place
res_dict_to_df = res_dict_to_df[['mtch_cluster_ID', 'After: funciton name']]
print(res_dict_to_df.head(5))
final_df = pd.concat([std_dict_to_df, res_dict_to_df], axis=1)
final_df
Explanation: show all stats (Ans) and matching results (algorithm)
End of explanation
<END_TASK>