CODEWITHSUNDEEP
pythondatetimepandas
fotxoa
fotxoa

Reputation: 5

Pivoting Tables with Pandas

I'm trying to pivot a table with one row to a table with 12 columns and 150 years using pandas. Basically, the year goes and the Y-Axis and months will go on the X-Axis.

My df is returning the following:

DATAFRAME RESULTS

How do I use the dates in the first column to create the table with the year on the x-axis and 12 months on the y-axis? 

for county in CntyList:
    for model in models:
        for num in range(0, 33):
            #Outfile = r'E:\\ClimateChange\\Tables\\Counties\\' + str(county) + r'_' + str(folder) + r'_' + str(model) + r'.csv'
            rows = concatDF.ix[num]
            print(rows)

Upvotes: 0

Views: 235

Answers (1)

Ted Petrou
Ted Petrou

Reputation: 61947

I am not exactly sure what we are asking since its important to post a minimal example of raw data (not an image) as well as a sample of the output you are looking for.

Regardless of this, I have attempted to first recreate some fake data for you and then pivot it in such a manner as you have described.

First create fake data

I am assuming you have 1800 rows of raw data - 1 month for each of 150 years beginning from 1950.

months = np.tile(np.arange(1,13), 150) * 10000
years = np.repeat(np.arange(1950, 2100), 12)
idx = months + years
df = pd.DataFrame(index=idx, data={'num' :np.random.rand(len(idx))})

Output of raw data

Here is the head of the dataframe - the first 5 months of 1950

            num
11950  0.324358
21950  0.577816
31950  0.133126
41950  0.707563
51950  0.667286

And the tail - the last 5 months of 2099

             num
82099   0.103834
92099   0.920796
102099  0.302548
112099  0.298861
122099  0.958643

Create datetime from index

Now we can turn the index into a pandas timestamp with the to_datetime function

date = pd.to_datetime(idx, format='%m%Y')

Turn the year and month into dataframe columns

df['Year'] = date.year
df['Month'] = date.month

Finally, pivot the data to get a table with 150 rows and 12 columns

df.pivot(index='Year', columns='Month')

With output

            num                                                              \
Month        1         2         3         4         5         6         7    
Year                                                                          
1950   0.324358  0.577816  0.133126  0.707563  0.667286  0.214770  0.833923   
1951   0.727718  0.818254  0.132464  0.124236  0.074853  0.183405  0.387825   
1952   0.156100  0.968507  0.588337  0.410274  0.811571  0.790409  0.554290   
1953   0.313295  0.366085  0.442786  0.834929  0.565413  0.215566  0.395442   
1954   0.185577  0.498335  0.726637  0.209410  0.426887  0.487188  0.202640   


Month        8         9         10        11        12  
Year                                                     
1950   0.646929  0.622495  0.417010  0.718361  0.752805  
1951   0.531334  0.969626  0.556064  0.114697  0.212898  
1952   0.451180  0.488284  0.344732  0.054810  0.276036  
1953   0.338134  0.456241  0.647255  0.966014  0.865256  
1954   0.966250  0.870074  0.853948  0.411874  0.322245

For you to implement

Convert your index to a datetime with to_datetime. Add columns year and month to your dataframe and then pivot it. Let me know in the comments if this is what you want.

Upvotes: 1

Related Questions