Transpose Column data into rows from a dataframe-CodePudding

This is the Input

| Type - I | Type - II | Type - I | Type - II |
|----------|-----------|----------|-----------|
| 560      | 189       | 128      | 244       |
| 379      | 460       | 357      | 679       |
| 238      | 568       | 125      | 147       |
| 389      | 357       | 780      | 459       |

This is the Output desired

| Type - I | Type - II |   |   |
|----------|-----------|---|---|
| 560      | 189       |   |   |
| 128      | 244       |   |   |
| 379      | 460       |   |   |
| 357      | 679       |   |   |
| 238      | 568       |   |   |
| 125      | 147       |   |   |
| 389      | 357       |   |   |
| 780      | 459       |   |   |

Tried many ways but was not able to do it.

CodePudding user response：

You can de-duplicate the columns and stack:

(df.set_axis(pd.MultiIndex.from_frame(df.groupby(axis=1, level=0)
                                        .cumcount().reset_index()),
             axis=1)
   .stack()
   #.reset_index(drop=True) # uncomment if a clean index is needed
)

Output:

index  Type - I  Type - II
  0                       
0 0         560        189
  1         128        244
1 0         379        460
  1         357        679
2 0         238        568
  1         125        147
3 0         389        357
  1         780        459

CodePudding user response：

As I understand, you want to split your 3rd and 4th columns between 1rd and 2nd columns' rows.

In a hard-coded way:

1- You may declare a new pandas frame which has a 2 column and (2 * your previous_row).

2- Then assign Type-I and Type-II to even rows.

3- And, Left Type-I and Type-II to odd rows.

CodePudding user response：

Here is a way:

(df.stack()
.to_frame()
.assign(cc = lambda x: x.groupby(level=1).cumcount())
.set_index('cc',append=True)
.droplevel(0)[0]
.unstack(level=0))

CodePudding user response：

Another possible solution, based on the the idea of creating two sequences of indexes (even and odd), then concatenating the two dataframe blocks, and finally creating a new index with the sequence evens odds and sorting by index:

evens = [x for x in range(2*len(df)) if x % 2 == 0]
odds = [x for x in range(2*len(df)) if x % 2 != 0]

out = pd.concat([df.iloc[:,:2], df.iloc[:,2:]])
out.index = evens   odds
out = out.sort_index()

Output:

  Type - I  Type - II
0       560        189
1       128        244
2       379        460
3       357        679
4       238        568
5       125        147
6       389        357
7       780        459

CodePudding user response：

If the columns are duplicates, you can take use that pattern in your reshaping by moving into numpy:

cols = df.columns.unique()
new_df = df.to_numpy().reshape(-1, len(cols))
pd.DataFrame(new_df, columns = cols)
   Type - I  Type - II
0       560        189
1       128        244
2       379        460
3       357        679
4       238        568
5       125        147
6       389        357
7       780        459

Another option is with pivot_longer from pyjanitor, where for this particular use case, you pass a regular expression with groups (to names_pattern) to aggregate the desired column labels into new groups (in names_to) - in this case we wish to keep the column label, so we use .value as a placeholder to initiate that:

# pip install pyjanitor
import pandas as pd
import janitor

df.pivot_longer(index = None, 
                names_to = '.value', 
                names_pattern = r"(. )", 
                sort_by_appearance=True) 
   Type - I  Type - II
0       560        189
1       128        244
2       379        460
3       357        679
4       238        568
5       125        147
6       389        357
7       780        459