Skip to content Skip to sidebar Skip to footer

Wide To Long Dataset Using Pandas

There are a lot of questions out there with similar titles but I'm unable to solve the issues that I'm having with my dataset. Dataset: ID Country Type Region Gender IA01_Raw I

Solution 1:

This will get you started. The essence is using set_index, column conversion to MultiIndex, then stack. Better solutions exist, possibly, but I would do it this way because it is an easy step to your output.

# Set the index with columns that we don't want to "transpose"
df2 = df.set_index([
   'ID', 'Country', 'Type', 'Region', 'Gender', 'QA_Include', 'QA_Comments'])
# Convert headers to MultiIndex -- this is so we can melt IA values
df2.columns = pd.MultiIndex.from_tuples(map(tuple, df2.columns.str.split('_')))
# Call stack to replicate data, then reset the index
out =  df2.stack(level=0).reset_index().rename({'level_7': 'IA'}, axis=1)

out

     ID Country Type  Region  Gender QA_Include  QA_Comments    IA  Class1  Class2 Raw
0   SC1  France    A  Europe    Male        yes          NaN  IA01       8       1   4
1   SC1  France    A  Europe    Male        yes          NaN  IA02       4       1   J
2   SC2  France    A  Europe  Female        yes          NaN  IA01       7       2   2
3   SC2  France    A  Europe  Female        yes          NaN  IA02       6       4   Q
4   SC3  France    B  Europe    Male        yes          NaN  IA01       7       2   3
5   SC3  France    B  Europe    Male        yes          NaN  IA02       8       2   K
6   SC4  France    A  Europe    Male        yes          NaN  IA01       8       2   4
7   SC4  France    A  Europe    Male        yes          NaN  IA02       2       1   A
8   SC5  France    B  Europe    Male        yes          NaN  IA01       7       1   1
9   SC5  France    B  Europe    Male        yes          NaN  IA02       1       3   F
10  ID6  France    A  Europe    Male        yes          NaN  IA01       8       1   2
11  ID6  France    A  Europe    Male        yes          NaN  IA02       3       7   R
12  ID7  France    B  Europe    Male        yes          NaN  IA01       8       1   2
13  ID7  France    B  Europe    Male        yes          NaN  IA02       4       6   Q
14  UC8  France    B  Europe    Male        yes          NaN  IA01       8       2   4
15  UC8  France    B  Europe    Male        yes          NaN  IA02       4       2   P

Solution 2:

u can use pd.lreshape

pd.lreshape(df.assign(IA01=['01']*len(df), IA02=['02']*len(df),IA09=['09']*len(df)), 
            {'IA': ['IA01', 'IA02','IA09'],
             'Raw': ['IA01_Raw','IA02_Raw','IA09_Raw'], 
             'Class1': ['IA01_Class1','IA02_Class1','IA09_Class1'], 
             'Class2': ['IA01_Class2', 'IA02_Class2','IA09_Class2']
             })


edit : 

pd.lreshape(df.assign(IA01=['01']*len(df), IA02=['02']*len(df),IA09=['09']*len(df)), 
            {'IA': ['IA01', 'IA02','IA09'],
             'Raw': ['IA01_Raw_baseline','IA02_Raw_midline','IA09_Raw_whatever'], 
             'Class1': ['IA01_Class1_baseline','IA02_Class1_midline','IA09_Class1_whatever'], 
             'Class2': ['IA01_Class2_baseline', 'IA02_Class2_midline','IA09_Class2_whatever']
             })

edit: Just add column names of which ever columns you want from the input in Raw/Class1/Class2 column of the output to the list inside the dictionary

documentation for this is not available . use help(pd.lreshape) or refer here

Output:

    Country Gender  ID  QA_Comments QA_Include  Region  Type    IA  Raw Class1  Class2
0   France  Male    SC1 NaN         yes         Europe  A       01  4   8       1
1   France  Female  SC2 NaN         yes         Europe  A       01  2   7       2
2   France  Male    SC3 NaN         yes         Europe  B       01  3   7       2
3   France  Male    SC4 NaN         yes         Europe  A       01  4   8       2
4   France  Male    SC5 NaN         yes         Europe  B       01  1   7       1
5   France  Male    ID6 NaN         yes         Europe  A       01  2   8       1
6   France  Male    ID7 NaN         yes         Europe  B       01  2   8       1
7   France  Male    UC8 NaN         yes         Europe  B       01  4   8       2
8   France  Male    SC1 NaN         yes         Europe  A       02  J   4       1
9   France  Female  SC2 NaN         yes         Europe  A       02  Q   6       4
10  France  Male    SC3 NaN         yes         Europe  B       02  K   8       2
11  France  Male    SC4 NaN         yes         Europe  A       02  A   2       1
12  France  Male    SC5 NaN         yes         Europe  B       02  F   1       3
13  France  Male    ID6 NaN         yes         Europe  A       02  R   3       7
14  France  Male    ID7 NaN         yes         Europe  B       02  Q   4       6
15  France  Male    UC8 NaN         yes         Europe  B       02  P   4       2
16  France  Male    SC1 NaN         yes         Europe  A       09  W   6       3
17  France  Female  SC2 NaN         yes         Europe  A       09  X   5       2
18  France  Male    SC3 NaN         yes         Europe  B       09  Y   5       5
19  France  Male    SC4 NaN         yes         Europe  A       09  P   5       2
20  France  Male    SC5 NaN         yes         Europe  B       09  T   5       2
21  France  Male    ID6 NaN         yes         Europe  A       09  I   5       2
22  France  Male    ID7 NaN         yes         Europe  B       09  A   8       2
23  France  Male    UC8 NaN         yes         Europe  B       09  K   7       5

Post a Comment for "Wide To Long Dataset Using Pandas"