CODEWITHSUNDEEP
pandas
SKC
SKC

Reputation: 11

Transpose Column data into rows from a dataframe

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.

Upvotes: 1

Views: 108

Answers (5)

rhug123
rhug123

Reputation: 8768

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))

Also this should work:

df.T.groupby(level=0).agg(list).T.explode(['Type - I','Type - II']).reset_index(drop=True)

Variation of above:

df.groupby(level=0,axis=1).agg(lambda x: x.to_numpy().tolist()).explode(['Type - I','Type - II'])

Upvotes: 0

sammywemmy
sammywemmy

Reputation: 28659

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

Upvotes: 0

PaulS
PaulS

Reputation: 25313

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

Upvotes: 0

Thorin
Thorin

Reputation: 1

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.

Upvotes: 0

mozway
mozway

Reputation: 260300

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

Upvotes: 2

Related Questions