Melting a dataframe based on a flag

user_12690344 0
Viewed 98

I've a dataframe like this:

pd.DataFrame({'time':['01-01-2020','02-01-2020','01-01-2020','02-01-2020'],'level':['q','q','r','r'],'a':[1,2,3,4],'b':[12,34,54,67],'c':[18,29,39,47],'a_1':[0.1,0.2,0.3,0.4],'a_2':[0,1,0,1],'b_1':[0.28,0.47,0.02,0.05],'b_2':[1,1,0,1],'c_1':[0.18,0.40,0.12,0.01],'c_2':[1,1,0,0]})
>>  time      level a   b   c   a_1 a_2  b_1   b_2  c_1   c_2
0   01-01-2020  q   1   12  18  0.1 0   0.28    1   0.18    1
1   02-01-2020  q   2   34  29  0.2 1   0.47    1   0.40    1
2   01-01-2020  r   3   54  39  0.3 0   0.02    0   0.12    0
3   02-01-2020  r   4   67  47  0.4 1   0.05    1   0.01    0

I wish to melt the data with time and level as index have all other columns as rows which had a flag 1 corresponding to their prefixes. Eg. I wish to have the values of a and a_1 listed as values and items if the value a_2 was 1. Desired output:

>>   time   level column values items
0   01-01-2020  q   b   12  0.28
1   01-01-2020  q   c   18  0.18
2   02-01-2020  q   a   2   0.20
3   02-01-2020  q   b   34  0.47
4   02-01-2020  q   c   29  0.40
5   02-01-2020  r   a   4   0.40
6   02-01-2020  r   b   67  0.05

I can get all the values irrespective of the flags and then filter for flags==1. But,not sure how to "melt" /"unstack" in this case. I tried a lot of ways but in vain. Please help me out here.

pandas dataframe python-3.x
3 Answers

Let's try with melt:

i, c = ['time', 'level'], pd.Index(['a', 'b','c'])

# mask the values where flag=0
m = df[c + '_1'].mask(df[c + '_2'].eq(0).values)

# melt the dataframe & assign the items column
s = df[[*i, *c]].melt(i, var_name='columns')\
                .assign(items=m.values.T.reshape((-1, 1)))

# drop the nan values and sort the dataframe
s = s.dropna(subset=['items']).sort_values(i, ignore_index=True)

Details:

mask the values in columns ending with suffix _1 where the values in the corresponding flag columns equals 0:

   a_1   b_1   c_1
0  NaN  0.28  0.18
1  0.2  0.47  0.40
2  NaN   NaN   NaN
3  0.4  0.05   NaN

melt the dataframe containing the columns a, b, c, then reshape the masked values and assign a new column items in melted dataframe:

          time level columns  value  items
0   01-01-2020     q       a      1    NaN
1   02-01-2020     q       a      2   0.20
2   01-01-2020     r       a      3    NaN
3   02-01-2020     r       a      4   0.40
4   01-01-2020     q       b     12   0.28
5   02-01-2020     q       b     34   0.47
6   01-01-2020     r       b     54    NaN
7   02-01-2020     r       b     67   0.05
8   01-01-2020     q       c     18   0.18
9   02-01-2020     q       c     29   0.40
10  01-01-2020     r       c     39    NaN
11  02-01-2020     r       c     47    NaN

Lastly drop the NaN values in items and sort the values on time and level to get the final result:

         time level columns  value  items
0  01-01-2020     q       b     12   0.28
1  01-01-2020     q       c     18   0.18
2  02-01-2020     q       a      2   0.20
3  02-01-2020     q       b     34   0.47
4  02-01-2020     q       c     29   0.40
5  02-01-2020     r       a      4   0.40
6  02-01-2020     r       b     67   0.05
user_128331660

There may be a more elegant way, but this works. Extract data for each column name (a, b, c), select those that have flags set to 1 and concatenate the results.

df.set_index(['time', 'level'], inplace=True)
parts = []
for name in 'a','b','c':
    d = df[[name, f'{name}_1', f'{name}_2']]\
         .rename(columns={name: 'values', f'{name}_1': 'items', f'{name}_2': 'flag'})
    d['column'] = name
    parts.append(d[d.flag == 1])
pd.concat(parts)[['column','values','items']].reset_index()
user_44929320

Step 1: reorder the columns such that the numbers come before the letters:

res = df.copy()
res.columns = ["_".join(entry.split("_")[::-1]) for entry in res]

Step2 : reorder the columns (again) such that "num" is prefixed if the column is in ("a","b","c")

res.columns = [f"num_{letter}" if letter in ("a", "b", "c") 
               else letter 
               for letter in res]
res

time    level   num_a   num_b   num_c   1_a 2_a 1_b 2_b 1_c 2_c
0   01-01-2020  q   1   12  18  0.1 0   0.28    1   0.18    1
1   02-01-2020  q   2   34  29  0.2 1   0.47    1   0.40    1
2   01-01-2020  r   3   54  39  0.3 0   0.02    0   0.12    0
3   02-01-2020  r   4   67  47  0.4 1   0.05    1   0.01    0

Step 3: Use pandas wide to long to reshape the data, filter for rows equal to 1, rename the columns and finally reset the index:

(
    pd.wide_to_long(
        res,
        stubnames=["num", "1", "2"],
        i=["time", "level"],
        j="column",
        sep="_",
        suffix=".",
    )
     # this is where the filter for rows equal to 1 occur
    .query("`2`==1")
    .drop(columns="2")
    .set_axis(["values", "items"], axis="columns")
    .reset_index()
)


time    level   column  values  items
0   01-01-2020  q   b   12  0.28
1   01-01-2020  q   c   18  0.18
2   02-01-2020  q   a   2   0.20
3   02-01-2020  q   b   34  0.47
4   02-01-2020  q   c   29  0.40
5   02-01-2020  r   a   4   0.40
6   02-01-2020  r   b   67  0.05

This is another way, but same idea of renaming the columns - makes it easy to reshape with wide to long:

result = df.rename(
    columns=lambda x: f"values_{x}"
    if x in ("a", "b", "c")
    else f"items_{x[0]}"
    if re.search(".1$", x)
    else f"equals1_{x[0]}"
    if re.search(".2$", x)
    else x
)


(
    pd.wide_to_long(
        result,
        stubnames=["values", "items", "equals1"],
        i=["time", "level"],
        j="column",
        sep="_",
        suffix=".",
    )
    .query("equals1==1")
    .iloc[:, :-1]
    .reset_index()
)

Another option is the pivot_longer function from pyjanitor, using the .value placeholder:

# pip install pyjanitor
import pandas as pd
import janitor

(df
.pivot_longer(index = ['time', 'level'], 
              names_to = ["column", ".value"], 
              names_pattern = r"(.)_?(.?)", 
              sort_by_appearance = True)
.query('`2` == 1')
.drop(columns = '2')
.rename(columns={'':'values', '1':'items'})
)

          time level column  values  items
1   01-01-2020     q      b      12   0.28
2   01-01-2020     q      c      18   0.18
3   02-01-2020     q      a       2   0.20
4   02-01-2020     q      b      34   0.47
5   02-01-2020     q      c      29   0.40
9   02-01-2020     r      a       4   0.40
10  02-01-2020     r      b      67   0.05
user_71757130
Related