Ronak Shah Ronak Shah - 4 months ago 12
Python Question

Sum alternate columns of a dataframe

I have a dataframe which looks like

A 00:00 00:30 01:00 01:30 02:00 .....22:30 23:00 23:30
1 2 3 3 4 3 1 6 4
2 5 6 2 6 5 2 1 2


I am trying to get,

A 00:00 01:00 02:00 ..... 23:00
1 6 6 7 7
2 7 8 11 3


The column
23:30
gets added in
00:00
.

I have tried using
numpy.sum
and
pandas.DataFrame.sum
which sums all the columns. How can I tell it to sum every alternate column?

Answer

I think this should work:

In [261]:
df = pd.DataFrame(np.random.randn(5,6), columns=['00:00','00:30','01:00','01:30','02:00','02:30'])
df

Out[261]:
      00:00     00:30     01:00     01:30     02:00     02:30
0  0.176952  1.161850  0.894800 -0.246474  1.252235 -0.816835
1  0.817057 -1.338584 -0.983922 -0.073771 -2.188114  1.819888
2 -0.637196 -0.429361  1.267454  0.040461  1.256472 -0.242053
3  0.270544  0.403675  0.890263  1.767279  1.380494 -1.349156
4 -0.752082  0.380903 -0.795439  1.176303  0.176784  0.693317

In [262]:    
rhs = df.ix[:,1::2]
df.ix[:,::2] + pd.concat([rhs.ix[:,-1:],rhs.ix[:,:-1]],axis=1).values

Out[262]:
      00:00     01:00     02:00
0 -0.639884  2.056650  1.005761
1  2.636945 -2.322505 -2.261885
2 -0.879249  0.838093  1.296933
3 -1.078612  1.293938  3.147772
4 -0.058764 -0.414535  1.353087

In [263]:
rhs

Out[263]:
      00:30     01:30     02:30
0  1.161850 -0.246474 -0.816835
1 -1.338584 -0.073771  1.819888
2 -0.429361  0.040461 -0.242053
3  0.403675  1.767279 -1.349156
4  0.380903  1.176303  0.693317

So in your case as you have 30 min intervals in your column names then the resulting df will use the hourly interval column names in the lhs and the values from the 30 min intervals and add these values

So here we use slice with step .ix[:,::2] to return all rows and step the columns when adding we return a numpy array using .values because otherwise you get all NaN values as pandas will attempt to align on the column names and you'll get no matches.

As you want to add 00:00 with 23:30 then we can concat the last column with the remainder of the columns so we get the column alignment when adding the columns

Comments