Pandas - Pivot/stack/unstack/melt

Question

I have a dataframe that looks like this:

name	value 1	value 2
A	100	101
A	100	102
A	100	103
B	200	201
B	200	202
B	200	203
C	300	301
C	300	302
C	300	303

And I'm trying to get to this:

name	value 1	value 2	value 3	value 4	value 5	value 6
A	100	101	100	102	100	103
B	200	201	200	202	200	203
C	300	301	300	302	300	303

Here is what i have tried so far; dataframe.stack() dataframe.unstack() dataframe.melt(id_vars=['name'])

I need to transpose the data by ensuring that;

1. The first row remains as it is but every subsequent value associated with the same name should be transposed to a coulmn.
2. Whereas the second value B (for. ex) should transpose it's associated value as a new value under the column A values, it should not form a separate altogether.

Answer 1

Try:

def fn(x):
    vals = x.values.ravel()
    return pd.DataFrame(
        [vals],
        columns=[f"value {i}" for i in range(1, vals.shape[0] + 1)],
    )


out = (
    df.set_index("name")
    .groupby(level=0)
    .apply(fn)
    .reset_index()
    .drop(columns="level_1")
)
print(out.to_markdown())

Prints:

	name	value 1	value 2	value 3	value 4	value 5	value 6
0	A	100	101	100	102	100	103
1	B	200	201	200	202	200	203
2	C	300	301	300	302	300	303

Answer 2

Flatten values for each name

(
    df.set_index('name')
    .groupby(level=0)
    .apply(lambda x: pd.Series(x.values.flat))
    .rename(columns=lambda x: f'value {x + 1}')
    .reset_index()
)

Answer 3

One option using melt, groupby`, and pivot_wider (from pyjanitor):

# pip install pyjanitor
import pandas as pd
import janitor

(df
.melt('name', ignore_index = False)
.sort_index()
.drop(columns='variable')
.assign(header = lambda df: df.groupby('name').cumcount() + 1)
.pivot_wider('name', 'header', names_sep = ' ')
)
  name  value 1  value 2  value 3  value 4  value 5  value 6
0    A      100      101      100      102      100      103
1    B      200      201      200      202      200      203
2    C      300      301      300      302      300      303