Split the column cell value of location in 3 different columns

0 votes

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)
Apr 9, 2023 in Others by Kithuzzz
• 38,010 points
346 views

1 answer to this question.

0 votes

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
answered Apr 9, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,420 points
470 views
0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
281 views
0 votes
1 answer

Sum the total of a column in excel and paste the sum to a different workbook

Get Column Sum From Closed Workbook Option Explicit Sub ...READ MORE

answered Jan 26, 2023 in Others by narikkadan
• 63,420 points
369 views
0 votes
1 answer

Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,420 points
472 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 4,069 views
0 votes
1 answer
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,420 points
552 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,420 points
1,050 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP