CODEWITHSUNDEEP
pythonpandas
charisma bathara
charisma bathara

Reputation: 47

Negate the values based on indicator

i want to add negate the values of Income when D is found

# Preparing data
# Note that I have clean up the `jan` to `Jan` and `Charge` to `charge`
[In]
data = [{'Month': 'Jan', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'k', 'Income': 200},
{'Month': 'Jan', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'k',  'Income': 10},
{'Month': 'Feb', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'd', 'Ex/Im': 'export', 'Income': 100},
{'Month': 'Jan', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'k', 'Income': 400},
{'Month': 'Jan', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'k',  'Income': 10},
{'Month': 'Feb', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'd', 'Ex/Im': 'export', 'Income': 200}]

[Out]   Months   Name      id   Info   d/k  Ex/Im    Income
    0   Jan    Alice sal  212  charge  k    export     200
    1   Jan    Alice sal  212  charge  k    export      10
    2   Jan    Alice sal  212  charge  d    export     100
    3   Jan    Alice sal  212  charge  k    Import     400
    4   Jan    Alice sal  212  charge  k    Import      10
    5   Jan    Alice sal  212  charge  d    Import     200

df = pd.DataFrame(data)

*note: name from my data frame not only alice sal, i have similar case from every name

i've already tried

# 1. Negate the values of `Income` when `D` is found, 

[In]:
df.loc[df['d/k'] == 'D', 'Income'] = -df[df['d/k'] == 'D']['Income']
print(df)

[Out]  
       Months   Name      id   Info   d/k  Ex/Im    Income
    0   Jan    Alice sal  212  charge  k    export     200
    1   Jan    Alice sal  212  charge  k    export      
    2   Jan    Alice sal  567  charge  d    export     
    3   Jan    Alice sal  212  charge  k    Import     400
    4   Jan    Alice sal  212  charge  k    Import     
    5   Jan    Alice sal  567  charge  d    Import 


# 2. Group by

[In]:
    gdf = df.groupby(['id', 'Month', 'Ex/Im', 'Info']).agg({'Income':'sum', 'Name':'first'})
print(gdf)

[Out]:
                 Income
Month           Jan
Ex/Im           export    import
Info            charge    charge
id    Name            
212   Alice sal 200         400

ther's error warning:

FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison result = method(y)

my expected:

                 Income
Month           Jan
Ex/Im           export    import
Info            charge    charge
id    Name            
212   Alice sal 120         210

Upvotes: 0

Views: 72

Answers (2)

ch33hau
ch33hau

Reputation: 2961

Everything is correct, except your data and the negate part:

data = [
    {'Month': 'Jan', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'k', 'Ex/Im': 'export', 'Income': 200},
    {'Month': 'Jan', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'k', 'Ex/Im': 'export',  'Income': 10},
    {'Month': 'Jan', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'd', 'Ex/Im': 'export', 'Income': 100},
    {'Month': 'Jan', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'k', 'Ex/Im': 'Import', 'Income': 400},
    {'Month': 'Jan', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'k', 'Ex/Im': 'Import',  'Income': 10},
    {'Month': 'Jan', 'Name': 'Alice sal', 'id': 212, 'Info': 'charge', 'd/k': 'd', 'Ex/Im': 'Import', 'Income': 200}]
  • You have month as Feb in data but all records from table view are Jan.
  • Ex/Im column missing in some records
  • Since the negate part is compare against D, you should either change the D to d, or change the value in d/k column to D

Upvotes: 0

BENY
BENY

Reputation: 323326

After change the value by using condition , that is just a pivot problem 

df.loc[df['d/k']=='d','Income']=df.Income*-1

df.pivot_table(index=['id','Name'],columns=['Months','Ex/Im','Info'],values='Income',aggfunc='sum')
Out[423]: 
Months          Jan       
Ex/Im        Import export
Info         charge charge
id  Name                  
212 Alicesal    210    110

Upvotes: 1

Related Questions