Adding column in dataframes in Pandas using data in existing Dataframe

Question

I have following Dataframe looks like in below image:

I need to add one more column 'Key' to existing Dataframe such as it looks like Dataframe in below image:

Is there a way to create a Column "Key" based on columns Field and Seq

Welcome to StackOverflow! You should provide real code in your questions (not pictures). Read about how to ask a question (particularly how to create a good example) in order to get responses. You can also take the tour. — Alex
– Alex, Commented Mar 3, 2018 at 17:33
You can do df['Key']="blah" and then can change every value of row by "loc" on index . — Asim
– Asim, Commented Mar 3, 2018 at 17:36

jpp · Accepted Answer · 2018-03-03 19:30:12Z

1

Here is one solution.

import pandas as pd

df = pd.DataFrame({'Field': ['Indicator', 'A', 'B', 'Code', '1', '2', '3', 'Name', 'Address'],
                   'Count': [26785, 785, 26000, 12345, 45, 300, 12000, 12312, 1212],
                   'Seq': [1.0, 1.1, 1.1, 2.0, 2.1, 2.1, 2.1, 3.0, 4.0]})

sep = df.loc[df['Seq'].apply(lambda x: x == int(x)), 'Field'].tolist()

df['key'] = pd.Series(np.where(~df['Field'].isin(sep), None, df['Field'])).ffill()
df.loc[df['Field'] != df['key'], 'key'] += '+' + df['Field']

#    Count      Field  Seq          key
# 0  26785  Indicator  1.0    Indicator
# 1    785          A  1.1  Indicator+A
# 2  26000          B  1.1  Indicator+B
# 3  12345       Code  2.0         Code
# 4     45          1  2.1       Code+1
# 5    300          2  2.1       Code+2
# 6  12000          3  2.1       Code+3
# 7  12312       Name  3.0         Name
# 8   1212    Address  4.0      Address

Explanation

Add a 'key' column and replace values not in sep with None, then use ffill() to fill the None values.
Update 'key' column only where 'Field' and 'key' are misaligned.

edited Mar 3, 2018 at 19:30

answered Mar 3, 2018 at 17:55

jpp

166k37 gold badges301 silver badges362 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Deepak ..G Over a year ago

Thanks!, can you suggest me how to generate "sep" list dynamically if data is changing every time.

Deepak ..G Over a year ago

Actually the list in sep should be values in Field when Seq is like 1.0, 2.0, 3.0, 4.0 and so on. Not based in Field length What would be solution for this?

Deepak ..G Over a year ago

Hi All these days, code is working fine and now i got a warning message: At this line: sep = df.loc[df['Seq'].apply(lambda x: x == int(x)), 'Field'].tolist() SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead

jpp Over a year ago

@Deepak..G, There might be nothing wrong with this. It's a warning, not an error. If you google this error, you'll probably find lots of reasons but no guaranteed solution.

Collectives™ on Stack Overflow

Adding column in dataframes in Pandas using data in existing Dataframe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related