I have the below dataframe
| salesman | north_access | south_access | east_access | west_access |
|---|---|---|---|---|
| A | 1 | 0 | 0 | 1 |
| B | 0 | 1 | 1 | 1 |
| C | 1 | 0 | 1 | 1 |
I want to convert the above into the below format
| salesman | direction | access |
|---|---|---|
| A | north | 1 |
| A | south | 0 |
| A | east | 0 |
| A | west | 1 |
| B | north | 0 |
| B | south | 1 |
| B | east | 1 |
| B | west | 1 |
I tried exploring the split and transpose function but didnt get the expected results.
Can someone please help with the code to make the above changes in python, thanks in advance.
>Solution :
Another solution (using pd.wide_to_long):
df.columns = [f'access_{c.split("_")[0]}' if "_access" in c else c for c in df.columns]
x = pd.wide_to_long(
df, stubnames="access", suffix=r".*", i=["salesman"], j="direction", sep="_"
).reset_index()
print(x)
Prints:
salesman direction access
0 A north 1
1 B north 0
2 C north 1
3 A south 0
4 B south 1
5 C south 0
6 A east 0
7 B east 1
8 C east 1
9 A west 1
10 B west 1
11 C west 1