Unpivot Dataframe w/ Partial String

Question

I have a dataframe (totaldf) such that:

           ...     Hom   ...    March Plans   March Ships   April Plans   April Ships   ...

0                  CAD   ...    12              5           4             13
1                  USA   ...    7               6           2             11
2                  CAD   ...    4               9           6             14
3                  CAD   ...    13              3           9             7
...                ...   ...    ...             ...         ...           ...

for all months of the year. I would like it to be:

           ...     Hom   ...    Month   Plans    Ships    ...

0                  CAD   ...    March    12          5             
1                  USA   ...    March    7           6             
2                  CAD   ...    March    4           9             
3                  CAD   ...    March    13          3
4                  CAD   ...    April    4           13            
5                  USA   ...    April    2           11             
6                  CAD   ...    April    6           14             
7                  CAD   ...    April    9           7
...                ...   ...    ...      ...         ...

Is there an easy way to do this without splitting string entries? I have played around with totaldf.unstack() but since there are multiple columns I'm unsure as to how to properly reindex the dataframe.

Answer 1

If you convert the columns to a MultiIndex you can use stack:

In [11]: df1 = df.set_index("Hom")

In [12]: df1.columns = pd.MultiIndex.from_tuples(df1.columns.map(lambda x: tuple(x.split())))

In [13]: df1
Out[13]:
    March       April
    Plans Ships Plans Ships
Hom
CAD    12     5     4    13
USA     7     6     2    11
CAD     4     9     6    14
CAD    13     3     9     7

In [14]: df1.stack(level=0)
Out[14]:
           Plans  Ships
Hom
CAD April      4     13
    March     12      5
USA April      2     11
    March      7      6
CAD April      6     14
    March      4      9
    April      9      7
    March     13      3

---

In [21]: res = df1.stack(level=0)

In [22]: res.index.names = ["Hom", "Month"]

In [23]: res.reset_index()
Out[23]:
   Hom  Month  Plans  Ships
0  CAD  April      4     13
1  CAD  March     12      5
2  USA  April      2     11
3  USA  March      7      6
4  CAD  April      6     14
5  CAD  March      4      9
6  CAD  April      9      7
7  CAD  March     13      3

Answer 2

You can use pd.wide_to_long, with a little extra work in order to have the right stubnames, given that as mentioned in the docs:

The stub name(s). The wide format variables are assumed to start with the stub names.

So it will be necessary to slightly modify the column names so that the stubnames are at the beginning of each column name:

m = df.columns.str.contains('Plans|Ships')
cols = df.columns[m].str.split(' ')
df.columns.values[m] = [w+month for month, w in cols]

print(df)
   Hom  PlansMarch  ShipsMarch  PlansApril  ShipsApril
0  CAD          12           5           4          13
1  USA           7           6           2          11
2  CAD           4           9           6          14
3  CAD          13           3           9           7

Now you can use pd.wide_to_long using ['Ships', 'Plans'] as stubnames in order to obtain the output you want:

((pd.wide_to_long(df.reset_index(), stubnames=['Ships', 'Plans'], i = 'index', 
                j = 'Month', suffix='\w+')).reset_index(drop=True, level=0)
                .reset_index())

x  Month  Hom  Ships  Plans
0  March  CAD      5     12
1  March  USA      6      7
2  March  CAD      9      4
3  March  CAD      3     13
4  April  CAD     13      4
5  April  USA     11      2
6  April  CAD     14      6
7  April  CAD      7      9