Split the column cell value of location in 3 different columns

Question

I've tried simple Python code, but I want to divide the location column into three separate columns. If there is only one state or one country, we need to separate the columns appropriately.

i/p

Location
Bengaluru, Karnataka, India
Delhi, India
India

Expected output:

City	State	Country
Bengaluru	Karnataka	India
	Delhi	India
		India

df = pd.read_excel('filename.xlsx')
df[["City", "State", "Country"]] = df["Location"].str.split(", ", expand=True)
print(df["City"])
df.to_excel('filename.xlsx', index=False)

Answer 1

If the Location column has always the same pattern, you can use :

out = pd.DataFrame([[np.nan] * (2 - e.count(",")) + e.split(",")
                    for e in df["Location"]], columns=["City", "State", "Country"])
​

Output :

print(out)

        City       State Country
0  Bengaluru   Karnataka   India
1        NaN       Delhi   India
2        NaN         NaN   India