Home > Blockchain >  How to use "/".join with a column list in a dataframe in Python
How to use "/".join with a column list in a dataframe in Python

Time:10-06

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.

CodePudding user response:

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
  • Related