CODEWITHSUNDEEP
pythonpandasdataframepivot-table
jenryb
jenryb

Reputation: 2117

Reorganizing Dataframe by Date

I have a pivot table that shows customer calls by month. It looks like this

CompanyName 1   2   3   4   5   6   7   8   9   10  11  12
Customer 1  17  30  29  39  15  27  15  10  36  21  18  15
Customer 2  4   11  13  22  34  27  32  17  29  31  17  14
Customer 3  10  7   23  21  7   15  25  0   21  9   12  17
Customer 4  6   10  11  8   3   4   3   8   11  11  18  14
Customer 5  13  7   6   12  6   8   2   10  11  7   10  11

I make a date list so that I can display them nicely on a graph. 

date_list[::-1]
Out[63]: 
['Jul 2015',
 'Jun 2015',
 'May 2015',
 'Apr 2015',
 'Mar 2015',
 'Feb 2015',
 'Jan 2015',
 'Dec 2014',
 'Nov 2014',
 'Oct 2014',
 'Sep 2014',
 'Aug 2014']

I would like to reorder the pivot table so that it matches the date_list like so:

CompanyName 7   8   9   10  11  12  1   2   3   4   5   6
Customer 1  15  10  36  21  18  15  17  30  29  39  15  27
Customer 2  32  17  29  31  17  14  4   11  13  22  34  27
Customer 3  25  0   21  9   12  17  10  7   23  21  7   15
Customer 4  3   8   11  11  18  14  6   10  11  8   3   4
Customer 5  2   10  11  7   10  11  13  7   6   12  6   8

I was looking at how to reorder columns but since this will be dynamically changing by month I got a bit baffled.

Upvotes: 0

Views: 62

Answers (2)

Alexander
Alexander

Reputation: 109526

Let's say your pivot table is named pt.

# Months are off by one because of Python's zero based indexing.
months = {'Jan': 0,
          'Feb': 1,
          'Mar': 2,
          'Apr': 3,
          'May': 4,
          'Jun': 5,
          'Jul': 6,
          'Aug': 7,
          'Sep': 8,
          'Oct': 9,
          'Nov': 10,
          'Dec': 11}

# Get the index value of the first month from your list.
month = months[date_list[0][:3]]

# Now concatenate the results and create you column names.
pt_new = pd.concat([pt.iloc[:, month:], pt.iloc[:, :month]], axis=1)
pt_new.columns = date_list

>>> pt_new
            Aug 2014  Sep 2014  Oct 2014  Nov 2014  Dec 2014  Jan 2015   
Customer 1        10        36        21        18        15        17   
Customer 2        17        29        31        17        14         4   
Customer 3         0        21         9        12        17        10   
Customer 4         8        11        11        18        14         6   
Customer 5        10        11         7        10        11        13   

            Feb 2015  Mar 2015  Apr 2015  May 2015  Jun 2015  Jul 2015  
Customer 1        30        29        39        15        27        15  
Customer 2        11        13        22        34        27        32  
Customer 3         7        23        21         7        15        25  
Customer 4        10        11         8         3         4         3  
Customer 5         7         6        12         6         8         2

Upvotes: 1

Jianxun Li
Jianxun Li

Reputation: 24742

Suppose df is your pivot table.

df

              1   2   3   4   5 ...   8   9  10  11  12
CompanyName                     ...                    
Customer1    17  30  29  39  15 ...  10  36  21  18  15
Customer2     4  11  13  22  34 ...  17  29  31  17  14
Customer3    10   7  23  21   7 ...   0  21   9  12  17
Customer4     6  10  11   8   3 ...   8  11  11  18  14
Customer5    13   7   6  12   6 ...  10  11   7  10  11

You can use pd.concat to do the trick.

pd.concat([df.iloc[:,6:], df.iloc[:,:6]], axis=1)

              7   8   9  10  11 ...   2   3   4   5   6
CompanyName                     ...                    
Customer1    15  10  36  21  18 ...  30  29  39  15  27
Customer2    32  17  29  31  17 ...  11  13  22  34  27
Customer3    25   0  21   9  12 ...   7  23  21   7  15
Customer4     3   8  11  11  18 ...  10  11   8   3   4
Customer5     2  10  11   7  10 ...   7   6  12   6   8

[5 rows x 12 columns]

Upvotes: 1

Related Questions