python - 如何将横向excel数据更改为表格格式

Question

我想使用 Python 操作 excel 数据，请帮助我...

Excel 数据如下所示：

Script_name     Homepage    Signinlink  signin
 SC-001           1              1        1
 SC-002           1              1        0

预期结果：

Transaction_Name    Count
SC-001_Homepage       1
SC-001_Signinlink     1
SC-001_signin         1
SC-002_Homepage       1
SC-002_Signinlink     1
SC-002_signin         0

我将每个脚本编号与事务名称连接起来，并在计数列下显示每个事务的计数。我正在使用 Xlrd 来阅读 Excel。提前致谢。

标签： python

Answer 1

根据您的查询，以下脚本可能会有所帮助。

import pandas as pd
import csv

# data =  {'Script_name':['SC-001', 'SC-002'], 'Homepage': [1, 1] , 'Signinlink': [1, 1], 'signin': [1, 0]}
# data = pd.DataFrame(data, columns=data.keys())

# To read excel file
data = pd.read_excel(r"Excel File Path", sheet_name='Name of Sheet which contains data', dtype=object)

df = pd.melt(data, id_vars=['Script_name'], value_name='Count')
df['Transaction_Name'] = df['Script_name'] + '_' + df['variable']

print(df)

  Script_name    variable  Count   Transaction_Name
0      SC-001    Homepage      1    SC-001_Homepage
1      SC-002    Homepage      1    SC-002_Homepage
2      SC-001  Signinlink      1  SC-001_Signinlink
3      SC-002  Signinlink      1  SC-002_Signinlink
4      SC-001      signin      1      SC-001_signin
5      SC-002      signin      0      SC-002_signin

Final_df = df[['Transaction_Name', 'Count']]
print(Final_df)

Final_df.to_csv(r'Destination Path', index=False)

导入和比较：-

# Sample DS Created:

df1 =  {'Transaction_Name':['SC-001_Homepage', 'SC-002_Homepage', 'SC-001_Signinlink'], 'Count': [1, 1, 2]}
df1 = pd.DataFrame(df1, columns=df1.keys())

df2 =  {'Transaction_Name':['SC-001_Homepage', 'SC-002_Homepage', 'SC-001_Signinlink'], 'Count': [2, 1, 2]}
df2 = pd.DataFrame(df2, columns=df2.keys())

'''
# You can import csv

df1 = pd.read_csv(r"First CSV File Path", dtype=object)
df2 = pd.read_csv(r"Second CSV File Path", dtype=object)

'''

print('\n', df1)
print('\n',df2)

df1['Compare'] = df1['Transaction_Name'] + df1['Count'].astype(str)
df2['Compare'] = df2['Transaction_Name'] + df2['Count'].astype(str)

print('\n', df1.loc[~df1['Compare'].isin(df2['Compare'])])