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>You could use the <code>df.drop_duplicates()</code> which will return the DataFrame with only unique values, then count the entries.</p>

<p>Something like this should work:</p>

<pre><code>import pandas as pd
df = pd.read_csv('inmates_062016.csv', index_col=0, parse_dates=True)

uniqueDF = df.drop_duplicates()
countUniques = len(uniqueDF.index)
print(countUniques)
</code></pre>

<p>Result:</p>

<pre><code>&gt;&gt; 11845
</code></pre>

<p><a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop_duplicates.html" rel="nofollow">Pandas drop_duplicates Documentation</a></p>

<p><a href="https://data.wprdc.org/datastore/dump/869ca391-7508-4a64-8a27-09cc4799923f" rel="nofollow">Inmates June 2016 CSV</a></p>

<p>The problem with this approach / data is that there could be many individual inmates that are the same age / gender / race that would be filtered out.</p>

python pandas dataframe grouping data-cleaning