I have a dataframe that looks like this:

And I'm trying to get to this:

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.

CodePudding user response：

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:

CodePudding user response：

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()
)

CodePudding user response：

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