-1

Year values to become columns & the rest of the columns to become rows. Any ideas?

the data:

   Year Propertyval Cumulative
0  1    1224000.00  24000.00
1  2    1248480.00  48480.00
2  3    1273449.60  73449.60

Desired format:

   Desc          1           2           3
0  Propertyval   1248480.00  1273449.60  1273449.60
1  Cumulative    24000.00    48480.00    73449.60
Jonny
  • 314
  • 4
  • 6

2 Answers2

5

Does this work for you?

import pandas as pd
df = pd.read_clipboard()
df_new = df.pivot_table(columns="Year", values=["Propertyval", "Cumulative"])

Update: If you also want to reset the index and change the column name, then this would be more accurate:

import pandas as pd
df = pd.read_clipboard()
df_new = df.pivot_table(columns="Year", values=["Propertyval", "Cumulative"]).reset_index().rename(columns={'index':'Desc'}).rename_axis(None, axis=1)

Thanks for the hint @Tobias P. G.

intedgar
  • 631
  • 1
  • 11
  • This is a much better answer than the other one. +1 – Mayank Porwal Nov 06 '21 at 09:17
  • 1
    To get the desired columns and index you need to do some additional steps: `df_new = df.pivot_table(columns="Year", values=["Propertyval", "Cumulative"]).reset_index().rename(columns={'index':'Desc'}).rename_axis(None, axis=1)` – Tobias P. G. Nov 06 '21 at 09:41
  • Yes, you are right. That would be more accurate. Thanks. – intedgar Nov 06 '21 at 12:12
2

There might be a simpler way, but these steps should do the trick.

# Read the sample dataframe from clipboard
df = pd.read_clipboard()
# Transpose the index and columns
df = df.T
# Rename the column based on first row
df.rename(columns=df.iloc[0].astype(int), inplace = True)
# Drop the first row
df.drop(df.index[0], inplace = True)
# Reset index
df = df.reset_index()
# Rename previous index column
df = df.rename(columns={'index':'Desc'})

df

Output:

    Desc        1.0         2.0         3.0
0   Propertyval 1224000.0   1248480.0   1273449.6
1   Cumulative  24000.0     48480.0     73449.6
Tobias P. G.
  • 827
  • 8
  • 15