Wide To Long Returns Empty Output - Python Dataframe

I have a dataframe which can be generated from the code as given below df = pd.DataFrame({'person_id' :[1,2,3],'date1': ['12/31/2007','11/25/2009','10/06/2005'],'val1': [2,4,6],'

Solution 1:

The issue is with your column names, the numbers used to convert from wide to long need to be at the end of your column names or you need to specify a suffix to groupby. I think the easiest solution is to create a function that accepts regex and the dataframe.

import pandas as pd
import re

defchange_names(df, regex):
    # Select one of three column groups
    old_cols = df.filter(regex = regex).columns
    # Create list of new column names
    new_cols = []
    for col in old_cols:
        # Get the stubname of the original column
        stub = ''.join(re.split(r'\d', col))
        # Get the time point
        num = re.findall(r'\d+', col) # returns a list like ['1']# Make new column name
        new_col = stub + num[0]
        new_cols.append(new_col)

    # Create dictionary mapping old column names to new column names
    dd = {oc: nc for oc, nc inzip(old_cols, new_cols)}
    # Rename columns
    df.rename(columns = dd, inplace = True)

    return df


tdf = pd.DataFrame({'person_id' :[1,2,3],'h1date': ['12/31/2007','11/25/2009','10/06/2005'],'t1val': [2,4,6],'h2date': ['12/31/2017','11/25/2019','10/06/2015'],'t2val':[1,3,5],'h3date': ['12/31/2027','11/25/2029','10/06/2025'],'t3val':[7,9,11]})

# Change date columns
tdf = change_names(tdf, 'date$')
tdf = change_names(tdf, 'val$')

print(tdf)
    person_id      hdate1  tval1      hdate2  tval2      hdate3  tval3
0112/31/2007212/31/2017112/31/202771211/25/2009411/25/2019311/25/202992310/06/2005610/06/2015510/06/202511

Solution 2:

Try adding additional argument in the function which allows the strings suffix.

pd.long_to_wide(.......................,suffix='\w+')

Solution 3:

This is quite late to answer this question. But putting the solution here in case someone else find it useful

    tdf = pd.DataFrame({'person_id' :[1,2,3],'h1date': ['12/31/2007','11/25/2009','10/06/2005'],'t1val': [2,4,6],'h2date': ['12/31/2017','11/25/2019','10/06/2015'],'t2val':[1,3,5],'h3date': ['12/31/2027','11/25/2029','10/06/2025'],'t3val':[7,9,11]})
    
    ## You can use m13op22 solution to rename your columns with numeric part at the ## end of the column name. This is important.
    
    tdf = tdf.rename(columns={'h1date': 'hdate1', 't1val': 'tval1',
                              'h2date': 'hdate2', 't2val': 'tval2',
                              'h3date': 'hdate3', 't3val': 'tval3'})

   ## Then use the non-numeric portion,  (in this example 'hdate', 'tval') as ## stubnames. The mistake you were doing was using ['date', 'val'] as stubnames.df = pd.wide_to_long(tdf, stubnames=['hdate', 'tval'], i='person_id', j='grp').sort_index(level=0)
    
    print(df)