How to transform rows of other columns to columns on the basis of unique values of a column?

user_14291703 0
Viewed 72

Suppose I have a df in the following structure,

column1 | column2 | column3 | column4 | column5 | column6 | column7
   A    |    B    |    C    |    10   |    78   |   12    |  202001
   A    |    B    |    D    |    21   |    64   |   87    |  202001
   A    |    B    |    E    |    21   |    64   |   87    |  202001
   X    |    K    |    C    |    54   |    23   |   23    |  202001
   X    |    K    |    D    |    21   |    55   |   87    |  202001
   X    |    K    |    E    |    21   |    43   |   22    |  202001
   A    |    B    |    C    |    10   |    78   |   12    |  202002
   A    |    B    |    D    |    23   |    64   |   87    |  202002
   A    |    B    |    E    |    21   |    11   |   34    |  202002
   Z    |    K    |    C    |    10   |    78   |   12    |  202002
   Z    |    K    |    D    |    21   |    13   |   56    |  202002
   Z    |    K    |    E    |    12   |    77   |   34    |  202002

relation between column1 to column2 - one to many

relation between column2 to column1 - one to many

Expected Output:

column1 | column2 | column3 | column4_202001 | column5_202001 | column6_202001 | column4_202002 | column5_202002 | column6_202002 |
   A    |    B    |    C    |      10        |       78       |       12       |      10        |      78        |      12        |
   A    |    B    |    D    |      21        |       64       |       87       |      23        |      64        |      87        |
   A    |    B    |    E    |      21        |       64       |       87       |      21        |      11        |      34        |   
   X    |    K    |    C    |      54        |       23       |       23       |       0        |       0        |       0        |   
   X    |    K    |    D    |      21        |       55       |       87       |       0        |       0        |       0        |   
   X    |    K    |    E    |      21        |       43       |       22       |       0        |       0        |       0        |    
   Z    |    K    |    C    |       0        |        0       |        0       |      10        |      78        |      12        |    
   Z    |    K    |    D    |       0        |        0       |        0       |      21        |      13        |      56        |   
   Z    |    K    |    E    |       0        |        0       |        0       |      12        |      77        |      34        |

Also, while transforming, for every column7 can I create an empty column right beside column6_yyyymm?

Final Output,

column1 | column2 | column3 | column4_202001 | column5_202001 | column6_202001 |   empty_202001 | column4_202002 | column5_202002 | column6_202002 | empty_202002 ....
   A    |    B    |    C    |      10        |       78       |       12       |                |      10        |      78        |      12        |
   A    |    B    |    D    |      21        |       64       |       87       |                |      23        |      64        |      87        |
   A    |    B    |    E    |      21        |       64       |       87       |                |      21        |      11        |      34        |   
   X    |    K    |    C    |      54        |       23       |       23       |                |      0         |       0        |       0        |   
   X    |    K    |    D    |      21        |       55       |       87       |                |      0         |       0        |       0        |   
   X    |    K    |    E    |      21        |       43       |       22       |                |      0         |       0        |       0        |    
   Z    |    K    |    C    |       0        |        0       |        0       |                |      10        |      78        |      12        |    
   Z    |    K    |    D    |       0        |        0       |        0       |                |      21        |      13        |      56        |   
   Z    |    K    |    E    |       0        |        0       |        0       |                |      12        |      77        |      34        |

How can I achieve Final Output using a python function and/or pandas library? If there is anything unclear please let me know.

UPDATE:

For all empty_yyyymm columns I want to implement the following function,

        def get_final(row):
        if row['column2'].isin(['H', 'S', 'Z']):
            return 0
        elif row['column4_yyyymm'] + row['column5_yyyymm'] - row['column6_yyyymm'] < 0 and not row['column2'].isin(['H', 'S', 'Z']):
            return 0
        else:
            return row['column4_yyyymm'] + row['column5_yyyymm'] - row['column6_yyyymm']

How can achieve this too?

Note: yyyymm is generic way of referring column7. It is not actually a column.

pandas data-manipulation dataframe python
2 Answers

try:

