Reshape Pandas DatafRames by binary columns value

Question

Can't figure out how to reshape my DataFrame into new one by several binary columns value.

Input:

data         code    a  b   c

2016-01-07   foo     0  0   0
2016-01-12   bar     0  0   1
2016-01-03   gar     0  1   0
2016-01-22   foo     1  1   0
2016-01-26   bar     1  1   0

I want to reshape by binary values, i.e. column a/b/c, if their value == 1, I need every time new column with all data.

Expected output:

         data       code        
  a   2016-01-22    foo      
  a   2016-01-26    bar       
  b   2016-01-03    gar     
  b   2016-01-22    foo      
  b   2016-01-26    bar       
  c   2016-01-12    bar

Stucked here from the morning, will appreciate help very much !

score 2 · Accepted Answer · answered Dec 15 '20 at 10:18

2

Use DataFrame.melt with filtering 1 in boolean indexing, DataFrame.pop is used for removing column after filtration:

df = df.melt(['data','code'], var_name='type')
df = df[df.pop('value').eq(1)]
print (df)
          data code type
3   2016-01-22  foo    a
4   2016-01-26  bar    a
7   2016-01-03  gar    b
8   2016-01-22  foo    b
9   2016-01-26  bar    b
11  2016-01-12  bar    c

answered Dec 15 '20 at 10:18

jezrael

822,522
95
1,334
1,252

1

Save my time ! How can I also add additional column with number of each type appears ? (for all a - 2, b - 3, c - 1)? – unkind58 Dec 15 '20 at 10:33
1

@unkind58 - sure, you can check [this](https://stackoverflow.com/q/17995024) – jezrael Dec 15 '20 at 10:39
1

@unkind58 - it means `df['size'] = df.groupby(['type'])['type'].transform('size')` – jezrael Dec 15 '20 at 10:40
`df['number'] = df['type'].map(dict(a=1,b=2,c=3))` – adir abargil Dec 15 '20 at 10:41
@jezreal what transform size do? – adir abargil Dec 15 '20 at 10:43
2

@adirabargil - It repeat counts for new column – jezrael Dec 15 '20 at 10:44

Reshape Pandas DatafRames by binary columns value

1 Answers1