Melt and Merge on Substring - Python & Pandas

user_1302551 0
Viewed 1462

I have data which has data like 

id      name    model_#   ms   bp1   cd1    sf1    sa1   rq1   bp2   cd2   sf2   sa2   rq2 ... 
1       John    23984     1    23    234    124     25   252   252    62   194    234   234 ... 
2       John    23984     2    234   234    242     62   262   622   262   622    26    262 ...

for hundreds of models with up to 10 ms and variables counting up to 21.

I have usually used pd.melt for doing my analysis where i look at bp1:bp21 or whatever. I currently have a need to create a melt where I look at bp1 values along with rq 1 values.

I am looking to effectively create something like this: 

              id  model_#  ms  variable_x  value_x variable_y  value_y
0            113    77515   1        bp1     23        rq1      252
1            113    77515   1        bp2     252       rq2      262
2            113    77515   1        bp3     26        rq3      311

Right now the best I have been able to do is:

              id  model_#  ms variable_x  value_x variable_y  value_y
0            113    77515   1        bp1     23        rq1      252
1            113    77515   1        bp1     23        rq2      262
2            113    77515   1        bp1     23        rq3      311
3            113    77515   1        bp1     23        rq4      246

via: 

df = pd.melt(dat, id_vars=['id', 'mod_req', 'ms'], value_vars=bp)
df1 = pd.melt(dat, id_vars=['id', 'mod_req', 'ms'], value_vars=rq)
df2 = pd.merge(df,df1, on=['id', 'mod_req', 'ms'])

Is there an easy way to merge on substring such that bp1 will connect with rq1 and so forth? This would mean taking a melted dataframe which only looks at bp1:bp21 and a other melted dataframe rq1:rq21 and merging based on the substring values( bp1 rq1, not bp1 rq2)

melt pandas merge python
2 Answers

One option is with pivot_longer from pyjanitor, using a list of regular expressions, taking advantage of the ordering (bp1, rq1, bp2, rq2, ...):

# currently in dev
# pip install git+https://github.com/pyjanitor-devs/pyjanitor.git
import pandas as pd
import janitor

df.pivot_longer(
    index = ['id', 'name', 'model_#'], 
    names_to = ('variable_x', 'variable_y'), 
    values_to = ['values_x', 'values_y'], 
    names_pattern = ['bp', 'rq'])

   id  name  model_# variable_x  values_x variable_y  values_y
0   1  John    23984        bp1        23        rq1       252
1   2  John    23984        bp1       234        rq1       262
2   1  John    23984        bp2       252        rq2       234
3   2  John    23984        bp2       622        rq2       262
user_71757130
Related