Home How can I use for loops to convert the columns and its contents from a df to a more organized ones on Python? Pandas related

Questions

How can I use for loops to convert the columns and its contents from a df to a more organized ones on Python? Pandas related

byMR

January 9, 2022

I got the following df as the result of a previous process that made some Cartesian Products:

  Permutations                        FilePermutations
0 Fondo+Cuerpo+Ojos+Color+Pinzas+Puas Oceano.png+Cuerpo_cangrejo.png+Antenas.png+Amarillo.png+None+None
1 Fondo+Cuerpo+Ojos+Color+Pinzas+Puas Oceano.png+Cuerpo_cangrejo.png+Antenas.png+Amarillo.png+None+Arena.png
2 Fondo+Cuerpo+Ojos+Color+Pinzas+Puas Oceano.png+Cuerpo_cangrejo.png+Antenas.png+Amarillo.png+None+Marron.png
3 Fondo+Cuerpo+Ojos+Color+Pinzas+Puas Oceano.png+Cuerpo_cangrejo.png+Antenas.png+Amarillo.png+None+Purpura.png
4 Fondo+Cuerpo+Ojos+Color+Pinzas+Puas Oceano.png+Cuerpo_cangrejo.png+Antenas.png+Amarillo.png+None+Verde.png
.
.
.

I would like to convert that df to this one, where every "_" became " " and every ".png" at the end of a word got deleted:

0 | Fondo | Oceano | Cuerpo | Cuerpo cangrejo | Ojos | Antenas | Color | Amarillo | Pinzas | None | Puas | None
1 | Fondo | Oceano | Cuerpo | Cuerpo cangrejo | Ojos | Antenas | Color | Amarillo | Pinzas | None | Puas | Arena
2 | Fondo | Oceano | Cuerpo | Cuerpo cangrejo | Ojos | Antenas | Color | Amarillo | Pinzas | None | Puas | Marron
3 | Fondo | Oceano | Cuerpo | Cuerpo cangrejo | Ojos | Antenas | Color | Amarillo | Pinzas | None | Puas | Purpura
4 | Fondo | Oceano | Cuerpo | Cuerpo cangrejo | Ojos | Antenas | Color | Amarillo | Pinzas | None | Puas | Verde
.
.
.

I have tried the following code:

import pandas as pd

old_df = pd.read_csv("cartesian.csv", index_col=0)
new_columns = old_df.iloc[0]['Permutations'].split("+")
new_data = []
for i in range(0, len(old_df)):
    row_data = old_df.iloc[i]['FilePermutations'].split("+")
    current_data = []
    for j, column in enumerate(new_columns):
        current_data.append(f"{column} {row_data[j]}")
    new_data.append(current_data)

updated_df = pd.DataFrame(data=new_data, columns=new_columns)
print(updated_df)

But only managed to get this new_df:

              Fondo                      Cuerpo  ...       Pinzas              Puas
0  Fondo Oceano.png  Cuerpo Cuerpo_cangrejo.png  ...  Pinzas None         Puas None
1  Fondo Oceano.png  Cuerpo Cuerpo_cangrejo.png  ...  Pinzas None    Puas Arena.png
2  Fondo Oceano.png  Cuerpo Cuerpo_cangrejo.png  ...  Pinzas None   Puas Marron.png
3  Fondo Oceano.png  Cuerpo Cuerpo_cangrejo.png  ...  Pinzas None  Puas Purpura.png
4  Fondo Oceano.png  Cuerpo Cuerpo_cangrejo.png  ...  Pinzas None    Puas Verde.png
..                                          ...  ...                            ...

[360 rows x 6 columns]

May I get some assistance please?

>Solution :

Use Series.str.split and df.replace with pd.concat:

In [415]: res = pd.concat([df.Permutations.str.split('+', expand=True), df.FilePermutations.str.split('+', expand=True)], 1).replace({'_': ' ', '.png': ''}, regex=True)

In [416]: res
Out[416]: 
       0       1     2      3       4     5       0                1        2         3     4        5
0  Fondo  Cuerpo  Ojos  Color  Pinzas  Puas  Oceano  Cuerpo cangrejo  Antenas  Amarillo  None     None
1  Fondo  Cuerpo  Ojos  Color  Pinzas  Puas  Oceano  Cuerpo cangrejo  Antenas  Amarillo  None    Arena
2  Fondo  Cuerpo  Ojos  Color  Pinzas  Puas  Oceano  Cuerpo cangrejo  Antenas  Amarillo  None   Marron
3  Fondo  Cuerpo  Ojos  Color  Pinzas  Puas  Oceano  Cuerpo cangrejo  Antenas  Amarillo  None  Purpura
4  Fondo  Cuerpo  Ojos  Color  Pinzas  Puas  Oceano  Cuerpo cangrejo  Antenas  Amarillo  None    Verde