时间戳

首页 > 解决方案 > 用 pandas 构建数据框

python - 用 pandas 构建数据框

问题描述

我正在阅读一个 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

标签： pythonpandasnumpy

解决方案

如何处理的一个例子：

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

同样重要的是要注意这部分依赖于顺序数字索引。

推荐阅读