Pandas Python: Merging every two rows in one dataframe

Question

How do I get from

Idx            A B C
2004-04-01     1 1 0
2004-04-02     1 1 0
2004-05-01     0 0 0
2004-05-02     0 0 0

to

Idx            A B C
2004-04        2 2 0
2004-05        0 0 0

Notes: How do I collapse both the index (more specifically, making the index convert into just the month) and every two rows?

Is using a rolling mean the best way?

UPDATE - I made the above version simple, but unutbu's answer does not seem to work

                       Time      A   B
1    2004-01-04 - 2004-01-10     0   0
2    2004-01-11 - 2004-01-17     0   0
3    2004-01-18 - 2004-01-24     0   0
4    2004-01-25 - 2004-01-31     0   0
5    2004-02-01 - 2004-02-07     56  0
6    2004-02-08 - 2004-02-14     67  0

Answer 1

You can aggregate rows using a groupby/sum operation:

import pandas as pd
import numpy as np

df = pd.DataFrame([('2004-04-01', 1L, 1L, 0L), ('2004-04-02', 1L, 1L, 0L),
       ('2004-05-01', 0L, 0L, 0L), ('2004-05-02', 0L, 0L, 0L)],
                  columns=['Idx', 'A', 'B', 'C'])
df['Idx'] = pd.DatetimeIndex(df['Idx'])

You could group by the year and month:

print(df.groupby([d.strftime('%Y-%m') for d in df['Idx']]).sum())
#          A  B  C
# 2004-04  2  2  0
# 2004-05  0  0  0

# [2 rows x 3 columns]

Or, group by every two rows:

result = df.groupby(np.arange(len(df))//2).sum()
result.index = df.loc[1::2, 'Idx']
print(result)
#             A  B  C
# Idx                
# 2004-04-02  2  2  0
# 2004-05-02  0  0  0

# [2 rows x 3 columns]

Note: df.loc[1::2, 'Idx'] was used, instead of df.loc[::2, 'Idx'] so the Idx for the aggregated rows would correspond to the second date, not the first, in each group.

If you want just the year and month, then you could use this list comprehension to set the index:

result.index = [d.strftime('%Y-%m') for d in df.loc[1::2, 'Idx']]
print(result)
#          A  B  C
# 2004-04  2  2  0
# 2004-05  0  0  0

# [2 rows x 3 columns]

However, it's more powerful to have a DatetimeIndex for the index rather than a string-valued index when dealing with dates. So you might want to retain the DatetimeIndex, do most of your work with the DatetimeIndex, and just convert to a year-month string at the end for presentation purposes...

---

Regarding the UPDATED question:

import pandas as pd
import numpy as np

data = np.rec.array([('2004-01-04 - 2004-01-10', 0L, 0L),
       ('2004-01-11 - 2004-01-17', 0L, 0L),
       ('2004-01-18 - 2004-01-24', 0L, 0L),
       ('2004-01-25 - 2004-01-31', 0L, 0L),
       ('2004-02-01 - 2004-02-07', 56L, 0L),
       ('2004-02-08 - 2004-02-14', 67L, 0L)], 
      dtype=[('Time', 'O'), ('A', '<i8'), ('B', '<i8')])
df = pd.DataFrame(data)

Having one Time column holding two dates makes data manipulation more difficult. It would be better to have two DatetimeIndex columns, Start and End:

df[['Start', 'End']] = df['Time'].str.extract('(?P<Start>.+) - (?P<End>.+)')
del df['Time']
df['Start'] = pd.DatetimeIndex(df['Start'])
df['End'] = pd.DatetimeIndex(df['End'])

Then you could group by the Start column:

print(df.groupby([d.strftime('%Y-%m') for d in df['Start']]).sum())
#            A  B
# 2004-01    0  0
# 2004-02  123  0

# [2 rows x 2 columns]

Or group by every two rows, essentially the same as before:

result = df.groupby(np.arange(len(df))//2).sum()
result.index = df.loc[1::2, 'Start']
print(result)
#               A  B
# Start             
# 2004-01-11    0  0
# 2004-01-25    0  0
# 2004-02-08  123  0

# [3 rows x 2 columns]