Loop Adding New Column for Each Letter of the Alphabet

user_16458721 0
Viewed 303

I have this simple looking issue but can for the love the almighty not figure out how to solve it.

I have a data frame that has a column with strings. I can apply the code below to add a new column that shows how often the letter "a" occurs in the string next to it. Using a loop, I can do this with every word in the column.

What I want to do now is do the same thing for every letter in the alphabet by integrating my loop into another loop. So the end result should be a data frame with one column for each letter that shows the occurence of that letter in the string.

Loops inside loops really confuse me and while I can picture how it should work in theory, I have a hard time getting it to work in practice. :(

Thank you so much!

df = pd.DataFrame(["fancy","nice","aweseome","wow"])
A = []
for i in range(len(df)):
    A.append(df.iloc[:, 0][i].count("a"))
df["A"] = A
df

EDIT:

Hey again friends, now I would like to apply the solution to as many string columns as there are in the df:

df = pd.DataFrame([["fancy", "cold"], ["nice","sunny"],["awesome", "warm"], ["wow", "rainy"]])

print(df)

for i in range(1,len(df.columns)):
    for letter in ascii_lowercase:
        df[letter] = df.iloc[:, i].apply(lambda x: x.lower().count(letter))
        df.append(df)
        
print(df)

My code overwrites the a-z columns from the first string column. However, I would like that it adds separate a-z columns for each string column, if that makes sense.

If this messes with the order of variables in the data frame, then it could also be a solution to store the a-z output for each column in a new data frame (either one that is appended to or one for each column). I could not really get this to work either. :/

Any help? :) THX

pandas python
5 Answers

This is how I would do it:

from string import ascii_lowercase

for letter in ascii_lowercase:
    df[letter] = df[0].apply(lambda x: x.lower().count(letter))
user_134241850

You can use the power of Pandas to test all rows, and put the output into a new column. You can use chr(x) to get all letters from a-z, and get the count of the lowercase word. For example:

df = pd.DataFrame({'word': ["fancy", "nice", "awesome", "wow"]})
df['word_lowercase'] = df['word'].str.lower()

for i in range(97, 123):
    letter = chr(i)
    df[letter] = df['word_lowercase'].str.count(letter)
user_163178920

This might be a bit beyond this particular question, but perhaps it will be useful to someone else as I see frequency chart type questions like this rather often.

We can explode the strings into rows with Series.explode and str.upper, then use pd.crosstab to get the values counts and join back to df:

import pandas as pd

df = pd.DataFrame(["fancy", "nice", "aweseome", "wow"])
s = df[0].agg(list).explode().str.upper()
df = df.join(pd.crosstab(s.index, s))

print(df)

df:

          0  A  C  E  F  I  M  N  O  S  W  Y
0     fancy  1  1  0  1  0  0  1  0  0  0  1
1      nice  0  1  1  0  1  0  1  0  0  0  0
2  aweseome  1  0  3  0  0  1  0  1  1  1  0
3       wow  0  0  0  0  0  0  0  1  0  2  0

reindex can be used after crosstab to ensure all letters are present:

import string

import pandas as pd

df = pd.DataFrame(["fancy", "nice", "aweseome", "wow"])
s = df[0].agg(list).explode().str.upper()
df = df.join(pd.crosstab(s.index, s)
             .reindex(columns=list(string.ascii_uppercase),
                      fill_value=0))

print(df.to_string())

df:

          0  A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z
0     fancy  1  0  1  0  0  1  0  0  0  0  0  0  0  1  0  0  0  0  0  0  0  0  0  0  1  0
1      nice  0  0  1  0  1  0  0  0  1  0  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0  0
2  aweseome  1  0  0  0  3  0  0  0  0  0  0  0  1  0  1  0  0  0  1  0  0  0  1  0  0  0
3       wow  0  0  0  0  0  0  0  0  0  0  0  0  0  0  1  0  0  0  0  0  0  0  2  0  0  0
user_154978880 
      import string
      import pandas as pd

      # alph is a list of all letters from a-z and A-Z 
      #(uppercase and lowercase)

      alph = list(string.ascii_letters)

      '''
       ascii_lowercase  ==> for lowercase letters only
      ascii_uppercase  ==> for uppercase letters only
      '''

      '''
       num_letters is the numbers of letters from a-z and A- 
       Z which is 52
       26 lowercase letters and 26 uppercase letters
       so you do not have to worry about it being uppercase 
       or lowercase
       '''
       num_letters = len(alph)

      # this is the dataframe containing a list of the words.
      
      df = pd.DataFrame(['fancy', 'nice', 'awesome', 'wow'])

      '''
       p is the iterator to loop through alph therefore, every 
       letter  in
       the alph is known as p
      '''
     for p in alph:
        # this variable makes every letter (p) a string
          letter = str(p)

        # this sets every letter in alph to a variable which 
           # stores an empty list
          p = []

       '''
        x is used to loop through the dataframe and use 
            the len() function to establish
       '''
           for x in range(len(df)):
                   p.append(df.iloc[:, 0][x].count(letter))
           df[letter] = p

   print(df)

Try this All explanations are embedded within the comments and docstrings.

This gives you control over lettering. Its just a few lines once u remove the comments and docstrings

You can also play around with it. I will also post another method with fewer codes

user_122617480 
     import pandas as pd
     import string

     x = list(string.ascii_uppercase)
     df = pd.DataFrame(['fancy', 'nice', 'awesome', 'wow'])


     alphabet = string.ascii_uppercase
     for letters in alphabet:
          df[letters] = df[0].apply(lambda x: x.upper().count(letters))
     print(df)

The other method I spoke of

user_122617480
Related