# Load up metadata map

metadata_fp = '../../../data/mapping-files/emp_qiime_mapping_qc_filtered.tsv'

metadata = pd.read_csv(metadata_fp, header=0, sep='\t')

metadata.head()

metadata.columns

# take just the columns we need for this figure panel

fig2a = metadata.loc[:,['#SampleID','empo_1','empo_3','adiv_observed_otus']]
fig2a.head()

"""
Explanation: Figure 2 csv data generation
Figure data consolidation for Figure 2, which deals with alpha and beta diversity of samples
Figure 2a: alpha diversity plot
For this figure, we need to output an Excel sheet that contains the following columns parsed from the universal metadata file:

observed sequences
Empo level 3
End of explanation
"""


# take just the columns we need for this figure panel, and drop values with more than one NaN

fig2b = metadata.loc[:,['#SampleID','temperature_deg_c','ph','adiv_observed_otus']].dropna(thresh=3)
fig2b.head()

"""
Explanation: Figure 2b: alpha diversity by temperature and pH
For this figure, we need to output an Excel sheet that contains the following columns parsed from the universal metadata file:

observed sequences
pH
temperature
End of explanation
"""


pcoa = pd.read_csv('./emp_90_gg_1k_unweighted_unifrac.txt.pc.first_ten',
                   header=None,
                   sep='\t',
                   names = ['#SampleID','PC1','PC2','PC3','PC4','PC5','PC6','PC7','PC8','PC9','PC10'])

empo = metadata.loc[:,['#SampleID','empo_0','empo_1','empo_2','empo_3']]

fig2c = pd.merge(left = empo, right = pcoa)
fig2c.head()

"""
Explanation: Figure 2c: beta diversity PCoA
For this figure, we need to output an Excel sheet that contains PCoA coordinate values merged with EMPO information
End of explanation
"""


# load in rRNA info

fig2d = pd.read_csv('../../../data/predicted-rrna-copy-number/emp_rrna_averagecopy_empo.csv')

fig2d.head()

"""
Explanation: Figure 2d: Estimated rRNA operon copy number by environment
For this figure, we will output each sample as a separate row with calculated rRNA info, plus metadata
End of explanation
"""


fig2 = pd.ExcelWriter('Figure2_data.xlsx')

fig2a.to_excel(fig2,'Fig-2a')
fig2b.to_excel(fig2,'Fig-2b')
fig2c.to_excel(fig2,'Fig-2c')
fig2d.to_excel(fig2,'Fig-2d')

fig2.save()

"""
Explanation: Write to Excel notebook
End of explanation
"""


