CODEWITHSUNDEEP
pythonstringpandasconcatenation
Javier
Javier

Reputation: 513

How to get values of other columns and put it in a specific column? (in same rows)

I have 3 columns with the same name

   PUESTO       PUESTO       PUESTO
   MEDICO       AYUDANTE     AYUDANTE II
   ENFERMERO    nan          nan
   JARDINERO    nan          nan
   INGENIERO    nan          nan
   METEOROLOGO  OBSERVADOR   nan
   ABOGADO      PRACTICANTE  PRACTICANTE II
   CONTADOR     PRACTICANTE  PRACTICANTE II

I want to get the information of the 2nd and 3rd column and put it in the 1rst column like this:

   PUESTO       

   MEDICO
   AYUDANTE
   AYUDANTE II    

   ENFERMERO           

   JARDINERO            

   INGENIERO              

   METEOROLOGO
   OBSERVADOR     

   ABOGADO        
   PRACTICANTE
   PRACTICANTE II

   CONTADOR       
   PRACTICANTE
   PRACTICANTE II

So i tried this: 

df.iloc[:,1] = df.iloc[:,1].str.cat(df.iloc[:,2:3], sep ="\n")

But if there is a None value (nan) in any of the columns, the 1rst column is filled with nans. And i don't want that.

So i tried this:

for i in df.iloc[:,2:3]!=None:
   df.iloc[:,1] = df.iloc[:,1].str.cat(df.iloc[:,2:3], sep ="\n")

But this fill with nans the first column (if there is a nan value in any column) like the first slice of code that i showed. I will appreciate if you can help me. Thanks!

Upvotes: 0

Views: 53

Answers (2)

Valdi_Bo
Valdi_Bo

Reputation: 30991

From you expected result I see that you want to concatenate not only data from second and third column, but from all three columns (and save the concatenation result in the first column).

To do it, you can run:

df.iloc[:,0] = df.fillna('').apply(lambda row: row.str.cat(sep='\n').strip(), axis=1)

The advantage of my solution over the other is that the result has a single element resulting from each row (not a sequence of 3 elements).

When you print(df), the result may be a bit misleading:

                                  PUESTO       PUESTO          PUESTO
0          MEDICO\nAYUDANTE\nAYUDANTE II     AYUDANTE     AYUDANTE II
1                              ENFERMERO          NaN             NaN
2                              JARDINERO          NaN             NaN
3                              INGENIERO          NaN             NaN
4                METEOROLOGO\nOBSERVADOR   OBSERVADOR             NaN
5   ABOGADO\nPRACTICANTE\nPRACTICANTE II  PRACTICANTE  PRACTICANTE II
6  CONTADOR\nPRACTICANTE\nPRACTICANTE II  PRACTICANTE  PRACTICANTE II

But to check whether \n chars above are true newline chars, run:

for r in df.iloc[:, 0]:
    print(f'{r}\n')

i.e. print only the first column, with an empty line between consecutive elements, and the result will be:

MEDICO
AYUDANTE
AYUDANTE II

ENFERMERO

JARDINERO

INGENIERO

METEOROLOGO
OBSERVADOR

ABOGADO
PRACTICANTE
PRACTICANTE II

CONTADOR
PRACTICANTE
PRACTICANTE II

Upvotes: 1

Scott Boston
Scott Boston

Reputation: 153460

IIUC, use stack:

df.stack(dropna=False).fillna('')

Output:

0  PUESTO              MEDICO
   PUESTO.1          AYUDANTE
   PUESTO.2       AYUDANTE II
1  PUESTO           ENFERMERO
   PUESTO.1                  
   PUESTO.2                  
2  PUESTO           JARDINERO
   PUESTO.1                  
   PUESTO.2                  
3  PUESTO           INGENIERO
   PUESTO.1                  
   PUESTO.2                  
4  PUESTO         METEOROLOGO
   PUESTO.1        OBSERVADOR
   PUESTO.2                  
5  PUESTO             ABOGADO
   PUESTO.1       PRACTICANTE
   PUESTO.2    PRACTICANTE II
6  PUESTO            CONTADOR
   PUESTO.1       PRACTICANTE
   PUESTO.2    PRACTICANTE II

Upvotes: 2

Related Questions