Looping over columns in Pandas

user_13740495 0
Viewed 206

I am trying to divide every two columns by the last two columns in the data set. For example, I want to divide column[0] and column[2] by column[-2] and then store the result in column[0] and column[2], respectively.

Ideally, what I want is to obtain from this:

   fra1  ger1   fra2  ger2  fra pop  ger pop
0    12    14    525    52       14       14

something like this:

           fra1          ger1          fra2          ger2  
0    12/fra pop    14/ger pop   525/fra pop    52/ger pop

that is, I want to create a new Dataframe (that keeps the original column labels) by dividing values of each country by its population. Doing this manually for every column would take too much time with the real dataset, and I cannot figure out how to run a loop.

Can anybody help? Thanks a lot!

pandas indexing loops
4 Answers

If you change your original organization you can do this much more easily, but from this point probably best to just use some logic to determine the prefixes and then perform the division for each subgroup and then join the results with concat in the end.

# Prefix is everything before `' pop'`
prefixes = [x.rsplit(' ', 1)[0] for x in df.columns if x.endswith('pop')]
#['fra', 'ger']    

l = []
for pref in prefixes:
    l.append(df[[x for x in df.columns if x.startswith(pref) and not x.endswith('pop')]]
                .divide(df[f'{pref} pop'], axis=0))

res = pd.concat(l, axis=1)
#       fra1  fra2  ger1      ger2
#0  0.857143  37.5   1.0  3.714286
user_43333590

You can select the columns to fit your use case with df.columns and slicing

Setting up the dataframe

import pandas as pd
import io

t = '''
   fra1  ger1   fra2  ger2  fra pop  ger pop
0    12    14    525    52       14       14'''
df = pd.read_csv(io.StringIO(t), sep='\s\s+', engine='python')
df

Out:

   fra1  ger1  fra2  ger2  fra pop  ger pop
0    12    14   525    52       14       14

The slices [:4] [-2:] and the multiplication factor 2 for the column names to devide by have to be adjusted for your real data

df[df.columns[:4]].div(df[df.columns[-2:].tolist()*2].values)

Out:

       fra1  ger1  fra2      ger2
0  0.857143   1.0  37.5  3.714286
user_142777220

I think I also found a solution:

divisor = df.iloc[:,-2:]
for index, column in enumerate(df):
    values = df[column]
    if index < 2:
        num1 = values
        df[column] = num1/divisor.iloc[:,index]
    if 1 < index < 3:
        num2 = values
        df[column] = num2/divisor.iloc[:,index-2]
user_137404950

Here is a solution that uses pandas' multi-indexing and broadcasting. Multi-indexing puts country and metric in two separate levels of the column labels. Broadcasting lets you divide every German (or French) metric by the German (or French) population.

from io import StringIO
import pandas as pd

# add 2nd row to validate results below
t = '''
   fra1  ger1   fra2  ger2  fra pop  ger pop
0    12    14    525    52       14       14
1     2    3       4     5        6        7
'''
df = pd.read_csv(StringIO(t), sep='\s\s+', engine='python')

# create hierarchical index (i.e., multi-index)
midx = [('france', 'm1'), ('germany', 'm2'),
        ('france', 'm2'), ('germany', 'm2'),
        ('france', 'pop'), ('germany', 'pop')]
midx = pd.MultiIndex.from_tuples(midx, names=['country', 'metric'])
df.columns = midx

# create `metrics` data frame (excludes population)
metrics = df.loc[:, (slice(None), ['m1', 'm2'])]

# create population data frame (and remove one level of index)
pop = df.loc[:, (slice(None), 'pop')].droplevel(level='metric', axis=1)

result = metrics.div(pop, level='country')
print(result)

country    france   germany     france   germany
metric         m1        m2         m2        m2
0        0.857143  1.000000  37.500000  3.714286
1        0.333333  0.428571   0.666667  0.714286

More info here: https://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html

user_136085990
Related