How to groupby column, and then create a scatterplot of counts

Question

I have a dataframe similar to the one below:

    id      date       available    
0   1944    2019-07-11  f               
1   1944    2019-07-11  t           
2   159454  2019-07-12  f           
3   159454  2019-07-13  f         
4   159454  2019-07-14  f          

I would like form a scatter plot where each id has a corresponding point; the x value is the number of t occurrences, and the y value is the number of f occurrences in the available column.

I have tried:

grouped = df.groupby(['listing_id'])['available'].value_counts().to_frame()
grouped.head()

This gives me something like

                        available
listing_id  available   
1944        t            364
            f            1 
2015        f            184
            t            181
3176        t            279
            f            10

But I'm not sure how to work this anymore. How can I get my desired plot? Is there a better way to proceed?

Answer 1

Using your data:

• reset the index

df.reset_index(inplace=True)

   id available  count
 1944         t    364
 1944         f      1
 2015         f    184
 2015         t    181
 3176         t    279
 3176         f     10

• create a t & f dataframe:

t = df[df.available == 't'].reset_index(drop=True)

     id available  count
0  1944         t    364
1  2015         t    181
2  3176         t    279

f = df[df.available == 'f'].reset_index(drop=True)

     id available  count
0  1944         f      1
1  2015         f    184
2  3176         f     10

Plot the data:

plt.scatter(x=t['count'], y=f['count'])
plt.xlabel('t')
plt.ylabel('f')
for i, txt in enumerate(f['id'].tolist()):
    plt.annotate(txt, (t['count'].loc[i] + 3, f['count'].loc[i]))

Answer 2

You can group by both listing_id and available, do a count and then unstack and then plot using seaborn.

Below I used some random numbers, the image is only for illustration.

import seaborn as sns

data = df.groupby(['listing_id', 'available'])['date'].count().unstack()

sns.scatterplot(x=data.t, y=data.f, hue=data.index, legend='full')

Answer 3

Assuming you won't have to use the date column:

# Generate example data
N = 100
np.random.seed(1)
df = pd.DataFrame({'id': np.random.choice(list(range(1, 6)), size=N),
                   'available': np.random.choice(['t', 'f'], size=N)})
df = df.sort_values('id').reset_index(drop=True)

# For each id: get t and f counts, unstack into columns, ensure 
# column order is ['t', 'f']
counts = df.groupby(['id', 'available']).size().unstack()[['t', 'f']]

# Plot
fig, ax = plt.subplots()
counts.plot(x='t', y='f', kind='scatter', ax=ax)

# Optional: label each data point with its id. 
# This is rough and might not look good beyond a few data points
for label, (t, f) in counts.iterrows():
    ax.text(t + .05, f + .05, label)

Output: