CODEWITHSUNDEEP
pythonpython-3.xpandas
Laz Martin
Laz Martin

Reputation: 13

Excel Rows to sentences Python

Let's say I have an Excel file with 5 rows and 2 columns. 

apples      color
honeycrsp   red
gala        red
goldendel   orange
fuji        red
grannys     green

I want to place each of the rows into a repeating sentence sentence. For example I want column1 and column2 to be added to a sentence.

e.g "this apple is honeycrsp with color red"

This is what I have coded so far:

import pandas as pd;

FILE_PATH = "C:\\Users\\apples.xls";
xl = pd.ExcelFile(FILE_PATH);
df = xl.parse('testone');

apples = []
color =[]
apples = list(df['apples'])
color = list(df['color'])

def f(string, n, c=0):
    if c < n:
       print(string)
       f(string, n, c=c + 1)

f('this apple is{0} with color {0}'.format(apples,color), 3)

Desired output:

"this apple is honeycrsp with color red"

"this apple is gala with color red"

"this apple is goldendel with color orange"

"this apple is fuji with color red"

"this apple is grannys with color green"

Upvotes: 1

Views: 249

Answers (2)

pythonweb
pythonweb

Reputation: 1073

import pandas as pd

FILE_PATH = "C:\\Users\\apples.xls"
xl = pd.ExcelFile(FILE_PATH)
df = xl.parse('testone')

apples = list(df['apples'])
colors = list(df['color'])

for apple, color in zip(apples, colors):
    print('this apple is {0} with color {1}'.format(apple, color))

Ouputs:

this apple is honeycrsp with color red
this apple is gala with color red
this apple is goldendel with color orange
this apple is fuji with color red
this apple is grannys with color green

You can put the last two lines in a function if you would like. I think this is a much simpler and readable solution though.

Also, some mistakes to avoid in the future:

  • Never use ; in python
  • putting numbers inside of the {} tags when using .format will cause the arguments to be formatted by index (hence the reason you were getting apples where you wanted colors)

Upvotes: 1

bigbounty
bigbounty

Reputation: 17408

Read the data as a dataframe and use apply

import pandas as pd

data = pd.DataFrame({'apples':["honeycrsp","gala","goldendel","fuji","grannys"],'color':["red","red","orange","red","greeen"]})

def concat(r):
    return return 'this apple is ' + r[0] + ' with color ' + r[1]

data.apply(concat,axis=1)

The above program displays the following

0       this apple is honeycrsp with color red
1            this apple is gala with color red
2    this apple is goldendel with color orange
3            this apple is fuji with color red
4      this apple is grannys with color greeen

If you don't want index to appear

s = data.apply(concat,axis=1)
print(s.to_string(index=False))

that gives you the result

this apple is honeycrsp with color red
this apple is gala with color red
this apple is goldendel with color orange
this apple is fuji with color red
this apple is grannys with color greeen

Upvotes: 0

Related Questions