Using Excel file as mapping for pandas dataframe in Python

user_16572743 0
Viewed 208

I'm trying to use an excel "master list" of error codes and the larger group in which they belong to group errors by the larger group in a pandas data frame. I'm unsure of how to do this. I previously tried adding the error codes to a dictionary with the group name as they key and grouping by that, but it wouldn't properly group the codes. I'll add examples of my master list and data frame below.

Master list:

CODE Type 1 Type 2 Type 3 Type4
Code A X
Code B X
Code C X
Code D X
Code E X

and so on for 10 columns and 57 rows

DataFrame:

CODE Numerator Denominator Error Rate
CODE B 6 10 0.6
CODE A 4 10 0.4
CODE C 9 10 0.9
CODE D 0 10 0

and so on for whichever error codes show up

I'd Like for the output to be:

CODE Numerator Denominator Error Rate
Type 1 15 10 1.5
Type 2 4 10 0.4
Type 4 0 10 0

and so on for appropriate groupings.

How do I go about doing this?

I've used dictionaries and I've used isin() with a list of types to no avail.

Here's a less simplified version of my example files:

CODE Power Cycle Panics Temp App Restart
Panic X
Power Reset-Power_Reset-Hard Power X
Power Reset-Hard_Power-Hard Power X
HARD-App-Restart X
Code-Reason Temperature X

and so on for 10 columns and 57 rows

CODE Numerator Denominator Error Rate
Power Reset-Power_Reset-Hard Power 6 10 0.6
Panic 4 10 0.4
Power Reset-Hard_Power-Hard Power 9 10 0.9
HARD-App-Restart 0 10 0

and so on for as many rows for whichever error codes show up

Desired output: |CODE|Numerator|Denominator|Error Rate| |----|---------|-----------|----------| |Power Cycle| 15 | 10 | 1.5 | |Panic| 4 | 10 | 0.4 | |App Restart | 0 | 10 | 0 |

pandas dataframe mapping python
1 Answers

Say first dataframe df1 second df2

#convert Code to CODE in first dataframe with str.upper for standardization
df1.CODE = df1.CODE.str.upper()

#replace X with column names and remove nan values
df1["new"] = df1.filter(like="T").replace("X", pd.Series(df1.columns, df1.columns)).stack().to_numpy()

#replace CODE A,B,C,D,E with Type 1,2,3,4
df2["CODE"] = df2[["CODE"]].replace(df1[["CODE","new"]].set_index("CODE").to_dict()["new"],regex=True)

df2

    CODE    Numerator   Denominator Error Rate
0   Type 1      6          10         0.6
1   Type 2      4          10         0.4
2   Type 1      9          10         0.9
3   Type 4      0          10         0.0
Admin1
Related