I have a data frame (survey data) called df that looks like this (this is sample data):
| respondent_id | r1age | r2age | r3age | r4age | r1smoke | r2smoke | r3smoke | r4smoke | r1income | r2income | r3income | r4income |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 16178 | 35 | 38 | 41 | 44 | 1 | 1 | 1 | 1 | 60 | 62 | 68 | 70 |
| 161719 | 65 | 68 | 71 | 74 | 0 | 0 | 0 | 1 | 50 | 52 | 54 | 56 |
| 161720 | 47 | 50 | 53 | 56 | 0 | 1 | 0 | 1 | 80 | 82 | 85 | 87 |
The number after the “r” represents the wave or period of each interview. There are only four interviews for each respondent, and data for 3 different variables (age, whether the respondent smokes, and his/her gross annual income in $10,000).
I’m interested in transforming this to get the following instead:
| respondent_id | t_1_period | t_age | t_1_age | t_smoke | t_1_smoke | t_income | t_1_income |
|---|---|---|---|---|---|---|---|
| 16178 | 1 | 38 | 35 | 1 | 1 | 62 | 60 |
| 16178 | 2 | 41 | 38 | 1 | 1 | 68 | 62 |
| 16178 | 3 | 44 | 41 | 1 | 1 | 70 | 68 |
| 161719 | 1 | 68 | 65 | 0 | 0 | 52 | 50 |
| 161719 | 2 | 71 | 68 | 0 | 0 | 54 | 52 |
| 161719 | 3 | 74 | 71 | 1 | 0 | 56 | 54 |
| 161720 | 1 | 50 | 47 | 1 | 0 | 82 | 80 |
| 161720 | 2 | 53 | 50 | 0 | 1 | 85 | 82 |
| 161720 | 3 | 56 | 53 | 1 | 0 | 87 | 85 |
I’m interested in repeating the respondents such that the number of observations for each respondent are the number of interviews/waves – 1 (that is, the unique transitions), and for each variable there must be t (current period) and t_1 (previous period) columns, again, for each transition. Additionally, I add a t_1_period column representing the number of the previous period for that observation.
I have tried the following:
df = pd.melt(df, id_vars=["respondent_id"])
variable_names = ["age", "smoke", "income"]
new_rows = []
for respondent_id in df["respondent_id"].unique():
df_temp = df[df["respondent_id"] == respondent_id]
for i in range(2, 5):
new_row = {"respondent_id": respondent_id, "t_1_period": i-1}
for var in variable_names:
if var not in ["income"]:
current_var = f"r{i}{var}"
previous_var = f"r{i-1}{var}"
new_row[f"t_{var}"] = df_temp[df_temp["variable"] == current_var]["value"].values[0]
new_row[f"t_1_{var}"] = df_temp[df_temp["variable"] == previous_var]["value"].values[0]
elif var == "income":
current_var = f"h{i}{var}"
previous_var = f"h{i-1}{var}"
new_row[f"t_h{var}"] = df_temp[df_temp["variable"] == current_var]["value"].values[0]
new_row[f"t_1_h{var}"] = df_temp[df_temp["variable"] == previous_var]["value"].values[0]
new_rows.append(new_row)
df_periods = pd.DataFrame(new_rows)
In my real data, I have much more than 3 variables: I sometimes have up to 100. Additionally, all variables are always present for all periods, however some of then can have NaNs, but the columns are there. In terms of respondents, I can also have a lot: as much as 50,000 for example. Note that some variables start with “h” instead of “r”, and others with “s” (not present in this example).
My question: is there a faster way of transforming this? Every time I want to transform the data in this t vs. t-1 version for all variables I decide to include in variable_names I have to wait a lot. I believe there must be a better way of doing this. I appreciate your help, thank you.