I have this dataframe:
| Index | id | 1-t | 2-t | 3-t |
|---|---|---|---|---|
| 2022-01-06 | 0 | 5 | 4 | 2 |
| 2022-01-05 | 1 | 3 | 5 | 4 |
| 2022-01-04 | 2 | 4 | 3 | 5 |
| 2022-01-03 | 3 | 3 | 0 | 1 |
| 2022-01-02 | 4 | 2 | 3 | 0 |
| 2022-01-01 | 5 | 1 | 2 | 4 |
The value in columns "-t" is the id, and I want to replace this value with the index value. I have 100 columns to replace (n columns -t), I tried using loop FOR with .loc but it did not work.
Does anyone help me?
Output expected:
| Index | id | 1-t | 2-t | 3-t |
|---|---|---|---|---|
| 2022-01-05 | 0 | 2022-01-01 | 2022-01-02 | 2022-01-04 |
| 2022-01-05 | 1 | 2022-01-03 | 2022-01-01 | 2022-01-02 |
| 2022-01-04 | 2 | 2022-01-02 | 2022-01-03 | 2022-01-01 |
| 2022-01-03 | 3 | 2022-01-03 | 2022-01-05 | 2022-01-05 |
| 2022-01-02 | 4 | 2022-01-04 | 2022-01-03 | 2022-01-05 |
| 2022-01-01 | 5 | 2022-01-05 | 2022-01-04 | 2022-01-02 |
CodePudding user response：
for nm in df.columns:
if nm.endswith("-t"):
tmp = df.Index[df.loc[:, nm]]
tmp.index = df.index
df.loc[:, nm] = tmp
df
# Index id 1-t 2-t 3-t
# 0 2022-01-06 0 2022-01-01 2022-01-02 2022-01-04
# 1 2022-01-05 1 2022-01-03 2022-01-01 2022-01-02
# 2 2022-01-04 2 2022-01-02 2022-01-03 2022-01-01
# 3 2022-01-03 3 2022-01-03 2022-01-06 2022-01-05
# 4 2022-01-02 4 2022-01-04 2022-01-03 2022-01-06
# 5 2022-01-01 5 2022-01-05 2022-01-04 2022-01-02
OR
def foo(i, s):
ans = i[s]
ans.index = i.index
return ans
df.apply(lambda x: foo(df.Index, x) if x.name.endswith("-t") else x)
CodePudding user response：
Assuming your data looks like the following:
id 1-t 2-t 3-t
Index
2022-01-06 0 5 4 2
2022-01-05 1 3 5 4
2022-01-04 2 4 3 5
2022-01-03 3 3 0 1
2022-01-02 4 2 3 0
2022-01-01 5 1 2 4
i.e. what you labeled Index in your table above is the actual index of the Pandas dataframe, all you need to do is use the Dataframe.filter routine like so:
for col in data.filter(like='-t'):
data[col] = data.index[data[col]]
print(data)
# id 1-t 2-t 3-t
#Index
#2022-01-06 0 2022-01-01 2022-01-02 2022-01-04
#2022-01-05 1 2022-01-03 2022-01-01 2022-01-02
#2022-01-04 2 2022-01-02 2022-01-03 2022-01-01
#2022-01-03 3 2022-01-03 2022-01-06 2022-01-05
#2022-01-02 4 2022-01-04 2022-01-03 2022-01-06
#2022-01-01 5 2022-01-05 2022-01-04 2022-01-02
there might even be a way to replace all the columns at once. In case Index is just the name of a column, replace data.index by data.Index.
EDIT: I forgot to use the index value in the column. Should work now.
CodePudding user response：
This works for me:
for i in range(len(df)):
df.loc[i,"1-t"]=df.loc[df.loc[i,"1-t"],"Index"]
df.loc[i,"2-t"]=df.loc[df.loc[i,"2-t"],"Index"]
df.loc[i,"3-t"]=df.loc[df.loc[i,"3-t"],"Index"]