0

I have a pandas dataframe which looks like this:

Category            Sector 1    Blanks  Sector 2    Sector 3    Sector 4    Sector 5    Sector 6    Other   Sector 7
Blank                   0           1       0           0           0           0           0           0       0
3D                      1           0       0           0           0           1           1           0       0
3DPrinting              0           0       1           0           0           1           1           0       0
3DTechnology            0           0       0           1           0           1           1           0       0
B2B                     0           0       0           0           1           0           0           0       1
B2B Express Delivery    0           0       0           0           0           0           0           0       1
K-12 Education          0           0       0           0           0           0           0           1       0
M2M                     0           0       0           0           0           0           0           1       0
P2P Money Transfer      0           0       0           0           1           0           0           0       1

I am trying to reshape this dataset using the following code (only the first sector will be chosen):

cols = list(df.columns.difference(['Category']))
cols
df = pd.melt(df, id_vars=['Category'], value_vars=cols) 
df = df[~(df.value == 0)]
df = df.drop('value', axis = 1)
df = df.rename(columns = {"variable" : "Sectors"})
df.head()

Which does the job and I am able to get a dataframe reshaped as I want, which looks like this:

Category                Sectors
3D                      Sector 1
3DPrinting              Sector 2
3DTechnology            Sector 3
B2B                     Sector 4
B3B Express Delivery    Sector 7
....
....

Somehow I am not satisfied and looking for an alternative to the above many lines of codes.

Is there a possibility?

LeMarque
  • 733
  • 5
  • 21

1 Answers1

2

Try with melt:

(df.melt('Category', var_name='Sectors')
   .query('value>0 & Sectors != "Blanks"')
)

Output:

                Category   Sectors  value
1                     3D  Sector 1      1
20            3DPrinting  Sector 2      1
30          3DTechnology  Sector 3      1
40                   B2B  Sector 4      1
44    P2P Money Transfer  Sector 4      1
46                    3D  Sector 5      1
47            3DPrinting  Sector 5      1
48          3DTechnology  Sector 5      1
55                    3D  Sector 6      1
56            3DPrinting  Sector 6      1
57          3DTechnology  Sector 6      1
69        K-12 Education     Other      1
70                   M2M     Other      1
76                   B2B  Sector 7      1
77  B2B Express Delivery  Sector 7      1
80    P2P Money Transfer  Sector 7      1
Quang Hoang
  • 146,074
  • 10
  • 56
  • 74
  • thanks for the shortcut, I could not understand why whole code is wrapped around () – LeMarque Mar 26 '21 at 17:31
  • 1
    @I_m_LeMarque that allows for linebreak within the code. You can also use `\` before the linebreak without wrapping the code in `()`. – Quang Hoang Mar 26 '21 at 17:33