Create a new field within a table and enter the value of another but taking the value of a previous row

Question

Create a new field within a table and enter the value of another but taking the value of a previous row

Navigation

#1 by (0 votes)

0

I would like to do the following: I have a table with several fields 'IdActive, Date, Close' and I want to add another new field 'Closure_1' which I want to introduce the values that are in the Closing field but taking the value before the record in which I am. Here I put an image of the data of my table and I have put the field that I would like to create with the resulting data to see if you could help me.

Here I put the code that I am using:

import pyodbc
import pandas as pd
import numpy as np

df = pd.DataFrame({'IdActivo': [1,2,3,3,2,1,3],
                   'Fecha' : ['2009-01-01','2009-02-01','2009-02-01','2009-03-01','2009-03-01','2009-03-01','2009-04-01'],
                   'Cierre' : [25.5,26.5,25.8,26.8,24.8,27.5,27.8]})


def Cierre1(df):
    ord_df = df.sort_values(by=['IdActivo', 'Fecha'])
    print(ord_df)


Cierre1(df)

I have sorted the table by the IdActive and Date fields.

Waiting for your answer I say goodbye to you

Charo

I have continued working on it and I have put the following:

def Cierre1(df):
    ord_df = df.sort_values(by=['IdActivo', 'Fecha'])
    ord_df ['Cierre1'] = ord_df.iloc[1:]['Cierre']
    idx = ord_df.iloc[1:]['IdActivo'].values != ord_df.iloc[:len(ord_df) - 1]['IdActivo'].values
    idx = np.append([True], idx)
    ord_df.loc[idx, 'Cierre1'] = np.nan
    print(ord_df)

Cierre1(df)

the result you give me now is the following

python pandas

asked by Charoeci1 20.01.2017 в 13:21

source

1 answer

How to upload a web application on a free server? [closed] Will it be possible to transform my latitude and longitude to a zip code usa?

score 0 · Answer 1

I have already solved my problem. Here I tell you how I have done it. I have created a variable NumIntervalo to put the interval number that I want to show. This is the code:

import pyodbc
import pandas as pd
import numpy as np

df = pd.DataFrame({'IdActivo': [1,2,3,3,2,1,1,3,2],
                   'Fecha' : ['2009-01-01','2009-02-01','2009-02-01','2009-03-01','2009-03-01','2009-03-01','2009-04-01','2009-04-01','2009-04-01'],
                   'Cierre' : [25.5,26.04,88.8,26.8,24.8,27.5,23.05,27.8,30.20]})

NumIntervalo =2 # Me he creado una variable para poner el número de intervalo que quiero mostrar. Así sólo con cambiar el valor aquí no tendré que modificar el procedimiento.

def Cierre1(df,NumIntervalo):
    ord_df = df.sort_values(by=['IdActivo', 'Fecha'])
    ord_df ['Cierre1'] = ord_df['Cierre'].shift(NumIntervalo)
    idx = ord_df.iloc[NumIntervalo:]['IdActivo'].values != ord_df.iloc[:len(ord_df) - NumIntervalo]['IdActivo'].values
    for i in range(NumIntervalo):
        idx = np.append([False], idx)


    ord_df.loc[idx, 'Cierre1'] = np.nan
    print(ord_df)

Cierre1(df,NumIntervalo)

Here I put an image with the result.