CODEWITHSUNDEEP
pythonpandasdataframe
user11357465
user11357465

Reputation:

Transform Columns Into Rows for certain subsets of both columns and rows?

How would you transform rows into columns using my data? My current dataset looks like 'Original df' shown below, and I want it to look like the 'New df2'. Just to be clear, Session 1, matches with ADS1 and RDS1 and hence Appoint 2 corresponds to ADS2 RDS2.

Original df:

Name    Session1    Session2    Session1t    Session2t   ADS1    RDS1    ADS2    RDS2     

Sam     23.09.2017  24.09.2017  11:00:00     11:00:00    3        -9        6        8
Sarah   24.09.2017  27.09.2017  12:00:00     12:00:00    2        Nan       7        8
Steve   23.10.2017  31.10.2017  11:00:00     12:00:00    5         9        7        9
Mark    23.09.2017              11:00:00                 0         3        
James   23.09.2017  26.09.2017               11:00:00    4         7        1        4

New df:

Name    Sessions        Sessiontimes      ADS       RDS

Sam     23.09.2017      11:00:00           3       -9
Sam     24.09.2017      11:00:00           6        8
Sarah   24.09.2017      12:00:00           2        NaN
Sarah   27.09.2017      12:00:00           7        8
Steve   23.10.2017      11:00:00           5        9
Steve   31.10.2017      12:00:00           7        9
Mark    23.09.2017      11:00:00           0        3
James   23.09.2017                         4        7
James   26.09.2017      11:00:00           1        4

Upvotes: 0

Views: 41

Answers (1)

user3483203
user3483203

Reputation: 51155

This is a great scenario for wide_to_long. However, you should first change how you store the session times, so they are easier to match with each other, and don't conflict with the other Session series.

df.columns = df.columns.str.replace(r'Session(\d+)t', r'Time\1')

pd.wide_to_long(
  df, stubnames=['Session', 'Time', 'ADS', 'RDS'], i='Name', j='ID'
).dropna()


             Session      Time  ADS  RDS
Name  ID
Sam   1   23.09.2017  11:00:00  3.0   -9
Sarah 1   24.09.2017  12:00:00  2.0  Nan
Steve 1   23.10.2017  11:00:00  5.0    9
Mark  1   23.09.2017  11:00:00  0.0    3
Sam   2   24.09.2017  11:00:00  6.0    8
Sarah 2   27.09.2017  12:00:00  7.0    8
Steve 2   31.10.2017  12:00:00  7.0    9
James 2   26.09.2017  11:00:00  1.0    4

Upvotes: 1

Related Questions