python - 将文本解析到它自己的字段中，计数并将选择字段 reshape 为宽格式

Question

我正在使用 Python 进行分析，通过计算交互次数和阅读消息来了解我们在社交媒体 channel 中保持对话多长时间。

我认为方法是让第一个表看起来像第二个表。到达那里的步骤:

1. 将我们的@username 解析到它自己的字段中。 99% 的时间，我们的回复以@username 开头
2. 计算@username 出现的次数 - 这表示我们向用户发送的消息数
3. 将入站和出站消息从长格式转换为宽格式。不确定我必须创建多少个字段，但只要我知道该技术，我以后就可以担心字段的数量。

现在时间戳对我来说不是那么重要，但它可能在未来。无论如何，我会使用与入站和出站消息相同的技术

餐 table 前

    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

餐 table 后

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     nulluser3   1         Twitter   What is a pie       @user3 it is a food  null           null                  null     null 

Answer 1

注意
您需要 groupby user 和 df.Account 因为同一用户可能有来自不同帐户的消息。

# 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<User>@\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)

pd.concat([messages, df1], axis=1).reset_index()

---

分解辅助函数

# 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])