<jupyter_start><jupyter_text>Evaluation of Baselines @Jen, fyi die Namen von den Klassen sind ganz unten im Notebook, weil die etwas bulky sind :D<jupyter_code>from sklearn.metrics import f1_score
import pandas as pd
from matplotlib.pyplot import figure
roberta_res = pd.read_csv('../../../../src/data/schemafiltereddata/roberta_predict_results_None.txt', sep='\t')
distilbert_res = pd.read_csv('../../../../src/data/schemafiltereddata/distilbert_predict_results_None.txt', sep='\t')
bert_res = pd.read_csv('../../../../src/data/schemafiltereddata/bert_predict_results_None.txt', sep='\t')
#res = pd.read_csv("predict_results_None_tinybert_large_2epochs.txt", sep="\t")
tinybert_res = pd.read_csv("predict_results_None_tinybert_large_25epochs.txt", sep="\t")
small_tinybert = pd.read_csv("predict_results_tinybert_small.txt", sep="\t")
tinybert_res.drop('index', axis=1, inplace=True)
real = pd.read_csv('../../../../src/data/schemafiltereddata/TrainTestTables/test_tables_9000_prepped.csv')<jupyter_output><empty_output><jupyter_text>F1 Scores<jupyter_code>#roberta
f1_score(roberta_res.prediction, real.label, average='micro')
# distilbert
f1_score(distilbert_res.prediction, real.label, average='micro')
# bert
f1_score(bert_res.prediction, real.label, average='micro')
# tinybert large - 25 epochs
f1_score(tinybert_res.prediction, real.label, average='micro')
# tinybert small - 25 epochs
f1_score(small_tinybert.prediction, real.label, average='micro')<jupyter_output><empty_output><jupyter_text>Evaluation of Results Create dataset<jupyter_code># we join the predictions to the test dataset to compare them
real['tinybert_pred'] = tinybert_res.prediction
real['roberta_pred'] = roberta_res.prediction
real['bert_pred'] = bert_res.prediction
real['distilbert_pred'] = distilbert_res.prediction
real.drop(['Unnamed: 0'], axis=1, inplace=True)
real
real.label.max()
real[real['label']==1]
df = pd.DataFrame()
label_list = []
tinybert_list = []
roberta_list = []
bert_list = []
distilbert_list = []
for i in range(0, 201):
    print('Label:', i)
    print('tinybert:', f1_score(real[real['label']==i].label, real[real['label']==i].tinybert_pred, average='micro'))
    print('roberta:', f1_score(real[real['label']==i].label, real[real['label']==i].roberta_pred, average='micro'))
    print('bert:', f1_score(real[real['label']==i].label, real[real['label']==i].bert_pred, average='micro'))
    print('distilbert:', f1_score(real[real['label']==i].label, real[real['label']==i].distilbert_pred, average='micro'))
    
    label_list.append(i)
    tinybert_list.append(f1_score(real[real['label']==i].label, real[real['label']==i].tinybert_pred, average='micro'))
    roberta_list.append(f1_score(real[real['label']==i].label, real[real['label']==i].roberta_pred, average='micro'))
    bert_list.append(f1_score(real[real['label']==i].label, real[real['label']==i].bert_pred, average='micro'))
    distilbert_list.append(f1_score(real[real['label']==i].label, real[real['label']==i].distilbert_pred, average='micro'))

df['Label'] = label_list
df['TinyBert'] = tinybert_list
df['Roberta'] = roberta_list
df['Bert'] = bert_list
df['Distilbert'] = distilbert_list

df.to_excel('F1_scores_matching.xlsx')<jupyter_output>Label: 0
tinybert: 0.967741935483871
roberta: 0.967741935483871
bert: 0.967741935483871
distilbert: 0.967741935483871
Label: 1
tinybert: 0.75
roberta: 0.25
bert: 0.25
distilbert: 0.75
Label: 2
tinybert: 0.9390243902439024
roberta: 0.9512195121951219
bert: 0.9573170731707317
distilbert: 0.9512195121951219
Label: 3
tinybert: 0.9280575539568345
roberta: 0.9568345323741008
bert: 0.9568345323741008
distilbert: 0.9496402877697842
Label: 4
tinybert: 0.4727272727272727
roberta: 0.6363636363636364
bert: 0.5909090909090909
distilbert: 0.6
Label: 5
tinybert: 0.7088948787061995
roberta: 0.9164420485175202
bert: 0.9056603773584906
distilbert: 0.894878706199461
Label: 6
tinybert: 0.3387096774193548
roberta: 0.17741935483870971
bert: 0.24193548387096775
distilbert: 0.20967741935483872
Label: 7
tinybert: 0.3333333333333333
roberta: 0.14285714285714285
bert: 0.2857142857142857
distilbert: 0.3333333333333333
Label: 8
tinybert: 0.31666666666666665
roberta: 0.0
bert: 0.03333333333333333
distilbert: 0.2666[...]<jupyter_text>Which test colums are especially often misclassified?<jupyter_code>real[real['label'] != real['tinybert_pred']].label.value_counts().sort_index() / real.label.value_counts().sort_index()
relative_fails = (real[real['label'] != real['tinybert_pred']].label.value_counts().sort_index() / real.label.value_counts().sort_index())
relative_fails.sort_values().tail(15)

figure(figsize=(25, 15), dpi=80)
relative_fails.sort_values().plot(kind='bar', color='cornflowerblue')
relative_fails = (real[real['label'] != real['roberta_pred']].label.value_counts().sort_index() / real.label.value_counts().sort_index())
figure(figsize=(25, 15), dpi=80)
relative_fails.sort_values().plot(kind='bar', color='cornflowerblue')
relative_fails = (real[real['label'] != real['bert_pred']].label.value_counts().sort_index() / real.label.value_counts().sort_index())
figure(figsize=(25, 15), dpi=80)
relative_fails.sort_values().plot(kind='bar', color='cornflowerblue')
relative_fails = (real[real['label'] != real['distilbert_pred']].label.value_counts().sort_index() / real.label.value_counts().sort_index())
figure(figsize=(25, 15), dpi=80)
relative_fails.sort_values().plot(kind='bar', color='cornflowerblue')
relative_fails.sort_values().plot()
relative_fails.sort_values().head(40)
# nas are perfectly classified
relative_fails.sort_values().tail(40)<jupyter_output><empty_output><jupyter_text>- 180    reviewsProduct- 92     headlineRecipe- 109    itemlistelementProduct- 57     datepublishedProduct- 49     copyrightyearCreativeWork- 118    mainentityofpageCreativeWork- 144    weightProduct- 18     aggregateratingRecipe- 146    offersBook- 201    founderLocalBusiness<jupyter_code>real[real['label']==180]
# reviewsProduct 
real[real['label']==180]
real[real['label']==201]
real[real['label']==92]
# headlineRecipe wird oft verwechselt mit 137 -> nameRecipe
real[real['label']==92]
real[real['label']==137]
real[real['label']==146]
# 146 often confused with 147
real[real['label']==197]
# NANs are perfectly classified<jupyter_output><empty_output><jupyter_text>Was passiert mit leerem Text -> wird immer zu 164 klassifiziert??<jupyter_code>real[real['text']==" "]
# 164 -> pricerangeLocalBusiness
# test set has in general weird values for 164 
real[real['label']==164].text.value_counts()
real[real['text']==" "].pred.value_counts()<jupyter_output><empty_output><jupyter_text>What columns are they misclassified with?<jupyter_code>figure(figsize=(20, 250), dpi=80)
real[real['label'] != real['pred']].groupby('label').pred.value_counts().plot(kind='barh')
print(real[real['label'] != real['pred']].groupby('label').pred.value_counts())<jupyter_output><empty_output><jupyter_text>What are possible reasons for this misclassification -> data type, values of columns (e.g. empty, random etc.)<jupyter_code>labels_new = {'worstratingProduct': 0,
 'acceptsreservationsRestaurant': 1,
 'additionalnamePerson': 2,
 'additionalpropertyProduct': 3,
 'addressHotel': 4,
 'addressLocalBusiness': 5,
 'addressPerson': 6,
 'addressProduct': 7,
 'addressRestaurant': 8,
 'addresslocalityLocalBusiness': 9,
 'addressregionLocalBusiness': 10,
 'affiliationPerson': 11,
 'aggregateratingBook': 12,
 'aggregateratingCreativeWork': 13,
 'aggregateratingHotel': 14,
 'aggregateratingLocalBusiness': 15,
 'aggregateratingProduct': 16,
 'aggregateratingRecipe': 17,
 'aggregateratingRestaurant': 18,
 'alternatenameProduct': 19,
 'alternativeheadlineCreativeWork': 20,
 'areaservedLocalBusiness': 21,
 'articlebodyCreativeWork': 22,
 'audienceProduct': 23,
 'authorBook': 24,
 'authorCreativeWork': 25,
 'authorProduct': 26,
 'authorRecipe': 27,
 'availabilityProduct': 28,
 'bestratingProduct': 29,
 'birthdatePerson': 30,
 'birthplacePerson': 31,
 'bookeditionBook': 32,
 'bookformatBook': 33,
 'brandProduct': 34,
 'breadcrumbProduct': 35,
 'byartistMusicAlbum': 36,
 'byartistMusicRecording': 37,
 'categoryProduct': 38,
 'citystatezipLocalBusiness': 39,
 'colorProduct': 40,
 'commentcountCreativeWork': 41,
 'conditionProduct': 42,
 'contactpointLocalBusiness': 43,
 'contactpointPerson': 44,
 'cookingmethodRecipe': 45,
 'cooktimeRecipe': 46,
 'copyrightholderCreativeWork': 47,
 'copyrightyearCreativeWork': 48,
 'creatorCreativeWork': 49,
 'datecreatedCreativeWork': 50,
 'datecreatedMusicRecording': 51,
 'datemodifiedCreativeWork': 52,
 'datemodifiedRecipe': 53,
 'datepublishedBook': 54,
 'datepublishedCreativeWork': 55,
 'datepublishedMusicRecording': 56,
 'datepublishedProduct': 57,
 'datepublishedRecipe': 58,
 'deathdatePerson': 59,
 'depthProduct': 60,
 'disambiguatingdescriptionProduct': 61,
 'doortimeEvent': 62,
 'durationEvent': 63,
 'durationMusicRecording': 64,
 'emailHotel': 65,
 'emailLocalBusiness': 66,
 'emailPerson': 67,
 'enddateEvent': 68,
 'episodenumberTVEpisode': 69,
 'worksforPerson': 70,
 'familynamePerson': 71,
 'faxnumberLocalBusiness': 72,
 'faxnumberPerson': 73,
 'genderPerson': 74,
 'genreBook': 75,
 'genreCreativeWork': 76,
 'genreMusicRecording': 77,
 'geoHotel': 78,
 'geoLocalBusiness': 79,
 'geoPlace': 80,
 'geoProduct': 81,
 'geoRestaurant': 82,
 'givennamePerson': 83,
 'gtin12Product': 84,
 'gtin13Product': 85,
 'gtin14Product': 86,
 'gtin8Product': 87,
 'gtinProduct': 88,
 'hasmapLocalBusiness': 89,
 'hasmenuRestaurant': 90,
 'headlineCreativeWork': 91,
 'headlineRecipe': 92,
 'heightPerson': 93,
 'heightProduct': 94,
 'homelocationPerson': 95,
 'identifierProduct': 96,
 'worklocationPerson': 97,
 'ingredientsRecipe': 98,
 'inlanguageBook': 99,
 'inlanguageCreativeWork': 100,
 'interactioncountCreativeWork': 101,
 'interactionstatisticCreativeWork': 102,
 'interactiontypeCreativeWork': 103,
 'isbnBook': 104,
 'ispartofRecipe': 105,
 'isrelatedtoProduct': 106,
 'issimilartoProduct': 107,
 'itemconditionProduct': 108,
 'itemlistelementProduct': 109,
 'jobtitlePerson': 110,
 'keywordsCreativeWork': 111,
 'keywordsRecipe': 112,
 'knowslanguagePerson': 113,
 'legalnameLocalBusiness': 114,
 'locationEvent': 115,
 'locationLocalBusiness': 116,
 'mainentityofpageCreativeWork': 117,
 'mainentityofpagePerson': 118,
 'mainentityofpageProduct': 119,
 'mainentityofpageRecipe': 120,
 'makesofferPerson': 121,
 'manufacturerProduct': 122,
 'materialProduct': 123,
 'memberofPerson': 124,
 'menuRestaurant': 125,
 'modelProduct': 126,
 'mpnProduct': 127,
 'nameBook': 128,
 'nameCreativeWork': 129,
 'nameEvent': 130,
 'nameHotel': 131,
 'nameLocalBusiness': 132,
 'nameMusicAlbum': 133,
 'nameMusicRecording': 134,
 'namePlace': 135,
 'nameProduct': 136,
 'nameRecipe': 137,
 'nameRestaurant': 138,
 'nameTVEpisode': 139,
 'nationalityPerson': 140,
 'numberofpagesBook': 141,
 'numtracksMusicAlbum': 142,
 'nutritionRecipe': 143,
 'weightProduct': 144,
 'offersBook': 145,
 'offersCreativeWork': 146,
 'offersProduct': 147,
 'openinghoursLocalBusiness': 148,
 'openinghoursRestaurant': 149,
 'openinghoursspecificationLocalBusiness': 150,
 'openinghoursspecificationPlace': 151,
 'openinghoursspecificationRestaurant': 152,
 'organizerEvent': 153,
 'partofseriesTVEpisode': 154,
 'paymentacceptedLocalBusiness': 155,
 'widthProduct': 156,
 'performersEvent': 157,
 'performtimeRecipe': 158,
 'postalcodeLocalBusiness': 159,
 'preptimeRecipe': 160,
 'priceProduct': 161,
 'pricecurrencyProduct': 162,
 'pricerangeHotel': 163,
 'pricerangeLocalBusiness': 164,
 'pricerangeRestaurant': 165,
 'publisherBook': 166,
 'publisherCreativeWork': 167,
 'publisherRecipe': 168,
 'ratingvalueProduct': 169,
 'recipecategoryRecipe': 170,
 'recipecuisineRecipe': 171,
 'recipeingredientRecipe': 172,
 'recipeinstructionsRecipe': 173,
 'recipeyieldRecipe': 174,
 'releasedateProduct': 175,
 'reviewLocalBusiness': 176,
 'reviewProduct': 177,
 'reviewRecipe': 178,
 'reviewcountProduct': 179,
 'reviewsProduct': 180,
 'sameasLocalBusiness': 181,
 'sameasPerson': 182,
 'sameasPlace': 183,
 'servescuisineRestaurant': 184,
 'shop-currencyProduct': 185,
 'starratingHotel': 186,
 'streetaddressLocalBusiness': 187,
 'suitablefordietRecipe': 188,
 'telephoneHotel': 189,
 'telephoneLocalBusiness': 190,
 'telephonePerson': 191,
 'telephonePlace': 192,
 'telephoneRestaurant': 193,
 'titleProduct': 194,
 'totaltimeRecipe': 195,
 'trackMusicAlbum': 196,
 'typicalagerangeEvent': 197,
 'versionCreativeWork': 198,
 'weightPerson': 199,
 'offerdetailsProduct': 200,
 'founderLocalBusiness': 201}
df = pd.Series(labels_new)
path = '/work-ceph/bizer-tp2021/data_integration_using_deep_learning/src/data/data/CSV_files'
files = pd.read_csv(path + '/all_files_cleaned.csv')
files
files['labels_new'] = files['column_name'] + files['filename'].str.split('_').str[0] 
new_df = files[['labels_new', 'data_type', 'filename']]
df_match = pd.DataFrame({'index': df.index, 'vals': df.values})
ultimate = pd.merge(new_df, df_match, left_on='labels_new', right_on='index')
ultimate.drop(['labels_new'], axis=1, inplace=True)

real_new = pd.merge(real, ultimate, left_on='label', right_on='vals')
real_new.drop(['vals'], inplace=True, axis=1)
real_new.data_type.value_counts()<jupyter_output><empty_output><jupyter_text>Shows how much problems the models have with the different datatypes<jupyter_code>(real_new[real_new['label']!= real_new['tinybert_pred']].data_type.value_counts() / real_new.data_type.value_counts()).plot(kind='bar', color='cornflowerblue')
real_new
for t in real_new['data_type'].unique():
    print(t, ':', f1_score(real_new[real_new['data_type']== t].label, real_new[real_new['data_type']== t].tinybert_pred, average='micro')) 
    
(real_new[real_new['label']!= real_new['roberta_pred']].data_type.value_counts() / real_new.data_type.value_counts()).plot(kind='bar',color='cornflowerblue')
(real_new[real_new['label']!= real_new['bert_pred']].data_type.value_counts() / real_new.data_type.value_counts()).plot(kind='bar',color='cornflowerblue')
(real_new[real_new['label']!= real_new['distilbert_pred']].data_type.value_counts() / real_new.data_type.value_counts()).plot(kind='bar',color='cornflowerblue')
real_new<jupyter_output><empty_output><jupyter_text>When gtin numbers are confused, then often with other gtin numbers eg 85<jupyter_code>real_new[real_new['label']==84]
real_new[real_new['label']==85]
# distinguish classes -> Hotel etc. 
# avrg f1 score per class -> comparable ?
real_new.filename
products = real_new[real_new['filename']=='Product_stats.csv']
f1_score(products.label, products.tinybert_pred, average='micro')
f1_score(products.label, products.roberta_pred, average='micro')
f1_score(products.label, products.bert_pred, average='micro')
f1_score(products.label, products.distilbert_pred, average='micro')
recipe = real_new[real_new['filename']=='Recipe_stats.csv']
creativework = real_new[real_new['filename']=='CreativeWork_stats.csv']
event = real_new[real_new['filename']=='Event_stats.csv']
musicrecording = real_new[real_new['filename']=='MusicRecording_stats.csv']
localbusiness = real_new[real_new['filename']=='LocalBusiness_stats.csv']
person = real_new[real_new['filename']=='Person_stats.csv']
hotel = real_new[real_new['filename']=='Hotel_stats.csv']
book = real_new[real_new['filename']=='Book_stats.csv']
place = real_new[real_new['filename']=='Place_stats.csv']
restaurant = real_new[real_new['filename']=='Restaurant_stats.csv']
musicalbum = real_new[real_new['filename']=='MusicAlbum_stats.csv']
tvepisode = real_new[real_new['filename']=='TVEpisode_stats.csv']
f1_score(recipe.label, recipe.tinybert_pred, average='micro')
f1_score(recipe.label, recipe.roberta_pred, average='micro')
f1_score(recipe.label, recipe.bert_pred, average='micro')
f1_score(recipe.label, recipe.distilbert_pred, average='micro')
f1_score(creativework.label, creativework.tinybert_pred, average='micro')
f1_score(creativework.label, creativework.roberta_pred, average='micro')
f1_score(creativework.label, creativework.bert_pred, average='micro')
f1_score(creativework.label, creativework.distilbert_pred, average='micro')
f1_score(event.label, event.tinybert_pred, average='micro')
f1_score(event.label, event.roberta_pred, average='micro')
f1_score(event.label, event.bert_pred, average='micro')
f1_score(event.label, event.distilbert_pred, average='micro')
f1_score(musicrecording.label, musicrecording.tinybert_pred, average='micro')

