Home > Back-end >  Create columns of relative values with a set starting base value
Create columns of relative values with a set starting base value

Time:03-16

In python, I have a dataframe similar to this:

print(transport)
year    cycles   cars     lorries       
1993    249      21000    1507
1994    249      21438    1539
1995    257      21817    1581
1996    253      22364    1630
1997    253      22729    1668

I would like to look at the change over time for each form of transport, relative to the earliest value i.e. for 1993. I would like to create a new dataframe based on transport where 1993 is the base year with the value set to 100.0 and all subsequent values be relative to that.

I'm really new to python and I can't figure out how to approach this.

CodePudding user response:

You can divide all the values by the corresponding number from 1993 and multiply by 100.0 to get the results -

df = df.set_index('year')
df / df.loc[1993] * 100.0
#           cycles        cars     lorries
# year                                    
# 1993  100.000000  100.000000  100.000000
# 1994  100.000000  102.085714  102.123424
# 1995  103.212851  103.890476  104.910418
# 1996  101.606426  106.495238  108.161911
# 1997  101.606426  108.233333  110.683477

CodePudding user response:

You can use iterrows:

transport.set_index("year", inplace=True)

y1993 = transport[transport.index == 1993]

for index, row in transport.iterrows():
  for column in transport.columns:
    transport.loc[index, column] = ((row[column])/(y1993[column].values[0])) * 100
transport

Output

year cycles cars lorries
1993 100 100 100
1994 100 102.086 102.123
1995 103.213 103.89 104.91
1996 101.606 106.495 108.162
1997 101.606 108.233 110.683
  • Related