Home / Questions / 236116
Accepted
Juan
Asked: 2020-02-08 10:37:04 +0800 CST 2020-02-08 10:37:04 +0800 CST

Show information between two fields (including them) and add a row with the total sums of a column

  • 772

I am using the information from the following file:

To read it and display it I use the following:

import pandas as pd
import chardet
import numpy as np

### Si quieren usar el codigo de ejemplo, la siguiente linea
### tarda bastante en hacerse porque busca la codificación que usa el 
### archivo, ( si usan el archivo reducido es instantáneo)
### {'encoding': 'Windows-1252', 'confidence': 0.73, 'language': ''}

with open('Direccion donde esta el CSV', 'rb') as f:
result = chardet.detect(f.read())

datos = pd.read_csv('Direccion donde esta el archivo', 
encoding=result['encoding'], low_memory=False)
df = pd.DataFrame(datos)
limpiarNaN = df.replace(np.nan, '0')
archivoConvertidoInt = limpiarNaN.describe(include = [np.number])
archivoConvertidoInt = limpiarNaN.replace("NR", "0")
archivoConvertidoInt['Wsets'] = archivoConvertidoInt.Wsets.astype(float)
archivoConvertidoInt['WRank'] = archivoConvertidoInt.WRank.astype(float)
archivoConvertidoInt.set_index("Location", inplace = True)

(archivoConvertidoInt.loc[['Adelaide' , 'St. Petersburg'], 'Series' : 
'Round']
.groupby(["Location", "Series", "Court", "Surface", "Round"]) 
["Series",].count())

This returns me the following:

As you can see, it shows me information about the locations of Adelaide and St. Petersburg.

enter image description here

Now:

1) How can I show the same information but not only from Adelaide and St. Petersburg but from all the locations between them inclusive, if I use : to separate it gives me a syntax error.

enter image description here

and 2) Adding a sum at the end I can know how much is the total number of series, how can I add it as a new row?

enter image description here

How to get something like this:

enter image description here

python

1 Answers

  1. Best Answer

    The index of the dataframe (the "Location" column) is not ordered, so all the rows of the same location are not followed, which prevents you from using .loc["Localidad1":"Localidad2"], you have to prefix it .sort_index().

    On the other hand, I think you have some extra brackets around the range of cities.

    With the example dataframe you posted, I think the following does what you were looking for (I've changed it St. Petesburgto Montecarlo, since St. Petersburg was not in the extract you posted)

    resultado = (archivoConvertidoInt
 .sort_index()
 .loc["Adelaide":"Montecarlo", 'Series':'Round']
 .groupby(["Location", "Series", "Court", "Surface", "Round"])["Series",]
 .count())

    It generates a long dataframe that starts like this:

    Beginning

    and ends like this:

    Final

    Regarding adding a row with the total, in principle it should be as simple as:

    resultado.loc["Total"] = resultado.sum()

    But the problem in your case is that by having the dataframe a multi-index and adding a row to it that is not multi-index , it forces the rest of the dataframe to stop being multi-index (and convert each index into a tuple). To avoid this, let's create that last row with a multi-index as well , specifying text for all five levels of the index. So:

    resultado.loc[("Total", "", "", "", "")] = resultado.sum()

    Now the table ends like this:

    with full

    • 2