-1

Here the dataset:

df = pd.read_csv('https://data.lacity.org/api/views/d5tf-ez2w/rows.csv?accessType=DOWNLOAD')

The problem: I have a pandas dataframe of traffic accidents in Los Angeles. Each accident has a column of mo_codes which is a string of numerical codes (which I converted into a list of codes). Here is a screenshot:

first

I also have a dictionary of mo_codes description for each respective mo_code and loaded in the notebook.

second

Now, using the code below I can combine the numeric code with the description:

mo_code_list_final = []
for i in range(20):
  for j in df.mo_codes.iloc[i]:
    print(i, mo_code_dict[j])

So, I haven't added this as a column to Pandas yet. I wanted to ask if there is a better way to solve the problem I have which is, how best to add the textual description in pandas as a column.

Also, is there an easier way to process this with a pandas function like .assign instead of the for loop. Maybe a list comprehension to process the mo_codes into a new dataframe with the description?

Thanks in advance.

ps. if there is a technical word for this type of problem, pls let me know.

nonoumasy
  • 61
  • 1
  • 8
  • http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question – sammywemmy Mar 01 '20 at 07:10
  • https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples kindly use this a guide to present ur question in a proper format – sammywemmy Mar 01 '20 at 07:11

2 Answers2

1
import pandas
codes = {0:'Test1',1:'test 2',2:'test 3',3:'test 4'}
df1 = pandas.DataFrame([["red",[0,1,2],5],["blue",[3,1],6]],columns=[0,'codes',2])

# first explode the list into its own rows
df2 = df1['codes'].apply(pandas.Series).stack().astype(int).reset_index(level=1, drop=True).to_frame('codes').join(df1[[0,2]])

#now use map to apply the text descriptions
df2['desc'] = df2['codes'].map(codes)

print(df2)
"""
   codes     0  2    desc
0      0   red  5   Test1
0      1   red  5  test 2
0      2   red  5  test 3
1      3  blue  6  test 4
1      1  blue  6  test 2
"""
Joran Beasley
  • 110,522
  • 12
  • 160
  • 179
  • thanks Joran. That solution sounds practical. I guess I can always groupby the 'id' of each incident to group it logically.it seems that having a row for each code allows for easier analysis, ie I can do a frequency count of each specific mo_code. – nonoumasy Mar 01 '20 at 20:35
0

I figured out how to finally do this. However, I found the answer in Javascript but the same concept applies. You simply create a dictionary of mocodes and its string value.

export const mocodesDict = {
  "0100": "Suspect Impersonate",
  "0101": "Aid victim",
  "0102": "Blind",
  "0103": "Crippled",
...
}

After that, its as simple as doing this

mocodesDict[item)]

where item you want to convert.

nonoumasy
  • 61
  • 1
  • 8