<jupyter_start><jupyter_text>Here we want to find the fields that they agreed on, and therefore, were not discussed in the damnmeeting! We relabel everything that has mustard as double-crop.<jupyter_code>import pandas as pd
import csv

import os, os.path
import sys
import shutil # to move files from one directory to another
perry_dir = "/Users/hn/Documents/01_research_data/NASA/Perry_and_Co/"
plots_dir = "/Users/hn/Documents/01_research_data/NASA/snapshots/TS/06_snapshot_flat_PNG/"
choices_set_1_xl = pd.ExcelFile(perry_dir + "set1_PerryandCo.xlsx")
choices_set_1_sheet_names = choices_set_1_xl.sheet_names  # see all sheet names

response_set_1_xl = pd.ExcelFile(perry_dir + "Perry_and_Co_Responses_set_1.xlsx")
response_set_1_sheet_names = response_set_1_xl.sheet_names  # see all sheet names
print (choices_set_1_sheet_names)
print (response_set_1_sheet_names)
a_choice_sheet = choices_set_1_xl.parse(choices_set_1_sheet_names[3])
sample_response = response_set_1_xl.parse(response_set_1_sheet_names[3])
print (list(a_choice_sheet.columns))<jupyter_output>['Question_in_set', 'Question_overall', 'ID', 'NDVI_TS_Name', 'corrected_RGB', 'TOA_RGB', 'latitude', 'longitude', 'CropTyp', 'Irrigtn', 'DataSrc', 'Acres', 'ExctAcr', 'LstSrvD', 'county']<jupyter_text>Count number of questions<jupyter_code>question_count = 0

for a_choice_sheet in choices_set_1_sheet_names:
    
    # read a damn sheet
    a_choice_sheet = choices_set_1_xl.parse(a_choice_sheet)

    # add them to the damn list
    question_count += a_choice_sheet.shape[0]

print('There are [{ques_count}] questions.'.format(ques_count=question_count))<jupyter_output>There are [294] questions.<jupyter_text>Disagreements start here:<jupyter_code>## Define the damn output dataframe
unwanted_opinions = ["<EMAIL>"]

output_df = pd.DataFrame(columns=['Disagreement_form', 'disagreement_Question', \
                                  'opinion_count', 'ID', "PerrysVote", "AndrewsVote", "TimsVote"], 
                         index=range(question_count))
output_df.head(1)
curr_row = 0

extended_choices = pd.DataFrame()

###### populate the output datafrme

for response_sheet_name in response_set_1_sheet_names:
    
    # pick up the numeric part of the sheet names from google Disagreement_forms sheets
    # this is the Disagreement_form number as well.
    sheet_numeric_part = [s for s in response_sheet_name.split()[-1] if s.isdigit()]
    sheet_numeric_part = "".join(sheet_numeric_part)
    
    # Disagreement_form sheet names of choices excel sheets
    choice_sheet_name = "extended_" + sheet_numeric_part
    
    a_choice_sheet = choices_set_1_xl.parse(choice_sheet_name)
    a_response_sheet = response_set_1_xl.parse(response_sheet_name)
    
    extended_choices = pd.concat([extended_choices, a_choice_sheet])
    #
    # drop unwanted opinions, i.e. keep experts opinions
    #
    a_response_sheet = a_response_sheet[~(a_response_sheet["Email Address"].isin(unwanted_opinions))].copy()
    
    for a_col_name in a_response_sheet.columns:
        if "QUESTION" in a_col_name:
            question_number = a_col_name.split()[1]
            
            output_df.loc[curr_row, "Disagreement_form"] = sheet_numeric_part
            output_df.loc[curr_row, "disagreement_Question"] = question_number
            output_df.loc[curr_row, "opinion_count"] = len(a_response_sheet[a_col_name].unique())
            output_df.loc[curr_row, "ID"] = a_choice_sheet.loc[int(question_number)-1, "ID"]
            
             
            output_df.loc[curr_row, "PerrysVote"] = a_response_sheet[a_response_sheet["Email Address"] == \
                                                                 "<EMAIL>"][a_col_name].values[0]
            
            output_df.loc[curr_row, "AndrewsVote"] = a_response_sheet[a_response_sheet["Email Address"] == \
                                                                 "<EMAIL>"][a_col_name].values[0]
            
            output_df.loc[curr_row, "TimsVote"] = a_response_sheet[a_response_sheet["Email Address"] == \
                                                                 "<EMAIL>"][a_col_name].values[0]
            
            curr_row += 1

extended_output = pd.merge(output_df, extended_choices, on=['ID'], how='left')
extended_output.head(2)

disagreement_table = extended_output[extended_output.opinion_count != 1].copy()
# disagreement_table = disagreement_table[disagreement_table.CropTyp != "grass hay"].copy()
disagreement_table.reset_index(inplace=True, drop=True)

disagreement_table.drop(labels=['TOA_RGB', 'latitude', 'longitude', 'Irrigtn', 'DataSrc', 'Acres',
                                'ExctAcr', 'LstSrvD', 'county', 'Question_in_set', 'Question_overall',
                                'corrected_RGB'], 
                         axis='columns', inplace=True)


disagreement_table.head(1)
print (extended_output.shape)
extended_output.head(2)
drop_cols = ['Disagreement_form', 'disagreement_Question', 'NDVI_TS_Name', 'corrected_RGB', 
             'TOA_RGB', 'latitude', 'longitude', 'Irrigtn', 'DataSrc', 'Acres',
             'ExctAcr', 'LstSrvD', 'county', 'Question_in_set', 'Question_overall']
