1

I try to forward a data to excel but received the subject error.

I want to combine some of excel files into one excel file. First I created all_info array in order to collect all data into. 

all_info = []

After that I read every excel files. Excel files follows; sample1, sample2, and so on.

data = pd.read_excel('sample1.xlsx', 'Sheet1')

I add each excel files' data into all_info array with below.

for i in range(0, len(data)):
    all_info.append(data)

After I combine all data within all_info array, I executed below code but received the subject error. 

df = pd.DataFrame(all_info)
df.to_excel("all_info.xlsx")

You may see error details below. 

ValueError                                Traceback (most recent call last)
<ipython-input-85-5bff7d788042> in <module>()
  1 df = pd.DataFrame(all_tweets)
----> 2 df.to_excel("all_info.xlsx")

 /usr/lib/python2.7/dist-packages/pandas/core/frame.pyc in to_excel(self, excel_writer, sheet_name, na_rep, float_format, cols, header, index, index_label, startrow, startcol, engine, merge_cells)
1202         formatted_cells = formatter.get_formatted_cells()
1203         excel_writer.write_cells(formatted_cells, sheet_name,
-> 1204                                  startrow=startrow, startcol=startcol)
1205         if need_save:
1206             excel_writer.save()

/usr/lib/python2.7/dist-packages/pandas/io/excel.pyc in write_cells(self, cells, sheet_name, startrow, startcol)
525             colletter = get_column_letter(startcol + cell.col + 1)
526             xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
--> 527             xcell.value = _conv_value(cell.val)
528             style = None
529             if cell.style:

/usr/lib/pymodules/python2.7/openpyxl/cell.pyc in _set_value(self, value)
339     def _set_value(self, value):
340         """Set the value and infer type and display options."""
--> 341         self.bind_value(value)
342 
343     value = property(_get_value, _set_value,

/usr/lib/pymodules/python2.7/openpyxl/cell.pyc in bind_value(self, value)
278     def bind_value(self, value):
279         """Given a value, infer type and display options."""
--> 280         self._data_type = self.data_type_for_value(value)
281         if value is None:
282             self.set_value_explicit('', self.TYPE_NULL)

/usr/lib/pymodules/python2.7/openpyxl/cell.pyc in data_type_for_value(self, value)
260         elif isinstance(value, (datetime.datetime, datetime.date, datetime.time, datetime.timedelta)):
261             data_type = self.TYPE_NUMERIC
--> 262         elif not value:
263             data_type = self.TYPE_STRING
264         elif isinstance(value, basestring) and value[0] == '=':

/usr/lib/python2.7/dist-packages/pandas/core/generic.pyc in __nonzero__(self)
674         raise ValueError("The truth value of a {0} is ambiguous. "
675                          "Use a.empty, a.bool(), a.item(), a.any() or a.all()."
--> 676                          .format(self.__class__.__name__))
677 
678     __bool__ = __nonzero__

ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
Behzat
  • 121
  • 2
  • 10
  • 1
    Your question is confusing. If 'data' is a pd.DataFrame and you iterate over range(0,len(data)) and then add data to your list 'all_info', you simply add the whole DataFrame 'data' i times to the list. Your DataFrame df = pd.DataFrame(all_info) is a Frame holding i times the DataFrame 'data'. That exporting does not work seems logical... – user2532323 Oct 11 '15 at 21:20
  • @user2532323 Sorry for that. I try to get more clear now. Is it still confusing ? – Behzat Oct 12 '15 at 14:01
  • Are you trying to create a single List with all of the columns of data? Currently, a list of dataframes is being created, one full dataframe for every row in the excel file that you loaded. – leroyJr Oct 12 '15 at 14:10
  • @leroyJr yes, I try to create a single list. I also collect all columns of data with above but I can not forward it into all_info.xlsx file. – Behzat Oct 12 '15 at 14:19
  • Can you provide an example of what your input and output data are supposed to look like? It's not clear what you are actually trying to accomplish. – leroyJr Oct 12 '15 at 14:36

1 Answers1

1

Do you maybe want to do something like this?

fnames = ['sample1.xls', 'sample2.xls']
all_info = [pd.read_excel(f, 'Sheet1') for f in fnames]
df = pd.concat(all_info)
df.to_excel("all_info.xlsx")

Here, all_info is a list of DataFrames, where each elements is holding the different Excel Data. Then they are concatenated into one DataFrame and saved to disk...

user2532323
  • 237
  • 1
  • 2
  • 7