Parsing text into it's own field, counting, and reshaping select fields to wide format
<p>I'm doing an analysis using Python to see how long we are keeping conversations in our social media channels by counting the number of interactions and reading the messages.</p>

<p>I'm thinking the approach would be to make the first table look like the second table.  Steps to get there:</p>

<ol>
<li>Parse our the @username into it's own field.  99% of the time, our responses start with @username</li>
<li>Count the number of times the @username shows up - this indicates the number of messages we send the user</li>
<li>Turn the inbound and outbound messages from long to wide format.  Not certain how many fields I'd have to create but as long as I know the technique, I can worry about the # of fields later.</li>
</ol>

<p>Right now timestamp isn't that important to me but it could be in the future.  Regardless, I would use the same technique as the inbound and outbound messages</p>

<p>Before table</p>

<pre>
    Inbound Message      Outbound message     Inbound Time     Outbound Time     Account
    Hello, how are you   @userA I'm good!     mm/dd/yy hh:mm   mm/dd/yy hh:mm    FB
    Where is the cat?    @userB what cat?     mm/dd/yy hh:mm   mm/dd/yy hh:mm    Twitter
    What is a pie        @user3 it is a food  mm/dd/yy hh:mm   mm/dd/yy hh:mm    Twitter
    The black cat        @userB I don't know  mm/dd/yy hh:mm   mm/dd/yy hh:mm    Twitter
</pre>

<p>After table</p>

<pre>
User    Messages  Account   Inbound 1           Outbound 1           Inbound 2      Outbound 2           Inbound 3 Outbound 3 
userA   1         FB        Hello, how are you  @user1 I'm good!     null           null                  null     null 
userB   2         Twitter   Where is the cat?   @user2 what cat?     The black cat  @user2 I don't know   null     null
user3   1         Twitter   What is a pie       @user3 it is a food  null           null                  null     null 

</pre>

<p><strong><em>Note</em></strong><br>
You need to <code>groupby</code> <code>user</code> and <code>df.Account</code> because it is possible to have messages from different accounts for the same user.</p>

<pre><code># helper function to flatten multiindex objects
def flatten_multiindex(midx, sep=' '):
    n = midx.nlevels
    tups = zip(*[midx.get_level_values(i).astype(str) for i in range(n)])
    return pd.Index([sep.join(tup) for tup in tups])

in_out = ['Inbound Message', 'Outbound message']

# this gets a fresh ordering for each group
handler = lambda df: df.reset_index(drop=True)

# Use regular expression to extract user
user = df['Outbound message'].str.extract(r'(?P&lt;User&gt;@\w+)', expand=False)

df1 = df.groupby([user, df.Account])[in_out].apply(handler) \
        .unstack().sort_index(1, 1)
df1.columns = flatten_multiindex(df1.columns)

# I separated getting group sizes from long to wide pivot
messages = df.groupby([user, df.Account]).size().to_frame('Messages')

pd.concat([messages, df1], axis=1)
</code></pre>

<p><a href="http://i.stack.imgur.com/e0S1P.png" rel="nofollow"><img src="http://i.stack.imgur.com/e0S1P.png" alt="enter image description here"></a></p>

<pre><code>pd.concat([messages, df1], axis=1).reset_index()
</code></pre>

<p><a href="http://i.stack.imgur.com/YIETr.png" rel="nofollow"><img src="http://i.stack.imgur.com/YIETr.png" alt="enter image description here"></a></p>

<hr>

<p>Breaking down the helper function</p>

<pre><code># helper function to flatten multiindex objects
def flatten_multiindex(midx, sep=' '):

    # get the number of levels in the index
    n = midx.nlevels

    # for each level in the index, get the values and
    # convert it to strings so I can later ' '.join on it
    #
    # zip forms the tuples so I can pass each result to ' '.join
    tups = zip(*[midx.get_level_values(i).astype(str) for i in range(n)])
    # do the ' '.join and return as an index object
    return pd.Index([sep.join(tup) for tup in tups])
</code></pre>

python pandas dataframe aggregate reshape