<jupyter_start><jupyter_text>1. Read raw data<jupyter_code>df = pd.read_csv('data/ISS mandate - Raw data.csv')
test = df[['TI','DE','ID' , 'DI']].head(100)
test
test['DE'][0]
test['DE'] = test['DE'].str.replace('; ', ', ')
test['ID'] = test['ID'].str.replace('; ', ', ')

test['DE'] = test['DE'].str.replace('-', ' ')
test['ID'] = test['ID'].str.replace('-', ' ')

test['DE'] = test['DE'].fillna(' ')
test['ID'] = test['ID'].fillna(' ')
# 2. Combine keyword columns
test['keywords'] = test['DE'] + ', ' + test['ID']
# 3. lowercase keyword case
test['keywords'] = test['keywords'].str.lower()

test = test[['TI','keywords' , 'DI']]
# 4. singularize
# test['keywords'] = test['keywords'].apply(lambda x: ', '.join([inf.singularize(item) for item in x.split(', ')]))
# maybe lowercase afterwards
# POS tags - if noun don't singularize
# 5. Remove duplicates
# https://stackoverflow.com/questions/47316783/python-dataframe-remove-duplicate-words-in-the-same-cell-within-a-column-in-pyt
test['keywords'] = (test['keywords'].str.split(', ')
                              .apply(lambda x: OrderedDict.fromkeys(x).keys())
                              .str.join(', '))
# 6. Split strings
test['keywords'] = test['keywords'].str.split(', ', expand=False)
test['keywords'][0]
test.shape[0]
# 7. pandas explode - Transform each element of a list-like to a row, replicating the index values.
test_ = test.explode('keywords')

test_ = test_[test_['keywords'] != ' ']

test_.reset_index(inplace=True, drop=True)
test_['keywords'].nunique()
test_['keywords']<jupyter_output><empty_output><jupyter_text>___  Extract from Wordnet<jupyter_code>from nltk.corpus import wordnet
wn_lemmas = set(wordnet.all_lemma_names())

def wordnet_exits(word):
    word = word.replace(' ', '_')
    if word in wn_lemmas:
        return True
    else:
        return False
wordnet_exits('microgravity')
def acquire_definition(keyword):
    define = wn.synsets(keyword)[0].definition()
    return define
def acquire_hypernym(keyword):
    hypernym = wn.synsets(keyword)[0].hypernyms()[0].name().split(".")[0]
    return hypernym
def acquire_wordnet_id(keyword):
    synset = wn.synsets(keyword)[0].name()
    ss = wn.synset(synset)
    offset = str(ss.offset()).zfill(8) + '-' + ss.pos()
    return offset
acquire_wordnet_id('combustion')

test_.reset_index(drop=True, inplace=True)
test_.head()
test_.shape[0]
df_test_ = test_.copy()
df_test_.rename(columns={'TI': 'topic_title', 'keywords': 'definition', 
                       'DI': 'reference' }, inplace=True)
df_test_[df_test_['definition'].str.contains('^mar$', regex=True)]
df_test_['definition'] = df_test_['definition'].str.replace('^mar$', 'mars', regex=True)
df_test_[df_test_['definition'].str.contains('^mars', regex=True)]
# df_test_ = df_test_.head(10)
df_test_.shape[0]
data = []
j = 1
for index, row in df_test_.iterrows():
    print('index: ', index, 'keywords: ', row['definition'])
    if wordnet_exits(row['definition']):
        word = row['definition']
        word = word.replace(' ', '_')
        print('Word exists in WordNet')
        print('*****************')
        print('j: ', j)
        key = '<KEY>)
        print('initial key: ', key)

        data.append(dict(zip(
            ('key',
             'type', 
             'topic_title', 
             'definition', 
             'reference',
            'wordnet_ID'), 
            (key,
            'topic',
             row['topic_title'],
             word,  
             '',
           acquire_wordnet_id(word))
        )))
        j = j + 1
        print('j: ', j)
        key = 'T' + str(j)
        print('key: ', key)

        data.append(dict(zip(
            ('key',
             'type', 
             'topic_title', 
             'definition', 
             'reference',
            'wordnet_ID'), 
            (key,
            'topic',
             word,
             acquire_definition(word),  
             '',
            acquire_wordnet_id(word))
        )))
        
        try:
            hypernym = acquire_hypernym(word)
            j = j + 1
            print('j: ', j)
            key = 'T' + str(j)
            print('key: ', key)

            data.append(dict(zip(
                ('key',
                 'type', 
                 'topic_title', 
                 'definition', 
                 'reference',
                'wordnet_ID'), 
                (key,
                'cluster',
                 hypernym,
                 acquire_definition(hypernym),  
                 '',
                acquire_wordnet_id(hypernym))
            )))
        except:
            pass

        j = j + 1
        print('j: ', j)
        key = 'T' + str(j)
        print('key: ', key)
        # repeat for i >= 4 through 9
        i = 4
#         while i <=9:
#             try:
#                 hypernym = acquire_hypernym(hypernym)
#                 key = 'T' + str(j)
#                 print('j: ', j)
#                 key = 'T' + str(j)
#                 print('key: ', key)

#                 data.append(dict(zip(
#                     ('key',
#                      'type', 
#                      'topic_title', 
#                      'definition', 
#                      'reference',
#                 'wordnet_ID'), 
#                     (key,
#                     'cluster',
#                      hypernym,
#                      acquire_definition(hypernym),  
#                      '',
#                 '')
#                 )))
#             except:
#                 pass
#             i = i +1
#             j = j + 1

        df_topic = pd.DataFrame(data)
    
    else:
        print('Word DOES NOT exists in WordNet')
        print('*****************')

df_topic.head(10)
df_topic[df_topic['type'] == 'cluster'].shape[0]
df_topic[df_topic['key'] == 'T264']
df_topic[df_topic['definition'].str.contains('^ab', regex=True)]
df_topic[df_topic['key'] == 'T204']
df_topic[df_topic['topic_title'].str.contains('device', regex=True)]
df_topic[df_topic['key'].isin(['T176','T177', 'T178'])]
df_topic[df_topic['key'].isin(['T358','T359', 'T360'])]
df_topic[df_topic['key'].isin(['T781','T782', 'T783'])]
# df_topic.to_csv('20200122_df_topic.csv', index=None)
list_of_duplicated_topic_titles = \
df_topic[(df_topic.duplicated('topic_title')) 
         & (df_topic['type'] == 'cluster')
#          & (df_topic['topic_title'].str.contains('^device', regex=True))
        ]['topic_title'].values.tolist()
# list_of_duplicated_topic_titles
df_topic[(df_topic['topic_title'].str.contains('device'))]
df_topic[df_topic['topic_title'].str.contains('measurement')]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18014484007242004, 'fraction_numerical': 0.012824381412190706, 'mean_word_length': 2.1506653992395437, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '531720', 'license': 'MIT', 'n_tokens_mistral': 2202, 'n_tokens_neox': 2109, 'n_words': 355, 'path': '20200124_brane_etl.ipynb', 'repo_name': 'TheBrane/science-knowledge-data-acquisition', 'size': '6633'}