Joining excel rows to a single string to be used in pandas DataFrame

Question

I am new to Pandas.

I have and excel file with 10 sheets in it. I am trying to achieve this.

As no answers were provided on that question I am going to use this method to check if a string in a DataFrame row contains a word from excel sheet:

file = pd.read_excel(open('config_values.xlsx', 'rb'),
                     sheet_name='ContainsFree')

df['Contains Language'] = df.Search_Query.str.contains(first_sheet, regex=True)

However, when I use '|'.join(file) I get the first row of the excel sheet rather than the joined string:

excel_sheet_1

gratuit
free
gratis
...

After '|'join.(file) I get:

gratuit

Expected:

gratuit|free|gratis

What am I doing wrong in order to join all rows in an excel sheet?

Thank you for your suggestions.

Thank you, but still I cant join all the rows in to a single string using `'|'.join(file)`, I only get the first entry of the sheet. — Jonas Palačionis, Jan 06 '20 at 08:03

luigigi · Accepted Answer · 2020-01-06T08:31:34.660

1

Try:

file = pd.read_excel('config_values.xlsx', sheet_name='ContainsFree', header=None)
'|'.join(file[0].astype(str))

'gratuit|free|gratis'

edited Jan 06 '20 at 08:31

answered Jan 06 '20 at 08:05

luigigi

That worked, just added `file = '|'.join(file[0])`, thank you. – Jonas Palačionis Jan 06 '20 at 08:07
Would you mind helping with joining `ints`? I get an error: `TypeError: sequence item 0: expected str instance, int found`, when I use `containsYear = '|'.join(str(containsYear[0]))` I get `0| | | | | |2|0|1|0|`. When I try to join `2001,2002,2003,...` – Jonas Palačionis Jan 06 '20 at 08:27
1

@JonasPalačionis I edited my answer. Try to add `astype(str)` – luigigi Jan 06 '20 at 08:31

1 Answers1