<jupyter_start><jupyter_text>Cleaning and Formatting Ocean Voting Data* Notebook Begin: November 9, 2021* Notebook End: November 23, 2021  Format Goal Our goal here is to create a data frame with the following data for each Ocean vote:* Round Number* Wallet Address* Project Name* Amount Voted* Vote Type (Yes or No)  Preparations<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Early Rounds Rounds 1 through 4 had this format:* All projects on a single ballot* Voters chose the one they liked, dedicating some number of OCEAN To it.   Getting Rounds 1 through 4 dataRounds 1 through 4 results were summarized in these articles:* [Round 1](https://blog.oceanprotocol.com/oceandao-round-one-grant-results-c9e586ddb6a?gi=abc68bc98a9b)* [Round 2](https://blog.oceanprotocol.com/oceandao-round-two-grant-results-cade89f5be92)* [Round 3](https://blog.oceanprotocol.com/oceandao-round-three-grant-results-86a5f35b16b3)* [Round 4](https://blog.oceanprotocol.com/oceandao-round-5-deadline-round-4-results-c681ecf2ce99)The data was sourced from these pages:* [Round 1](https://snapshot.org//officialoceandao.eth/proposal/QmPLfq9J2Mr4FDAWpYaBq2veYJpiKVgU6JkXYUvV8qa2FC)* [Round 2](https://vote.oceanprotocol.com//proposal/QmVtJoo7hp3G5RXAVu9EjHT38Uknipqs7a78P7o8viLQtm)* [Round 3](https://vote.oceanprotocol.com//proposal/QmRNQBN8ojfqr5CwTLufJawnNd58pSv6B7tWuvA7YvmBqb)* [Round 4](https://vote.oceanprotocol.com//proposal/QmYdPQ59SBTV5MbyQZ2AGrEWYBe2jaqDhydM9xWhz6LAsX)Rounds 1 through 4 were single ballot, with each address voting for a single project that they wanted to receive funds.  Transparency/Process Notes: 🐙 If you had asked me how I got this data on November 9, I would have told you I clicked "Generate Report" on the relevant vote.oceanprocotol.com snapshot page. However, when I look for such a button on November 14, I don't see any such button. Note to myself and other analysts: keep good records as you're sourcing data. It might be worth recording your sessions (I could have Zoomed myself and done a cloud recording of my screen) to make doubly sure of process. The number of votes in the site is different than the votes in each .csv file, because votes with a balance of 0 OCEAN were not recorded to the .csv.  Exploring Round Data  Now let's look at Round 1  data.<jupyter_code>rd_1_data = pd.read_csv("ocean-round-1-data.csv")<jupyter_output><empty_output><jupyter_text>This is basically what we want. We'd like to add two things here: 1. Project Name replacing number in "choice"2. "Vote"; "Yes" or "No". all votes are "Yes" in this round, but in later rounds that won't be the case.  We are going to use the data in "All Proposals.csv" downloaded from this [airtable link](https://airtable.com/shrd5s7HSXc2vC1iC/tbls5o7pVfAUU39eX)<jupyter_code>all_proposals_data = pd.read_csv("All Proposals.csv")
all_proposals_data.head()<jupyter_output><empty_output><jupyter_text>The "Snapshot Batch Index" column gives a way to give the original "choice" column a bit more data. First we construct a mapping to replace "choice" with "Project Name".<jupyter_code>rd_1_proposal_data = all_proposals_data[all_proposals_data["Round"] == 1]
rd_1_proposal_data.head()
rd_1_names_dict = {b_index:proj_name for b_index,proj_name in
                   zip(rd_1_proposal_data["Snapshot Batch Index"],
                      rd_1_proposal_data["Project Name"])}
rd_1_names_dict
rd_1_data["Project Name"] = rd_1_data['choice'].map(rd_1_names_dict)
rd_1_data.head()<jupyter_output><empty_output><jupyter_text>Since it might be nice to carry over all of this information, we'll go ahead and make a function that does this process in general.<jupyter_code>def transfer_data(df1: pd.DataFrame, df2: pd.DataFrame, source_col: str,
                  match_col: str, target_cols: list) -> None:
    """
    This function transfers the information in the target_col column
    from df2 to df1. Modifies data frame in place. 
    
    """
    for target_col in target_cols:
        names_dict = {x:y for x,y in
                   zip(df2[match_col],
                      df2[target_col])}
        df1[target_col] = df1[source_col].map(names_dict)
    return None<jupyter_output><empty_output><jupyter_text>Let's make sure it works.<jupyter_code>transfer_data(rd_1_data, rd_1_proposal_data, "choice",
               "Snapshot Batch Index",["Project Name", "Proposal State", 
                                       "Proposal Standing", "Grant Category", 
                                       "Earmarks", "OCEAN Granted"])
rd_1_data<jupyter_output><empty_output><jupyter_text>Cleaning and Saving Data, Rounds 1 through 4<jupyter_code>all_proposals_data = pd.read_csv("All Proposals.csv")

info_to_add = ["Project Name", "Proposal State",
               "Proposal Standing", "Grant Category", 
                                       "Earmarks", "OCEAN Granted"]
info_to_drop_at_end = ["authorIpfsHash", "choice", "dateUtc", "timestamp"]

def process_early_round_data(rd_num:int) -> pd.DataFrame:
    rd_proposal_data = all_proposals_data[all_proposals_data["Round"] == rd_num]
    rd_filename = "ocean-round-" + str(rd_num) + "-data.csv"
    rd_data = pd.read_csv(rd_filename)
    rd_data["Vote"] = "Yes"
    rd_data["Round"] = rd_num
    transfer_data(rd_data, rd_proposal_data, "choice",
                 "Snapshot Batch Index", info_to_add)
    rd_data["OCEAN Granted"].fillna(value = 0, inplace = True)
    rd_data.drop(columns = info_to_drop_at_end, inplace = True)
    rd_data.sort_values(by = "Project Name", inplace = True)
    return rd_data
    
rds_data = [process_early_round_data(k) for k in range(1,5)]
early_rds_data = pd.concat(rds_data)
early_rds_data.to_csv("OceanData0Rounds1To4.csv")<jupyter_output><empty_output><jupyter_text>Rounds 5 Through 10  The voting in rounds 5 through 10 followed a different format than Rounds 1 through 4. Addresses could vote for multiple projects, and they were allowed to vote YES or NO. The raw data is available in the "OceanDAOVotes.xlsx" file in this repo, sourced from https://docs.google.com/spreadsheets/d/1e4xb6m-aKcBhwob_p7ereSFneXFPpDjUbSjz13smmHI/editgid=1839685417 **Transparency/Process Note:** :octopus: I had to change a sheet name in the workbook from "Round5Results" to "Round 5 Results" to match the name format of the other rounds involved here.This data took a bit more cleaning. First, we had to recognize that in the sheets with overall results, each project had an ipfshash value (a long string beginning with "Q"). There are sheets with the **first 31** characters of that hash value for each project, which contain all of the vote data. This change in format required new functions that would process the data to make it match the same format as Rounds 1 through 4.   Useful Functions for Rounds 5 through 10  Load Snapshot Data<jupyter_code>snapshot_data = pd.read_csv("snapshot-vote-data.csv")
def lookup_time(wallet:str, hash_str:str) -> str:
    get_voter = snapshot_data[snapshot_data["voter"]==wallet]
    get_vote = get_voter[get_voter["ipfsHash"] == hash_str]
    get_time = get_vote["created"]
    return get_time

def create_timestamp_column(df:pd.DataFrame) -> list:
    timestamps = [lookup_time(df.loc[k,"address"],df.loc[k,"ipfsHash"]) 
                  for k in range(len(df))]
    return timestamps

def truncate_hash(ipfshash:str) -> str:
    """
    Truncate the hash to 31 characters for accessing sheet names.
    
    """
    return ipfshash[:31]

def get_project_data(ipfshash:str, round_number:int,
                              proposal_name:str) -> pd.DataFrame:
    """
    Given the ipfshash for a project,  a round number, and a proposal name,
    access the data corresponding to that. 
    """
    truncated_hash = truncate_hash(ipfshash)
    project_data = pd.read_excel("OceanDAOVotes.xlsx", 
                                 sheet_name = truncated_hash)
    project_data["round"] = round_number
    project_data["Proposal Name"] = proposal_name
    project_data["ipfsHash"] = ipfshash

    project_data.rename(columns = {'balace': 'balance'}, inplace = True)
    
    return project_data

def get_round_data(round_number: int) -> pd.DataFrame:
    """
    Get the data from a given round. 
    """
    our_sheet_name = "Round " + str(round_number) + " Results"
    base_data = pd.read_excel("OceanDAOVotes.xlsx", sheet_name = our_sheet_name)
    return base_data

def get_project_data_from_round(round_number: int) -> pd.DataFrame:
    """
    Get the project data from a given round. 
    """
    round_data = get_round_data(round_number)
    project_df_list = []
    for project_index in range(len(round_data)):
        project_ipfshash = round_data.loc[project_index,"ipfsHash"]
        proposal_name = round_data.loc[project_index,"Project Name"]
        project_data = get_project_data(project_ipfshash,
                                        round_number,
                                       proposal_name)
        project_df_list.append(project_data)
    all_data = pd.concat(project_df_list)
    return all_data

choice_map = {1: "Yes", 2: "No"}

info_to_add = ["Project Name", "Proposal State",
               "Proposal Standing", "Grant Category", 
                                       "Earmarks", "OCEAN Granted"]
info_to_drop_at_end = ["choice","version","authorIpfsHash",
                      "relayIpfsHash", "round"]

early_rounds_cols = early_rds_data.columns

def process_late_round_data(rd_num:int, cols_to_keep) -> pd.DataFrame:
    rd_data = get_project_data_from_round(rd_num)
    rd_proposal_data = all_proposals_data[all_proposals_data["Round"] == rd_num]
    rd_data["Vote"] = rd_data["choice"].map(choice_map)
    rd_data["Round"] = rd_num
    transfer_data(rd_data, rd_proposal_data, "ipfsHash",
                 "ipfsHash", info_to_add)
    rd_data["OCEAN Granted"].fillna(value = 0, inplace = True)
    rd_data.reset_index(inplace=True)
    rd_data = rd_data[cols_to_keep]
    return rd_data



rounds_data_5_to_10 = [process_late_round_data(k, early_rounds_cols) for k in range(5,11)]
late_rounds_data = pd.concat(rounds_data_5_to_10)


final_data = pd.concat([early_rds_data, late_rounds_data])

final_data.dropna(subset = ["Project Name"], inplace = True)

final_data.loc[pd.isnull(final_data["Proposal Standing"]), "Proposal Standing"] = final_data.loc[pd.isnull(final_data["Proposal Standing"]), "Proposal State"]
        
final_data.reset_index(inplace = True)
final_data.drop(columns = ["index"], inplace = True)
late_rounds_data.columns
final_data.head()
final_data.to_csv("ocean-votes-round-1-to-10.csv")<jupyter_output><empty_output><jupyter_text>Transparency: A Disappearing Proposal in Round 5There are two proposals entitled "Data Union App" in the "Round 5 Results" sheet of the "OceanDAOVotes.xlsx" spreadsheet, but only one corresponding project (labeled "Data Union Foundation") in the "All Proposals" data pulled from the AirTable. The ipfsHash for one of them does not appear in the AirTable data. I have decided to call this "Disappeared Data Union Proposal" for now, it could possibly be dropped once we understand what happened.<jupyter_code>all_proposals = pd.read_csv("All Proposals.csv")
rd_5_proposal_data = all_proposals[all_proposals["Round"] == 5]
rd_5_proposal_names = rd_5_proposal_data["Project Name"]
rd_5_vote_data = pd.read_excel("OceanDAOVotes.xlsx", sheet_name = "Round 5 Results")
rd_5_vote_names = rd_5_vote_data["Project Name"]
rd_5_proposal_names
len(rd_5_proposal_names)
rd_5_vote_names
len(rd_5_vote_names)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09545651233232368, 'fraction_numerical': 0.017568152315015146, 'mean_word_length': 4.236067059356593, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 46, 'https://': 10, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '99224', 'license': 'Apache-2.0', 'n_tokens_mistral': 4017, 'n_tokens_neox': 3809, 'n_words': 1018, 'path': 'OceanDAO/source-data/OceanDAO-data-cleaning.ipynb', 'repo_name': 'xm3van/te_rewards_research', 'size': '11555'}