Merge multiple columns into one by placing one below the other based on column value pandas dataframe

Question

I have the following dataframe df:

Video               1   1   1   1   1   1   1   1   1   1   ... 36  36  36  36  36  36  36  36  36  36
Confidence Value    3   3   4   4   4   5   5   3   5   3   ... 3   3   3   2   4   2   3   3   3   3

Where the row Video is the row with the names of the columns in the dataframe (i.e. the row with bold font that states the names of each column).

What I want is to rearrange this dataframe so that the output is this:

Video 1 2 3 ... 36
0     3 5 4 ... 3
1     1 2 3 ... 2
2     2 4 4 ... 5
3     4 5 4 ... 3
...

I have tried searching different ways to append, concatenate, merge etc. the columns in the way that I want but I can't figure out how since there are multiple instances of each Video, i.e. multiple 1, 2, .. 36.

So, for each of these multiple instances, I want to make one column of these with the Video number as the column name, and the rows be all the confidence values, as shown above.

Is that possible?

(1) How is your expected result related to your data? (2) How many columns are there for each `Video` number? Are they equal? — Bill Huang, Nov 23 '20 at 13:35
Does this answer your question? [How to pivot a dataframe?](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) — Ch3steR, Nov 23 '20 at 14:20
Question 10 in dupe. You have to do one extra step before `df = df.T` — Ch3steR, Nov 23 '20 at 14:20
@BillHuang It's not an equal number, but the answer you provided works — Oam, Nov 23 '20 at 15:18

Bill Huang · Accepted Answer · 2020-11-23T14:02:53.920

A transpose-pivot construct may be what suits your need.

Data

df = pd.read_csv(io.StringIO("""
Video               1   1   1   1   1   2   2   2   2   2   35  35  35  35  35  36  36  36  36  36
Confidence Value    3   3   4   4   4   5   5   3   5   3   3   3   3   2   4   2   3   3   3   3
"""), sep=r"\s{2,}", engine="python", header=None, index_col=0)

print(df)
                  1   2   3   4   5   6   7   ...  14  15  16  17  18  19  20
0                                             ...                            
Video              1   1   1   1   1   2   2  ...  35  35  36  36  36  36  36
Confidence Value   3   3   4   4   4   5   5  ...   2   4   2   3   3   3   3
[2 rows x 20 columns]

Code

This should work for indefinite number of confidence values per video:

idx = df.transpose().groupby("Video").cumcount().values
ans = df.transpose().set_index(idx).pivot(columns="Video", values="Confidence Value")

Note: If the number of confidence values per video are the same (5 in the example), then the groupby-cumcount step can be further simplified:

ans = df.transpose().set_index(np.tile(range(5), 4)).pivot(columns="Video", values="Confidence Value")

Result

print(ans)

Video  1   2   35  36
0       3   5   3   2
1       3   5   3   3
2       4   3   3   3
3       4   5   2   3
4       4   3   4   3

Merge multiple columns into one by placing one below the other based on column value pandas dataframe

1 Answers1

Data

Code

Result