df1 = (df.set_index(['column1', 'column2', 'column3', 'column7'])
   .rename_axis(['idx'], axis=1)
   .unstack('column7')
   .reset_index().fillna(0))
df1.columns = df1.columns.map(lambda x: '_'.join([str(i) for i in x]) if (x[1])!='' else x[0])
column1 column2 column3 column4_202001 column4_202002 column5_202001 column5_202002 column6_202001 column6_202002
0 A B C 10.0 10.0 78.0 78.0 12.0 12.0
1 A B D 21.0 23.0 64.0 64.0 87.0 87.0
2 A B E 21.0 21.0 64.0 11.0 87.0 34.0
3 X K C 54.0 0.0 23.0 0.0 23.0 0.0
4 X K D 21.0 0.0 55.0 0.0 87.0 0.0
5 X K E 21.0 0.0 43.0 0.0 22.0 0.0
6 Z K C 0.0 10.0 0.0 78.0 0.0 12.0
7 Z K D 0.0 21.0 0.0 13.0 0.0 56.0
8 Z K E 0.0 12.0 0.0 77.0 0.0 34.0
user_66603730

First create empty column by DataFrame.assign, then reshape by DataFrame.set_index with DataFrame.unstack and sorting datetimes in second level by DataFrame.sort_index:

df = (df.assign(empty = np.nan)
        .set_index(['column1','column2','column3','column7'])
        .unstack(fill_value=0)
        .sort_index(level=1, axis=1))

Then set values missing to all empty columns, flatten MultiIndex in columns by map and last convert index to columns by DataFrame.reset_index:

df['empty'] = np.nan
#if need fill by empty string
#df['empty'] = ''
df.columns = df.columns.map(lambda x: f'{x[0]}_{x[1]}')
df = df.reset_index()
print (df)
  column1 column2 column3  column4_202001  column5_202001  column6_202001  \
0       A       B       C              10              78              12   
1       A       B       D              21              64              87   
2       A       B       E              21              64              87   
3       X       K       C              54              23              23   
4       X       K       D              21              55              87   
5       X       K       E              21              43              22   
6       Z       K       C               0               0               0   
7       Z       K       D               0               0               0   
8       Z       K       E               0               0               0   

   empty_202001  column4_202002  column5_202002  column6_202002  empty_202002  
0           NaN              10              78              12           NaN  
1           NaN              23              64              87           NaN  
2           NaN              21              11              34           NaN  
3           NaN               0               0               0           NaN  
4           NaN               0               0               0           NaN  
5           NaN               0               0               0           NaN  
6           NaN              10              78              12           NaN  
7           NaN              21              13              56           NaN  
8           NaN              12              77              34           NaN

EDIT: First count new column empty by conditions and then apply solution above without set NaN like:

m1 = df['column2'].isin(['H', 'S', 'Z'])

s = df['column4'] + df['column5'] - df['column6']
m2 = (s < 0) & ~m1

out = np.where(m1 | m2, 0, s)
df = (df.assign(empty = out)
        .set_index(['column1','column2','column3','column7'])
        .unstack(fill_value=0)
        .sort_index(level=1, axis=1))

df.columns = df.columns.map(lambda x: f'{x[0]}_{x[1]}')
df = df.reset_index()

print (df)
  column1 column2 column3  column4_202001  column5_202001  column6_202001  \
0       A       B       C              10              78              12   
1       A       B       D              21              64              87   
2       A       B       E              21              64              87   
3       X       K       C              54              23              23   
4       X       K       D              21              55              87   
5       X       K       E              21              43              22   
6       Z       K       C               0               0               0   
7       Z       K       D               0               0               0   
8       Z       K       E               0               0               0   

   empty_202001  column4_202002  column5_202002  column6_202002  empty_202002  
0            76              10              78              12            76  
1             0              23              64              87             0  
2             0              21              11              34             0  
3            54               0               0               0             0  
4             0               0               0               0             0  
5            42               0               0               0             0  
6             0              10              78              12            76  
7             0              21              13              56             0  
8             0              12              77              34            55
user_29010020
Related