How to create a new rows from column values of pandas data frame

Question

I have dataframe like below

Input

Date         Country    Type         Zip_Incl     Zip_Excl
10/4/2020      FR   Regional        57_67_68    
2/1/2020       GB   Regional                      AB_DD
17/3/2021      GB   Regional        BT_TY         TS_TN
18/3/2021      GB   Regional        
19/1/2021     IN    Regional                      68

I need to transform the input based on below conditions:

1)If Zip_incl is not empty then value to Zip_incl should be passed into Zip_Final

2)IF Zip_incl and Zip_Excl values are present then value of Zip_incl should be passed into Zip_Final

3)If Zip incl is empty and value is present for Zip_Excl is present then Zip_Excl should be passed to Zip_Final

Output

Date      Country   Type    Zip_Incl     Zip_Excl   Zip_Final
10/4/2020   FR  Regional     57                     57
10/4/2020   FR  Regional     67                     67
10/4/2020   FR  Regional     68                     68
2/1/2020    GB  Regional                 AB         AB
2/1/2020    GB  Regional                 DD         DD
17/3/2021   GB  Regional     BT          TS         BT
17/3/2021   GB  Regional     TY          TN         TY
18/3/2021   GB  Regional            
19/1/2021   IN  Regional                 68         68

How can this be done?

Do you mind to check if my updates make any sense to you?

rpanai
– rpanai

2020-06-12 14:02:08 +00:00
Commented Jun 12, 2020 at 14:02 — rpanai
– rpanai, Commented Jun 12, 2020 at 14:02
@rpanai,it works fine.

aeapen
– aeapen

2020-06-12 14:14:48 +00:00
Commented Jun 12, 2020 at 14:14 — aeapen
– aeapen, Commented Jun 12, 2020 at 14:14

rpanai · Accepted Answer · 2020-06-12 14:01:49Z

Assuming the dtypes are all string I'd consider the following

import pandas as pd
import numpy as np
df = pd.DataFrame({"Type":["Regional"]*5,
                   "Zip_Incl":["57_67_68", "", "BT_TY", "", ""],
                   "Zip_Excl":["","AB_DD", "TS_TN", "", "68"]})

# this tell us the element that are not ""
(~df[["Zip_Incl", "Zip_Excl"]].eq(""))

   Zip_Incl  Zip_Excl
0      True     False
1     False      True
2      True      True
3     False     False
4     False      True

While the following returns the first not empty string in every row

sel = (~df.eq("")).values.argmax(1)

Now with some numpy tricks we can get your output

mat = df[["Zip_Incl", "Zip_Excl"]].values
df["Zip_Final"] = mat[np.arange(mat.shape[0]), sel]

Update In case your df is not that big and you are looking for a not numpy solution you could do

def fun(row):
    if row["Zip_Incl"] != "":
        return row["Zip_Incl"]
    elif row["Zip_Excl"] != "":
        return row["Zip_Excl"]
    else:
        return ""

df["Zip_Final"] = df.apply(fun, axis=1)

In both cases the output is

       Type  Zip_Incl Zip_Excl Zip_Final
0  Regional  57_67_68           57_67_68
1  Regional              AB_DD     AB_DD
2  Regional     BT_TY    TS_TN     BT_TY
3  Regional                             
4  Regional                 68        68

Update2: I just realized you want then to split Zip_Final in different row. Using one of the previous methods you could add these lines

df["Zip_Final"] = df["Zip_Final"].str.split("_")

# you need pandas > 0 .25
df = df.explode("Zip_Final")

print(df)

       Type  Zip_Incl Zip_Excl Zip_Final
0  Regional  57_67_68                 57
0  Regional  57_67_68                 67
0  Regional  57_67_68                 68
1  Regional              AB_DD        AB
1  Regional              AB_DD        DD
2  Regional     BT_TY    TS_TN        BT
2  Regional     BT_TY    TS_TN        TY
3  Regional                             
4  Regional                 68        68

BENY · Accepted Answer · 2020-06-12 13:20:28Z

In your case we can do bfill with axis=1 then split the string and explode it

df['Zip_F']=df.filter(like='Zip').bfill(1).iloc[:,0].str.split('_')
df=df.explode('Zip_F')
df
        Date Country      Type  Zip_Incl Zip_Excl Zip_F
0  10/4/2020      FR  Regional  57_67_68      NaN    57
0  10/4/2020      FR  Regional  57_67_68      NaN    67
0  10/4/2020      FR  Regional  57_67_68      NaN    68
1   2/1/2020      GB  Regional       NaN    AB_DD    AB
1   2/1/2020      GB  Regional       NaN    AB_DD    DD
2  17/3/2021      GB  Regional     BT_TY    TS_TN    BT
2  17/3/2021      GB  Regional     BT_TY    TS_TN    TY
3  18/3/2021      GB  Regional       NaN      NaN   NaN
4  19/1/2021      IN  Regional       NaN       68    68

Update

df[['Zip_Incl','Zip_Excl']]=df[['Zip_Incl','Zip_Excl']].mask(df[['Zip_Incl','Zip_Excl']].notnull(),df.Zip_F,axis=0)
df
Out[178]: 
        Date Country      Type Zip_Incl Zip_Excl Zip_F
0  10/4/2020      FR  Regional       57      NaN    57
0  10/4/2020      FR  Regional       67      NaN    67
0  10/4/2020      FR  Regional       68      NaN    68
1   2/1/2020      GB  Regional      NaN       AB    AB
1   2/1/2020      GB  Regional      NaN       DD    DD
2  17/3/2021      GB  Regional       BT       BT    BT
2  17/3/2021      GB  Regional       TY       TY    TY
3  18/3/2021      GB  Regional      NaN      NaN   NaN
4  19/1/2021      IN  Regional      NaN       68    68

@ YOBEN_S What if I need to also split the values inside Zip_Incl and Zip_Excl also into individual row values.
@aeapen in this case you should reformulate your question. Cos some of the elements will split in 2 and others in 3 parts.

Collectives™ on Stack Overflow

How to create a new rows from column values of pandas data frame

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related