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 |