如何使用python复制Excel中我的搜索模式的右侧单元格值（可选）

Question

我想在Excel中复制列的右侧单元格值。它不包含标题

我使用的是python3.6，Pandas模块

我的输入文件是这样的

Name    Hierarchy   Module                                   Values
Name1   top         top                                        0
Name11               M1        m11                             1
Name11               M1        m12                             2
Name12                         M2       m21                    3
Name12                         M2       m22                    4
Name13                                   M3          m31       5
Name13                                   M3          m32       6
Name13                                   M5          m33       7
Name13                                   M6          m34       8
Name2   top2            top2                                   9
Name21                   M1     m11                           10

我想复制M*(M1, M1, M2,M2, M1 )和m* (m11, m12, m21, m22m m31, m32, m33, m34)

预期产量：

Name    Hierarchy   Module  Next_line_module    Values
Name1   top         top                           0
Name11              M1       m11                  1
Name11              M1       m12                  2
Name12              M2       m21                  3
Name12              M2       m22                  4
Name13              M3       m31                  5
Name13              M4       m32                  6
Name13              M5       m33                  7
Name13              M6       m34                  8
Name2    top2           top2                      9
Name21              M1     m11                    10

你能帮帮我吗

Answer 1

您可以使用pd.read_fwf尝试此操作：

from io import StringIO

txtfile = StringIO("""Name    Hierarchy   Module                                   Values
Name1   top         top                                        0
Name11               M1        m11                             1
Name11               M1        m12                             2
Name12                         M2       m21                    3
Name12                         M2       m22                    4
Name13                                   M3          m31       5
Name13                                   M3          m32       6
Name13                                   M5          m33       7
Name13                                   M6          m34       8
Name2   top2            top2                                   9
Name21                   M1     m11                           10""")

df = pd.read_fwf(txtfile, [(0,7),(8,19),(20,60),(61,66)])

df[['Module','Next_line_module']] = df['Module'].str.split('\s+', expand=True)

df_out = df.reindex(['Name','Hierarchy','Module','Next_line_module','Value'], axis=1)
print(df_out)

输出：

      Name Hierarchy Module Next_line_module  Value
0    Name1       top    top             None      0
1   Name11       NaN     M1              m11      1
2   Name11       NaN     M1              m12      2
3   Name12       NaN     M2              m21      3
4   Name12       NaN     M2              m22      4
5   Name13       NaN     M3              m31      5
6   Name13       NaN     M3              m32      6
7   Name13       NaN     M5              m33      7
8   Name13       NaN     M6              m34      8
9    Name2      top2   top2             None      9
10  Name21       NaN     M1              m11     10

Answer 2

如果您最初没有在答案中打印的字符串格式的数据，但在数据框中，您可以尝试：

# this line is just to show the column names I used
# you don't have to rename your module columns as long as they
# are in lexical order (here 'Module1' < 'Module2')
df.columns=['Name', 'Hierarchy', 'Module1', 'Module2', 'Module3', 'Module4', 'Value']

# then I assign an aux column as the row id
# if you have a regular index (single column), 
# you can instead also use df['old_id']=  df.index
df['old_id']= range(len(df))

# now transform all of the module "cells" into one row each using .melt
melted= df[['Name', 'Value', 'old_id', 'Module1', 'Module2', 'Module3', 'Module4']].melt(id_vars=['Name', 'Value', 'old_id'], value_vars=['Module1', 'Module2', 'Module3', 'Module4']).sort_values(['Name', 'variable'])
# filter the result and make sure it is sorted properly
melted=pd.DataFrame(melted[~melted['value'].isna()], copy=True).sort_values(['Name', 'variable'])
melted['field_group']=melted.groupby('old_id').cumcount()

# then transform it back to the shape, we want
# by creating an index based on the old row-id and the field_group
# which just contains 0 for the first filled module field in the row with the same row-id and 1 for the second
melted.set_index(['old_id', 'field_group'], inplace=True)
unstacked= melted['value'].unstack(-1)

# assign the reshaped module columns back to the original dataframe
df[['Module', 'Next_line_module']]= unstacked
df[['Name', 'Hierarchy', 'Module', 'Next_line_module', 'Value']].fillna('')

其输出为：

      Name Hierarchy Module Next_line_module  Value
0    Name1       top    top                       0
1   Name11               M1              m11      1
2   Name11               M1              m12      2
3   Name12               M2              m21      3
4   Name12               M2              m22      4
5   Name13               M3              m31      5
6   Name13               M3              m32      6
7   Name13               M5              m33      7
8   Name13               M6              m34      8
9    Name2      top2   top2                       9
10  Name21               M1              m11     10