CODEWITHSUNDEEP
pythonpandas
sjd
sjd

Reputation: 1401

Pandas transpose based on condition

I have a dataframe

Key   Col1 Col2 Col3   Colother
1     11    12    0     'a'
2     0     21    31    'b'
3     5     6     7     'c'

Expected output

Key   Col1 Col2 Col3   Colother  Colnew
1     11    12   0    'a'        11
1     11    12   0    'a'        12
2     0     21   31   'b'        21
2     0     21   31   'b'        31 
3     5     6    7    'c'        5
3     5     6    7    'c'        6
3     5     6    7    'c'        7

I have tried with df.T but it has limited options. as i dont want to create rows for 0 columns. Right now i am left with iterating over rows.

Upvotes: 2

Views: 92

Answers (4)

Tamil Selvan V
Tamil Selvan V

Reputation: 466

You can use join to achieve this

import pandas as pd
import numpy as np
x = [
    {
    "key": 1, "col1": 11, "col2": 12, "col3": 0, "colother": 'a'
    },
        {
    "key": 2, "col1": 0, "col2": 21, "col3": 31, "colother": 'b'
    },    {
    "key": 3, "col1": 5, "col2": 6, "col3": 7, "colother": 'c'
    },
]

df = pd.DataFrame(x)

print(df.join(df[["col1", "col2", "col3"]].stack().droplevel(1).rename('colnew').replace(0, np.nan).dropna()))

output:

   col1  col2  col3 colother  key  colnew
0    11    12     0        a    1    11.0
0    11    12     0        a    1    12.0
1     0    21    31        b    2    21.0
1     0    21    31        b    2    31.0
2     5     6     7        c    3     5.0
2     5     6     7        c    3     6.0
2     5     6     7        c    3     7.0

Upvotes: 1

Shubham Sharma
Shubham Sharma

Reputation: 71689

Let us try stack and join:

c = ['Col1', 'Col2', 'Col3']
df.join(df[c].mask(df[c].eq(0)).stack().droplevel(1).rename('ColNew'))

   Key  Col1  Col2  Col3 Colother  ColNew
0    1    11    12     0      'a'      11
0    1    11    12     0      'a'      12
1    2     0    21    31      'b'      21
1    2     0    21    31      'b'      31
2    3     5     6     7      'c'       5
2    3     5     6     7      'c'       6
2    3     5     6     7      'c'       7

Upvotes: 3

jezrael
jezrael

Reputation: 862406

Use DataFrame.melt with DataFrame.merge:

df1 = df.melt(['Key','Colother'], value_name='Colnew').drop('variable', axis=1)
df = df.merge(df1[df1['value'].ne(0)])

print (df)
   Key  Col1  Col2  Col3 Colother Colnew
0    1    11    12     0      'a'     11
1    1    11    12     0      'a'     12
2    2     0    21    31      'b'     21
3    2     0    21    31      'b'     31
4    3     5     6     7      'c'      5
5    3     5     6     7      'c'      6
6    3     5     6     7      'c'      7

Also is possible filter in query, thank you @Scott Boston for suggestion:

df1 = df.melt(['Key','Colother'], value_name='Colnew').drop('variable', axis=1)
df = df.merge(df1.query('Colnew != 0'))
print (df)
   Key  Col1  Col2  Col3 Colother  Colnew
0    1    11    12     0      'a'      11
1    1    11    12     0      'a'      12
2    2     0    21    31      'b'      21
3    2     0    21    31      'b'      31
4    3     5     6     7      'c'       5
5    3     5     6     7      'c'       6
6    3     5     6     7      'c'       7

Upvotes: 2

BENY
BENY

Reputation: 323226

Try with agg then explode

df['Colnew'] = df[['Col1','Col2','Col3']].agg(list,1)
df = df.explode('Colnew').loc[lambda x : x['Colnew']!=0]
df
Out[364]: 
   Key  Col1  Col2  Col3 Colother Colnew
0    1    11    12     0      'a'     11
0    1    11    12     0      'a'     12
1    2     0    21    31      'b'     21
1    2     0    21    31      'b'     31
2    3     5     6     7      'c'      5
2    3     5     6     7      'c'      6
2    3     5     6     7      'c'      7

Upvotes: 3

Related Questions