pandas 从长到宽重塑，通过两个变量 [英] Pandas long to wide reshape, by two variables

问题描述

我有长格式的数据，我正在尝试将数据重塑为宽格式，但似乎没有使用melt/stack/unstack 的直接方法来做到这一点:

Salesman Height 产品价格克努特 6 蝙蝠 5克努特 6 球 1克努特 6 魔杖 3史蒂夫 5 笔 2

变成:

Salesman Height product_1 price_1 product_2 price_2 product_3 price_3克努特 6 球棒 5 球 1 魔杖 3史蒂夫 5 笔 2 NA NA NA NA

我认为 Stata 可以用 reshape 命令做这样的事情.

解决方案

一个简单的枢轴可能足以满足您的需求，但我这样做是为了重现您想要的输出:

df['idx'] = df.groupby('销售员').cumcount()

只需添加组内计数器/索引即可完成大部分工作，但列标签不会如您所愿:

print df.pivot(index='Salesman',columns='idx')[['product','price']]产品价格idx 0 1 2 0 1 2推销员Knut 球棒 5 1 3史蒂夫笔 NaN NaN 2 NaN NaN

为了更接近您想要的输出，我添加了以下内容:

df['prod_idx'] = 'product_' + df.idx.astype(str)df['prc_idx'] = 'price_' + df.idx.astype(str)product = df.pivot(index='Salesman',columns='prod_idx',values='product')prc = df.pivot(index='Salesman',columns='prc_idx',values='price')reshape = pd.concat([product,prc],axis=1)reshape['Height'] = df.set_index('Salesman')['Height'].drop_duplicates()打印重塑product_0 product_1 product_2 price_0 price_1 price_2 高度推销员Knut 球棒 5 1 3 6史蒂夫笔 NaN NaN 2 NaN NaN 5

如果您想将过程推广到更多变量，我认为您可以执行以下操作(尽管它可能不够高效):

df['idx'] = df.groupby('销售员').cumcount()tmp = []对于 ['product','price'] 中的 var:df['tmp_idx'] = var + '_' + df.idx.astype(str)tmp.append(df.pivot(index='Salesman',columns='tmp_idx',values=var))reshape = pd.concat(tmp,axis=1)

<块引用>

@Luke 说:

我认为 Stata 可以用 reshape 命令做这样的事情.

您可以，但我认为您还需要一个组内计数器来在 stata 中进行重塑以获得所需的输出:

 +-------------------------------------------+|业务员idx身高产品价格||-------------------------------------------|1. |克努特 0 6 蝙蝠 5 |2. |Knut 1 6 球 1 |3. |克努特 2 6 魔杖 3 |4. |史蒂夫 0 5 笔 2 |+--------------------------------------------+

如果你添加 idx 那么你可以在 stata 中做 reshape:

重塑宽产品价格，i(salesman) j(idx)

I have data in long format and am trying to reshape to wide, but there doesn't seem to be a straightforward way to do this using melt/stack/unstack:

Salesman  Height   product      price
  Knut      6        bat          5
  Knut      6        ball         1
  Knut      6        wand         3
  Steve     5        pen          2

Becomes:

Salesman  Height    product_1  price_1  product_2 price_2 product_3 price_3  
  Knut      6        bat          5       ball      1        wand      3
  Steve     5        pen          2        NA       NA        NA       NA

I think Stata can do something like this with the reshape command.

解决方案

A simple pivot might be sufficient for your needs but this is what I did to reproduce your desired output:

df['idx'] = df.groupby('Salesman').cumcount()

Just adding a within group counter/index will get you most of the way there but the column labels will not be as you desired:

print df.pivot(index='Salesman',columns='idx')[['product','price']]

        product              price        
idx            0     1     2      0   1   2
Salesman                                   
Knut         bat  ball  wand      5   1   3
Steve        pen   NaN   NaN      2 NaN NaN

To get closer to your desired output I added the following:

df['prod_idx'] = 'product_' + df.idx.astype(str)
df['prc_idx'] = 'price_' + df.idx.astype(str)

product = df.pivot(index='Salesman',columns='prod_idx',values='product')
prc = df.pivot(index='Salesman',columns='prc_idx',values='price')

reshape = pd.concat([product,prc],axis=1)
reshape['Height'] = df.set_index('Salesman')['Height'].drop_duplicates()
print reshape

         product_0 product_1 product_2  price_0  price_1  price_2  Height
Salesman                                                                 
Knut           bat      ball      wand        5        1        3       6
Steve          pen       NaN       NaN        2      NaN      NaN       5

Edit: if you want to generalize the procedure to more variables I think you could do something like the following (although it might not be efficient enough):

df['idx'] = df.groupby('Salesman').cumcount()

tmp = []
for var in ['product','price']:
    df['tmp_idx'] = var + '_' + df.idx.astype(str)
    tmp.append(df.pivot(index='Salesman',columns='tmp_idx',values=var))

reshape = pd.concat(tmp,axis=1)

@Luke said:

I think Stata can do something like this with the reshape command.

You can but I think you also need a within group counter to get the reshape in stata to get your desired output:

     +-------------------------------------------+
     | salesman   idx   height   product   price |
     |-------------------------------------------|
  1. |     Knut     0        6       bat       5 |
  2. |     Knut     1        6      ball       1 |
  3. |     Knut     2        6      wand       3 |
  4. |    Steve     0        5       pen       2 |
     +-------------------------------------------+

If you add idx then you could do reshape in stata:

reshape wide product price, i(salesman) j(idx)

这篇关于 pandas 从长到宽重塑，通过两个变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！