我有两个表:
df1:[1 rows x 23 columns]
1C 1E 1F 1H 1K ... 2M 2P 2S 2U 2W
total 1057 334 3609 3762 1393 ... 328 1611 1426 87 118
df2:[1 rows x 137 columns]
1CA 1CB 1CC 1CF 1CJ 1CS ... 2UB 2UJ 2WB 2WC 2WF 2WJ
total 11 381 111 20 527 2 ... 47 34 79 2 1 36
我需要减去两个表之间的值.
如1C-1CF,1E-1EF,1F-1FF等.
即我只需要减去工作表2中以F结尾的列.
答案:1C = 1C-1CF = 1037
使用Python代码怎么可能?
注意:
某些“ df1”在“ df2”中没有“ F”
df1:
['1C', '1E', '1F', '1H', '1K', '1M', '1N', '1P', '1Q', '1R', '1S', '1U', '1W', '2C', '2E', '2F', '2H', '2K', '2M', '2P', '2S', '2U', '2W']
df2:
['1CA', '1CB', '1CC', '1CF', '1CJ', '1CS', '1CU', '1EA', '1EB', '1EC', '1EF', '1EJ', '1ES', '1FA', '1FB', '1FC', '1FF', '1FJ', '1FS', '1FT', '1FU', '1HA', '1HB', '1HC', '1HF', '1HJ', '1HS', '1HT', '1HU', '1KA', '1KB', '1KC', '1KF', '1KJ', '1KS', '1KU', '1MA', '1MB', '1MC', '1MF', '1MJ', '1MS', '1MU', '1NA', '1NB', '1NC', '1NF', '1NJ', '1PA', '1PB', '1PC', '1PF', '1PJ', '1PS', '1PT', '1PU', '1QA', '1QB', '1QC', '1QF', '1QJ', '1RA', '1RB', '1RC', '1RF', '1RJ', '1SA', '1SB', '1SC', '1SF', '1SJ', '1SS', '1ST', '1SU', '1UA', '1UB', '1UC', '1UF', '1UJ', '1US', '1UU', '1WA', '1WB', '1WC', '1WF', '1WJ', '1WS', '1WU', '2CA', '2CB', '2CC', '2CJ', '2CS', '2EA', '2EB', '2EJ', '2FA', '2FB', '2FC', '2FJ', '2FU', '2HB', '2HC', '2HF', '2HJ', '2HU', '2KA', '2KB', '2KC', '2KF', '2KJ', '2KU', '2MA', '2MB', '2MC', '2MF', '2MJ', '2MS', '2MT', '2PA', '2PB', '2PC', '2PF', '2PJ', '2PU', '2SA', '2SB', '2SC', '2SF', '2SJ', '2UA', '2UB', '2UJ', '2WB', '2WC', '2WF', '2WJ']´
解决方法:
您可以尝试以下方法:
sheet2 = sheet2.filter(regex=(".*F$")) # Leave only 'F' columns in sheet2
sheet2.columns = [i[:-1] for i in sheet2.columns] # Remove 'F' in the end for column-wise substraction
result = sheet1 - sheet2 # Substract values
result[result.isnull()] = sheet1 # Leave sheet1 values if there's no appropriate 'F' column in sheet2
注意:如果在sheet2中没有适当的带有“ F”的列,则保留sheet1的值不变.
我像这样创建您的数据框:
sheet1 = pd.DataFrame({'1C': [1057], '1E': [334], '1F': [3609], '2F': [3609]})
sheet2 = pd.DataFrame({'1CA': [11], '1CB': [381], '1CC': [111], '1CF': [20], '1EF': [10], '1FF': [15]})
标签:pandas,dataframe,python
来源: https://codeday.me/bug/20191025/1925082.html