extended_output.drop(labels=drop_cols, axis=1, inplace=True)
extended_output.head(2)
pre_meeting_agreements = extended_output[extended_output.opinion_count==1].copy()
print (pre_meeting_agreements.shape)
double_place=['Mustard Crop', 'Either double or mustard crop']
#
# Correct Perry's vote so that mustard shit is labeled as double-cropped
#

pre_meeting_agreements['PerryVCorrected']=pre_meeting_agreements['PerrysVote']
idx = pre_meeting_agreements[pre_meeting_agreements.PerryVCorrected.isin(double_place)].index
pre_meeting_agreements.loc[idx, "PerryVCorrected"] = 'Double Crop'
pre_meeting_agreements.head(2)

#
# Correct Andrew's vote so that mustard shit is labeled as double-cropped
#
pre_meeting_agreements['AndrewVCorrected']=pre_meeting_agreements['AndrewsVote']
idx = pre_meeting_agreements[pre_meeting_agreements.AndrewVCorrected.isin(double_place)].index
pre_meeting_agreements.loc[idx, "AndrewVCorrected"] = 'Double Crop'
pre_meeting_agreements.head(2)

#
# Correct Tim's vote so that mustard shit is labeled as double-cropped
#

pre_meeting_agreements['TimsVCorrected']=pre_meeting_agreements['TimsVote']
idx = pre_meeting_agreements[pre_meeting_agreements.TimsVCorrected.isin(double_place)].index
pre_meeting_agreements.loc[idx, "TimsVCorrected"] = 'Double Crop'
print (pre_meeting_agreements.shape)
pre_meeting_agreements.head(2)



pre_meeting_agreements = pre_meeting_agreements[pre_meeting_agreements.PerryVCorrected==
                                                pre_meeting_agreements.AndrewVCorrected].copy()

pre_meeting_agreements = pre_meeting_agreements[pre_meeting_agreements.PerryVCorrected==
                                                pre_meeting_agreements.TimsVCorrected]

print (pre_meeting_agreements.shape)

# create Vote column in pre_meeting_agreements
pre_meeting_agreements['Vote'] = 1
pre_meeting_agreements.head(2)

# Change Vote to 2 for double cropped fields
double_index = pre_meeting_agreements[pre_meeting_agreements.PerryVCorrected=="Double Crop"].index
pre_meeting_agreements.loc[double_index, 'Vote']=2
pre_meeting_agreements = pre_meeting_agreements[["ID", 
                                                 "PerrysVote", "AndrewsVote", "TimsVote", 
                                                 "PerryVCorrected", "AndrewVCorrected", "TimsVCorrected",
                                                 "Vote"]]

out_folder = "/Users/hn/Documents/01_research_data/NASA/ML_data/"
out_name = out_folder + "set1_premeeting_consensus.csv"
pre_meeting_agreements.to_csv(out_name, index = False)
# pre_meeting_agreements = pre_meeting_agreements[pre_meeting_agreements.CropTyp != "grass hay"].copy()
drop_cols = ['Disagreement_form', 'disagreement_Question', 'NDVI_TS_Name']
disagreement_table.drop(labels=drop_cols, axis=1, inplace=True)
print (disagreement_table.shape)
disagreement_table.head(2)
174+120<jupyter_output><empty_output><jupyter_text>Read Experts Consensus after the meeting<jupyter_code>import pandas as pd
expert_label_dir = "/Users/hn/Documents/01_research_data/NASA/Perry_and_Co/"
file_name =  "Expert_label_Consensus_Set1_Meeting.xlsx"
Labels_xls = pd.ExcelFile(expert_label_dir + file_name)
sheet_names = Labels_xls.sheet_names  # see all sheet names
Expert_label_Consensus = Labels_xls.parse("Expert_label_Consensus")
needed_cols = ["ID", "Vote"]
Expert_label_Consensus = Expert_label_Consensus[needed_cols]
Expert_label_Consensus.head(2)<jupyter_output><empty_output><jupyter_text>Some of the fucking fields in the meeting had consensus, but we needed to discuss them!!!Toss them out of Expert_label_Consensus and save the new and true Expert_label_Consensus!<jupyter_code>disagreement_IDs = list(disagreement_table.ID.unique())
Expert_label_Consensus = Expert_label_Consensus[Expert_label_Consensus.ID.isin(disagreement_IDs)]
Expert_label_Consensus.Vote.unique()

Expert_label_Consensus = Expert_label_Consensus[Expert_label_Consensus.Vote.isin([1, 2])]
Expert_label_Consensus.Vote = Expert_label_Consensus.Vote.astype(int)
print (Expert_label_Consensus.Vote.unique())
Expert_label_Consensus.shape
out_folder = "/Users/hn/Documents/01_research_data/NASA/ML_data/"
out_name = out_folder + "set1_postmeeting_consensus.csv"
Expert_label_Consensus.to_csv(out_name, index = False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10176663493070982, 'fraction_numerical': 0.007933989209774674, 'mean_word_length': 4.486941381311666, 'pattern_counts': {'":': 0, '<': 23, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 17, 'profanity_word_list.json': 6, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '617276', 'license': 'MIT', 'n_tokens_mistral': 3322, 'n_tokens_neox': 3100, 'n_words': 509, 'path': 'NASA/Python_codes/Perry_and_Co/set_1_agreement_table.ipynb', 'repo_name': 'HNoorazar/Kirti', 'size': '9493'}