Worksheets › Class 12 › Data Handling using Pandas – II › Cheat sheet
Class 12 Data Handling using Pandas – II: cheat sheet
CBSE Informatics Practices. Analysing data with pandas: descriptive statistics, sorting, groupby, missing values with count, isnull, dropna and fillna, and moving data between pandas and MySQL.
Key ideas
- sum(), max(), min() and count() of whole numbers stay whole; mean() and median() always give a float (78.0).
- median() is the middle sorted value (the mean of the middle two if the count is even); mode() the commonest; std() the spread.
- sort_values(by='Marks') puts the rows lowest first (ascending=False: highest first); each row keeps its label.
- df.groupby('City')['Marks'].sum() gives one value per group, with the group labels in sorted order.
- A missing value (shown by pandas as Not a Number) makes its column a float column, so its sum prints like 225.0.
- count(), sum() and mean() skip missing values; isnull().sum() counts them; dropna() drops each row with one; fillna(0) fills in 0.
- pd.read_sql() reads an SQL query’s result into a DataFrame; df.to_sql() writes a DataFrame into a database table.
Remember
| mean() | total ÷ number of values present: (74 + 25 + 21 + 60 + 45) ÷ 5 = 45.0 |
| dropna().shape | (rows with no missing value, the same number of columns) |
| fillna(0).sum() | the same total as sum(), printed as a float |
| groupby | df.groupby('Store')['Price'].max(); adding ['North'] picks one group |
| Range of a column | df['Marks'].max() - df['Marks'].min() |
| describe() | count, mean, std, min, quartiles and max of each numeric column |
Watch out
- Writing a mean as a whole number: 210 ÷ 5 prints as 42.0.
- Dividing by every row when some values are missing: mean() divides by the values present.
- Expecting groupby to keep the order of the rows: the group labels come out sorted.
- Thinking dropna() removes only the missing cell: it removes the whole row.
Worked example
DataFrame df: index | Item | Store | Price | Stock P1 | Eraser | South | — | 90 P2 | Diary | North | 74 | 50 P3 | Pen | East | 25 | 27 P4 | Notebook | East | 21 | 56 P5 | Folder | East | 60 | 72 P6 | Crayons | North | 45 | 75 (— marks a missing value; pandas prints it as Not a Number.) Write the output of: (a) print(df['Price'].count()) (b) print(df['Price'].sum()) (c) print(df.dropna().shape)
Working: (a) and (b) skip missing values, and the column is a float column; (c) drops the rows with a missing value
Answer: (a) 5; (b) 225.0; (c) (5, 4)
Practise Data Handling using Pandas – II
Revise with this page, then practise: every Data Handling using Pandas – II sheet in the app is new, with an answer key and working.
Coming soon for Android and iPhone.