This is the first day of my participation in the August More text Challenge. For details, see: August More Text Challenge

The table used in this article is as follows:

Let’s look at the original situation:

Import pandas as pd df = pd.read_excel(r 'c :\Users\admin\Desktop\ test.xlsx ') print(df)Copy the code

result:

Classified Goods Physical store Sales Volume Online Cost of sales Selling Price 0 Fruit Apple 34 234 12 45 1 Home appliance TV 56 784 34 156 2 home appliance refrigerator 78 345 24 785 3 Books Python from getting started to giving up 25 34 13 89 4 Fruit grapes 789 56 7 398Copy the code

1. Non-null counts

The non-null count is the count of the number of non-null values in a certain elm

1.1 Operating the Full Table

1.1.1 Calculate the number of non-null values in each column

Df = pd.read_excel(r'C:\Users\admin\Desktop\ test.xlsx ') print(df.count())Copy the code

result:

Category 5 Goods 5 Physical store sales 5 Online sales 5 Cost 5 Price 5 dtype: int64Copy the code

1.1.2 Find the number of non-null values in each row

Df = pd.read_excel(r'C:\Users\admin\Desktop\ test.xlsx ') print(df.count(axis=1))Copy the code

result:

0    6
1    6
2    6
3    6
4    6
dtype: int64
Copy the code

1.2 Operate on a single row or column

1.2.1 Find the number of non-null values in a single column

Df = pd.read_excel(r'C:\Users\admin\Desktop\ test.xlsx ') print(df[' category '].count())Copy the code

result:

5
Copy the code

1.2.2 Find the number of non-null values in a single row

Df = pd.read_excel(r'C:\Users\admin\Desktop\ test.xlsx ') print(df.iloc[0].count())Copy the code

result:

6
Copy the code

1.3 Operate on multiple rows or columns

1.3.1 Calculate the number of non-null values in multiple columns

Df = pd read_excel (r 'C: \ Users \ admin \ Desktop \ test. XLSX') print (df [[" classification ", "goods"]], the count ())Copy the code

result:

Category 5 Goods 5 DType: INT64Copy the code

1.3.2 Calculate the number of non-null values in multiple rows

Df = pd.read_excel(r'C:\Users\admin\Desktop\ test.xlsx ') print(df.iloc[[0, 1]].count())Copy the code

result:

Category 2 Goods 2 Physical store sales 2 Online sales 2 Cost 2 Price 2 dtype: int64Copy the code

2 sum sum

2.1 Performing Operations on the Full Table

2.1.1 Sum each column

Df = pd.read_excel(r'C:\Users\admin\Desktop\ test.xlsx ') print(df.sum())Copy the code

result:

Category Fruit Home Appliance Home appliance Book Fruit Goods Apple TV Refrigerator Python From entry to abandonment Grape Physical store sales 982 Online sales 1453 Cost 90 Selling price 1473 dType: objectCopy the code

As you can see, the summation of the string type is a string concatenation, and the numeric type is a normal mathematical operation

2.1.2 Sum each row

Df = pd.read_excel(r'C:\Users\admin\Desktop\ test.xlsx ') print(df.sum(axis=1))Copy the code

result:

0     325
1    1030
2    1232
3     161
4    1250
dtype: int64
Copy the code

Looking at the results, we can see that the sum of each line ignores the text character type and only sums the number type. Like the first row of data

Classified goods physical store sales online cost of sales selling price 0 fruit apple 34 234 12 45Copy the code

The top 325 is 34+234+12+45, and so are the other rows

2.2 Operate on a single row or column

2.2.1 Sum a column

Df = pd.read_excel(r'C:\Users\admin\Desktop\ test.xlsx ') print(df[' sales '].sum())Copy the code

result:

982
Copy the code

2.2.2 Sum a row

Df = pd.read_excel(r'C:\Users\admin\Desktop\ test.xlsx ') print(df.iloc[[0]].sum())Copy the code

result:

Category fruit products Apple physical store sales 34 online sales 234 Cost 12 Price 45 dtype: objectCopy the code

Of course, summing a single line doesn’t seem to work

2.3 Perform operations on multiple rows or columns

2.3.1 Summation of multiple columns

Df = pd read_excel (r 'C: \ Users \ admin \ Desktop \ test. XLSX') print (df [[' entity shop sales, "online sales"]], the sum ())Copy the code

result:

Physical store sales 982 online sales 1453 dtype: Int64Copy the code

2.3.2 Sum over multiple rows

Df = pd.read_excel(r'C:\Users\admin\Desktop\ test.xlsx ') print(df.iloc[[0, 1]].sum())Copy the code

result:

Category fruit home appliances Apple TV physical store sales 90 online sales 1018 Cost 46 price 201 dType: objectCopy the code