CODEWITHSUNDEEP
pythonnumpypandasdataframe
Akshay Sakariya
Akshay Sakariya

Reputation: 107

How do I transpose a pandas dataframe while preserving the index and rename keys

This is what I have:

df: 
         A    B    C    D
0 foo    1    3    5    7
1 moo    2    4    6    8
2 poo    3    5    7    9

What I want:

df_Transposed:
         foo    moo   poo 
0    A    1      2     3
1    B    3      4     5
2    C    5      6     7
3    D    7      8     9

I tried transpose() but my keys became the range of the no. of columns. I want the keys to become ['foo', 'moo', 'poo']. And I want my index to remain the same as in df.

Do not assume square matrix and this is a single index frame.

Upvotes: 0

Views: 7420

Answers (5)

Akshay Sakariya
Akshay Sakariya

Reputation: 107

I was able to do it another way. Here's my code. After transposing:

df.columns = df.iloc[0]
df = df.reset_index()
df = df.drop(df.index[0])
df = df.reset_index()
df =df.drop(["level_0","index"],axis = 1)

Upvotes: 2

Merlin
Merlin

Reputation: 25639

This somewhat works, I couldnt find a way to drop labels(['level_0', 'index']) from multiIndex. 

    df3 = df.reset_index(0, drop=True).T.reset_index().reset_index()
    df3 = df3.set_index(['level_0', 'index'])
    df3

                       foo  moo  poo
     level_0 index               
       0       A        1    2    3
       1       B        3    4    5
       2       C        5    6    7
       3       D        7    8    9

df3.info()

<class 'pandas.core.frame.DataFrame'>
MultiIndex: 4 entries, (0, A) to (3, D)
Data columns (total 3 columns):
foo    4 non-null int64
moo    4 non-null int64
poo    4 non-null int64
dtypes: int64(3)
memory usage: 128.0+ bytes

Upvotes: 1

piRSquared
piRSquared

Reputation: 294348

One liner (and gross)

df.reset_index(0, drop=True).T.set_index([range(len(df.columns))], append=True).swaplevel(0, 1)

Less gross

df1 = df.reset_index(0, drop=True).T
idx = pd.MultiIndex.from_tuples(
    zip(range(len(df1)), df1.index.tolist())
)
df1 = df1.set_index(idx)
df1

Both produce

enter image description here

Upvotes: 1

Grr
Grr

Reputation: 16079

You could do something similar to piRSquared's answer, but with a different way of changing the index.

df1 = df.reset_index.(0, drop=True).T
df1.index = pd.MultiIndex.from_tuples([(i,x) for i,x in enumerate(df1.index)])

This will work for dataframes that aren't square as well as those that are square.

Upvotes: 1

unutbu
unutbu

Reputation: 879869

You could transpose, and then use droplevel to remove the extra column level

result.columns = result.columns.droplevel(0)

and use set_index to add a new index level:

result = result.set_index([np.arange(len(result)), result.index])

For example,

import pandas as pd

index = pd.MultiIndex.from_tuples([(0, 'foo'), (1, 'moo'), (2, 'poo')])
df = pd.DataFrame({'A': [1, 2, 3],
                   'B': [3, 4, 5],
                   'C': [5, 6, 7],},
                  index=index)

result = df.T
result.columns = result.columns.droplevel(0)
result = result.set_index([np.arange(len(result)), result.index])
print(result)

prints

     foo  moo  poo
0 A    1    2    3
1 B    3    4    5
2 C    5    6    7

Upvotes: 2

Related Questions