I currently have a data frame column, that is a list for each group and I am looking to add a "/" in between each value of each list. Below is the code I have this far.
test = df_MTM.groupby(['Underlying','Contract Month'])['Trade Price'].apply(list).reset_index()
test = "/".join(test)
The output will look like
Client.Underlying Contract Month Trade Price
0 Henry Hub 2022-02-01 [0.0, 0.0, 0.128, 3.145]
1 Henry Hub 2022-03-01 [0.0, 0.0, 0.128, 2.939]
2 Henry Hub 2022-04-01 [0.0, 0.0, 0.128, 2.518]
3 Henry Hub 2022-05-01 [0.0, 0.0, 0.128, 2.46]
4 Henry Hub 2022-06-01 [0.0, 0.0, 0.128, 2.489]
.. ... ... ...
77 NYMEX WTI 2022-05-01 [54.53]
78 NYMEX WTI 2022-06-01 [54.53]
79 NYMEX WTI 2022-07-01 [54.53]
80 NYMEX WTI 2022-08-01 [54.53]
81 NYMEX WTI 2022-09-01 [54.53]
The goal would be for it to output something like this for each list, the goal would be for them to output them like
0.0/0.0/0.128/3.145
I have used "/".join(test) in the past but I am not having luck with this method.
>Solution :
As far as I can see, there’s no reason to make them lists in the first place. You can convert the floats to str directly then join:
(
df_MTM
.groupby(['Client.Underlying', 'Contract Month'])
['Trade Price']
.apply(lambda s: '/'.join(s.astype(str)))
.reset_index()
)
Output:
Client.Underlying Contract Month Trade Price
0 Henry Hub 2022-02-01 0.0/0.0/0.128/3.145
1 Henry Hub 2022-03-01 0.0/0.0/0.128/2.939
2 Henry Hub 2022-04-01 0.0/0.0/0.128/2.518
3 Henry Hub 2022-05-01 0.0/0.0/0.128/2.46
4 Henry Hub 2022-06-01 0.0/0.0/0.128/2.489
5 NYMEX WTI 2022-05-01 54.53
6 NYMEX WTI 2022-06-01 54.53
7 NYMEX WTI 2022-07-01 54.53
8 NYMEX WTI 2022-08-01 54.53
9 NYMEX WTI 2022-09-01 54.53