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>Your transformation process should include several steps, I will give an idea only to one of them.</p>

<p>Concerning user extraction:
First you should apply <code>re.sub(pattern, repl, string)</code> function from the <strong>re</strong> package to the raws in the Outbound message column (in a loop). The <code>sub</code> function works together with the <code>compile</code> function, where the regular expression is specified:</p>

<pre><code>import re
# x - your string
# here you write your regular expression: a whitespace between a number and a word character (not syntax characters).
y = re.compile(r"([0-9])(\s)(\w)")
# here you replace the second capture group from the regex the whitespace with a _ symbol
y.sub('_\2',x)
</code></pre>

<p>, where you replace a whitespace with a <code>"_"</code>. In the next step you can split the cells on the <code>"_"</code> character:</p>

<pre><code>import re
# x - your data
re.split('_', x) 
</code></pre>

<p>Further examples for both functions you can find <a href="http://stackoverflow.com/questions/6711567/how-to-use-python-regex-to-replace-using-captured-group">here</a> and <a href="http://stackoverflow.com/questions/10974932/python-split-string-based-on-regular-expression">here</a>.</p>

<p><strong>EDIT:</strong></p>

<p>Since your user id doesn't always have numbers, you should apply another logic at extracting it from the Outbound message column. You can extract the first word from the string either with: <code>x.split(' ', 1)[0]</code>, where x - your string, to get the first word of the strin, or with a regex: <code>^\S*</code></p>

python pandas dataframe aggregate reshape