Essi Shams Essi Shams - 18 days ago 7
Python Question

Summing list element in nested dataframe vertically

I have a dataframe called dailyHistogram defined as follows:

dailyHistogram = pd.DataFrame(
{
'NumVisits':[[0 for x in range(1440)] for y in range (180)],
'DoW': [0]*ReportingDateRange.size
},
columns=['NumVisits','DoF'],
index=ReportingDateRange
)


Where NumVisits is a two-dimensional array (1440 by 180) and holds a histogram of some activity in 180 days. DoW is simply a column which holds the day of the week. The index in this dataframe is the dates on which the activities occurred.

My problem is in performing any operations on dailyHistogram["NumVisits"].

Here's what dailyHistogram["NumVisits"] looks like:


dailyHistogram["NumVisits"]

Out[193]:

2016-01-01 [5, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...

2016-01-02 [2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...

2016-01-03 [6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...

2016-01-04 [8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...

2016-06-26 [3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...

2016-06-27 [4, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, ...

2016-06-28 [7, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, ...

2016-06-29 [7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...

2016-06-30 [4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...

Freq: D, Name: NumVisits, dtype: object


I'd like to sum up each element of dailyHistogram["NumVisits"] vertically to arrive at one list with 1440 members.

Answer

Try this:

In [84]: dailyHistogram
Out[84]:
                        NumVisits  DoF
0  [1, 0, 1, 1, 1, 1, 0, 1, 1, 1]  NaN
1  [1, 1, 0, 1, 0, 1, 0, 0, 1, 0]  NaN
2  [1, 0, 0, 0, 0, 1, 1, 0, 1, 0]  NaN
3  [0, 1, 0, 0, 0, 0, 0, 0, 1, 1]  NaN
4  [1, 1, 0, 0, 1, 1, 1, 1, 1, 0]  NaN

In [85]: dailyHistogram.NumVisits.apply(pd.Series).sum().tolist()
Out[85]: [4, 3, 1, 2, 2, 4, 2, 2, 5, 2]

Setup:

dailyHistogram = pd.DataFrame({'NumVisits':[[np.random.choice([0,1]) for x in range(10)]
                                               for y in range (5)],
                                'DoW': [0]*5}
                              ,columns=['NumVisits','DoF'])
Comments