GIS analysis and visualizaiton typically needs data with 1 value per row. If your data is structured in columns, you need to 'unpivot' it to convert it to 1 value per row. Note that this will result in duplicate features, but they can be handled well in GIS.
Example Excel workbook is structured like this
id | species1 | species2 | .. | value 1 | value 2 | ... |
---|---|---|---|---|---|---|
1 | s1 | s2 | .. | 10 | 20 | ... |
This script will convert it to a table like follows
id | species | value |
---|---|---|
1 | s1 | 10 |
1 | s2 | 20 |
import pandas as pd
input = 'original.xlsx'
df = pd.read_excel(input)
df1 = pd.melt(df, id_vars=['id', 'species1'], value_vars=['value1']).rename(columns = {'value1':'value'})
df1 = pd.melt(df, id_vars=['id', 'species2'], value_vars=['value2']).rename(columns = {'value2':'value'})
merged = pd.concat([df1, df2])
# Drop rows which have null values
merged = merged.dropna(axis=0, how='any')
output = 'unpivoted.xlsx'
merged.to_excel(output, index=False)