CODEWITHSUNDEEP
pythonpandasdataframeseries
The Cat
The Cat

Reputation: 2475

DataFrame column data to series

I have the following DataFrame that contains some rainfall data. I want to reformat this so that I get a Series with 24 entries indexed with 'Month-Year' so that it will be easier to plot this as a line.

    JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
Year                                                
2015    96.6    58.9    23.5    18.6    62.9    26.7    60.0    108.6   67.9    58.1    78.0    80.4
2016    131.8   54.2    80.2    51.2    64.1    91.3    17.4    38.8    50.9    29.8    100.6   17.4

I was thinking I could iterate through each row and pull out each month individually and add it to a series. I was wondering if there was a better way.

Upvotes: 0

Views: 82

Answers (1)

Bharath M Shetty
Bharath M Shetty

Reputation: 30605

Use unstack with to_frame i.e 

ndf = df.unstack().to_frame().sort_index(level=1)

             0
    Year       
JAN 2015   96.6
FEB 2015   58.9
MAR 2015   23.5
APR 2015   18.6
MAY 2015   62.9
JUN 2015   26.7
JUL 2015   60.0
AUG 2015  108.6
SEP 2015   67.9
OCT 2015   58.1
NOV 2015   78.0
DEC 2015   80.4
JAN 2016  131.8
FEB 2016   54.2
MAR 2016   80.2
APR 2016   51.2
MAY 2016   64.1
JUN 2016   91.3
JUL 2016   17.4
AUG 2016   38.8
SEP 2016   50.9
OCT 2016   29.8
NOV 2016  100.6
DEC 2016   17.4

If you need index as a string then

ndf.index = ['{} {}'.format(i,j) for i,j in ndf.index.values]

If you need it as a datetime then 

ndf.index = pd.to_datetime(['{} {}'.format(i,j) for i,j in ndf.index.values])

Upvotes: 1

Related Questions