CODEWITHSUNDEEP
pythonpandas
Shubham R
Shubham R

Reputation: 7644

Map value from one row as a new column in pandas

I have a pandas dataframe:

SrNo    value
 a       nan
 1       100
 2       200
 3       300
 b       nan
 1       500
 2       600
 3       700
 c       nan
 1       900
 2       1000

i want my final dataframe as:

value   new_col
 100      a
 200      a
 300      a
 500      b
 600      b
 700      b
 900      c
1000      c

i.e for sr.no 'a' the values under a should have 'a' as a new column similarly for b and c

Upvotes: 0

Views: 912

Answers (3)

Bharath M Shetty
Bharath M Shetty

Reputation: 30605

Another way with replace numbers with nan and ffill() 

df['col'] = df['SrNo'].replace('([0-9]+)',np.nan,regex=True).ffill()
df = df.dropna(subset=['value']).drop('SrNo',1)

Output:

      value col
1    100.0   a
2    200.0   a
3    300.0   a
5    500.0   b
6    600.0   b
7    700.0   b
9    900.0   c
10  1000.0   c

Upvotes: 0

jezrael
jezrael

Reputation: 863176

Create new column by where with condition by isnull, then use ffill for replace NaNs by forward filling.

Last remove NaNs rows by dropna and column by drop:

print (df['SrNo'].where(df['value'].isnull()))
0       a
1     NaN
2     NaN
3     NaN
4       b
5     NaN
6     NaN
7     NaN
8       c
9     NaN
10    NaN
Name: SrNo, dtype: object

df['new_col'] = df['SrNo'].where(df['value'].isnull()).ffill()
df = df.dropna().drop('SrNo', 1)
print (df)
     value new_col
1    100.0       a
2    200.0       a
3    300.0       a
5    500.0       b
6    600.0       b
7    700.0       b
9    900.0       c
10  1000.0       c

Upvotes: 3

Zero
Zero

Reputation: 76947

Here's one way

In [2160]: df.assign(
               new_col=df.SrNo.str.extract('(\D+)', expand=True).ffill()
             ).dropna().drop('SrNo', 1)
Out[2160]:
     value new_col
1    100.0       a
2    200.0       a
3    300.0       a
5    500.0       b
6    600.0       b
7    700.0       b
9    900.0       c
10  1000.0       c

Upvotes: 1

Related Questions