python - 用 Pandas 构建数据框

Question

我正在读取一个 Excel 文件，其中产品和其他标签(每天、每月的产量等)在同一列中。我想创建一个新列并将产品名称放在与该产品相关的每一行中。有人可以支持吗？提前致谢! :)

情况如何:

8HP70 
Production/Day
Production/Month
Cum.Production
8HP70X 
Production/Day
Production/Month
Cum.Production
8HP75 
Production/Day
Production/Month
Cum.Production

**how I expect:**

Column A | Column B

8HP70 | Production/Day
8HP70 | Production/Month
8HP70 | Cum.Production
8HP70X | Production/Day
8HP70X | Production/Month
8HP70X | Cum.Production
8HP75 | Production/Day
8HP75 | Production/Month
8HP75 | Cum.Production

Answer 1

一个如何处理的例子:

import pandas as pd
l = [
    ['8HP70'],
    ['Production/Day'],
    ['Production/Month'],
    ['Cum.Production'],
    ['8HP70X'],
    ['Production/Day'],
    ['Production/Month'],
    ['Cum.Production'],
    ['8HP75'],
    ['Production/Day'],
    ['Production/Month'],
    ['Cum.Production'],
]

df = pd.DataFrame(l, columns=['Column B'])

## repeating product label for every 4 rows
products = df[df['Column B'].index % 4 == 0]

## replicating to a new column
df['Column A'] = products.values.repeat(4)

## removing the product duplication
df = df[df['Column A']!=df['Column B']]

Out[3]: 
            Column B Column A
1     Production/Day    8HP70
2   Production/Month    8HP70
3     Cum.Production    8HP70
5     Production/Day   8HP70X
6   Production/Month   8HP70X
7     Cum.Production   8HP70X
9     Production/Day    8HP75
10  Production/Month    8HP75
11    Cum.Production    8HP75

编辑

根据进一步的要求添加了更多逻辑。如果在第一个产品标签之前和一直到第一个产品标签之前有嘈杂的行，我们可以删除、执行我们的逻辑并重新附加(假设我们知道第一个产品标签):

df = pd.DataFrame(l, columns=['Column B'])


## Identify product starting location
prod_label = '8HP70'

## Get index of where first prod appear
prod_indic = df[df['Column B'] == prod_label].index[0]

## create a temp df only with product info
only_prod_df = df[df.index>=prod_indic].reset_index(drop=True)
products = only_prod_df[only_prod_df['Column B'].index % 4 == 0]

## replicating to a new column
only_prod_df['Column A'] = products.values.repeat(4)

## removing the product duplication
only_prod_df = only_prod_df[only_prod_df['Column A']!=only_prod_df['Column B']]

## append back to noisy rows
final_df = pd.concat([df[df.index<prod_indic], only_prod_df], 
                                  axis=0, sort=False, ignore_index=True)

            Column B Column A
0              noise      NaN
1              noise      NaN
2              noise      NaN
3     Production/Day    8HP70
4   Production/Month    8HP70
5     Cum.Production    8HP70
6     Production/Day   8HP70X
7   Production/Month   8HP70X
8     Cum.Production   8HP70X
9     Production/Day    8HP75
10  Production/Month    8HP75
11    Cum.Production    8HP75

同样重要的是要注意这篇文章依赖于顺序数字索引。