Monica Monica - 3 months ago 4
Python Question

How to substract values in DataFrames omiting some solumns

I created two dataFrames and I want to subtract their values omitting two first columns in the first DataFrame.

df = pd.DataFrame({'sys':[23,24,27,30],'dis': [0.8, 0.8, 1.0,1.0], 'Country':['US', 'England', 'US', 'Germany'], 'Price':[500, 1000, 1500, 2000]})
print df

index = {'sys':[23,24,27,30]}
df2 = pd.DataFrame({ 'sys':[23,24,27,30],'dis': [0.8, 0.8, 1.0,1.0],'Price2':[300, 600, 4000, 1000], 'Price3': [2000, 1000, 600, 2000]})

df = df.set_index(['sys','dis', 'Country']).unstack().fillna(0)
df = df.reset_index()

df.columns.names =[None, None]
df.columns = df.columns.droplevel(0)

infile = pd.read_csv('benchmark_data.csv')
infile_slice = infile[(infile.System_num==26)]['Benchmark']
infile_slice = infile_slice.reset_index()
infile_slice = infile_slice.drop(infile_slice.index[4])
del infile_slice['index']
print infile_slice

dfNew = df.sub(infile_slice['Benchmark'].values, axis=0)


In this case I can substract only all values from all columns. How can I skip two first columns from df?

I've tried:
dfNew = df.iloc[3:].sub(infile_slice['Benchmark'].values,axis=0)
, but it does not work.

DataFrames look like:

df:

England Germany US
0 23 0.8 0.0 0.0 500.0
1 24 0.8 1000.0 0.0 0.0
2 27 1.0 0.0 0.0 1500.0
3 30 1.0 0.0 2000.0 0.0


infile_slice:

Benchmark
0 3.3199
1 -4.0135
2 -4.9794
3 -3.1766

Answer

You could use iloc as follows:

df_0_2 = df.iloc[:,0:2]      # First 2 columns
df_2_end = df.iloc[:,2:].sub(infile_slice['Benchmark'].values, axis=0)

pd.concat([df_0_2, df_2_end], axis=1)

              England    Germany         US
0  23  0.8    -3.3199    -3.3199   496.6801
1  24  0.8  1004.0135     4.0135     4.0135
2  27  1.0     4.9794     4.9794  1504.9794
3  30  1.0     3.1766  2003.1766     3.1766
Comments