Add rows by pandas names

Question

Add rows by pandas names

Navigation

#1 by (2 votes)

1

I'm working with a data framework like this, but bigger and with more areas. I'm trying to add the value of the rows by their names. This is my code but I get an error. The total of the sums of the R or C zones go in the column total and the total of the sums of the zones M goes in the column total1 .

"C1" does not appear in the list.

This happens because the program is trying to add the value of C1 when it finds any other value that makes the condition of If statement true.

Marco:

ID  Zone1   CHC1    Valor1  Zone2     CHC2  Valor2  Zone3   CHC3    Valor3  total   total1
1   R5B     100      10       C2        0     20      R10A   2       5        35       0
1   C2       95      20      M2-6       5      6      R5B    7       3        23       6       
3   C2       40      4        C4       60      6       0     6       0        10       0
3   C1       100     8         0        0      0       0    100      0        8        0
5   M1-5     10      6       M2-6      86     15       0     0       0        0        21

Code:

import pandas as pd
df = pd.read_excel("C:PATH")

total = []
total1 = []

for i in range(df.shape[0]):
    temp = df.iloc[i].tolist()
    if ("R5B" in temp) |("R10A" in temp) | ("C2" in temp) | ("C1" in temp) | ("C4" in temp) :
        total.append(temp[temp.index("R5B")+2] + temp[temp.index("R10A")+2] +temp[temp.index("C2")+2] + temp[temp.index("C1")+2] +temp[temp.index("C4")+2])
    if ("M1-5" in temp) | ("M2-6" in temp):
        total1.append(temp[temp.index("M1-5")+2] + temp[temp.index("M2-6")+2])

df["Total"] = total
df["Total1"] = total1

print (df)

python pandas

asked by Jose Vasquez 05.07.2018 в 14:38

source

1 answer

ERROR: java.lang.NullPointerException: Attempt to invoke virtual method 'android.database.sqlite.SQLiteDatabase SpringBoot with Mysql, error connecting

score 2 · Accepted Answer

This accomplishes what you want, and it works with any amount of Zone1 , Zone2 , ..., ZoneN :

import pandas as pd

d = {"ID":     [1,1,3,3,5],
     "Zone1":  ["R5B","C2","C2","C1","M1-5"],
     "Valor1": [10,20,4,8,6],
     "Zone2":  ["C2","M2-6","C4","0","M2-6"],
     "Valor2": [20,6,6,0,15],
     "Zone3":  ["R10A","R5B","0","0","0"],
     "Valor3": [5,3,0,0,0]}

df = pd.DataFrame(data=d)

headers = list(df)
headers.sort()
valors = [h for h in headers if h.startswith("Valor")]
zones  = [h for h in headers if h.startswith("Zone")]

rc = ["R","C"]
m  = ["M"]

# Si ZonaX empieza por 'R' o 'C', sumará ValorX
df["total"] = sum(df[zone].str[0].isin(rc) * df[valor] 
                  for zone, valor in zip(zones, valors))

# Si ZonaX empieza por 'M', sumará ValorX
df["total1"] = sum(df[zone].str[0].isin(m) * df[valor] 
                  for zone, valor in zip(zones, valors))

print(df)

   ID Zone1  Valor1 Zone2  Valor2 Zone3  Valor3  total  total1
0   1   R5B      10    C2      20  R10A       5     35       0
1   1    C2      20  M2-6       6   R5B       3     23       6
2   3    C2       4    C4       6     0       0     10       0
3   3    C1       8     0       0     0       0      8       0
4   5  M1-5       6  M2-6      15     0       0      0      21