如何将用户数据添加到Excel文件？

Question

我有一个现成的文件与现成的表，我只需要添加数据有2列，请帮助

| Teachers       | Subjects       |
| -------------- | -------------- |
| TeachersName   | SubjectsName   |
| TeachersName   | SubjectsName   |
| TeachersName   | SubjectsName   |
| TeachersName   | SubjectsName   |

def teach_add():
df = pd.read_excel(open('bd1.xlsx','rb'),sheet_name='teachers')
print(df[['Teachers','Subjects']])
mas = []
num = 2
df['Teachers'] = mas
ind  = int(input("Enter index: "))
for i in range(0, num):
    num2 = num2.split(' ')
    mas.append(num2)
print(mas)
df.loc[ind]=[mas]
df.to_excel('bd1.xlsx', sheet_name='teachers')

Answer 1

我不知道你们的档案里有什么，但若你们已经有了行，那个么对于一个老师和一个科目来说，可能会简单得多

def teach_add():
    # read data from file
    df = pd.read_excel('bd1.xlsx', sheet_name='teachers')

    #print(df[['Teachers','Subjects']])
    
    # ask for data
    index  = int(input("Enter index: "))
    teacher = input("Enter teacher: ")
    subject = input("Enter subject: ")

    # add data to dataframe
    df['Teachers'][index] = teacher
    df['Subjects'][index] = subject

    # save data to file
    df.to_excel('bd1.xlsx', sheet_name='teachers')

在这里，代码中包含一些数据的最小工作代码-因此每个人都可以简单地复制它并运行它来查看它是如何工作的

import pandas as pd

df = pd.DataFrame({
            'Class': ['A', 'B', 'C'], 
            'Teachers':['', '', ''], 
            'Subjects':['', '', ''], 
    })

#print(df[['Teachers','Subjects']])
print(df)

index   = 1
teacher = 'Albert Einstein'
subject = 'Physics'

df['Teachers'][index] = teacher
df['Subjects'][index] = subject

index   = 2
teacher = 'Alfred Nobel'
subject = 'Chemistry'

df['Teachers'][index] = teacher
df['Subjects'][index] = subject

#print(df[['Teachers','Subjects']])
print(df)

以前

  Class Teachers Subjects
0     A                  
1     B                  
2     C  

之后

  Class         Teachers   Subjects
0     A                            
1     B  Albert Einstein    Physics
2     C     Alfred Nobel  Chemistry

---

如果你想请更多的老师，那么你应该使用for-loop。你可以问你想增加多少教师

def teach_add():
    # read data from file
    df = pd.read_excel('bd1.xlsx', sheet_name='teachers')

    #print(df[['Teachers','Subjects']])
    
    # how many rows you have in dataframe
    teachers_number = int(input("How many teachers: "))

    #  - loop  -

    for _ in range(teachers_number):

        # ask for data
        index   = int(input("Enter index: "))
        teacher = input("Enter teacher: ")
        subject = input("Enter subject: ")

        # add data to dataframe
        df['Teachers'][index] = teacher
        df['Subjects'][index] = subject


    #  - after loop  -

    # save data to file
    df.to_excel('bd1.xlsx', sheet_name='teachers')

或者，您可以获得有多少空行，并填充所有行，而无需请求索引

def teach_add():
    # read data from file
    df = pd.read_excel('bd1.xlsx', sheet_name='teachers')

    #print(df[['Teachers','Subjects']])
    
    # how many rows you have in dataframe
    rows_number = len(df)

    #  - loop  -

    for index in range(rows_number):

        # ask for data
        teacher = input("Enter teacher: ")
        subject = input("Enter subject: ")

        # add data to dataframe
        df['Teachers'][index] = teacher
        df['Subjects'][index] = subject

    #  - after loop  -

    # save data to file
    df.to_excel('bd1.xlsx', sheet_name='teachers')

如果DataFarme中没有行并且必须追加行，则可能还需要不同的代码

---

编辑：

pandas警告我它可以处理数据副本，我必须使用

df.loc[index, 'Teachers'] = teacher
df.loc[index, 'Subjects'] = subject

而不是

df['Teachers'][index] = teacher
df['Subjects'][index] = subject

我还必须使用read_excel(..., index=False)和to_excel(..., index=False)来跳过添加带有索引的列