Pivoting a Pandas Table - Peculiar Problem

user_10431629 0
Viewed 73

It seemed I had a simple problem of pivoting a pandas Table, but unfortunately, the problem seems a bit complicated to me.

I am providing a tiny sample table and the output I am looking to give the example of the problem I am facing:

Say, I have a table like this:

df = 
     AF   BF    AT  BT  
     1    4    100  70
     2    7    102  66
     3   11    200  90
     4   13    300  178
     5   18    403  200

So I need it into a wide/pivot format but the parameter name in each case will be set as the same. ( I am not looking to subset the string if possible)

My output table should like the following:

    dfout = 

         PAR   F    T
          A    1    100
          B    4    70
          A    2    102
          B    7    66
          A    3    200
          B    11   90
          A    4    300
          B   13    178
          A    5    403
          B   18    200

I tried pivoting, but not able to achieve the desired output. Any help will be immensely appreciated. Thanks.

pandas transform pivot lambda python
3 Answers

You can use pandas wide_to_long, but first you have to reorder the columns:

pd.wide_to_long(
    df.rename(columns=lambda x: x[::-1]).reset_index(),
    stubnames=["F", "T"],
    i="index",
    sep="",
    j="PAR",
    suffix=".",
).reset_index("PAR")



     PAR    F   T
index           
0   A   1   100
1   A   2   102
2   A   3   200
3   A   4   300
4   A   5   403
0   B   4   70
1   B   7   66
2   B   11  90
3   B   13  178
4   B   18  200

Alternatively, you could use the pivot_longer function from the pyjanitor, to reshape the data :

# pip install pyjanitor
import janitor
df.pivot_longer(names_to=("PAR", ".value"), names_pattern=r"(.)(.)")

   PAR  F   T
0   A   1   100
1   B   4   70
2   A   2   102
3   B   7   66
4   A   3   200
5   B   11  90
6   A   4   300
7   B   13  178
8   A   5   403
9   B   18  200

Update: Using data from @jezrael:

df

    C  AF  BF   AT   BT
0  10   1   4  100   70
1  20   2   7  102   66
2  30   3  11  200   90
3  40   4  13  300  178
4  50   5  18  403  200


pd.wide_to_long(
    df.rename(columns=lambda x: x[::-1]),
    stubnames=["F", "T"],
    i="C",
    sep="",
    j="PAR",
    suffix=".",
).reset_index()

     C  PAR F   T
0   10  A   1   100
1   20  A   2   102
2   30  A   3   200
3   40  A   4   300
4   50  A   5   403
5   10  B   4   70
6   20  B   7   66
7   30  B   11  90
8   40  B   13  178
9   50  B   18  200

if you use the pivot_longer function:

df.pivot_longer(index="C", names_to=("PAR", ".value"), names_pattern=r"(.)(.)")


    C   PAR F   T
0   10  A   1   100
1   10  B   4   70
2   20  A   2   102
3   20  B   7   66
4   30  A   3   200
5   30  B   11  90
6   40  A   4   300
7   40  B   13  178
8   50  A   5   403
9   50  B   18  200

pivot_longer is being worked on; in the next release of pyjanitor it should be much better. But pd.wide_to_long can solve your task pretty easily. The other answers can easily solve it as well.

user_71757130

Let's try:

(pd.wide_to_long(df.reset_index(),stubnames=['A','B'],
                i='index',
                j='PAR', sep='', suffix='[FT]')
   .stack().unstack('PAR').reset_index(level=1)
     )

Output:

PAR   level_1   F    T
index                 
0           A   1  100
0           B   4   70
1           A   2  102
1           B   7   66
2           A   3  200
2           B  11   90
3           A   4  300
3           B  13  178
4           A   5  403
4           B  18  200
user_42384080

Idea is create MultiIndex in columns by first and last letter and then use DataFrame.stack for reshape, last some data cleaning in MultiIndex in index:

df.columns= [df.columns.str[-1], df.columns.str[0]]
df = df.stack().reset_index(level=0, drop=True).rename_axis('PAR').reset_index()

print (df)
  PAR   F    T
0   A   1  100
1   B   4   70
2   A   2  102
3   B   7   66
4   A   3  200
5   B  11   90
6   A   4  300
7   B  13  178
8   A   5  403
9   B  18  200

EDIT:

print (df)
    C  AF  BF   AT   BT
0  10   1   4  100   70
1  20   2   7  102   66
2  30   3  11  200   90
3  40   4  13  300  178
4  50   5  18  403  200

df = df.set_index('C')
df.columns = pd.MultiIndex.from_arrays([df.columns.str[-1], 
                                        df.columns.str[0]], names=[None,'PAR'])
df = df.stack().reset_index()

print (df)
    C PAR   F    T
0  10   A   1  100
1  10   B   4   70
2  20   A   2  102
3  20   B   7   66
4  30   A   3  200
5  30   B  11   90
6  40   A   4  300
7  40   B  13  178
8  50   A   5  403
9  50   B  18  200
user_29010020
Related