1

I'm trying to manipulate my dataframe so that the column names (ex Monday, Tuesday) can be used in the x-axis of the seaborn scatterplot (shown below). Right now I have this:

          week  Sun  Mon  Tues  Wed  Thur  Fri  Sat  
0   2016-05-08    0    0     0    3     1    5    1    
1   2016-05-15    0    0     0    1     0    0    1     
2   2016-05-22    0    0     1    2     3    0    0      
3   2016-05-29    0    0     1    0     0    0    0     
4   2016-06-05    0    0     3   19    19   14    1     
5   2016-06-12    0   40    30   14     0    0    0     
6   2016-06-19    3   16    10   26    38   17   17    
7   2016-06-26    0    4     3    1     1    4    0

The only thing I can think of is to change my data format so that it becomes something like this:

 week             day      amount
 2016-05-08       Sun           1
 2016-05-15       Sun          30
 2016-05-22       Sun           0
 2016-05-29       Mon           6
 2016-06-19       Mon          40

I'm a bit confused how I would unstack it.

Any direction of how I can accomplish that would be greatly appreciated.

Scatterplot

Captam Morgan
  • 21
  • 4
  • You could try to do some really novel unpivoting, or just process the rows into the new structure. – Michael May 02 '17 at 22:55

1 Answers1

1

To rearrange your data from a wide format to long format:

df.set_index('week').stack().rename_axis(('week','day')).reset_index().rename(columns={0:'amount'})

The picture you attached is a stripplot generated by Seaborn.

No data manipulation needed with Seaborn.

import seaborn as sns  
print(df)
             week     day  amount
0  2016-05-08  Sunday       1
1  2016-05-15  Sunday      30
2  2016-05-22  Sunday       0
3  2016-05-29  Monday       6
4  2016-06-19  Monday      40

_ = sns.stripplot('day', 'amount', data=df)

enter image description here

However, if you wanted to manipulate the data anyway, you can use the following commands:

df_out = df.set_index(['week','day']).unstack()
df_out.columns = df_out.columns.droplevel()
print(df_out)

Output:

day        Monday Sunday
week                    
2016-05-08    NaN    1.0
2016-05-15    NaN   30.0
2016-05-22    NaN    0.0
2016-05-29    6.0    NaN
2016-06-19   40.0    NaN
Scott Boston
  • 147,308
  • 15
  • 139
  • 187
  • Thanks for your quick reply. The current format of my data frame isn't usable by seaborn yet. Any clue how I can reshape my data so that all the column days (Mon,Tues,Wed) go under one column? – Captam Morgan May 02 '17 at 19:50
  • df.set_index('week').stack().rename_axis(('week','day')).reset_index(level=1).rename(columns={0:'amount'}) – Scott Boston May 02 '17 at 21:54
  • @CaptamMorgan edit solution to show how to reshape your data from wide to long for use with Seaborn. – Scott Boston May 02 '17 at 21:58