1

I need to reshape a df and use the "year" information as a new column after reshaping. My data looks like this for df and will potentially contain more year data and players:

index      player A 2012    player B 2012     player A 2013    player B 2013
0          15               10                20               35
1          40               25                60               70

My final df needs to look like this for dfnew:

index      year       player A        player B
0          2012       15              10
0          2013       20              35
1          2012       40              25
1          2013       60              70

I"ve tried multiple variations of this code below and don't have a lot of experience in this but I don't know how to account for the changing "year" - i.e., 2012, 2013 and then to make that changing year into a new column.

df.pivot(index="index", columns=['player A','player B'])

Thank you very much,

user2100039
  • 1,280
  • 2
  • 16
  • 31

1 Answers1

2

Use wide_to_long:

df = pd.wide_to_long(df.reset_index(), 
                     stubnames=['player A','player B'], 
                     i='index',
                     j='Year',
                     sep=' ').reset_index(level=1).sort_index()
print (df)
       Year  player A  player B
index                          
0      2012        15        10
0      2013        20        35
1      2012        40        25
1      2013        60        70

Or Series.str.rsplit by last space with DataFrame.stack:

df.columns = df.columns.str.rsplit(n=1, expand=True)
df = df.stack().rename_axis((None, 'Year')).reset_index(level=1)
print (df)
   Year  player A  player B
0  2012        15        10
0  2013        20        35
1  2012        40        25
1  2013        60        70
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 1
    perfect - thank you! i just had to do one more step to sort by "index" then by "Year" to obtain the final ascending grouping. – user2100039 Aug 27 '21 at 14:29