<jupyter_start><jupyter_text>Coinopsy Scraped Data Cleaning  Load text file to df<jupyter_code>import pandas as pd

with open('coinopsy_raw.txt', 'r', encoding='utf-8') as f:
    contents = f.readlines()
    
coin_names = [value.strip() for index,value in enumerate(contents) if index % 2 == 0]
coin_details = [value.strip() for index,value in enumerate(contents) if index % 2 != 0]

df_coins = pd.DataFrame(columns=['Coin Name', 'Raw Details'])
df_coins['Coin Name'] = pd.Series(coin_names)
df_coins['Raw Details'] = pd.Series(coin_details)

df_coins
df_coins.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2409 entries, 0 to 2408
Data columns (total 2 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   Coin Name    2409 non-null   object
 1   Raw Details  2409 non-null   object
dtypes: object(2)
memory usage: 37.8+ KB<jupyter_text>Rearrrange data  Status<jupyter_code># get category from raw data and return category as seen from coinopsy
def category_check(raw_string):
    
    # categories from coinopsy 
    categories = ['Abandoned or No Volume', 'Joke or No Purpose', 'Scam or Other Issues', 'ICO Fail or Short Lived']
    for category in categories:
        if category in raw_string:
            return category
        
        
# get category from raw details  
df_coins.insert(1, 'Status', df_coins['Raw Details'].apply(lambda x: category_check(x)))
df_coins.head()
df_coins.info()
# check reason for nulls
df_coins[df_coins['Status'].isnull()]
# replace shortened status in raw details to values similar to original categories
dict_replace = {'Failed':'Fail', 'scam':'Scam or Other Issues','ico':'ICO Fail or Short Lived'}

for key in dict_replace.keys():
    df_coins['Raw Details'][df_coins['Status'].isnull()] = df_coins[df_coins['Status'].isnull()]['Raw Details'].apply(lambda x:x.replace(key, dict_replace[key]))
    
df_coins[df_coins['Status'].isnull()]
df_coins['Status'][df_coins['Status'].isnull()] = df_coins['Raw Details'][df_coins['Status'].isnull()].apply(lambda x: category_check(x))

df_coins[df_coins['Status'].isnull()]
df_coins['Status'][992, 1312] = 'Limited Volume/Limited Exchanges'
df_coins['Status'][998] = 'Wallet Issues, Low Volume'

df_coins.info()
df_coins['Raw Details'] = [e.replace(k, '') for e, k in zip(df_coins['Raw Details'].astype('str'), df_coins['Status'].astype('str'))]
df_coins<jupyter_output><empty_output><jupyter_text>Start Year, End Year<jupyter_code>df_coins['Start Year'] = df_coins['Raw Details'].apply(lambda x: x.split()[0])
df_coins['End Year'] = df_coins['Raw Details'].apply(lambda x: x.split()[1])

df_coins.drop(columns=['Raw Details'], inplace=True)
df_coins<jupyter_output><empty_output><jupyter_text>Note:According to coinopsy, coins with End Year values listed as 'current' are coins that can be still traded but listed as dead due to low volume and very small market cap (volume < 1000 usd per day and market cap in thousands). These coins, while very dangerous, will be removed from the list as there are similar coins still listed in coinmarketcap.<jupyter_code>df_coins['Start Year'].value_counts()
df_coins['End Year'].value_counts()
df_coins[df_coins['Start Year'] == 'Unknown']
# Braincoin data drom coinmarketcap
df_coins['Start Year'][1824] = 2016

# Caesarion data from coinmarketcap
df_coins['Start Year'][1829] = 2016
df_coins[df_coins['End Year'] == '2017/2018']
# replace 2017/2018 with 2018
df_coins.loc[df_coins[df_coins['End Year'] == '2017/2018'].index, 'End Year'] = 2018

# replace 2013? with 2013
df_coins.loc[df_coins[df_coins['End Year'] == '2013?'].index, 'End Year'] = 2013
# df_coins['Start Year'] = df_coins['Start Year'].astype('int32')
# df_coins['End Year'] = df_coins['End Year'].astype('int32')
df_coins['Start Year'] = pd.to_numeric(df_coins['Start Year'], errors='coerce')
df_coins['End Year'] = pd.to_numeric(df_coins['End Year'], errors='coerce')
df_coins.info()
df_coins.dropna(inplace=True)
df_coins['End Year'] = df_coins['End Year'].astype('int')
df_coins.info()
import matplotlib.pyplot as plt

plt.scatter(x=df_coins['Start Year'], y=df_coins['End Year'])
# inspect rows where end year < start year
err = df_coins[df_coins['Start Year'] > df_coins['End Year']].index

# swap values
df_coins.loc[err, ['Start Year', 'End Year']] = (df_coins.loc[err, ['End Year', 'Start Year']].values)
plt.scatter(x=df_coins['Start Year'], y=df_coins['End Year'])
df_coins.to_csv('coinopsy_cleaned.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15449500554938958, 'fraction_numerical': 0.025305216426193117, 'mean_word_length': 5.715350223546944, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 18, 'https://': 0, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '559105', 'license': 'MIT', 'n_tokens_mistral': 1672, 'n_tokens_neox': 1498, 'n_words': 396, 'path': 'transform_coinopsy.ipynb', 'repo_name': 'vanessapigwin/Dead-Coins', 'size': '4505'}