CODEWITHSUNDEEP
pythonexcelpandas
Ignition K6
Ignition K6

Reputation: 147

Python pandas if row contains string add to table

I have a spreadsheet that looks as follows:

enter image description here

my goal is to print out which "hmi" controls which "az", which is indicated by an "x" in its corresponding column. Example based on the above spreadsheet:

etc..

My idea output that i can work with and sort to an output excel sheet would be(where "X" is the corresponding number):

HMIX | AZX

ive imported the spreadsheet with pd.read_excel and my thought was using the following code to format it, however i think im close but on the wrong path:

    for index, row in dfConfig.iterrows():
        if row["az1"] != "NaN":
            print(row["hmi_number"])

Upvotes: 0

Views: 572

Answers (4)

ipj
ipj

Reputation: 3598

Another approach is based on stack() method and then accessing index by list comprehension. Given above dataframe df:

[print(f"HMI{x} controls {y}") for x,y in df.set_index("hmi_number").stack().index]

Solution is fast and very compact.

Upvotes: 1

No Copywrite Spectrums
No Copywrite Spectrums

Reputation: 27

If your looking for something that checks if something is a string try this 

>>> currentColumn = "x"
>>> isinstance(currentColumn, str)
True
>>> isinstance(currentColumn, int)
False
>>>

Upvotes: 1

Mattia Peracchi
Mattia Peracchi

Reputation: 58

Iterate over rows first, thank over columns, if you find the x, print the row and the columns.

for i, row in dfConfig.iterrows():
    for col in dfConfig:
        if row[col] == 'x':
            print(f'HMI{row["hmi_number"]} controls {col}')

Upvotes: 1

Scott Boston
Scott Boston

Reputation: 153500

Given df,

df = pd.DataFrame({'hmi_number':np.arange(1,10),
                  'az1':['x']+[np.nan]*8,
                  'az2':[np.nan]+['x']+[np.nan]*7,
                  'az3':[np.nan]*2+['x']*2+[np.nan]*5,
                  'az4':[np.nan]*4+['x']*2+[np.nan]*3,
                  'az5':[np.nan]*6+['x']*2+[np.nan],
                  'az6':[np.nan]*8+['x']})

Try this:

for hmi, az in (df.set_index('hmi_number')=='x').dot(df.columns[1:]).iteritems():
    print(f'HMI{hmi} controls {az}')

Output:

HMI1 controls az1
HMI2 controls az2
HMI3 controls az3
HMI4 controls az3
HMI5 controls az4
HMI6 controls az4
HMI7 controls az5
HMI8 controls az5
HMI9 controls az6

Upvotes: 3

Related Questions