How to modify (List of string data to single string) each element of data series using python, pandas?

user_14435465 0
Viewed 135

How to change each data inside the data series, let me explain in detail. I have a cricket match dataset and I want to find out how many times two teams play against each other from the whole dataset.

team_names                    total_count

['Afghanistan','Bangladesh']    1

enter image description here

I got a result where 'Afghanistan' and 'Bangladesh' play 1 time against each other. But here I want to modify this data frame. Instead of showing ['Afghanistan', 'Bangladesh'], I want to display data like:

team_names                total_count

'Afghanistan-Bangladesh'    1

How to do this?

(I have tried:

1) df["team_names1"] = df['team_names'].str.join("-")

2) df['team_names1'] = df['team_names'].apply(lambda x: '-'.join(x))

3) df['team_names1'] = df['team_names'].apply(lambda x: '-'.join(x),1)

but none of them gives me correct output. All the above code produce the same output:

team_names                                           total_count
[-'-A-f-g-h-a-n-i-s-t-a-n-'-,- -'-B-a-n-g-l-a-..]       1
pandas dataframe list python string
2 Answers

If the values of team_names is a list of strings, use:

df["team_names"] = df.team_names.str.join("-")

result:

               team_names  total_count
0  Afghanistan-Bangladesh            1

If the values are actually string literals instead, your best option is to convert them to the desired data type (lists of strings) first with ast.literal_eval:

import ast
df["team_names"] = df.team_names.apply(ast.literal_eval)
df["team_names"] = df.team_names.str.join("-")

result:

               team_names  total_count
0  Afghanistan-Bangladesh            1
user_50329410

Edit: I have re-created the DataFrame for clarity

df = pd.DataFrame({'team_names' : [['Afghanistan','Bangladesh'], ['Afghanistan','England']], 'total_count': [1,2] })
df['team_names'] = df['team_names'].apply(lambda x: str(x))
df

Output:

    team_names                      total_count
0   ['Afghanistan', 'Bangladesh']   1
1   ['Afghanistan', 'England']      2

Beware! The column 'team_names' are list look-alike, but they are actually string. To convert look-alike string back to list, use eval()

df['team_names'] = df['team_names'].apply(lambda x: '-'.join(eval(x)))    #NOT safe!

NOTE: Although this works, it's safer to use ast.literal_eval as suggested by @anon01

import ast
df['team_names'] = df['team_names'].apply(lambda x: '-'.join(ast.literal_eval(x)))    #use this, safer!

Output:

    team_names              total_count
0   Afghanistan-Bangladesh  1
1   Afghanistan-England     2
user_123706870
Related