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.

Black cat · Accepted Answer · 2025-01-07 09:50:47Z

0

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)

answered Jan 7 at 9:50

Black cat

7,6358 gold badges69 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Julia N. Jan 8 at 8:20

Thank you so much for your answer! But there is something not really working. Because if I enter the line: =TEXTSPLIT(TEXTJOIN("ß",FALSE,TRANSPOSE(MAP(B2:D241,LAMBDA(x, TEXTJOIN("|",FALSE,x,INDEX(A1:DQ1,0,COLUMN(x)),INDEX(A1:A241,ROW(x),0)))))),"|","ß",FALSE) which should be correct for my datasheet I get the following error: support.microsoft.com/en-us/office/… Do you know which mistake I made?

Black cat Jan 8 at 8:28

You have to place the formula with copy/paste in the formula bar directly, not in the cell. If you place it in the cell the text will be divided into two cells ( one line one cell)

Julia N. Jan 8 at 8:44

I placed it in the formula bar. But there is always a marked cell or area where the text appears. How is it possible to solve this problem?

Black cat Jan 8 at 9:28

This need not be solved. This is Excel show the active cell and the split range of the formula. Click another cell and disappears.

Julia N. Jan 8 at 9:34

I do not know where I made the mistake. But it still do not works and just results in the error message I linked above

|

rehaqds · Accepted Answer · 2025-01-08 18:38:00Z

0

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)

edited Jan 8 at 18:38

answered Jan 7 at 12:16

rehaqds

2,2452 gold badges6 silver badges16 bronze badges

4 Comments

Julia N. Jan 8 at 8:39

Thank you a lot for your answer. Do you know if there is an alternative to pandas. I do not have Python installed so I can not use this package.

rehaqds Jan 8 at 8:50

Oops, I thought you were using Pyhton/Pandas! There are no language tag on your question. Where does your dataframe comes from? R?

Julia N. Jan 8 at 9:04

Thank you for your answer. Yes, I use R (RStudio) for all my Statistics and Graphics.

rehaqds Jan 8 at 18:39

I am not an expert in R but the code I added in my response seems to work.

Collectives™ on Stack Overflow

Sum up and Convert a Dataframe

2 Answers 2

11 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related