Pandas从s3读取excel文件，并应用一些操作并将文件写入相同的位置

Question

我正在使用pandas从s3读取excel文件，我将在其中一列中做一些操作，并在同一位置写入新版本。基本上新版本会覆盖原来的版本。

与CSV文件，我能够实现使用下面的代码，但不确定excel(.xlsx)。谁来帮帮我。

key = source_path + folder_name + "/" + file_name
s3 = boto3.client('s3')
obj = s3.get_object(Bucket=bucketname, Key=key)
csv_buf = StringIO()
df = pd.read_csv(obj['Body'])
df["col1"] = df["col1"] * 100
df.to_csv(csv_buf, index=False)
csv_buf.seek(0)
s3.put_object(Bucket=bucketname, Body=csv_buf.getvalue(), Key=key)

我试图使用相同的代码使用read_excel和to_excel得到UnsupportedOperation: seek error.

key = source_path + folder_name + "/" + file_name
s3 = boto3.client('s3')
obj = s3.get_object(Bucket=bucketname, Key=key)
csv_buf = StringIO()
df = pd.read_excel(obj['Body'])
df["col1"] = df["col1"] * 100
df.to_excel(csv_buf, index=False)
csv_buf.seek(0)
s3.put_object(Bucket=bucketname, Body=csv_buf.getvalue(), Key=key)

错误信息:

---------------------------------------------------------------------------
UnsupportedOperation                      Traceback (most recent call last)
<ipython-input-298-9363cf7d6609> in <module>
6         obj = s3.get_object(Bucket=raw_bucket, Key=key)
7         csv_buf = StringIO()
----> 8         df = pd.read_excel(obj['Body'])
9         df["patient_ID"] = df["patient_ID"] * 100
10         df.to_excel(csv_buf, index=False)
~anaconda3libsite-packagespandasutil_decorators.py in wrapper(*args, **kwargs)
297                 )
298                 warnings.warn(msg, FutureWarning, stacklevel=stacklevel)
--> 299             return func(*args, **kwargs)
300 
301         return wrapper
~anaconda3libsite-packagespandasioexcel_base.py in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)
334     if not isinstance(io, ExcelFile):
335         should_close = True
--> 336         io = ExcelFile(io, storage_options=storage_options, engine=engine)
337     elif engine and engine != io.engine:
338         raise ValueError(
~anaconda3libsite-packagespandasioexcel_base.py in __init__(self, path_or_buffer, engine, storage_options)
1069                 ext = "xls"
1070             else:
-> 1071                 ext = inspect_excel_format(
1072                     content=path_or_buffer, storage_options=storage_options
1073                 )
~anaconda3libsite-packagespandasioexcel_base.py in inspect_excel_format(path, content, storage_options)
951     ) as handle:
952         stream = handle.handle
--> 953         stream.seek(0)
954         buf = stream.read(PEEK_SIZE)
955         if buf is None:
UnsupportedOperation: seek

示例文件

。没有patient_id1 1002 200年3 300400年4500

Answer 1

尝试下面的解决方案，它对我有效。现在您可以直接传递文件的s3位置。

key = source_path + folder_name + "/" + file_name
path = "s3://" + bucket_name + "/" + key
if target_file[num].endswith(".xlsx"):
df = pd.read_excel(path)
df["col1"] = df["col1"] * 100
df.to_excel(path)
elif target_file[num].endswith(".csv"):
df = pd.read_csv(path)
df["col1"] = df["col1"] * 100
df.to_csv(path)