Python pandas Dataframe column to rows manipulation

Question

I'm trying to transpose a few columns while keeping the other columns. I'm having a hard time with pivot codes or transpose codes as it doesn't really give me the output I need.

Can anyone help?

I have this data frame:

EmpID	Goal	week 1	week 2	week 3	week 4
1	556	54	33	24	54
2	342	32	32	56	43
3	534	43	65	64	21
4	244	45	87	5	22

My expected dataframe output is:

EmpID	Goal	Weeks	Actual
1	556	week 1	54
1	556	week 2	33
1	556	week 3	24
1	556	week 4	54

and so on until the full employee IDs are listed..

Does this answer your question? [Pandas Melt Function](https://stackoverflow.com/questions/34830597/pandas-melt-function) — Anurag Dabas, Jul 21 '21 at 16:40
`df.melt(['EmpID','Goal'],var_name='Weeks',value_name='Actual')` — Anurag Dabas, Jul 21 '21 at 16:45

score 0 · Answer 1 · answered Jul 23 '21 at 11:11

Something like this.

# Python - melt DF
import pandas as pd

d = {'Country Code': [1960, 1961, 1962, 1963, 1964, 1965, 1966],
'ABW':  [2.615300, 2.734390, 2.678430, 2.929920, 2.963250, 3.060540, 4.349760],
'AFG':  [0.249760, 0.218480, 0.210840, 0.217240, 0.211410, 0.209910, 0.671330],  
'ALB':  ['NaN', 'NaN', 'NaN', 'NaN', 'NaN', 'NaN', 1.12214]}
df = pd.DataFrame(data=d)

print(df)
df1 = (df.melt(['Country Code'], var_name='Year', value_name='Econometric_Metric')
        .sort_values(['Country Code','Year'])
        .reset_index(drop=True))
print(df1)

df2 = (df.set_index(['Country Code'])
        .stack(dropna=False)
        .reset_index(name='Econometric_Metric')
        .rename(columns={'level_1':'Year'}))
print(df2)


# BEFORE
       ABW      AFG      ALB  Country Code
0  2.61530  0.24976      NaN          1960
1  2.73439  0.21848      NaN          1961
2  2.67843  0.21084      NaN          1962
3  2.92992  0.21724      NaN          1963
4  2.96325  0.21141      NaN          1964
5  3.06054  0.20991      NaN          1965
6  4.34976  0.67133  1.12214          1966


# AFTER
    Country Code Year Econometric_Metric
0           1960  ABW             2.6153
1           1960  AFG            0.24976
2           1960  ALB                NaN
3           1961  ABW            2.73439
4           1961  AFG            0.21848
5           1961  ALB                NaN
6           1962  ABW            2.67843
7           1962  AFG            0.21084
8           1962  ALB                NaN
9           1963  ABW            2.92992
10          1963  AFG            0.21724
11          1963  ALB                NaN
12          1964  ABW            2.96325
13          1964  AFG            0.21141
14          1964  ALB                NaN
15          1965  ABW            3.06054
16          1965  AFG            0.20991
17          1965  ALB                NaN
18          1966  ABW            4.34976
19          1966  AFG            0.67133
20          1966  ALB            1.12214
    Country Code Year Econometric_Metric
0           1960  ABW             2.6153
1           1960  AFG            0.24976
2           1960  ALB                NaN
3           1961  ABW            2.73439
4           1961  AFG            0.21848
5           1961  ALB                NaN
6           1962  ABW            2.67843
7           1962  AFG            0.21084
8           1962  ALB                NaN
9           1963  ABW            2.92992
10          1963  AFG            0.21724
11          1963  ALB                NaN
12          1964  ABW            2.96325
13          1964  AFG            0.21141
14          1964  ALB                NaN
15          1965  ABW            3.06054
16          1965  AFG            0.20991
17          1965  ALB                NaN
18          1966  ABW            4.34976
19          1966  AFG            0.67133
20          1966  ALB            1.12214

Also, take a look at the link below, for more info.

https://www.dataindependent.com/pandas/pandas-melt/

Python pandas Dataframe column to rows manipulation

1 Answers1