Pandas - Pivot/stack/unstack/melt

Admin 1
Viewed 128

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.
pandas transpose dataframe pivot
3 Answers

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
user_100359850

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

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
user_71757130
Related