transpose pandas dataframe and appending vertically

user_10095005 0
Viewed 179

I currently have a Pandas dataframe, df like this

df = pd.DataFrame({'Name': ['A','B','C'], 'Type': ['Car', 'Car', 'Truck'] , '01/01/1991, RED': [10, 26, 30], '01/02/1991, YELLOW': [11,15,5], '01/05/1991, BLUE':[5,8,20]})

Name | Type  | 01/01/1991, RED | 01/02/1991, YELLOW | 01/05/1991, BLUE  |
A    | Car   | 10              | 11                 | 5                 | 
B    | Car   | 26              | 15                 | 8                 | 
C    | Truck | 30              | 5                  | 20                |

I am looking for the output of

Name | Date       | Type  | Color  | Number
A    | 01/01/1991 | Car   | RED    | 10
A    | 01/02/1991 | Car   | YELLOW | 11
A    | 01/05/1991 | Car   | BLUE   | 5
B    | 01/01/1991 | Car   | RED    | 26
B    | 01/02/1991 | Car   | YELLOW | 15
B    | 01/05/1991 | Car   | BLUE   | 8
C    | 01/01/1991 | Truck | RED    | 30
C    | 01/02/1991 | Truck | YELLOW | 5
C    | 01/05/1991 | Truck | BLUE   | 20

So far, I am able to transpose the table and clean the dates. But am not sure how to go about duplicating the dates in the following manner and set the colors. Would .pivot_table or .transpose() be better for this case? Any insights are appreciated.

pandas pivot-table dataframe append python
4 Answers

Try this, there are many ways to solve this reshaping:

dfi = df.set_index(["Name", "Type"])
dfi.columns = dfi.columns.str.split(", ", expand=True).rename(['Date', 'Color'])
df_out = (
    dfi.stack([0, 1])
    .rename("Number")
    .reset_index()
)
df_out

Output:

  Name   Type        Date   Color  Number
0    A    Car  01/01/1991     RED    10.0
1    A    Car  01/02/1991  YELLOW    11.0
2    A    Car  01/05/1991    BLUE     5.0
3    B    Car  01/01/1991     RED    26.0
4    B    Car  01/02/1991  YELLOW    15.0
5    B    Car  01/05/1991    BLUE     8.0
6    C  Truck  01/01/1991     RED    30.0
7    C  Truck  01/02/1991  YELLOW     5.0
8    C  Truck  01/05/1991    BLUE    20.0

Explained:

Move 'Name' and 'Type' into the index, split the column headers of remaining columns on comma and space to create MultiIndex column headers. Next, stack both levels to index, then do some renaming to make out columns as desired and reset_index.

user_63615310

First, use melt

data = df.melt(id_vars=['Name', 'Type'], value_name='Number')

Then split the variablecolumn into two

data[['Date', 'Color']] = data['variable'].str.split(',', 1, expand=True)

Finally drop the variable column, and order by Name and Date

data = data.drop(['variable'], axis=1).sort_values(by=['Name', 'Date'])
user_162407510

You can use this pipeline:

(df.melt(id_vars=['Name', 'Type'], value_name='Number')
   .assign(Date=lambda d: d['variable'].str[:10],
           Color=lambda d: d['variable'].str[12:])
   .drop('variable', axis=1)
 )

This melts the dataframe to long form and extracts the date and color from the original column names.

Output:

  Name   Type  Number        Date   Color
0    A    Car      10  01/01/1991     RED
1    B    Car      26  01/01/1991     RED
2    C  Truck      30  01/01/1991     RED
3    A    Car      11  01/02/1991  YELLOW
4    B    Car      15  01/02/1991  YELLOW
5    C  Truck       5  01/02/1991  YELLOW
6    A    Car       5  01/05/1991    BLUE
7    B    Car       8  01/05/1991    BLUE
8    C  Truck      20  01/05/1991    BLUE
user_163434640

You could use pivot_longer from pyjanitor to abstract the reshaping process - internally it uses pd.melt/pd.concat :

# pip install pyjanitor
import pandas as pd
import janitor
df.pivot_longer(['Name', 'Type'],
                names_to = ('Date', 'Color'), 
                names_sep = ', ', 
                sort_by_appearance = True)
 
  Name   Type        Date   Color  value
0    A    Car  01/01/1991     RED     10
1    A    Car  01/02/1991  YELLOW     11
2    A    Car  01/05/1991    BLUE      5
3    B    Car  01/01/1991     RED     26
4    B    Car  01/02/1991  YELLOW     15
5    B    Car  01/05/1991    BLUE      8
6    C  Truck  01/01/1991     RED     30
7    C  Truck  01/02/1991  YELLOW      5
8    C  Truck  01/05/1991    BLUE     20
user_71757130
Related