如何使用与excel fi中的串联数据相关的值创建列问题的回答

如何使用与excel fi中的串联数据相关的值创建列

回答此问题可获得 20 贡献值，回答如果被采纳可获得 50 分。

我创建了一个代码，用一个<code>excel</code>文件将同名但在不同表中的列连接起来 这是我的最新代码 <pre><code>import os import pandas as pd import numpy as np mydir = (os.getcwd()).replace('\\', '/') + '/' cluster_cell = pd.read_excel(r'' + mydir + 'Cluster_Cell.xlsx', usecols=['CELLNAME'], encoding='latin-1') GUL_EP_GSM = pd.read_excel(r'' + mydir + 'GU EP20191016171519.xlsx', sheet_name="GSM_CDDData", usecols=['CELLNAME'], encoding='latin-1') print(GUL_EP_GSM) GUL_EP_UMTS = pd.read_excel(r'' + mydir + 'GU EP20191016171519.xlsx', sheet_name="UMTS_CDDData", usecols=['CELLNAME'], encoding='latin-1') print(GUL_EP_UMTS) GUL_EP_LTE = pd.read_excel(r'' + mydir + 'GU EP20191016171519.xlsx', sheet_name="LTE_CDDData", usecols=['CELLNAME'], encoding='latin-1') print(GUL_EP_LTE) appended_cell_name = pd.DataFrame({'CELLNAME': cluster_cell['CELLNAME'] + GUL_EP_GSM['CELLNAME'] + GUL_EP_UMTS['CELLNAME'] + GUL_EP_LTE['CELLNAME']}) mask1 = appended_cell_name['CELLNAME'].isin(GUL_EP_GSM['CELLNAME']) mask2 = appended_cell_name['CELLNAME'].isin(GUL_EP_UMTS['CELLNAME']) mask3 = appended_cell_name['CELLNAME'].isin(GUL_EP_LTE['CELLNAME']) cond=[mask1,mask2,mask3] values=['GSM','UMTS','LTE'] appended_cell_name['system']=np.select(cond,values) GUL_EP_GSM['CELLNAME'] GUL_EP_UMTS['CELLNAME'] GUL_EP_LTE['CELLNAME'] appended_cell_name['system'].unique() print(appended_cell_name) appended_cell_name.to_excel("Cluser_Cell_Final.xlsx", index=False) </code></pre> 所以现在我需要为我创建的名为'System<code>contains</code>values<code>related to</code>pandas'的连接对象创建一个列 使它深入。。。。为了示例：- 对于<code>GUL_EP_GSM</code>对象，我想用值<code>GSM</code>向新创建的<code>column</code>添加值；对于<code>GUL_EP_UMTS</code>，我想向新创建的<code>column</code><code>UMTS</code>添加值；对于<code>GUL_EP_LTE</code>，我想向新创建的<code>column</code><code>LTE</code>添加值 如下图所示 这是以前的，只是我的代码 <a href="https://i.stack.imgur.com/BoxyE.png" rel="nofollow noreferrer">before</a> 这是之后我想做的 <a href="https://i.stack.imgur.com/6tQfJ.png" rel="nofollow noreferrer">after</a> 你知道怎么做吗？你知道吗 我需要把我的代码简化成最简单的方式。。。。你知道吗 错误 <pre><code>Traceback (most recent call last): File "C:/Users/mwx825326/PycharmProjects/MyExcelCombine/MyCopyAndPaste.py", line 11, in <module> appended_cell_name=pd.DataFrame({'CELLNAME':cluster_cell + GUL_EP_GSM + GUL_EP_UMTS + GUL_EP_LTE}) File "C:\Users\mwx825326\PycharmProjects\MyExcelCombine\venv\lib\site-packages\pandas\core\frame.py", line 411, in __init__ mgr = init_dict(data, index, columns, dtype=dtype) File "C:\Users\mwx825326\PycharmProjects\MyExcelCombine\venv\lib\site-packages\pandas\core\internals\construction.py", line 257, in init_dict return arrays_to_mgr(arrays, data_names, index, columns, dtype=dtype) File "C:\Users\mwx825326\PycharmProjects\MyExcelCombine\venv\lib\site-packages\pandas\core\internals\construction.py", line 77, in arrays_to_mgr index = extract_index(arrays) File "C:\Users\mwx825326\PycharmProjects\MyExcelCombine\venv\lib\site-packages\pandas\core\internals\construction.py", line 358, in extract_index raise ValueError("If using all scalar values, you must pass an index") ValueError: If using all scalar values, you must pass an index </code></pre> <h2>打印输出</h2> <code>appended_cell_name</code> <pre><code> CELLNAME system 0 3129648.0 0 1 3129652.0 0 2 3129656.0 0 3 3141617.0 0 4 3141621.0 0 ... ... ... 2347 NaN 0 2348 NaN 0 2349 NaN 0 2350 NaN 0 2351 NaN 0 </code></pre> <code>GUL_EP_GSM</code> <pre><code> CELLNAME 0 10169 1 10170 2 10171 3 10349 4 10350 .. ... 124 54237 125 54996 126 54997 127 54998 128 55966 </code></pre> <code>GUL_EP_UMTS</code> <pre><code> CELLNAME 0 1106382 1 1106383 2 1106384 3 1019810 4 1019811 .. ... 262 1102891 263 1102892 264 1018393 265 1018394 266 1018395 </code></pre> <code>GUL_EP_LTE</code> <pre><code> CELLNAME 0 2002388 1 2002389 2 2002390 3 2100118 4 2100119 .. ... 538 2304376 539 2304377 540 2205890 541 2205891 542 2205892 </code></pre> <code>mask1</code>输出 <pre><code>0 False 1 False 2 False 3 False 4 False ... 2347 False 2348 False 2349 False 2350 False 2351 False Name: CELLNAME, Length: 2352, dtype: bool </code></pre> <code>mask2</code>输出 <pre><code>0 False 1 False 2 False 3 False 4 False ... 2347 False 2348 False 2349 False 2350 False 2351 False Name: CELLNAME, Length: 2352, dtype: bool </code></pre> <code>mask3</code>输出 <pre><code>0 False 1 False 2 False 3 False 4 False ... 2347 False 2348 False 2349 False 2350 False 2351 False Name: CELLNAME, Length: 2352, dtype: bool </code></pre> 的输出 <pre><code>print(GUL_EP_LTE.info()) print(GUL_EP_UMTS.info()) print(GUL_EP_LTE.info()) </code></pre> 是 <pre><code>[2352 rows x 2 columns] <class 'pandas.core.frame.DataFrame'> RangeIndex: 543 entries, 0 to 542 Data columns (total 1 columns): CELLNAME 543 non-null int64 dtypes: int64(1) memory usage: 4.3 KB None <class 'pandas.core.frame.DataFrame'> RangeIndex: 267 entries, 0 to 266 Data columns (total 1 columns): CELLNAME 267 non-null int64 dtypes: int64(1) memory usage: 2.1 KB None <class 'pandas.core.frame.DataFrame'> RangeIndex: 543 entries, 0 to 542 Data columns (total 1 columns): CELLNAME 543 non-null int64 dtypes: int64(1) memory usage: 4.3 KB None </code></pre> <code>print(appended_cell_name. info())</code> <pre><code><class 'pandas.core.frame.DataFrame'> Int64Index: 2352 entries, 0 to 2351 Data columns (total 2 columns): CELLNAME 129 non-null float64 system 2352 non-null object dtypes: float64(1), object(1) memory usage: 45.9+ KB None </code></pre>

