如果数字等于

Question

我试图合并两个csv的基础上的条件。csv2上的值'KEYS'必须与CSV1上的'TCNUM'匹配，并将其附加到第三列。csv的是非常大的，它必须通过代码来完成。你知道吗

df1-CSV1：

ID                                       TC_NUM
dialog_testcase_0101.0001_greeting.xml  101.0001
dialog_testcase_0101.0002_greeting.xml  101.0002
dialog_testcase_0101.0003_greeting.xml  101.0003
dialog_testcase_0101.0004_greeting.xml  101.0004
dialog_testcase_0101.0005_greeting.xml  101.0005
dialog_testcase_0101.0006_greeting.xml  101.0006
dialog_testcase_0901.0008_greeting.xml  901.0007
dialog_testcase_0101.0008_greeting.xml  101.0008
dialog_testcase_0501.001_greeting.xml   501.001
dialog_testcase_0801.0011_greeting.xml  801.0011

df2-CSV2型：

KEYS             TC_NUM
FIT-3982    TC 101.0011, 101.0004
FIT-3980    TC 801.0011.901.007
FIT-3979    TC 101.0006, 501.001, 1907.0019, 1907.0020, 1907.0021

我想要的是：

CSV最终：

ID                                       TC_NUM        Keys
dialog_testcase_0101.0001_greeting.xml  101.0011     FIT-3982  
dialog_testcase_0101.0002_greeting.xml  101.0002       
dialog_testcase_0101.0003_greeting.xml  101.0006     FIT_3979
dialog_testcase_0101.0004_greeting.xml  101.0004     FIT-3982
dialog_testcase_0101.0005_greeting.xml  101.0005
dialog_testcase_0101.0006_greeting.xml  101.0011     FIT_3982
dialog_testcase_0901.0008_greeting.xml  901.0007     FIT_3979
dialog_testcase_0101.0008_greeting.xml  101.0008
dialog_testcase_0501.001_greeting.xml   501.001      FIT-3979
dialog_testcase_0801.0011_greeting.xml  801.0011     FIT-3980

我的密码。。你知道吗

mergedOpen = pd.merge(df1, df2, on=['TC_NUM'])
mergedOpen.set_index('TC_NUM', inplace=True)

mergedOpen.to_csv('MergedCSVOPEN.csv')

Answer 1

您可以在^{}之后从列TC_NUM中删除第一个3字符，然后用^{}和^{}为^{}创建新的DataFrame。两列TC_NUM必须设置为等于dtype-string或numeric。我选择numeric，所以我转换列df2.TC_NUM^{}：

df2.set_index('KEYS',inplace=True)

df2 = df2.TC_NUM.str[3:]
                .str.split(', ', expand=True)
                .unstack()
                .reset_index(drop=True, level=0)
                .reset_index(name='TC_NUM')

df2['TC_NUM'] = pd.to_numeric(df2['TC_NUM'])
print (df2)
        KEYS     TC_NUM
0   FIT-3982   101.0011
1   FIT-3980   801.0011
2   FIT-3979   101.0006
3   FIT-3982   101.0004
4   FIT-3980   901.0070
5   FIT-3979   501.0010
6   FIT-3982        NaN
7   FIT-3980        NaN
8   FIT-3979  1907.0019
9   FIT-3982        NaN
10  FIT-3980        NaN
11  FIT-3979  1907.0020
12  FIT-3982        NaN
13  FIT-3980        NaN
14  FIT-3979  1907.0021

mergedOpen = pd.merge(df1, df2, on='TC_NUM', how='left')
print (mergedOpen)
                                       ID    TC_NUM      KEYS
0  dialog_testcase_0101.0001_greeting.xml  101.0001       NaN
1  dialog_testcase_0101.0002_greeting.xml  101.0002       NaN
2  dialog_testcase_0101.0003_greeting.xml  101.0003       NaN
3  dialog_testcase_0101.0004_greeting.xml  101.0004  FIT-3982
4  dialog_testcase_0101.0005_greeting.xml  101.0005       NaN
5  dialog_testcase_0101.0006_greeting.xml  101.0006  FIT-3979
6  dialog_testcase_0901.0008_greeting.xml  901.0007       NaN
7  dialog_testcase_0101.0008_greeting.xml  101.0008       NaN
8   dialog_testcase_0501.001_greeting.xml  501.0010  FIT-3979
9  dialog_testcase_0801.0011_greeting.xml  801.0011  FIT-3980

mergedOpen.set_index('TC_NUM', inplace=True)
mergedOpen.to_csv('MergedCSVOPEN.csv')