<jupyter_start><jupyter_text>supplementary table - sample level get meta<jupyter_code>#samplesets
samplesets = v3_release.all_wild_sample_sets
df_meta = v3_release.load_sample_set_metadata(samplesets, True)
#fixes
df_meta.country = df_meta.country.str.replace("Gambia, The", "The Gambia").str.replace("Equatorial Guinea", "Bioko")
df_supp = df_meta.reset_index()
df_supp.columns
df_supp = df_supp[['sample_id', 'partner_sample_id', 'sample_set', 'country', 'location',
       'year', 'month', 'latitude', 'longitude', 'sex_call', 'aim_fraction_colu', 'aim_fraction_arab', 'species_gambcolu_arabiensis',
       'species_gambiae_coluzzii', 'is_arabiensis', 'is_gamb_colu',
       'is_gambiae', 'is_coluzzii']]
df_supp.rename(columns={'location':'site'}, inplace=True)
#round
df_supp = df_supp.round(3)
#export
df_supp.to_csv("../content/tables/per_sample_location_table.csv", index=False)<jupyter_output><empty_output><jupyter_text>main text table - site >10 level fix meta<jupyter_code>#consolidate all Mayotte sites into one (so we get a pie chart)
df_meta.loc[(df_meta.country == 'Mayotte'),'latitude'] = df_meta[df_meta.country == 'Mayotte'].latitude.mean()
df_meta.loc[(df_meta.country == 'Mayotte'),'longitude'] = df_meta[df_meta.country == 'Mayotte'].longitude.mean()
#counts by sites
df_site_counts = df_meta.groupby(['latitude', 'longitude', 'country']).size().reset_index(name='counts')
df_site_counts['site'] = range(len(df_site_counts))
#>= 10 sites 
df_10 = df_site_counts[df_site_counts.counts >= 10].copy()
len(df_10),len(df_10.latitude.unique()),len(df_10.longitude.unique())
#new full meta but only containing >=10 sites (to preserve all species columns)
df_10_meta = df_meta.merge(df_10[['longitude','latitude', 'site']])
len(df_10_meta.latitude.unique()),len(df_10_meta.longitude.unique())<jupyter_output><empty_output><jupyter_text>get crosstabbing<jupyter_code>arab_call = pd.crosstab(df_10_meta.site, df_10_meta.species_gambcolu_arabiensis)
colu_call = pd.crosstab(df_10_meta.site, df_10_meta.species_gambiae_coluzzii)
colu_call.rename({"intermediate": "gambiae/coluzzii"}, axis=1, inplace=True)
colu_call.head()
sex_call = pd.crosstab(df_10_meta.site, df_10_meta.sex_call)
sex_call.rename({"F": "female", "M": "male", "UKN": "sex_unknown"}, axis=1, inplace=True)
sex_call.head()<jupyter_output><empty_output><jupyter_text>get building<jupyter_code>#reindex and join
df_10 =  df_10.set_index('site')
df_10_final = df_10.join([arab_call, colu_call, sex_call])
#add locations - must be a better way of doing this using merge maybe?
site = []
for _, row in df_10_final.iterrows():
    all_site = df_meta[(df_meta.latitude == row.latitude) & (df_meta.longitude == row.longitude)].copy()
    if all_site.location.unique()[0] != 'Mtsamboro Forest Reserve':
        site.append(all_site.location.unique()[0])
    else:
        #fix_mayotte
        site.append('Mayotte')
        
df_10_final['region'] = site
#remove nans, fix types
df_10_final = df_10_final.fillna(value=0).astype({'coluzzii':int, 'gambiae':int, 'gambiae/coluzzii':int})
df_10_final = df_10_final[['country', 'region', 'latitude', 'longitude', 'counts', 'female', 'male', 
                           'sex_unknown', 'arabiensis', 'intermediate', 'coluzzii', 'gambiae', 'gambiae/coluzzii']]

#fix names
df_10_final.rename(columns={'region':'site', "intermediate":"arabiensis_x_gambiae",  'sex_unknown':'unassigned',
                            "gambiae/coluzzii": "coluzzii_x_gambiae"}, inplace=True)
#sort
df_10_final.sort_values(by=['country'], inplace=True)
#round
df_10_final = df_10_final.round(3)
#export
df_10_final.to_csv("../content/tables/site_location_table.csv", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15882034632034633, 'fraction_numerical': 0.020292207792207792, 'mean_word_length': 6.950537634408602, 'pattern_counts': {'":': 6, '<': 17, '<?xml version=': 0, '>': 21, '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': '451902', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 1466, 'n_tokens_neox': 1403, 'n_words': 212, 'path': 'notebooks/sampling_tables.ipynb', 'repo_name': 'malariagen/ag1000g-phase3-data-paper', 'size': '3696'}