Pandas - Finding Unique Entries in Daily Census Data
<p>I have census data that looks like this for a full month and I want to find out how many unique inmates there were for the month. The information is taken daily so there are multiples.   </p>

<pre><code>  _id,Date,Gender,Race,Age at Booking,Current Age
    1,2016-06-01,M,W,32,33
    2,2016-06-01,M,B,25,27
    3,2016-06-01,M,W,31,33
</code></pre>

<p>My method now is to group them by day and then add the ones that are not accounted for into the DataFrame. My question is how to account for two people with the same info. They would both get not added to the new DataFrame because one of them already exists? I'm trying to figure out how many people total were in the prison during this time. </p>

<p>_id is incremental, for example here is some data from the second day </p>

<pre><code>2323,2016-06-02,M,B,20,21
2324,2016-06-02,M,B,44,45
2325,2016-06-02,M,B,22,22
2326,2016-06-02,M,B,38,39
</code></pre>

<p>link to the dataset here: <a href="https://data.wprdc.org/dataset/allegheny-county-jail-daily-census" rel="nofollow">https://data.wprdc.org/dataset/allegheny-county-jail-daily-census</a></p>

<p>I think the trick here is to groupby as much as possible and check the differences in those (small) groups through the month:</p>

<pre><code>inmates = pd.read_csv('inmates.csv')

# group by everything except _id and count number of entries
grouped = inmates.groupby(
    ['Gender', 'Race', 'Age at Booking', 'Current Age', 'Date']).count()

# pivot the dates out and transpose - this give us the number of each
# combination for each day
grouped = grouped.unstack().T.fillna(0)

# get the difference between each day of the month - the assumption here
# being that a negative number means someone left, 0 means that nothing
# has changed and positive means that someone new has come in. As you
# mentioned yourself, that isn't necessarily true
diffed = grouped.diff()

# replace the first day of the month with the grouped numbers to give
# the number in each group at the start of the month
diffed.iloc[0, :] = grouped.iloc[0, :]

# sum only the positive numbers in each row to count those that have
# arrived but ignore those that have left
diffed['total'] = diffed.apply(lambda x: x[x &gt; 0].sum(), axis=1)

# sum total column
diffed['total'].sum()  # 3393
</code></pre>

python pandas dataframe grouping data-cleaning