1

I have tried several different ways to solve this problem but none of them helped me out.

I'm importing csv data out of Finanzblick, and I have an array called amount. If the values within this array' (e.g. 19.34) are positive (greater than zero) then it should be transferred to the array inflow. If the value is negative (e.g. -19.34) it should be transferred to 'outflow'

import pandas as pd
import numpy as np
from pandas.core.tools.numeric import to_numeric
df=pd.read_csv("C:/Users/PD/Desktop/Finanzblick Dokumente/2017_11/2017_11-
DB.csv", sep=';',usecols=(0,1,2,3,4), encoding='utf-8', decimal=',')
df.columns = ['Date', 'Payee', 'Verwendungszweck', 'Buchungstext', 'Betrag']

df['Memo'] = df[['Buchungstext', 'Verwendungszweck']].apply(lambda x: ' -- '.join(x), axis=1)

Betrag = df.Betrag.astype(int)
df['Inflow']  = np.where(df.Betrag > 0, df.Betrag, "")
df['Outflow'] = np.where(df.Betrag < 0, df.Betrag*(-1), "")

df.to_csv('C:/Users/PD/source/repos/Finanzblick YNAB/Finanzblick YNAB/2017_11-DB-import.csv',sep=';', index = False, columns=['Date', 'Payee', 'Memo', 'Inflow', 'Outflow'], decimal='.')

Greetings Phil

SiHa
  • 7,830
  • 13
  • 34
  • 43
The1ne
  • 13
  • 3
  • 2
    Welcome to StackOverflow. Please take the time to read this post on [how to provide a great pandas example](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) as well as how to provide a [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve) and revise your question accordingly. These tips on [how to ask a good question](http://stackoverflow.com/help/how-to-ask) may also be useful. – jezrael Jan 23 '18 at 09:43
  • But maybe need [this](https://stackoverflow.com/q/21608228/2901002) – jezrael Jan 23 '18 at 09:44
  • Using mask i get the result: TypeError: '<' not supported between instances of 'str' and 'int' Using int(df.amount) i got: TypeError: cannot convert the series to – The1ne Jan 23 '18 at 09:53
  • Change `int(df.amount)` to `df.amount.astype(int)` – jezrael Jan 23 '18 at 09:54
  • ValueError: invalid literal for int() with base 10: '-62,62' mask = df.amount.astype(int) < 0 column_name = 'outflow' df.loc[mask, outflow] = 0 – The1ne Jan 23 '18 at 09:58
  • I think need `decimal` in `read_csv` like `df=pd.read_csv("C:/Users/PD/Desktop/Finanzblick Dokumente/2017_11/2017_11- DB.csv", sep=';',usecols=(0,1,2,3,4), encoding='utf-8', decimal=',')` - it convert `,` to `.` and numbers to numeric floats. – jezrael Jan 23 '18 at 10:12
  • I think this works but next error is: "NameError: name 'outflow' is not defined" in code: "df.loc[mask, outflow] = 0" – The1ne Jan 23 '18 at 10:18
  • Check solution bellow. – jezrael Jan 23 '18 at 10:20

1 Answers1

0

If your positive and negative numbers are in an array the following will separate them by sign:

inflows  = np.where(amount > 0, amount, 0.)
outflows = np.where(amount < 0, amount, 0.)

If they are in a DataFrame, use this:

df['inflows']  = np.where(df.amount > 0, df.amount, 0.)
df['outflows'] = np.where(df.amount < 0, df.amount, 0.)
adr
  • 1,731
  • 10
  • 18