subtract each value in column by entire column
I have the following df1
:
prueba
12032018 7
08032018 1
06032018 9
05032018 5
I would like to get each value in the column beggining by the last (5
) and substract the entire column by that value. then iterate upwards and subtract the remaining values in the column. for each subtraction I would like to generate a column and generate a df with the results of each subtraction:
The desired output would be something like this:
05032018 06032018 08032018 12032018
12032018 2 2 6 0
08032018 4 8 0 NaN
06032018 4 0 NaN NaN
05032018 0 NaN NaN NaN
What I tried to obtain the desired output was, first take df1
and
df2=df1.sort_index(ascending=True)
create an empty df:
main_df=pd.DataFrame()
and then iterate over the values in the column df2
and subtract to the df1
column
for index, row in df2.iterrows():
datos=df1row['pruebas']
df=pd.DataFrame(data=datos,index=index)
if main_df.empty:
main_df= df
else:
main_df=main_df.join(df)
print(main_df)
However the following error outputs:
TypeError: Index(...) must be called with a collection of some kind, '05032018' was passed
2 answers

You can using
np.triu
, with array subtracts=df.prueba.values.astype(float) s=np.triu((ss[:,None]).T) s[np.tril_indices(s.shape[0], 1)]=np.nan pd.DataFrame(s,columns=df.index,index=df.index).reindex(columns=df.index[::1]) Out[482]: 05032018 06032018 08032018 12032018 12032018 2.0 2.0 6.0 0.0 08032018 4.0 8.0 0.0 NaN 06032018 4.0 0.0 NaN NaN 05032018 0.0 NaN NaN NaN

Kind of messy but does the work:
temp = 0 count = 0 df_new = pd.DataFrame() for i, v, date in zip(df.index, df["prueba"][::1], df.index[::1]): print(i,v) new_val = df["prueba"]  v if count > 0: new_val[count:] = np.nan df_new[date] = new_val temp += v count += 1 df_new