Pandas Melt row into a column

Question

I have a data frame which current reads per the below:

df_new = pd.DataFrame({'Week':['nan',14, 14, 14, 14, 14],
                          'Date':['NaT','2020-04-01', '2020-04-02', '2020-04-03', '2020-04-04', '2020-04-05'],
                          'site 1':['entry',0, 0, 0, 0, 0],
                          'site 1':['exit',0, 0, 0, 0, 0],
                          'site 2':['entry',1, 0,50, 7, 0],
                          'site 2':['exit',10, 0, 7, 19, 0],
                          'site 3':['entry',0, 100, 14, 9, 0],
                          'site 3':['exit',0, 0, 7, 0, 0],
                          'site 4':['entry',0, 0, 0, 0, 0],
                          'site 4':['exit',0, 0, 0, 0, 0],
                          'site 5':['entry',0, 0, 0, 0, 0],
                          'site 5':['exit',15, 0, 25, 0, 80],
                          })

What I desire however is columns dictating exit/entry per site (columns came from merged Excel headers)

An example of what's desired is below (ignore the actual values as I typed them out)

df_target = pd.DataFrame({'Week':[14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14],
                          'Date':['2020-04-01', '2020-04-02', '2020-04-03', '2020-04-04', '2020-04-05','2020-04-01', '2020-04-02', '2020-04-03', '2020-04-04', '2020-04-05','2020-04-01', '2020-04-02', '2020-04-03', '2020-04-04', '2020-04-05'],
                          'site':['site 1', 'site 1', 'site 1', 'site 1', 'site 1', 'site 1', 'site 1', 'site 1', 'site 1', 'site 2', 'site 2','site 2','site 2','site 2','site 2'],
                          'entry/exit':['exit','exit', 'exit', 'entry', 'entry', 'entry', 'entry', 'entry', 'entry', 'exit', 'exit', 'exit', 'exit', 'entry', 'entry'],
                          'Value':[12 ,1, 0, 50, 7, 0, 12 ,1, 0, 50, 7, 0, 12 ,1, 0]               
                          })

as an image:

I have tried

df_target = df_new.melt(id_vars=['Week','Date'], var_name="Site", value_name="Value")

but guess I need to somehow group by the second row too or consider it as a second header?

Does this answer your question? [Pandas Melt Function](https://stackoverflow.com/questions/34830597/pandas-melt-function) — wwnde, Jun 30 '20 at 11:12
can you look at your `df_new` dataframe, there entry columns are being overwritten, can you post the raw format from excel? — Umar.H, Jun 30 '20 at 11:17
@wwnde I think this only works if I concatenate the header with values from the first row — Jman88, Jun 30 '20 at 11:51

score 2 · Accepted Answer · answered Jun 30 '20 at 11:18

First create MultiIndex from input DataFrame:

#if possible
#df = pd.read_csv(file, header=[0,1], index_col=[0,1])

df_new.columns = [df_new.columns, df_new.iloc[0]]
df = df_new.iloc[1:]
print (df.columns)
MultiIndex([(  'Week',  'nan'),
            (  'Date',  'NaT'),
            ('site 1', 'exit'),
            ('site 2', 'exit'),
            ('site 3', 'exit'),
            ('site 4', 'exit'),
            ('site 5', 'exit')],
           )

And then convert first 2 MultiIndex columns to index, so possible use DataFrame.unstack for melting with Series.rename_axis and Series.reset_index:

df = (df.set_index(df.columns[:2].tolist())
        .unstack([0,1])
        .rename_axis(['site','entry/exit','Week','Date'])
        .reset_index(name='Value'))
print (df)
      site entry/exit  Week        Date Value
0   site 1       exit    14  2020-04-01     0
1   site 1       exit    14  2020-04-02     0
2   site 1       exit    14  2020-04-03     0
3   site 1       exit    14  2020-04-04     0
4   site 1       exit    14  2020-04-05     0
5   site 2       exit    14  2020-04-01    10
6   site 2       exit    14  2020-04-02     0
7   site 2       exit    14  2020-04-03     7
8   site 2       exit    14  2020-04-04    19
9   site 2       exit    14  2020-04-05     0
10  site 3       exit    14  2020-04-01     0
11  site 3       exit    14  2020-04-02     0
12  site 3       exit    14  2020-04-03     7
13  site 3       exit    14  2020-04-04     0
14  site 3       exit    14  2020-04-05     0
15  site 4       exit    14  2020-04-01     0
16  site 4       exit    14  2020-04-02     0
17  site 4       exit    14  2020-04-03     0
18  site 4       exit    14  2020-04-04     0
19  site 4       exit    14  2020-04-05     0
20  site 5       exit    14  2020-04-01    15
21  site 5       exit    14  2020-04-02     0
22  site 5       exit    14  2020-04-03    25
23  site 5       exit    14  2020-04-04     0
24  site 5       exit    14  2020-04-05    80

This works great thanks! I was contemplating doing the first step and then melting but then I would have to split the site again to pull exit/entry so the stack works better. — Jman88, Jun 30 '20 at 11:34

Pandas Melt row into a column

1 Answers1