CODEWITHSUNDEEP
pythonpandasdataframe
William Goodwin
William Goodwin

Reputation: 464

Pandas dataframe stack and melt

a = {'pk': 1, 'pk_name':'p1', 'c1':1, 'c1_val': 1, 'c1_val2': 1, 'c2':0, 'c2_val': 0, 'c2_val2': 1}
b = {'pk': 2, 'pk_name':'p2', 'c1':0, 'c1_val': 1, 'c1_val2': 1, 'c2':0, 'c2_val': 0, 'c2_val2': 1}
c = {'pk': 3, 'pk_name':'p3', 'c1':0, 'c1_val': 1, 'c1_val2': 1, 'c2':0, 'c2_val': 0, 'c2_val2': 1}
d = {'pk': 4, 'pk_name':'p4', 'c1':1, 'c1_val': 1, 'c1_val2': 1, 'c2':0, 'c2_val': 0, 'c2_val2': 1}
e = {'pk': 5, 'pk_name':'p5', 'c1':1, 'c1_val': 1, 'c1_val2': 1, 'c2':0, 'c2_val': 0, 'c2_val2': 1}

df = pd.DataFrame([a, b, c, d, e])

   pk pk_name  c1  c1_val  c1_val2  c2  c2_val  c2_val2
0   1      p1   1       1        1   0       0        1
1   2      p2   0       1        1   0       0        1
2   3      p3   0       1        1   0       0        1
3   4      p4   1       1        1   0       0        1
4   5      p5   1       1        1   0       0        1

I want to transform my dataframe to look like this:

    pk pk_name  c    val      val2  
0   1      p1   1       1        1   
1   2      p2   0       1        1   
2   3      p3   0       1        1   
3   4      p4   1       1        1   
4   5      p5   1       1        1   
5   1      p1   0       0        1  
6   2      p2   0       0        1
7   3      p3   0       0        1    
8   4      p4   0       0        1  
9   5      p5   0       0        1

Where the columns begining with c (c1, c2) are stacked and the val columns (val, val2) are melted to long format.

Upvotes: 1

Views: 67

Answers (2)

jezrael
jezrael

Reputation: 862581

Use lreshape with extracted columns names:

a = df.columns[df.columns.str.contains('^c\d+$')]
b = df.columns[df.columns.str.endswith('val2')]
c = df.columns[df.columns.str.endswith('val')]
df1 = pd.lreshape(df, {'c': a, 'val' : b, 'val2' : c})

print (df1)
   pk pk_name  c  val  val2
0   1      p1  1    1     1
1   2      p2  0    1     1
2   3      p3  0    1     1
3   4      p4  1    1     1
4   5      p5  1    1     1
5   1      p1  0    1     0
6   2      p2  0    1     0
7   3      p3  0    1     0
8   4      p4  0    1     0
9   5      p5  0    1     0

If order should be changed split columns to MultiIndex and then reshape by DataFrame.stack:

#rename columns with c and number - add `_c`
cols = df.columns[df.columns.str.contains('^c\d+$')]
df = df.rename(columns = dict(zip(cols, cols + '_c')))

df1 = df.set_index(['pk','pk_name'])
df1.columns = df1.columns.str.split('_', expand=True)
df1 = df1.stack(0).reset_index(level=2, drop=True).reset_index()
print (df1)
   pk pk_name  c  val  val2
0   1      p1  1    1     1
1   1      p1  0    0     1
2   2      p2  0    1     1
3   2      p2  0    0     1
4   3      p3  0    1     1
5   3      p3  0    0     1
6   4      p4  1    1     1
7   4      p4  0    0     1
8   5      p5  1    1     1
9   5      p5  0    0     1

Upvotes: 4

dimay
dimay

Reputation: 2804

Try this:

df1 = df[["pk", "pk_name","c1", "c1_val", "c1_val2"]].rename(columns={"c1": "c", "c1_val":"val","c1_val2":"val2"})
df2 = df[["pk", "pk_name","c2", "c2_val", "c2_val2"]].rename(columns={"c2": "c", "c2_val":"val","c2_val2":"val2"})
pd.concat([df1,df2])

Upvotes: 0

Related Questions