Is there a way to create new columns based on the two different conditions applied to two columns on a data frame?

user_11744404 0
Viewed 43

I have a dataset which is fairly large (around 50000 entries) structured in the following way.

state id Dist id Name
32 0 Jammu & Kashmir
32 0 Jammu & Kashmir
32 0 Jammu & Kashmir
32 1 Kupwara
32 1 Kupwara
32 4 Badgam
32 4 Badgam
32 14 Kathua
32 14 Kathua
12 0 Arunachal Pradesh
12 0 Arunachal Pradesh
12 10 Dibang Valley
12 10 Dibang Valley

To explain this, the state id identifies the state and if the district id happens to be 0 for that particular row, it means the value (which is there in other columns) is for the entire state. However, if the district id happens to be any other number other than 0, the value is for that particular district (which is within the state, given by the state id)

My aim is to get two more columns to this dataset, 'state_name' and 'district_name' such that state_name is filled by all the Name column which has Dist id = 0 and the similar state id. The second column district_name will be filled by the district name.

The expected output is the following table:

state id Dist id Name state_name district_name
32 0 Jammu & Kashmir Jammu & Kashmir -
32 0 Jammu & Kashmir Jammu & Kashmir -
32 0 Jammu & Kashmir Jammu & Kashmir -
32 1 Kupwara Jammu & Kashmir Kupwara
32 1 Kupwara Jammu & Kashmir Kupwara
32 4 Badgam Jammu & Kashmir Badgam
32 4 Badgam Jammu & Kashmir Badgam
32 14 Kathua Jammu & Kashmir Kathua
32 14 Kathua Jammu & Kashmir Kathua
12 0 Arunachal Pradesh Arunachal Pradesh -
12 0 Arunachal Pradesh Arunachal Pradesh -
12 10 Dibang Valley Arunachal Pradesh Dibang Valley
12 10 Dibang Valley Arunachal Pradesh Dibang Valley

How do I go about this?

data-wrangling pandas python
2 Answers

IIUC:

try via loc accessor and boolean masking and ffill() method for forward filling the values of 'state_name' column:

df.loc[df['Dist id'].eq(0),'state_name']=df.loc[df['Dist id'].eq(0),'Name']
df.loc[df['Dist id'].ne(0),'district_name']=df.loc[df['Dist id'].ne(0),'Name']
df['state_name']=df['state_name'].ffill()

OR

via np.where():

import numpy as np

df['state_name']=np.where(df['Dist id'].eq(0),df['Name'],np.nan)
df['district_name']=np.where(df['Dist id'].ne(0),df['Name'],np.nan)
df['state_name']=df['state_name'].ffill()

output of df:

    state id  Dist id               Name         state_name  district_name
0         32        0    Jammu & Kashmir    Jammu & Kashmir            NaN
1         32        0    Jammu & Kashmir    Jammu & Kashmir            NaN
2         32        0    Jammu & Kashmir    Jammu & Kashmir            NaN
3         32        1            Kupwara    Jammu & Kashmir        Kupwara
4         32        1            Kupwara    Jammu & Kashmir        Kupwara
5         32        4             Badgam    Jammu & Kashmir         Badgam
6         32        4             Badgam    Jammu & Kashmir         Badgam
7         32       14             Kathua    Jammu & Kashmir         Kathua
8         32       14             Kathua    Jammu & Kashmir         Kathua
9         12        0  Arunachal Pradesh  Arunachal Pradesh            NaN
10        12        0  Arunachal Pradesh  Arunachal Pradesh            NaN
11        12       10      Dibang Valley  Arunachal Pradesh  Dibang Valley
12        12       10      Dibang Valley  Arunachal Pradesh  Dibang Valley
user_142898920

The easiest way to accomplish this would be to duplicate the District ID, and then replace the appropriate IDs with the district name.

For instance, consider the following data frame:

import pandas as pd
df = pd.DataFrame({'District': [1, 4]})

first dataframe

The values are replaced in subsequent data frames:

df2=df.replace([1], 'Kupwara')
df3=df2.replace([4], 'Badgam')

new dataframe

user_70942440
Related