首先，导入数据集：

1.自动填充

数据表：
7.pandas实现Excel的自动填充功能 - 图1

首先，导入数据集：

Books.xlsx

  1. import pandas as pd
  3. books = pd.read_excel('tmp\Books.xlsx', skiprows=3, usecols='C:F')
  4. # 因为原数据集前三行是空的，所以我们使用skiprows=3 来跳过前三行，
  5. # 数据集的列是从C-F，所以我们用usecols=‘C:F'来取到正确的列
  1. 如何让其自动生成ID？
  1. # 通过for循环
  2. books = pd.read_excel('tmp\Books.xlsx', skiprows=3, usecols='C:F', dtype={'ID': str})
  3. for i in books.index:
  4.     books['ID'].at[i] = i + 1
  6.     # books.at[i, 'ID'] = i + 1
  7. print(books)
  9. """
  10.     ID      Name  InStore  Date
  11. 0    1  Book_001      NaN   NaN
  12. 1    2  Book_002      NaN   NaN
  13. 2    3  Book_003      NaN   NaN
  14. 3    4  Book_004      NaN   NaN
  15. 4    5  Book_005      NaN   NaN
  16. 5    6  Book_006      NaN   NaN
  17. 6    7  Book_007      NaN   NaN
  18. 7    8  Book_008      NaN   NaN
  19. 8    9  Book_009      NaN   NaN
  20. 9   10  Book_010      NaN   NaN
  21. 10  11  Book_011      NaN   NaN
  22. 11  12  Book_012      NaN   NaN
  23. 12  13  Book_013      NaN   NaN
  24. 13  14  Book_014      NaN   NaN
  25. 14  15  Book_015      NaN   NaN
  26. 15  16  Book_016      NaN   NaN
  27. 16  17  Book_017      NaN   NaN
  28. 17  18  Book_018      NaN   NaN
  29. 18  19  Book_019      NaN   NaN
  30. 19  20  Book_020      NaN   NaN
  31. """
  1. 规定索引可以除2的InStore为’Yes’,否则为’No’
  1. books = pd.read_excel('tmp\Books.xlsx', skiprows=3, usecols='C:F', dtype={'InStore': str})
  2. for i in books.index:
  3.       books.at[i, 'InStore'] = 'Yes' if i % 2 == 0 else 'No'
  4. print(books)
  6. """
  7.     ID      Name InStore  Date
  8. 0  NaN  Book_001     Yes   NaN
  9. 1  NaN  Book_002      No   NaN
  10. 2  NaN  Book_003     Yes   NaN
  11. 3  NaN  Book_004      No   NaN
  12. 4  NaN  Book_005     Yes   NaN
  13. 5  NaN  Book_006      No   NaN
  14. 6  NaN  Book_007     Yes   NaN
  15. 7  NaN  Book_008      No   NaN
  16. 8  NaN  Book_009     Yes   NaN
  17. 9  NaN  Book_010      No   NaN
  18. 10 NaN  Book_011     Yes   NaN
  19. 11 NaN  Book_012      No   NaN
  20. 12 NaN  Book_013     Yes   NaN
  21. 13 NaN  Book_014      No   NaN
  22. 14 NaN  Book_015     Yes   NaN
  23. 15 NaN  Book_016      No   NaN
  24. 16 NaN  Book_017     Yes   NaN
  25. 17 NaN  Book_018      No   NaN
  26. 18 NaN  Book_019     Yes   NaN
  27. 19 NaN  Book_020      No   NaN
  28. """

2.函数填充

Books (1).xlsx
7.pandas实现Excel的自动填充功能 - 图2
首先，导入数据集：

  1. import pandas as pd
  3. books = pd.read_excel('tmp\Books (1).xlsx', index_col='ID')
  4. # index_col = 'ID'设置index以ID列

1.求出价格Price(Price = ListPrice * Discount )

  1. books['Price'] = books['ListPrice'] * books['Discount']
  2. print(books)
  3. """
  4.         Name  ListPrice  Discount  Price
  5. ID                                      
  6. 1   Book_001         10       0.5    5.0
  7. 2   Book_002         20       0.5   10.0
  8. 3   Book_003         30       0.5   15.0
  9. 4   Book_004         40       0.5   20.0
  10. 5   Book_005         50       0.5   25.0
  11. 6   Book_006         60       0.5   30.0
  12. 7   Book_007         70       0.5   35.0
  13. 8   Book_008         80       0.5   40.0
  14. 9   Book_009         90       0.5   45.0
  15. 10  Book_010        100       0.5   50.0
  16. 11  Book_011        110       0.5   55.0
  17. 12  Book_012        120       0.5   60.0
  18. 13  Book_013        130       0.5   65.0
  19. 14  Book_014        140       0.5   70.0
  20. 15  Book_015        150       0.5   75.0
  21. 16  Book_016        160       0.5   80.0
  22. 17  Book_017        170       0.5   85.0
  23. 18  Book_018        180       0.5   90.0
  24. 19  Book_019        190       0.5   95.0
  25. 20  Book_020        200       0.5  100.0
  26. """

2.只计算5-15这一段的价格

  1. books = pd.read_excel('tmp\Books (1).xlsx', index_col='ID')
  2. for i in range(5, 16):
  3.     books['Price'].at[i] = books['ListPrice'].at[i] * books['Discount'].at[i]
  4. print(books)
  6. """
  7.         Name  ListPrice  Discount  Price
  8. ID                                      
  9. 1   Book_001         10       0.5    NaN
  10. 2   Book_002         20       0.5    NaN
  11. 3   Book_003         30       0.5    NaN
  12. 4   Book_004         40       0.5    NaN
  13. 5   Book_005         50       0.5   25.0
  14. 6   Book_006         60       0.5   30.0
  15. 7   Book_007         70       0.5   35.0
  16. 8   Book_008         80       0.5   40.0
  17. 9   Book_009         90       0.5   45.0
  18. 10  Book_010        100       0.5   50.0
  19. 11  Book_011        110       0.5   55.0
  20. 12  Book_012        120       0.5   60.0
  21. 13  Book_013        130       0.5   65.0
  22. 14  Book_014        140       0.5   70.0
  23. 15  Book_015        150       0.5   75.0
  24. 16  Book_016        160       0.5    NaN
  25. 17  Book_017        170       0.5    NaN
  26. 18  Book_018        180       0.5    NaN
  27. 19  Book_019        190       0.5    NaN
  28. 20  Book_020        200       0.5    NaN
  29. """

3.把每本书都涨价2元

  1. import pandas as pd
  3. books = pd.read_excel('tmp\Books (1).xlsx', index_col='ID')
  4. # books['ListPrice'] += 2
  5. books['ListPrice'] = books['ListPrice'].apply(lambda x: x + 2)
  6. print(books)
  8. # df.apply(f, axis=0)    将f函数应用到由各行各列所形成的一维数组上
  10. """
  11.         Name  ListPrice  Discount  Price
  12. ID                                      
  13. 1   Book_001         12       0.5    NaN
  14. 2   Book_002         22       0.5    NaN
  15. 3   Book_003         32       0.5    NaN
  16. 4   Book_004         42       0.5    NaN
  17. 5   Book_005         52       0.5    NaN
  18. 6   Book_006         62       0.5    NaN
  19. 7   Book_007         72       0.5    NaN
  20. 8   Book_008         82       0.5    NaN
  21. 9   Book_009         92       0.5    NaN
  22. 10  Book_010        102       0.5    NaN
  23. 11  Book_011        112       0.5    NaN
  24. 12  Book_012        122       0.5    NaN
  25. 13  Book_013        132       0.5    NaN
  26. 14  Book_014        142       0.5    NaN
  27. 15  Book_015        152       0.5    NaN
  28. 16  Book_016        162       0.5    NaN
  29. 17  Book_017        172       0.5    NaN
  30. 18  Book_018        182       0.5    NaN
  31. 19  Book_019        192       0.5    NaN
  32. 20  Book_020        202       0.5    NaN
  33. """