keeping first column value .melt func

Question

I want to use dataframe.melt function in pandas lib to convert data format from rows into column but keeping first column value. I ve just tried also .pivot, but it is not working good. Please look at the example below and please help:

       ID      Alphabet Unspecified: 1  Unspecified: 2
0      1          A          G              L
1      2          B         NaN            NaN
2      3          C          H             NaN
3      4          D          I              M
4      5          E          J             NaN
5      6          F          K              O

Into this:

        ID     Alphabet
0      1          A   
1      1          G        
2      1          L 
3      2          B
4      3          C    
5      3          H  
6      4          D   
7      4          I        
8      4          M 
9      5          E
10     5          J
11     6          F
12     6          K     
11     6          O

score 0 · Accepted Answer · answered Jun 25 '22 at 20:38

Try (assuming ID is unique and sorted):

df = (
    pd.melt(df, "ID")
    .sort_values("ID", kind="stable")
    .drop(columns="variable")
    .dropna()
    .reset_index(drop=True)
    .rename(columns={"value": "Alphabet"})
)
print(df)

Prints:

    ID Alphabet
0    1        A
1    1        G
2    1        L
3    2        B
4    3        C
5    3        H
6    4        D
7    4        I
8    4        M
9    5        E
10   5        J
11   6        F
12   6        K
13   6        O

score 0 · Answer 2 · answered Jun 25 '22 at 20:45

Don't melt but rather stack, this will directly drop the NaNs and keep the order per row:

out = (df
 .set_index('ID')
 .stack().droplevel(1)
 .reset_index(name='Alphabet')
 )

Output:

    ID Alphabet
0    1        A
1    1        G
2    1        L
3    2        B
4    3        C
5    3        H
6    4        D
7    4        I
8    4        M
9    5        E
10   5        J
11   6        F
12   6        K
13   6        O

score 0 · Answer 3 · answered Jun 30 '22 at 00:24

One option is with pivot_longer from pyjanitor:

# pip install pyjanitor
import pandas as pd
import janitor

(df
.pivot_longer(
    index = 'ID', 
    names_to = 'Alphabet', 
    names_pattern = ['.+'], 
    sort_by_appearance = True)
.dropna()
)
    ID Alphabet
0    1        A
1    1        G
2    1        L
3    2        B
6    3        C
7    3        H
9    4        D
10   4        I
11   4        M
12   5        E
13   5        J
15   6        F
16   6        K
17   6        O

In the code above, the names_pattern accepts a list of regular expression to match the desired columns, all the matches are collated into one column names Alphabet in names_to.

keeping first column value .melt func

3 Answers3