Sum up and Convert a Dataframe

Question

I hope someone can help me with the following problem. My dataframe is organized with insect species in the columns and locations in the rows like:

	Species A	Species B	Species C
Location A	0	1
Location B	2	12	0
Location C	0	5	0

What I need is something like this:

Number	Species	Location
0	Species A	Location A
2	Species A	Location B
0	Species A	Location C
1	Species B	Location A
12	Species B	Location B

and so on.

Thank you so much for your help and kindest regards, Julia

So far I have no Idea how to do this and which command will bring the desired result.

Answer 1

One solution with Pandas:

    # "Stack" the dataframe to get the wanted format
    df = df.stack().reset_index()

    # Rename the columns
    df.columns = ['Location', 'Species', 'number']

    # Update the columns order 
    df = df[df.columns.tolist()[::-1]]

    # Order data by Species
    df = df.sort_values('Species')

    # Remove the index
    df = df.reset_index(drop=True)
    
    display(df)

---

EDIT: in R language instead of Python

library(tidyr)
library(reshape2) 

df <- data.frame(
  ind = c("Location_A", "Location_B", "Location_C"),
  Species_A = c(0, 2, 0),
  Species_B = c(1, 12, 5),
  Species_C = c(NA, 0, 0) 
)

df <- melt(df, id="ind") 
colnames(df) <- c("Location","Species","number")
df <- df[, rev(colnames(df))]
print(df)

Answer 2

With Excel sheet you can do this with this formula

=TEXTSPLIT(TEXTJOIN("ß",FALSE,TRANSPOSE(MAP(B2:D4,LAMBDA(x,
TEXTJOIN("|",FALSE,x,INDEX(A1:D1,0,COLUMN(x)),INDEX(A1:A4,ROW(x),0)))))),"|","ß",FALSE)