How can I transform this Pandas Dataframe?

Question

I have a dataframe that just contains the last 14 days of the week and each with an integer value. I would like to combine the values of each day of the week like below.

Here is my dataframe:

    Day     Total
0   Tue     66
1   Wed     54
2   Thu     47
3   Fri     60
4   Sat     41
5   Sun     44
6   Mon     73
7   Tue     67
8   Wed     51
9   Thu     56
10  Fri     47
11  Sat     42
12  Sun     43
13  Mon     46

...and I would like it to look like this...

    Day     Total1  Total2
0   Tue     66      67
1   Wed     54      51
2   Thu     47      56
3   Fri     60      47
4   Sat     41      42
5   Sun     44      43
6   Mon     73      46

so that each day has two values rather than one.

Is there a fancy method to do this in Pandas?

Answer 1

Does not compare with jezrael's solutions but wanted to share a relatively simple method! This solution will not preserve order:

pd.pivot(df.Day, df.groupby('Day').cumcount(), df.Total).rename(columns={0: 'Total 1', 1: 'Total 2'}).reset_index()

   Day  Total 1  Total 2
0  Fri       60       47
1  Mon       73       46
2  Sat       41       42
3  Sun       44       43
4  Thu       47       56
5  Tue       66       67
6  Wed       54       51

Answer 2

First compare first value of Day selected by iat with column Day and with cumsum create new column. Then use pivot or set_index + unstack, last add add_suffix:

df['col'] = df['Day'].eq(df['Day'].iat[0]).cumsum()
df = df.pivot(index='Day', columns='col', values='Total').add_prefix('Total')
print (df)

col  Total1  Total2
Day                
Fri      60      47
Mon      73      46
Sat      41      42
Sun      44      43
Thu      47      56
Tue      66      67
Wed      54      51

Another solution:

df['col'] = df['Day'].eq(df['Day'].iat[0]).cumsum()
df = df.set_index(['Day', 'col'])['Total'].unstack().add_prefix('Total')
print (df)
col  Total1  Total2
Day                
Fri      60      47
Mon      73      46
Sat      41      42
Sun      44      43
Thu      47      56
Tue      66      67
Wed      54      51

If want sort index by days need ordered categorical:

days = ['Sun', 'Mon','Tue', 'Wed', 'Thu', 'Fri', 'Sat' ]
df['Day'] = df['Day'].astype('category', categories=days, ordered=True)
df['col'] = df['Day'].eq(df['Day'].iat[0]).cumsum()
df = df.set_index(['Day', 'col'])['Total'].unstack().add_prefix('Total')
print (df)
col  Total1  Total2
Day                
Sun      44      43
Mon      73      46
Tue      66      67
Wed      54      51
Thu      47      56
Fri      60      47
Sat      41      42

If need original ordering use reindex_axis:

df['col'] = df['Day'].eq(df['Day'].iat[0]).cumsum()
a = df.loc[df['col'] == 1, 'Day']
df = df.set_index(['Day', 'col'])['Total'].unstack()
       .add_prefix('Total')
       .reindex_axis(a, axis=0)
       .reset_index()
       .rename_axis(None, axis=1)
print (df)
   Day  Total1  Total2
0  Tue      66      67
1  Wed      54      51
2  Thu      47      56
3  Fri      60      47
4  Sat      41      42
5  Sun      44      43
6  Mon      73      46