1

I have a dataframe that is structured like so (similar to a pivot table):

A B December 2022 January 2023
A1 B1 100 200
A1 B2 101 201

I'd like to and transpose my dataframe in a way so it reads:

Month A B Value
December 2022 A1 B1 100
December 2022 A1 B2 101
January 2023 A1 B1 200
January 2023 A1 B2 201

etc. I've attempted

df.T

But it gives me:

A A1 A1
B B1 B2
December 2022 100 101
January 2023 200 201
redwytnblakII
  • 101
  • 8

1 Answers1

1

You should use pd.melt:

>>> df.melt(id_vars=['A', 'B'], var_name='Month', value_name='Value')
    A   B          Month  Value
0  A1  B1  December 2022    100
1  A1  B2  December 2022    101
2  A1  B1   January 2023    200
3  A1  B2   January 2023    201

then to reorder columns, you can use this hack:

>>> df.melt(id_vars=['A', 'B'], var_name='Month', value_name='Value') \
      .set_index('Month').reset_index()

           Month   A   B  Value
0  December 2022  A1  B1    100
1  December 2022  A1  B2    101
2   January 2023  A1  B1    200
3   January 2023  A1  B2    201

Update: according to @sammywemmy's comment:

var_cols = ['A', 'B']
out = df.melt(id_vars=var_cols, var_name='Month', value_name='Value') \
        [['Month'] + var_cols + ['Value']]
print(out)

# Output
           Month   A   B  Value
0  December 2022  A1  B1    100
1  December 2022  A1  B2    101
2   January 2023  A1  B1    200
3   January 2023  A1  B2    201
Corralien
  • 109,409
  • 8
  • 28
  • 52
  • index setting might be expensive, a possibly better option might be to just reorder the columns with `loc` or even reindex on the columns – sammywemmy Jan 05 '23 at 06:41
  • @sammywemmy. You are totally right and mostly because the list of columns is needed to fill `id_vars` parameter. I updated my answer. – Corralien Jan 05 '23 at 07:10