Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>There should be many ways to solve your problem. Here's mine, might not be the best one:</p> <h2>First, get the bookIds of reader1</h2> <pre><code>In [99]: bookIds = df[df['readerId']==1]['bookId'].values In [100]: bookIds Out[100]: array([1, 2, 3, 4]) </code></pre> <p>If yor are familiar with SQL, you can think it as: <code>SELECT bookId FROM df WHERE readerId == 1</code></p> <h2>Second, select those readerId == 1 and bookId is in the above <code>bookIds</code> array</h2> <pre><code>In [101]: df2 = df[df['readerId'] != 1 &amp; df['bookId'].isin(bookIds)] In [102]: df2 Out[102]: bookId readerId 1 1 2 3 2 3 5 3 3 6 3 4 8 4 3 9 4 4 10 4 5 11 4 6 </code></pre> <p>SQL: <code>SELECT bookId, readerId FROM df WHERE readerId != 1 and bookId in bookIds</code></p> <h2>Last, Group it by readerId and count them</h2> <pre><code>In [103]: df2.groupby('readerId').size() Out[103]: readerId 2 1 3 3 4 2 5 1 6 1 dtype: int64 </code></pre> <p>SQL: <code>SELECT COUNT(bookId) FROM df2 GROUP BY readerId</code></p> <p>Hope it might help you to learn pandas easier</p> <h1>[EDIT], To answer what you ask in the comment:</h1> <h2>Create another dataframe (only change the column)</h2> <pre><code>In [114]: df2 = df.rename(columns={'readerId': 'otherReaderId'}) </code></pre> <h2>Join them by <code>bookId</code>, and then group them by <code>readerId</code> and <code>otherReaderId</code></h2> <pre><code>In [115]: pd.merge(df, df2, on='bookId').groupby(['readerId', 'otherReaderId']).size() Out[115]: readerId otherReaderId 1 1 4 2 1 3 3 4 2 5 1 6 1 2 1 1 2 1 3 1 3 3 3 4 2 5 1 6 1 4 1 2 3 2 4 2 5 1 6 1 5 1 1 3 1 4 1 5 1 6 1 6 1 1 3 1 4 1 5 1 6 1 dtype: int64 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POpandas: counting all intersections between pair of keys
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USwaitingkuo
    1. USwaitingkuo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COGreat! Thanks for the quick answer. Is it possible to use the groupby method to create a new dataframe containing all readers and the count of the shared readers? or do I need to do it iteratively?
      singulars
      1. PO
      1. USak3nat0n
    2. COYou, you can join the dataframe to itself, and then some groupby tricks might achieve your goal.
      singulars
      1. PO
      1. USwaitingkuo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload