<jupyter_start><jupyter_text>Doing Rounds 9 and 10 At our request, the Ocean engineers graciously added time stamp data for Rounds 9 and 10 in the "OceanDAOVotes.xlsx" file. We will be using a slightly different process to extract the timestamps here.<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

def lookup_time_excel_sheet(wallet:str, hash_str:str) -> str:
    truncate_hash = hash_str[0:31]
    get_sheet = pd.read_excel("OceanDAOVotes.xlsx", 
                             sheet_name = truncate_hash)
    get_voter = get_sheet[get_sheet["address"] == wallet]
    get_time = get_voter["created"]
    real_time = get_time.loc[0]
    print(real_time)
    return get_time



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 = ['address', 'balance', 'timestamp', 'Vote', 'Round', 'Project Name',
       'Proposal State', 'Proposal Standing', 'Grant Category', 'Earmarks',
       'OCEAN Granted']

def process_very_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["created"] = [int(x) for x in rd_data["created"]]
    rd_data.rename(columns = {"created":"timestamp"},
                   inplace = True)
    rd_data = rd_data[cols_to_keep]
    return rd_data






rd9data = process_very_late_round_data(9, early_rounds_cols)
rd10data = process_very_late_round_data(10,early_rounds_cols)
late_rds_data = pd.concat([rd9data, rd10data])
late_rds_data.fillna(0, inplace = True)
rds_1_through_8 = pd.read_csv("../final-data/ocean-votes-round-1-to-8-with-timestamps.csv")
all_rounds_with_timestamps = pd.concat([rds_1_through_8,late_rds_data])
from datetime import datetime

human_times = [datetime.utcfromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
              for ts in all_rounds_with_timestamps["timestamp"]]

all_rounds_with_timestamps["datetime"] = human_times

all_rounds_with_timestamps.to_csv("../final-data/ocean-votes-rounds_1_to_10_with_almost_all_timestamps.csv")<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup