For Biostars https://www.biostars.org/p/9502587/¶

In [1]:

#Make an example similar to input
import pandas as pd
import numpy as np
import string
col_num = 4
df = pd.DataFrame(np.random.random((11,col_num))*100, columns=list(string.ascii_uppercase)[:col_num]) #columns letters of alphabet based on https://stackoverflow.com/a/48911848/8508004
list = []
for i in range(1, df.shape[0] + 1): # gets the length of the DataFrame.
    list.append(f'Sample{i:03d}') # Using f-string for format and 03d for leading zeros.
df['A'] = list
df["D"] = 0
df

Out[1]:

	A	B	C
0	Sample001	10.050909	47.809380
1	Sample002	87.467438	16.073652
2	Sample003	52.592303	84.550920
3	Sample004	7.191352	97.149418
4	Sample005	7.247809	94.281273
5	Sample006	92.237988	67.519471
6	Sample007	18.459786	91.910198
7	Sample008	49.190417	14.685278
8	Sample009	75.550560	51.601866
9	Sample010	96.480584	73.379443
10	Sample011	94.653988	22.448392

In [2]:

df.iloc[0:3]

Out[2]:

	A	B	C
0	Sample001	10.050909	47.809380
1	Sample002	87.467438	16.073652
2	Sample003	52.592303	84.550920

In [3]:

df.iloc[8:]

Out[3]:

	A	B	C
8	Sample009	75.550560	51.601866
9	Sample010	96.480584	73.379443
10	Sample011	94.653988	22.448392

Above shows set-up and the main 'top' and 'bottom' to merge.

Make the sum data for the row.¶

In [4]:

dfn = df.iloc[3:8].sum(0)
dfn

Out[4]:

A    Sample004Sample005Sample006Sample007Sample008
B                                       174.327351
C                                       365.545638
D                                                0
dtype: object

In [5]:

type(dfn)

Out[5]:

pandas.core.series.Series

Replace the moot value in column A with a number so that pivot_table below works easily.

In [6]:

# replace the moot value in column A with a number so that `pivot_table` below works easily. 
dfn.A = 13

In [7]:

dfn

Out[7]:

A            13
B    174.327351
C    365.545638
D             0
dtype: object

In [8]:

reoriented_dfn = pd.pivot_table(dfn.to_frame(), columns = dfn.index) #pivot_table from https://stackoverflow.com/a/28337349/8508004 
reoriented_dfn

Out[8]:

	A	B	C	D
0	13.0	174.327351	365.545638	0.0

That makes the sum line we'll need to put between top and bottom. Disregard the moot content in column A as we'll fix it shortly. (Putting a number there worked to allow the pivot easy. I developed that column with random numbers and so I knew it worked when that was a number. Had issues when I went to make column A be text strings.)

Concatenate the parts into one dataframe.¶

In [9]:

dff = pd.concat([df.iloc[0:3], reoriented_dfn,df.iloc[8:]], ignore_index=True)
dff

Out[9]:

	A	B	C
0	Sample001	10.050909	47.809380
1	Sample002	87.467438	16.073652
2	Sample003	52.592303	84.550920
3	13.0	174.327351	365.545638
4	Sample009	75.550560	51.601866
5	Sample010	96.480584	73.379443
6	Sample011	94.653988	22.448392

In [10]:

# Fix the moot value in column A for summed rows
dff.at[3,'A'] = "Total"  # based on https://stackoverflow.com/a/13842286/8508004
dff

Out[10]:

	A	B	C
0	Sample001	10.050909	47.809380
1	Sample002	87.467438	16.073652
2	Sample003	52.592303	84.550920
3	Total	174.327351	365.545638
4	Sample009	75.550560	51.601866
5	Sample010	96.480584	73.379443
6	Sample011	94.653988	22.448392

In [ ]: