1

I have a dataframe that looks like this:

df1 = pd.DataFrame([('company1',15,20,40,33,5, 9, 81, 34, 7), ('company2', 5,7,10,20,31, 14,18,37,42)],

              columns=['company','legs - dog', 'head - dog', 'tail - dog','legs - cat', 'head - cat', 'tail - cat','legs - rabbit', 'legs - rabbit', 'tail - rabbit'])

and I want the output to be like this:

df2 = pd.DataFrame([('company1','dog', 15,20,40), ('company1','cat', 33,5,9),('company1','rabbit', 81,34,7),('company2','dog', 5,7,10), ('company2','cat', 20,31,14), ('company2','rabbit', 18,37,42) ],

              columns=['company','animal', 'legs', 'head','tail'])

I want to keep the first column in df1 as the first column in df2. The other columns in df1 are in groups (in this case there are three columns per group).

The column heads in these groups have composite column names that I need to break out. For example the second column in df1 has the label 'legs - dog'. Each of the names of the grouped columns are strings are separated by ' - '. I need the first part of the string before the hyphen (in this case 'legs') to become a column name and the 2nd part of the string of the grouped column name in df1 (in this case 'dog') to be the element of a new row with the column name of 'animals'.

Any help in finding the solution to this would be much appreciated. Thanks!

Han
  • 63
  • 3
  • I think your input dataframe `df1` contains a `typo` at column `legs - rabbit` it should be `head - rabbit` according to your output.. – Shubham Sharma Mar 14 '21 at 18:53
  • 1
    @Shubham - You are right. I realize some of my df2 outputs were not correct so I have updated these too. – Han Mar 14 '21 at 19:01

1 Answers1

2

pd.wide_to_long

We can use pd.wide_to_long function here which is an alternative to pandas melt function and it is more user friendly when converting the dataframe from wide format to long format.

pd.wide_to_long(df1, i='company', j='animal', 
                stubnames=['legs', 'tail', 'head'], 
                sep=' - ', suffix='\w+')\
  .reset_index()

    company  animal  legs  tail  head
0  company1     dog    15    40    20
1  company2     dog     5    10     7
2  company1     cat    33     9     5
3  company2     cat    20    14    31
4  company1  rabbit    81     7    34
5  company2  rabbit    18    42    37
Shubham Sharma
  • 68,127
  • 6
  • 24
  • 53