0 条评论
分类：Python问答

默认排序时间排序

1 个回答

匿名 1天前

　擅长：python、mysql、java

您需要： <pre><code>GUL_EP_GSM=GUL_EP_GSM.astpye(float) GUL_EP_UMTS=GUL_EP_UMTS.astpye(float) GUL_EP_LTE=GUL_EP_LTE.astpye(float) #appended_cell_name['CELLNAME']=appended_cell_name['CELLNAME'].astype(int) #or convert appended_cell_name to int </code></pre> 因为附加的单元格名称['CELLNAME']的类型是float 那么你的代码是： <pre><code>import os import pandas as pd import numpy as np mydir = (os.getcwd()).replace('\\', '/') + '/' cluster_cell = pd.read_excel(r'' + mydir + 'Cluster_Cell.xlsx', usecols=['CELLNAME'], encoding='latin-1') GUL_EP_GSM = pd.read_excel(r'' + mydir + 'GU EP20191016171519.xlsx', sheet_name="GSM_CDDData", usecols=['CELLNAME'], encoding='latin-1') GUL_EP_UMTS = pd.read_excel(r'' + mydir + 'GU EP20191016171519.xlsx', sheet_name="UMTS_CDDData", usecols=['CELLNAME'], encoding='latin-1') GUL_EP_LTE = pd.read_excel(r'' + mydir + 'GU EP20191016171519.xlsx', sheet_name="LTE_CDDData", usecols=['CELLNAME'], encoding='latin-1') #converting to float GUL_EP_GSM=GUL_EP_GSM.astpye(float) GUL_EP_UMTS=GUL_EP_UMTS.astpye(float) GUL_EP_LTE=GUL_EP_LTE.astpye(float) #print dataframes astype float print(GUL_EP_GSM) print(GUL_EP_UMTS) print(GUL_EP_LTE) #creating appended_cell_name dataframe appended_cell_name = pd.DataFrame({'CELLNAME': cluster_cell['CELLNAME'] + GUL_EP_GSM['CELLNAME'] + GUL_EP_UMTS['CELLNAME'] + GUL_EP_LTE['CELLNAME']}) #creating mask with isin mask1 = appended_cell_name['CELLNAME'].isin(GUL_EP_GSM['CELLNAME']) mask2 = appended_cell_name['CELLNAME'].isin(GUL_EP_UMTS['CELLNAME']) mask3 = appended_cell_name['CELLNAME'].isin(GUL_EP_LTE['CELLNAME']) #using np.select cond=[mask1,mask2,mask3] values=['GSM','UMTS','LTE'] appended_cell_name['system']=np.select(cond,values) #print appended_cell_name print(appended_cell_name) #save in excell appended_cell_name.to_excel("Cluser_Cell_Final.xlsx", index=False) </code></pre> <hr/> 你有三个数据帧叫做：GUL\u EP\u GSM，GUL\u EP\u UMTS，GUL\u EP\u LTE。那你需要和<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.isin.html" rel="nofollow noreferrer">Series.isin</a>核对一下如果附加的单元格名称数据框的单元格名称列中的值位于每个数据框的列中，则相应地填写系统列。你知道吗 唯一需要更改我的解决方案的是每个示例中列的标签： <code>GUL_EP_GSM['name_of_colum_in_your data']</code>而不是 <pre><code>GUL_EP_GSM ['GUL_EP_GSM'] </code></pre> <hr/> 使用<a href="https://docs.scipy.org/doc/numpy/reference/generated/numpy.select.html" rel="nofollow noreferrer">np.select</a>+<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.isin.html" rel="nofollow noreferrer">Series.isin</a>： 准备数据 <pre><code>GUL_EP_GSM=pd.DataFrame({'CELLNAME':[53119,53120,53121,54438,54439,54440,55976,55977,55978]}) GUL_EP_UMTS=pd.DataFrame({'CELLNAME':[41773,41774,41775,44620,44621,44622]}) GUL_EP_LTE=pd.DataFrame({'GELLNAME':[2303909,2303923,2303924]}) appended_cell_name=pd.DataFrame({'CELLNAME':[53119,53120,53121,54438,54439,54440,55976,55977,55978,41773,41774,41775,44620,44621,44622,2303909,2303923,2303924]}) </code></pre> <hr/> 创建遮罩 <pre><code>mask1=appended_cell_name['CELLNAME'].isin(GUL_EP_GSM['CELLNAME']) mask2=appended_cell_name['CELLNAME'].isin(GUL_EP_UMTS['CELLNAME']) mask3=appended_cell_name['CELLNAME'].isin(GUL_EP_LTE['CELLNAME']) </code></pre> <blockquote> Solve with <a href="https://docs.scipy.org/doc/numpy/reference/generated/numpy.select.html" rel="nofollow noreferrer">np.select</a> </blockquote> 然后应用<a href="https://docs.scipy.org/doc/numpy/reference/generated/numpy.select.html" rel="nofollow noreferrer">np.select</a>： <pre><code>cond=[mask1,mask2,mask3] values=['GSM','UMTS','LTE'] appended_cell_name['system']=np.select(cond,values) print(appended_cell_name) </code></pre> <hr/> <blockquote> Also you can use <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.mask.html" rel="nofollow noreferrer">Series.mask</a>(This is other solution): </blockquote> <pre><code>appended_cell_name['system']= ( appended_cell_name['CELLNAME'].mask(mask1,'GSM') .mask(mask2,'UMTS') .mask(mask3,'LTE') ) print(appended_cell_name) </code></pre> <hr/> 输出： <pre><code> CELLNAME system 0 53119 GSM 1 53120 GSM 2 53121 GSM 3 54438 GSM 4 54439 GSM 5 54440 GSM 6 55976 GSM 7 55977 GSM 8 55978 GSM 9 41773 UMTS 10 41774 UMTS 11 41775 UMTS 12 44620 UMTS 13 44621 UMTS 14 44622 UMTS 15 2303909 LTE 16 2303923 LTE 17 2303924 LTE </code></pre> 使用的数据帧： <pre><code>print(GUL_EP_GSM) GUL_EP_GSM 0 53119 1 53120 2 53121 3 54438 4 54439 5 54440 6 55976 7 55977 8 55978 </code></pre> <hr/> <pre><code>print(GUL_EP_UMTS) CELLNAME 0 41773 1 41774 2 41775 3 44620 4 44621 5 44622 </code></pre> <hr/> <pre><code>print(GUL_EP_LTE) CELLNAME 0 2303909 1 2303923 2 2303924 </code></pre>

如何使用与excel fi中的串联数据相关的值创建列

1 个回答

相关Python问题