python (pandas) creating a new column based on values from different rows

Related searches

I have a data frame from a cvs file looking like this:

     #F      E    G
0     1  n.e.   153
1     1     60   15
2     1     99   10
3     1      S   23
4     2  n.e.   190
5     2     60   44
6     2     99   22
7     2      S   67

I would like to add a new column to this.

For every [#F] group, the [G] value in each row should be divided by the [G]-value in the row where [E]='n.e.' So, in the end, it should look like this:

     #F      E    G     rel
0     1  n.e.   153   1.000
1     1     60   15   0.098
2     1     99   10   0.065
3     1      S   23   0.150
4     2  n.e.   190   1.000
5     2     60   44   0.232
6     2     99   22   0.116
7     2      S   67   0.353

I have tried several approaches using a function, groups or np.where but the problem is a bit more complicated than what I have experience in and nothing works out in the end.

Thanks for your help.

I would use Series.div. Creating a series with the corresponding value of n.e. for each group using Series.where and Groupby.transform.

Note that first, omit the NaN values, so the position of the .ne. value in the group does not matter. We could also use max instead first.

df['Rel']=df['G'].div(df['G'].where(df['E'].eq('n.e.'))
                             .groupby(df['#F']).transform('first'))
print(df)
  #F     E    G       Rel
0  1  n.e.  153  1.000000
1  1    60   15  0.098039
2  1    99   10  0.065359
3  1     S   23  0.150327
4  2  n.e.  190  1.000000
5  2    60   44  0.231579
6  2    99   22  0.115789
7  2     S   67  0.352632

EDIT

df['Rel']=df['G'].div(df['G'].where(df['E'].eq('n.e. '))
                             .groupby(df['#F']).transform('first'))

Create a new column in Pandas DataFrame based on the existing , Create a new column in Pandas DataFrame based on the existing columns. Last Updated: 23-01-2019. While working with data in Pandas, we perform a vast array of operations on the data to get the data in the Let's discuss several ways in which we can do that. df[ 'Discounted_Price' ] = df. apply ( lambda row: row. Create a new column by assigning the output to the DataFrame with a new column name in between the []. Operations are element-wise, no need to loop over rows. Use rename with a dictionary or function to rename row labels or column names.

IIUC transform

df['Rel']=df.G/df.groupby(['name','#F']).G.transform('max')
df
   name  #F     E    G       Rel
0  T.w.   1  n.e.  153  1.000000
1  T.w.   1    60   15  0.098039
2  T.w.   1    99   10  0.065359
3  T.w.   1     S   23  0.150327
4  T.w.   2  n.e.  190  1.000000
5  T.w.   2    60   44  0.231579
6  T.w.   2    99   22  0.115789
7  T.w.   2     S   67  0.352632

method two

s=df.loc[df.E.eq('n.e.')].set_index(['name', '#F']).G.reindex(pd.MultiIndex.from_frame(df[['name', '#F']])).values
df['Rel']=df.G/s

Or

df['Rel']=df.G/df.G.mul(df.E.eq('n.e.')).groupby([df['name'],df['#F']]).transform('max')
df
   name  #F     E    G       Rel
0  T.w.   1  n.e.  153  1.000000
1  T.w.   1    60   15  0.098039
2  T.w.   1    99   10  0.065359
3  T.w.   1     S   23  0.150327
4  T.w.   2  n.e.  190  1.000000
5  T.w.   2    60   44  0.231579
6  T.w.   2    99   22  0.115789
7  T.w.   2     S   67  0.352632

How to create a new column based on values from other columns in , apply() to create a new column based on values from other columns. Call pandas. DataFrame. apply(function, axis=1) using the syntax DataFrame[new_column] = pandas. While working with data in Pandas, we perform a vast array of operations on the data to get the data in the desired form. One of these operations could be that we want to create new columns in the DataFrame based on the result of some operations on the existing columns in the DataFrame.

df.loc[df['E'] == 'n.e.', 'G_ne'] = df['G']
df['G_ne'] = df['G_ne'].fillna(method='ffill')
df['rel'] = df['G'] / df['G_ne']

print(df)

Output:

   #F     E    G   G_ne       rel
0   1  n.e.  153  153.0  1.000000
1   1    60   15  153.0  0.098039
2   1    99   10  153.0  0.065359
3   1     S   23  153.0  0.150327
4   2  n.e.  190  190.0  1.000000
5   2    60   44  190.0  0.231579
6   2    99   22  190.0  0.115789
7   2     S   67  190.0  0.352632

How to create new columns derived from existing columns?, The calculation is again element-wise, so the / is applied for the values in each row. Also other mathematical operators (+, -, *, /) or logical operators (<, >, =,…)� Obinna I.-December 21st, 2019 at 6:22 am none Comment author #28567 on Python: Add column to dataframe in Pandas ( based on other column or list or default value) by thispointer.com Thank you so much for such a powerful blog.

Cookbook — pandas 1.1.1 documentation, Add another line with different logic, to do the -else. In [7]: df.loc[df.AAA < 5, ['BBB' Select rows with data closest to certain value using argsort. In [25]: df = pd. Efficiently and dynamically creating new columns using applymap. In [53]: df = pd . Shift groups of the values in a column based on the index. In [135]: df = pd. In this post we will see two different ways to create a column based on values of another column using conditional statements. First we will use NumPy’s little unknown function where to create a column in Pandas using If condition on another column’s values. Next we will use Pandas’ apply function to do the same.

Many of the links contain expanded information, above what the in-line examples offer. Efficiently and dynamically creating new columns using applymap In [ 69]: df Out[69]: One Two X Y X Y row 0 1.1 1.2 1.11 1.22 1 1.1 1.2 1.11 1.22 2 1.1 1.2 1.11 1.22 Shift groups of the values in a column based on the index. pandas.Series.map() to create new DataFrame columns based on a given condition in Pandas We could also use pandas.Series.map() to create new DataFrame columns based on a given condition in Pandas. This method is applied elementwise for Series and maps values from one column to the other based on the input that could be a dictionary, function

pandas - create new column based on duplicates id and add details to the new column but wondered if there was rows from a DataFrame based on column values? 947.

Comments
  • is the row with n.e. guaranteed to be the first row of each group?
  • unfortunately no, and the groups might also be larger or smaller
  • please consider accept an answer stackoverflow.com/help/someone-answers :)
  • Thank you, but I must be missing something. I am getting a column of NaN values anyway with your method.
  • I think the problem is that your value is n.e. and I am usign n.e..Check section EDIT
  • Thank you. But this will only work if G is also the maximum at E=n.e., right?
  • Maybe I am missing something, but only your first method worked for me in the example df. The second gave me NaN the other inf values in the new column
  • Thanks for your help, but I must be missing something. I am getting two columns of NaN values with your method