f1_score(musicrecording.label, musicrecording.roberta_pred, average='micro')

f1_score(musicrecording.label, musicrecording.bert_pred, average='micro')

f1_score(musicrecording.label, musicrecording.distilbert_pred, average='micro')

f1_score(localbusiness.label, localbusiness.tinybert_pred, average='micro')

f1_score(localbusiness.label, localbusiness.roberta_pred, average='micro')

f1_score(localbusiness.label, localbusiness.bert_pred, average='micro')

f1_score(localbusiness.label, localbusiness.distilbert_pred, average='micro')

f1_score(person.label, person.tinybert_pred, average='micro')

f1_score(person.label, person.roberta_pred, average='micro')

f1_score(person.label, person.bert_pred, average='micro')

f1_score(person.label, person.distilbert_pred, average='micro')

f1_score(hotel.label, hotel.tinybert_pred, average='micro')

f1_score(hotel.label, hotel.roberta_pred, average='micro')

f1_score(hotel.label, hotel.bert_pred, average='micro')

f1_score(hotel.label, hotel.distilbert_pred, average='micro')

f1_score(book.label, book.tinybert_pred, average='micro')

f1_score(book.label, book.roberta_pred, average='micro')

f1_score(book.label, book.bert_pred, average='micro')

f1_score(book.label, book.distilbert_pred, average='micro')

f1_score(place.label, place.tinybert_pred, average='micro')

f1_score(place.label, place.roberta_pred, average='micro')

f1_score(place.label, place.bert_pred, average='micro')

f1_score(place.label, place.distilbert_pred, average='micro')

f1_score(restaurant.label, restaurant.tinybert_pred, average='micro')
f1_score(restaurant.label, restaurant.roberta_pred, average='micro')
f1_score(restaurant.label, restaurant.bert_pred, average='micro')
f1_score(restaurant.label, restaurant.distilbert_pred, average='micro')
f1_score(musicalbum.label, musicalbum.tinybert_pred, average='micro')
f1_score(musicalbum.label, musicalbum.roberta_pred, average='micro')
f1_score(musicalbum.label, musicalbum.bert_pred, average='micro')
f1_score(musicalbum.label, musicalbum.distilbert_pred, average='micro')
f1_score(tvepisode.label, tvepisode.tinybert_pred, average='micro')
f1_score(tvepisode.label, tvepisode.roberta_pred, average='micro')
f1_score(tvepisode.label, tvepisode.bert_pred, average='micro')
f1_score(tvepisode.label, tvepisode.distilbert_pred, average='micro')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15621503543076348, 'fraction_numerical': 0.06579998934413128, 'mean_word_length': 8.796450939457202, 'pattern_counts': {'":': 0, '<': 40, '<?xml version=': 0, '>': 46, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 4, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '430909', 'license': 'MIT', 'n_tokens_mistral': 7701, 'n_tokens_neox': 6568, 'n_words': 1080, 'path': 'notebooks/Schema/Baseline/Evaluation/Evaluation Baselines.ipynb', 'repo_name': 'JenniferHahn/data_integration_using_deep_learning', 'size': '18769'}